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
