August 20, 2009 at 4:14 am
I'm not particularly good with SQL as you will probably find out but I would have thought this would work.
I have a process involving transactions that I want to rollback on error, but I also want to trigger a SS agent job at the same time - the agent job then fires off an email to tell a bunch of people that the process hasn't worked and has rolled back.
At the moment I'm using a GOTO tag if an error in the transaction occurs, but I have tried other ways and each time the EXEC line is ignored:
IF @@Error <> 0 GOTO On_Error
On_Error:
ROLLBACK TRANSACTION
EXEC msdb.dbo.sp_start_job N'SFDC_On_Error'
If I run the EXEC line by itself the job starts and the email is triggered so I know the syntax is okay.
Am I trying to go about this the wrong way? Any suggestions welcome.
Thanks in advance,
Paul
Keep the rubber side down and the shiny side up.
August 20, 2009 at 8:22 am
Are there any errors on the job?
Do you get any error messages?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 20, 2009 at 8:27 am
Yes, I've deliberately put a row of data in the source table which causes part of the process to fall over with message:
Msg 241, Level 16, State 1, Line 25
Conversion failed when converting datetime from character string.
Keep the rubber side down and the shiny side up.
August 20, 2009 at 11:57 am
I wondered if there was any specific reason for wrapping your 'send mail' function in an agent job? I would have thought you could have used the likes of database mail(sp_send_dbmail) directly within the proc.
August 21, 2009 at 2:18 am
No specific reason except that my SQL knowledge isn't that great and until you mentioned it I wasn't aware you could do that :ermm:
I've had a quick look at sp_send_dbmail and it looks very neat; I've managed to set up an account and have tested that it works 😀 hoorah!!
However, if the proc won't initiate EXEC sp_start_job why would it treat EXEC sp_send_dbmail any differently?
As useful as it is (and it will get used, thank you :-)) it doesn't actually solve my problem.
Keep the rubber side down and the shiny side up.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply