I was given advice earlier this week regarding how to
assign a returning value from a Stored Procedure into a
Global Varaible from a Execute SQL Task. I must be
missing the point as i still can not get it to work. The
only parameter it is allowing is an input Parameter.

Stored Proc

CREATE PROCEDURE UpdateHeaders
@RtnVal Integer Output,
@Licence Varchar(6),
@ModifiedDate Varchar(10),
@Serial Varchar(3),
@Volume Varchar(6),
@Status Varchar(1),
@Type Varchar(1),
@Action Varchar(1)
AS
.......

ExecuteSQlTask :

EXEC
UpdateHeaders '217353','21/01/2004','009','000000','Unsubm
itted','Live','A', ? OUTPUT
or
EXEC ? =
UpdateHeaders '217353','21/01/2004','009','000000','Unsubm
itted','Live','A'

neither of which produce an output parameter. can any one
help show me where ive gone wrong, or point me in the
directon on some info on this

Thanks

? expects you to be passing IN values not retrieving

so you may want to redo this to

Declare @out int
EXEC UpdateHeaders
'217353','21/01/2004','009','000000','Unsubmitted','Live','A', @out OUTPUT
SELECT @out as OutputValue

You should now be able to map this to an output parameter.



--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Peter Newman" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Alle

Thanks again. I can see where i was going wrong.. and surprise surprise it work

again a big thank you

Allen
currently im using an Active x task to build the EXEC Updateheaders sql for the executeSQl task.. I was wondering that if i can use global varables values then i could do away with one of the task

your code example ( which works )
Declare @out in
EXEC UpdateHeaders '217353','21/01/2004','009','000000','Unsubmitted','Live','A', @out OUTPUT
SELECT @out as OutputValu

Now if '217353' is stored in a Global Varaible sLicence , could i use the imput parameter of the executesqltask, and if so how to declare it in the query?

So you want to use IN and OUTPUT parameters.

Have a read of this article we wrote for dealing with this

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


We basically fool designer at design time because we know the runtime parser
is more robust



--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Peter Newman" <anonymous (AT) discussions (DOT) microsoft.com> wrote

values then i could do away with one of the tasks
query?