Execute SQL Task not populating Global Variable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Execute SQL Task not populating Global Variable

Author

Topic

Yukon2005
Starting Member

8 Posts

Posted - 2006-03-07 : 05:11:24

Hi All,

I have a "Execute SQL Task" that executes a stored procedure (usp_one) that executes another stored procedure (usp_two). The result should be populated in a global variable called gvHasError. Yet it doesn't.

usp_one:

CREATE PROC usp_one
@chvInput varchar(100)
AS
declare @rc int
exec @rc=usp_two @chvInput

select @rc as [HasError]
return @rc
GO

CREATE PROC usp_two
@chvInput varchar(100)
AS
declare @intCount int

select @intCount = count(*) from mytable

if @intCount between 0 and 10 begin
return 0
end

return 1
go

When I run usp_one from Query Analyzer I get the correct "HasError" value.

I have created a global variable called gvHasError, type Integer and gvInput, type string.

When I execute the stored procedure from within the "Execute SQL Task" as EXECUTE usp_one ?

Input Variable = gvInput and Output Parameter HasError (RowValue) = gvHasError. I gvHasError = 0 in the beginning. It should change to 1 when I execute the procedure but it doesn't.

Does anybody know what could be causing this?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources