Hi,
I have a stored procedure which returns a message which I use in an Email. I have tested the procedure within SQL server and it works fine. But when I call it from within a script, I am not getting the message back.
In SQL Server:
declare @m varchar(max)
exec spJobSummary 69, @m output
select @m
output:
-
Job [SAP_ODS_LOAD] completed with a status of "FINISHED" in 86 minutes.
In DS (2 line test script shown):
ADMIN.DBO.SPJOBSUMMARY(69,$message,$rtCode,$rtVal);
smtp_to('leigh[at]test.com.au', 'Results of ' || job_name(), $rtCode || '//' || $rtVal || '~~' || $message || '//', 0,0);
(nb: email edited to comploy with forum rules).
Mail from: </SMTP_TEST> sent at: Fri Jan 13 10:42:30 2012
Message:
=======
ACTA_SP_OK//~~//
So the proc is running ok, but the message
comes back blank.
I can't find an example of using stored procs with output parameters and I suspect the problem is in how I am passing it in my script. If anyone has any suggestions, I'll be very grateful !
Thanks.