March 26, 2002 at 4:02 am
Hi,
I am using a Execute SQL Task to call a Stored Procedure and pass it a single Input Parameter. This worked fine, the SQL reads :
sp_REPLICATE_PopulateDestination_IUD ?
the ? Parameter gets its value from a Global Parameter.
But, when I try to get the OutputParameters (there are 3 columns in the recordset returned by the SP) and try to assign them to Global Parameters it does not assign/change the values of the Global Parameters. Even if I assign it to a RowSet (ADO Recordset) the Global Parameter stays Null.
Could this be a BUG ? The values returned are all integers, same as the Global Variables. The SP does run successfully independantly of the DTS process and returns the 3 columns.
Any help welcomed,
Dirk
March 26, 2002 at 7:05 am
Can you provide a sample of your data and the DDL of the tables involved. Also give me the steps to recreate this problem as I have no issues.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 26, 2002 at 7:44 am
Hi,
I did find a suggested solution at http://www.sqldts.com/main.asp?nav=1,6,234,0
I followed it to a tee, but still the SP's return values does not override my Packages' Global Variables.
The SP that I use builds a few dynamic SQL statements and then
EXEC sp_executesql @DynamicSQL
them. Do you want the source code of my SP ?
Maybe you can try and simulate the above sample and see if you can get it working.
Another problem is that when I manage to call a SP that returns OUTPUT parameters and map them to Global Variables (via an Exec SQL Task) I cannot use the ? for Input parameters in conjunction with it.
I am thinking of doing the whole thing in a ActiveX script rather than using the Input and Output parameters of a Exec SQL Task functionality.
Any comment welcomed.
Thanks,
Dirk
March 26, 2002 at 8:52 am
ActiveX seems like a slution, but yes I would be interested in your code to see what is happening. I feel the dynamic SQL is blowing you out of the water with the data as it will exist outside the current scope of execution. Let me see then I can offer what I think will work.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 26, 2002 at 9:42 am
Hi,
Thanks for the feedback. Here is the SP's code :
/*------------------------------------------------------------------------------------------
' Name: sp_REPLICATE_PopulateDestination_IUD
'
' Purpose: Build a dynamic SQL Strings to syncronise data from CASReplica (Source) to
' IMSLive (Destination) as per a certain TransferID on the TransferTable
'
' Returns: Nothing
'
' Usage: Used by IMS Replication DTS Routines
'
' Change history:
'
' Date Who Change
' ------------------------------------------------------------------------------------------
' 15/03/2002 DV Initial version.
'
------------------------------------------------------------------------------------------*/
CREATE PROCEDURE sp_REPLICATE_PopulateDestination_IUD (@RecIns integer OUTPUT,@RecUpd integer OUTPUT,@RecDel integer OUTPUT, @TransferID as integer)
AS
--------------------------------------------------------------------
DECLARE @DynamicSQL nvarchar(4000)
DECLARE @SourceDB varchar(50)
DECLARE @SourceTable varchar(50)
DECLARE @SourceField varchar(50)
DECLARE @DestDB varchar(50)
DECLARE @DestTable varchar(50)
DECLARE @DestField varchar(50)
DECLARE @DataConvert bit
DECLARE @udfName varchar(50)
DECLARE @UpdateSQL varchar(4000)
DECLARE @InsertSQL varchar(4000)
DECLARE @DestSQL varchar(2000)
DECLARE @SourceSQL varchar(2000)
--DECLARE @ReturnString nvarchar(250)
---------------------------------------------------------------------
BEGIN
--Get DB and Table Detail
SELECT @SourceDB = SourceDB, @SourceTable = SourceTable, @DestDB = DestDB, @DestTable = DestTable
FROM TransferTable
WHERE TransferID = @TransferID
--SET @ReturnString = 'Table : ' + @DestDB + '.[dbo].' + @DestTable
-- Delete records in IMSLive (Destination) that does not exist in CASStaging (Source)
SELECT @DynamicSQL = 'DELETE ' + @DestDB + '.[dbo].' + @DestTable +
' FROM ' + @DestDB + '.[dbo].' + @DestTable +
' Dest LEFT OUTER JOIN ' + @SourceDB + '.[dbo].' + @SourceTable +
' Source ON Dest.UDFGUID = Source.UDFGUID WHERE (Source.UDFGUID IS NULL)'
--PRINT@DynamicSQL
EXEC sp_executesql @DynamicSQL;
--SET @ReturnString = @ReturnString + ', Deleted : ' + LTrim(Str(@@RowCount)) + ' record(s)'
SET @RecDel = @@RowCount
--Update records in IMSLive (Destination) that that has a newer UpdateDate in CASStaging (Source)
DECLARE MapCURSOR CURSOR LOCAL FOR
SELECT SourceField, DestField, DataConvert, udfName
FROM TransferField
WHERE TransferID = @TransferID
OPEN MapCURSOR
FETCH NEXT
FROM MapCURSOR
INTO @SourceField, @DestField, @DataConvert, @udfName
SELECT @UpdateSQL = 'UPDATE ' + @DestDB + '.[dbo].' + @DestTable + ' SET '
SELECT @SourceSQL = ''
SELECT @DestSQL = ''
WHILE @@FETCH_STATUS = 0
BEGIN
IF @DataConvert = 1
BEGIN
SELECT @UpdateSQL = @UpdateSQL + '[' + @DestField + ']' + '=[dbo].' + @udfName + '(SOURCE.' + '[' + @SourceField + ']),'
SELECT @SourceSQL = @SourceSQL + '[dbo].' + @udfName + '(SOURCE.[' + @SourceField + ']),'
END
ELSE
BEGIN
SELECT @UpdateSQL = @UpdateSQL + '[' + @DestField + ']' + '=SOURCE.' + '[' + @SourceField + '],'
SELECT @SourceSQL = @SourceSQL + 'SOURCE.[' + @SourceField + '],'
END
SELECT @DestSQL = @DestSQL + '[' + @DestField + '],'
FETCH NEXT
FROM MapCURSOR
INTO @SourceField, @DestField, @DataConvert, @udfName
END
CLOSE MapCURSOR
DEALLOCATE MapCURSOR
--Delete the last comma
SELECT @UpdateSQL = SubString(@UpdateSQL, 1, DataLength(@UpdateSQL) - 1)
SELECT @SourceSQL = SubString(@SourceSQL, 1, DataLength(@SourceSQL) - 1)
SELECT @DestSQL = SubString(@DestSQL, 1, DataLength(@DestSQL) - 1)
SELECT @UpdateSQL = @UpdateSQL + ' FROM ' + @SourceDB + '.[dbo].' + @SourceTable + ' SOURCE,' + @DestDB + '.[dbo].' + @DestTable + ' DEST'
SELECT @UpdateSQL = @UpdateSQL + ' WHERE SOURCE.UDFGUID = DEST.UDFGUID AND SOURCE.UPDATEDATE > DEST.UPDATEDATE'
--PRINT @UpdateSQL
SELECT @DynamicSQL = @UpdateSQL
EXEC sp_executeSQL @DynamicSQL
--SET @ReturnString = @ReturnString + ', Updated : ' + LTrim(Str(@@RowCount)) + ' record(s)'
SET @RecUpd = @@RowCount
--Insert new records that does not exits in the Dest
SELECT @InsertSQL = 'INSERT INTO ' + @DestDB + '.[dbo].' + @DestTable + '(' + @DestSQL + ') ' +
'SELECT ' + @SourceSQL + ' FROM ' + @SourceDB + '.[dbo].' + @SourceTable + ' Source ' +
'LEFT OUTER JOIN ' + @DestDB + '.[dbo].' + @DestTable + ' Dest ' +
'ON Source.UDFGUID = Dest.UDFGUID WHERE (Dest.UDFGUID IS NULL)'
--PRINT @InsertSQL
SELECT @DynamicSQL = @InsertSQL
EXEC sp_executeSQL @DynamicSQL
--SET @ReturnString = @ReturnString + ', Inserted : ' + LTrim(Str(@@RowCount)) + ' record(s)'
SET @RecIns = @@RowCount
--SELECT @RecIns as RecIns, @RecUpd as RecUpd, @RecDel as RecDel
RETURN 1
END
GO
I will mail you the DTS Packages' Structured Storage File.
Thanks again fir the help, let me know what else your require.
Regards,
Dirk
March 27, 2002 at 12:42 am
James,
This is getting to a level of confusion. Why can't you build the package from the file I mailed you ? All you have to do is build a simple sample DTS Package that calls a SP with OUTPUT parameters and try to override the DTS Package's local Global Variables with the values returned by the SP.
Do you want me to mail you my DB script ?
Let me know, in the meanwhile I am following Plan B, building a ActiveX script to handle the whole process.
Dirk
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply