SQL Server Performance Forum – Threads Archive
Error Handling
So, there’s this stored procedure that I have that is run nightly. It does 2 things. First it obtains data from another server. Then it performs a backup job. I know this sounds weird, but the data extraction procedure has to run before or around the time of the backup procedure for various reasons. The problem I have bumped into, and it should occur rarely, is that one day the other server was just offline. So when the user went to execute the stored procedure for the backup, data was not extracted and the backup was not performed. It is acceptable to not get that data right away on a rare occasion, however it is not acceptable if the backup doesn’t run. What I’d like to know, is there some sort of way that I can have this stored procedure not execute the first part when there is a connection error but go ahead and execute the second part no matter what? I looked into Try and Catch but unfortunately it does not work for non-existant objects which would be the case of a connection problem.I don’t know if it will be helpful on your scenario but worth a look on this. http://www.sommarskog.se/error-handling-II.html Thanks, Name
———
Dilli Grg (1 row(s) affected)
You could place the code which you want to execute in catch block as well which will get executed even after first error occurs.
But i’m not sure, it is the right approach or not.
I went back to review Try and Catch on BOL and after re-reading it a few times I was able to determine that using a stored procedure in my Try block instead of using a statement that accesses a non-existing object would probably work. Lucky for me, I was right about that. So the final layout of my stored procedure was like the following: BEGIN TRY exec sp_myStoredProcedure END TRY
BEGIN CATCH
— execute email statement to DBA for failure of data transfer
END CATCH exec sp_runBackup
If anyone else runs into something like this, then refer to these statements in BOL (I know they sound complicated somewhat but take note of the boldface items):
quote:Unlike the syntax error in the example above, an error that occurs during statement-level recompilation will not prevent the batch from compiling, but it will terminate the batch as soon as recompilation for the statement fails. For example, if a batch has two statements and the second statement references a table that does not exist, deferred name resolution causes the batch to compile successfully and start execution without binding the missing table to the query plan until that statement is recompiled. The batch stops executing when it gets to the statement that references the missing table and returns an error. This type of error will not be handled by a TRY…CATCH construct at the same level of execution at which the error occurred. The following example demonstrates this behavior.
quote:Using TRY…CATCH to handle errors that occur during compilation or statement-level recompilation is possible by executing the error-generating code in a separate batch within the TRY block. For example, this can be done by placing the code in a stored procedure or by executing a dynamic Transact-SQL statement using sp_executesql. This allows TRY…CATCH to catch the error at a higher level of execution than the error occurrence. To illustrate, the following code example shows a stored procedure that generates an object name resolution error. The batch that contains the TRY…CATCH construct is executing at a higher level than the stored procedure, and the error, which occurs at a lower level, is caught.
]]>