Re: Output Parameter To Global Variable (DTS)

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

  • Next message: Anil: "DTS ... On Job scheduler...." 
    Date: Tue, 12 Oct 2004 17:30:46 +0100

    The concept works fine for me, the problem is normally around settting up
    the parameter mapping as you get syntax errors when you click teh parameters
    button. If you use some dummy code to help map the parameters and then
    switch in the real code via disco edit this should work fine.

    e.g.

    Map your parameters with this code-

    SELECT id AS OutputTest1, id AS OutputTest2 FROM sysobjects where id = ? and
    id = ?

    My procedure looks like this-

    CREATE PROCEDURE dbo.spInputAndOutputTest
        @InputTest1 int,
        @InputTest2 int,
        @OutputTest1 int OUTPUT,
        @OutputTest2 int OUTPUT
    AS
      SET @OutputTest1 = @InputTest1 * 23
      SET @OutputTest2 = @InputTest2 * 45
    GO

    So I now go into Disco edit and put in the real t-sql line, or even do this
    through the task UI, just don't click parameters again-

    DECLARE @OutputTest1 int, @OutputTest2 int
    EXEC dbo.spInputAndOutputTest ?, ?, @OutputTest1 OUTPUT, @OutputTest2 OUTPUT
    SELECT @OutputTest1 AS OutputTest1, @OutputTest2 AS OutputTest2

    A related article-

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

    -- 
Darren Green
http://www.sqldts.com
"Paul Timmerman" <PaulTimmerman@discussions.microsoft.com> wrote in message
news:03E2C25D-25EF-45B5-965E-5F183E7E78A3@microsoft.com...
> I have a DTS question. I am executing a stored procedure with 2 input
> parameters and 2 output parameters.
>
> The input params are working perfectly both when running the SP in QA and
> when executing the DTS package.
>
> The output params are causing me a headache. I can confirm that the
> stored procedure outputs the parameters just fine (works when I test in
> QA). When I execute the DTS task, only the first output param from the
> stored procedure is populated into the global variable in the DTS package.
> It is very puzzling to me as one of the output params works fine, so the
> other should too, right? I can confirm that they are mapped properly.
>
> The SQL task looks like (object names masked):
>
> DECLARE @Output1 VARCHAR(128), @Output2 VARCHAR(128)
> EXEC admin..storedprocedure ?,?, @Output1 OUTPUT, @Output2 OUTPUT
> SELECT @Output1 AS Output1, @Output2 AS Output2
>
> Both Output1 and Output2 show up on my screen for output parameters. I
> simply map them to global variables I have created. Again, only the first
> one works.
>
> Please tell me I am missing something simple.....
>
> TIA
>
> Paul
>
  • Next message: Anil: "DTS ... On Job scheduler...."