You can use the SQL function RAISERROR to spit back error messages to the caller, write it to the event & application log, or cause a job to fail and exit if it matches the criteria.
When I first read about this function, I was a bit confused but having used it for a while now, I’m starting to get the hang of it.
The following will cause the message to be spit back to the result pane immediately rather then at the end of execution: raiserror(‘this is a test, print me now!’,0,1) with nowait.
In the past, I used to use: print ‘this is a test, print me now’. The problem with the print statement is that it is returned to you when SQL is done executing your script or procedure.
In one of my SQL job, I have a step that does a file count in a directory. If that directory doesn’t contain a certain amount of files, I need it to exit, mark the job as failed, write to the error and application log. The following RAISERROR statement will do the trick:
raiserror(‘file count does not match’,16,-1) with log.