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

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 (AT) discussions (DOT) microsoft.com> wrote

I thought I had the parameters mapped properly. I have read the mentioned
article and used the dummy code.

When I run

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

in QA, the ouput variables return as NULL. I think that is normal.

I can get around this whole problem by simply SELECTING the values at the
end of the stored procedure thereby having the SP return a result set. This
of course nullifies the need for the OUTPUT parameters. I would however like
to get them to work. Whatever I do I seem to get NULL valeus for the output
parameters.

Paul

"Darren Green" wrote:

In message <9A2E6860-3F67-4FA3-A74D-4F7B1FEC0089 (AT) microsoft (DOT) com>, Paul
Timmerman <PaulTimmerman (AT) discussions (DOT) microsoft.com> writes
If you run this in QA I would expect an error because you still have the
? place holders in there. If you supply real values instead then you
should get decent output values as well, as per the simple calculation
in the procedure.


Sorry, I am at a loss, as the sample code I posted previously works fine
for me. Only thing I can think of is that since the output values are
derived from the input, make sure the input is not null for the sample
proc, or you could even hard code the output values, although this no
longer tests that the input is received correctly.


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

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