Re: DTSSql Task and Stored Procedure

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Darren Green (darren.green_at_reply-to-newsgroup-sqldts.com)
Date: 05/27/04 

Date: Thu, 27 May 2004 19:01:30 +0100

Have a read of the "Using SET NOCOUNT ON" section. Try it, e.g.

> CREATE PROCEDURE [dbo].[test_procedure]
> @in_message,
> @out_Param integer OUTPUT
> AS
SET NOCOUNT ON
> declare @temp_var as int 

-- 
Darren Green
http://www.sqldts.com
"guru" <anonymous@discussions.microsoft.com> wrote in message
news:173739FB-2CA1-4594-BAC4-AB318DF0F7E8@microsoft.com...
> I am trying to used DTSSql Task to execute a stored procedure with input
and out parameters.  I followed the steps
> mentioned in http://www.sqldts.com/?234 ,  it works.
> I changed the stored procedure to include a simple insert statement and
return the identity value.  The output parameter does not return the correct
identity/expected value
>
>
> stored procedure code :
>
> CREATE PROCEDURE [dbo].[test_procedure]
> @in_message,
> @out_Param integer OUTPUT
> AS
> declare @temp_var as int
>
> insert into sp_test_table(msg) values (@in_message)
> select @temp_var =  ident_current ('sp_test_table')
> set @out_Param = @temp_var
> GO
>
> If I execute the following sql code block in Query Analyzer it works.
>
> DECLARE @OutputTest int
> EXEC dbo.test_procedure 'test',  @OutputTest OUTPUT
> SELECT @OutputTest AS OutputTest
>
> If i execute the same using the DTSSql task, the global variable mapped to
the output parameter does not return correct value,
> it just contains the value it was initialized with
>
> Any pointers/help on this front will be helpful.
>
> Thanks in advance
>

Relevant Pages