May 31, 2011 at 1:10 pm
I created a stored procedure.
CREATE PROCEDURE PR_SWL_TEST
@X integer OUTPUT
AS
Print @X
Select @X = 10 where 1=1
Print @X
Return @X
When I run it like this
DECLARE @rc int
DECLARE @X int
-- TODO: Set parameter values here.
Set @X = 2
EXECUTE @rc = [excelleRx_DBA].[dbo].[PR_SWL_TEST]
@X = 2 OUTPUT
Print @X
GO
I get this error
Msg 179, Level 15, State 1, Line 9
Cannot use the OUTPUT option when passing a constant to a stored procedure.
When I run it like this, it works
DECLARE @rc int
DECLARE @X int
-- TODO: Set parameter values here.
Set @X = 2
EXECUTE @rc = [excelleRx_DBA].[dbo].[PR_SWL_TEST]
@X = @X OUTPUT
Print @X
GO
2
10
10
Now, this came up because an SSIS package that uses a store procedure is now failing with this error "Cannot use the OUTPUT option when passing a constant to a stored procedure." We think it is passing constants like the first example that fails. The SP above was run from SSMS and not in an SSIS package.
Does anyone know why this behavior is happening? Is there a DB Option or an ansi setting that may have changed recently that should be changed back (although we don't know of any changes)? The production stored procedure has been work fine for months and failed with this error today.
I am running Microsoft SQL Server 2008 (SP2) standard - 10.0.4000.0 on Windows Server 2003 SP 2 Standard.
There were no recent changes to the operating environment.
thanks
Steve
May 31, 2011 at 8:03 pm
To my knowledge the SQL engine has always worked like that. I'm not sure if there have been any changes on the SSIS side that could result in a variable being replaced with the constant that it currently has. The best way to handle this may be to add a second variable that's just an output parameter. Have @x just be input so it can take a constant and have @xout just be an output so you never try to pass a constant to it.
June 1, 2011 at 12:29 am
June 1, 2011 at 6:29 am
Yes, with further testing I realized that SQL Server 2000 through SQL Server 2008 R2 all did the samething. This changes the question slightly.
How does SSIS pass parameters to a stored procedure in an ole Command task?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply