My database is in SQL Server 2005. I routinely upload data into a DB2 database using a linked server. Problem is that my query (see below) only uploads one record at a time. I have my stored procedure set up to loop through the records until all have been uploaded.

I'd like to change this to a single bulk insert query. I've tried it and get an error that reads, "Server '[Server Name]' is not configured for RPC."

Our DB2 DBAs are telling me that this is a Microsoft issue and that there isn't anything they can do at their end to correct it.

Any suggestions? Here is the SQL:

-- Insert data into SSD_MBL_DEV table and then upload to DB2

DECLARE @mainLoopControl int,
@NextDevice int,
@WS_ID varchar(11)

SET @mainLoopControl = 1

WHILE @mainLoopControl = 1

BEGIN

SELECT @NextDevice = MIN(CAST(RIGHT(WS_ID,5) AS int))
FROM MCC_Import
WHERE HH4 = 1
AND (WS_ID LIKE 'PEPCUH%' AND LEN(WS_ID) = 11)
AND Environment = 'PROD'
AND WS_ID NOT IN
(SELECT DEV_NM FROM
OPENQUERY(SSDDEV3,'SELECT DEV_NM from SSD.MBL_DEV'))

SELECT @WS_ID = WS_ID
FROM MCC_Import
WHERE CAST(RIGHT(WS_ID,5) AS int) = @NextDevice

INSERT INTO SSD_MBL_DEV (DEV_NM, MDL_NM, DPLOY_STAT_TS, LAST_UPDT_TS, LAST_UPDT_USEID)
SELECT WS_ID, Model_Type, GETDATE(), GETDATE(), Tech_Name
FROM MCC_Import
WHERE WS_ID = @WS_ID

DECLARE
@DEV_NM varchar(11),
@MFG_NM varchar(25),
@MDL_NM varchar(25),
@OS_NM varchar(32),
@OS_VRSN varchar(24),
@SER_NBR varchar(11),
@DEV_TYPE_CODE char(3),
@DPLOY_STAT_CODE smallint,
@DPLOY_STAT_TS smalldatetime,
@CTRY_CODE char(3),
@CO_CODE char(3),
@RT_NBR int,
@STRUC_NODE_SYS_NBR char(6),
@LAST_UPDT_TS smalldatetime,
@LAST_UPDT_USEID varchar(12)

SELECT @DEV_NM = (SELECT DEV_NM FROM SSD_MBL_DEV),
@MFG_NM = (SELECT MFG_NM FROM SSD_MBL_DEV),
@MDL_NM = (SELECT MDL_NM FROM SSD_MBL_DEV),
@OS_NM = (SELECT OS_NM FROM SSD_MBL_DEV),
@OS_VRSN = (SELECT OS_VRSN FROM SSD_MBL_DEV),
@SER_NBR = (SELECT SER_NBR FROM SSD_MBL_DEV),
@DEV_TYPE_CODE = (SELECT DEV_TYPE_CODE FROM SSD_MBL_DEV),
@DPLOY_STAT_CODE = (SELECT DPLOY_STAT_CODE FROM SSD_MBL_DEV),
@DPLOY_STAT_TS = (SELECT DPLOY_STAT_TS FROM SSD_MBL_DEV),
@CTRY_CODE = (SELECT CTRY_CODE FROM SSD_MBL_DEV),
@CO_CODE = (SELECT CO_CODE FROM SSD_MBL_DEV),
@RT_NBR = (SELECT RT_NBR FROM SSD_MBL_DEV),
@STRUC_NODE_SYS_NBR = (SELECT STRUC_NODE_SYS_NBR FROM SSD_MBL_DEV),
@LAST_UPDT_TS = (SELECT LAST_UPDT_TS FROM SSD_MBL_DEV),
@LAST_UPDT_USEID = (SELECT LAST_UPDT_USEID FROM SSD_MBL_DEV)

EXEC ('INSERT INTO SSD.MBL_DEV VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
@DEV_NM,
@MFG_NM,
@MDL_NM,
@OS_NM,
@OS_VRSN,
@SER_NBR,
@DEV_TYPE_CODE,
@DPLOY_STAT_CODE,
@DPLOY_STAT_TS,
@CTRY_CODE,
@CO_CODE,
@RT_NBR,
@STRUC_NODE_SYS_NBR,
@LAST_UPDT_TS,
@LAST_UPDT_USEID) AT SSDDEV3

-- Reset loop variable

SELECT @NextDevice = NULL
SELECT @NextDevice = MIN(CAST(RIGHT(WS_ID,5) AS int))
FROM MCC_Import
WHERE HH4 = 1
AND (WS_ID LIKE 'PEPCUH%' AND LEN(WS_ID) = 11)
AND WS_ID NOT IN
(SELECT DEV_NM FROM
OPENQUERY(SSDDEV3,'SELECT DEV_NM from SSD.MBL_DEV'))
AND CAST(RIGHT(WS_ID,5) AS int) > CAST(RIGHT(@WS_ID,5) AS int)

IF ISNULL(@NextDevice,0) = 0
BEGIN

-- Done with all devices

BREAK

END

-- Set up next device

TRUNCATE TABLE SSD_MBL_DEV
SELECT @WS_ID = WS_ID
FROM MCC_Import
WHERE CAST(RIGHT(WS_ID,5) AS int) = @NextDevice

END

-- Delete data from SSD_MBL_DEV table

TRUNCATE TABLE SSD_MBL_DEV