Hi all,

I want to call stored procedure from my DTS, after execute the SQL , i
want to pass the global variable to ActiveX Script. However, the global
variable in ActiveX Script only hold the default value

Here is my stored procedure:

CREATE PROCEDURE sp_test123
@TotalCountSum int OUTPUT

AS

SET NOCOUNT ON
-- Create two integer values
DECLARE @tableOneCount int, @tableTwoCount int

-- Get the number of rows from the first table

SELECT @tableOneCount = (SELECT COUNT(*) FROM FundCode)

SELECT @tableTwoCount = (SELECT COUNT(*) FROM FundCode_1)

SELECT @TotalCountSum=@tableOneCount - @tableTwoCount


GO


Then i call the stored procedure from "Execute SQL Task" in DTS

DECLARE @TotalCountSum INT
EXEC sp_test123 @TotalCountSum OUTPUT
SET NOCOUNT ON
SELECT @TotalCountSum AS 'CountDiff'


I had set a global variable "TotalCountSum" , so i map the CountDiff to
TotalCountSum.
After that, i write an ActiveX Script:


Function Main()

if DTSGlobalVariables("TotalCountSum").Value <> 0 then
Main = DTSTaskExecResult_Success
else
Main = DTSTaskExecResult_Failure
end if

End Function


If the TotalCountSum not equal to 0, then i will send an email
notification out. However, the TotalCountSum keep return only the
default value that i set to it. Can anyone tell me how to solve it?

Thanks

Hello too.changmian (AT) gmail (DOT) com,

You could rewrite the proc to be

CREATE PROCEDURE sp_test123
@TotalCountSum int OUTPUT

AS

SET NOCOUNT ON

SET @TotalCountSum=(SELECT COUNT(*) FROM FundCode) + (SELECT COUNT(*) FROM
FundCode_1)


GO


Have you seen this as well?

http://www.sqldts.com/default.aspx?234



Regards

Allan Mitchell
Konesans Ltd
T +44 7966 476 572
F +44 2071 008 479
http://www.konesans.com