Still Having probs with EXECUTESQL & Stored Procedures

#1

After reading the helpfull comments on here and the suggested articiles, i though i had this problem licked

My ExecuteQSQlTask looks like thi

DECLARE @SPReturn Int
EXEC @SPReturn = Boss_UpdateHeaders '217680', '21/01/2004', '924', '000000', 'Unsubmitted', 'Test', 'A'
SELECT @SPReturn AS SpOutpu

I have assigned the output paramenter to a global variable, ( and Integer ). Whe i run the task, the global varaible does not get updated
If i copy the code to a tSQl window it returns @spreturn with a value. If i use the build query ( executesqltask) it doesent return anything.

I can not see where i am going wrong

#2

In message <D248DC74-316B-4912-80E3-50DFC91A39D3 (AT) microsoft (DOT) com>, Peter
Newman <anonymous (AT) discussions (DOT) microsoft.com> writes

Quote:

After reading the helpfull comments on here and the suggested
articiles, i though i had this problem licked.

My ExecuteQSQlTask looks like this

DECLARE @SPReturn Int
EXEC @SPReturn = Boss_UpdateHeaders '217680', '21/01/2004', '924',
'000000', 'Unsubmitted', 'Test', 'A'
SELECT @SPReturn AS SpOutput

I have assigned the output paramenter to a global variable, ( and
Integer ). Whe i run the task, the global varaible does not get updated,
If i copy the code to a tSQl window it returns @spreturn with a value.
If i use the build query ( executesqltask) it doesent return anything..

I can not see where i am going wrong

If your stored procedure performs one or more DML operations that cause
informational resultsets to be generated, then these will confuse the
task. An informational resultset will be familiar to anyone that uses
Query Analyser, as you will have seen them as messages in results, for
example (1 row(s) affected). These messages are indistinguishable from
the SELECT resultset you are really interested in, and since the task
reads the first resultset it encounters, an informational resultset can
mask your real data. To suppress this behaviour add the SET NOCOUNT ON
statement to the top or your stored procedure or script.

Global Variables and Stored Procedure Parameters
(http://www.sqldts.com/default.aspx?234)

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

#3

In message <567953DF-8F4F-4991-A07D-81DC9C861FA5 (AT) microsoft (DOT) com>, Ména
<anonymous (AT) discussions (DOT) microsoft.com> writes

Quote:

Hi,
I have the same problem with my stored procedure
CREATE PROCEDURE TESTSP
@RTNVAL Integer OUTPUT
AS
SET NOCOUNT ON
Select @RTNVAL = Count(*) from employees
GO

using in the Execute sql task
declare @NbrLigne varchar(3)
execute testsp @NbrLigne
select @NbrLigne
and making the output parameter in row value

but it doesnot work
please help
thx
../Ména

You parameters clash which isn't good practice, but not fatal.
For a parameter to exhibit the output behaviour it must be declared as
such in the procedure and also flagged in the call-

execute testsp @NbrLigne OUT

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

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

Still Having probs with EXECUTESQL & Stored Procedures

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

Still Having probs with EXECUTESQL & Stored Procedures - 02-17-2004 , 09:16 AM

Re: Still Having probs with EXECUTESQL & Stored Procedures - 02-17-2004 , 11:27 AM

Re: Still Having probs with EXECUTESQL & Stored Procedures - 02-17-2004 , 12:38 PM