RE: Initializing Global Variable trought output parametr of ExecuteSQL

#1

The problem only occurs if stored procedure has "print" statements. So, it
looks like DTS task captures this "print" statements as the output instead of
my real
select @new_run_id AS RUN_ID_VAR.

I tried SET NOCOUNT ON. It doesn't help.

Does anybody have any ideas on how to handle this???

"Alex Prints" wrote:

Quote:

I use DTS on SQL Server 2000 SP3.

I'm trying to initialize global variable through the output parameter of
Execute SQL Task and I'm having problem.

If I assign a constant value to the local task variable, it can be captured
in global variable through output parameter of Execute SQL Task. For example:

DECLARE @new_run_id INT
set @new_run_id = 12345
select @new_run_id AS RUN_ID_VAR

But if I use stored procedure which returns a value, it doesn't go through
output parameter of Execute SQL Task to my global variable.

DECLARE @new_run_id INT
--set @new_run_id = 12345
EXECUTE SPMN_CREATE_RUN_ID @new_run_id output
select @new_run_id AS RUN_ID_VAR

The procedure itself works, because I can run the same 3 lines of code in
SQL Analyzer and @new_run_id variable is getting the result.

BTW, I check my global variable through the following ActiveX task as:

Function Main()
msgbox DTSGlobalVariables("RUN_ID").value
Main = DTSTaskExecResult_Success
End Function

Does anybody know what could be the issue with using stored procedure
instead of constant value???

Thank you,
Alex.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

RE: Initializing Global Variable trought output parametr of ExecuteSQL

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

RE: Initializing Global Variable trought output parametr of ExecuteSQL - 11-09-2005 , 06:37 PM