July 23, 2012 at 5:07 am
Hi, I have created a SP which will look for certain values to exist in some user tables, and based on that use RAISERROR to write to the system event viewer (for the purpose of being picked up by an alerting/monitoring team). It all works well from the perspective of me running it from Management Studio - the syntax is good and it writes a variety of messages as desired, and gives this sort of message to screen:
"Msg 50000, Level 15, State 1, Procedure TEST_SP, Line 96
MESSAGE TEST - This works !!!"
The only problem I have now is that the software I am forced to utilise to run this SP is a Perl based scheduler and apparently required a return value of some sort passed back to it. This gives me 2 questions:
1) If I was to return say a fixed result of 'Yes' or '1' to the Perl scheduler how would I incorporate that into the SP? I have looked at adding a RETURN command but am getting this sort of result "A RETURN statement with a return value cannot be used in this context."
2) I obviously don't want the RAISERROR message itself passed back to the scheduler - written to the log/EV only. Is there a way to make it 'non-verbose'? Will it actually get returned anyway, or can I forget about it?
I hope that is clear?!
July 23, 2012 at 7:32 am
is there any reason that you need to use the pearl script to scheule over a SQL job?
also look up output
http://msdn.microsoft.com/en-us/library/ms177564.aspx
***The first step is always the hardest *******
July 24, 2012 at 4:16 am
Yeah, Autosys software and anything scheduled 'should' be run that way :rolleyes:
I had glanced at OUTPUT previously but discounted it as it didn't seem appropriate, but have tried to incorporate it. Right now my stored procedure is a bit like this:
create procedure sp_xxxx
if exists (select item from tbl_check where date > getdate()-1)
begin RAISERROR ('This works - here is a nice message!',12,1) with log end
else RAISERROR ('This is a failure!!!'15,1) with log
end
--now with this section added to return an output*********
--------Creating the table which will store permanent table
CREATE TABLE TestTable (ID INT)
----Creating temp table to store ovalues of OUTPUT clause
DECLARE @TmpTable TABLE (ID INT)
----Insert values in real table as well use OUTPUT clause to insert
----values in the temp table.
INSERT TestTable (ID)
OUTPUT Inserted.ID INTO @TmpTable
VALUES (1)
----Check the values in the temp table and real table
----The values in both the tables will be same
--SELECT ID FROM @TmpTable
SELECT ID FROM TestTable
----Clean up time
DROP TABLE TestTable
GO
OK, so if I run 'Exec sp_xxxx' from within SQL MgmtStudio I get what I want in terms of the entry in event viewer and also the Messages tab of the query displays "Msg 50000, Level 12, State 1, Procedure sp_xxxx, Line 96 'This works - here is a nice message!". Since adding the OUTPUT code to the bottom, I am also getting a nice Results tab which shows ID 1.
The batch team have told me this "Perl does need something returned though to tell it whether it’s ran successfully or not". My issue/question is when I run the procedure from a SQL job with the results to a csv file, all i am getting is the message. I ONLY want to output the output value of '1'. I would like this to be passed back to the Perl scheduler to keep that happy that the job has run, and let the SP take care of its only thing.
Any ideas how to run my SP to do what it needs to (ie write to the EV) but not return those messages and just return a pre-determined value upon completion??
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply