October 7, 2002 at 8:18 pm
I'm trying to execute a stored procecure from a ExecuteSQL task and pass it a couple of output params to set some global variables. The following code works fine:
DECLARE @ReturnValue int, @Msg Varchar(255)
EXEC StoredProc @ReturnValue OUT,@Msg OUT
SELECT @ReturnValue AS ReturnValue, @Msg AS Msg
This will let me set the output params to the global variables.
If the stored proc has nothing more than setting those values, it works. If I add any code to the procedure, it doesn't work anymore. The global params just don't get set anymore.
Odd problem...as though the variables fall out of scope within the stored proc or something.
Anyone have any ideas? I know I can do with ADO but I'm trying to avoid the overhead of creating another connection. Greatly appreciated.
Thanks.
October 8, 2002 at 3:35 am
Can you give us a bit of the procedure so we can understand what you may be doing wrong or happening that is due to design. Both when works and when doesn't work code will help.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 8, 2002 at 5:36 am
Thanks for the response.
The goal is to be able to pass back error conditions and messages back to the DTS package for error handling.
The code to call the stored proc is:
DECLARE @ErrorNumber int, @ErrorMsg Varchar(255)
EXEC TW_SPWrapper @ErrorNumber OUTPUT,@ErrorMsg OUTPUT
SELECT @ErrorNumber AS ReturnValue, @ErrorMsg AS Msg
The code for the stored proc is as follows:
Alter Procedure TW_SPWrapper
(
@Err_Number int = 0 OUTPUT,
@Err_Msg Varchar(255) = NULL OUTPUT
)
As
declare @res int
set @Err_Number = 0
set @Err_Msg = 'Success'
Exec @res = TW_Quote_Revisions @Err_Number OUTPUT, @Err_Msg OUTPUT
return (@res)
If you simply comment out the calling of the other stored procedure, it works. Anything else, such as an update stmt or query, will also cause the proc not to send back the values into the global variables.
Thanks for any help
October 15, 2002 at 7:24 am
As it turns out, I resolved my problem using the 'set nocount on' at the beginning of the stored proc.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply