-
Bulk Insert from SQL Server 2005 Using Linked Server
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|