December 14, 2009 at 7:57 am
Hi!
I’m currently working on a performance test bench for our database tier and ran into a problem catching attention event (signal) in the test stored procedures.
Basically what I’m doing is running SP2 remotely from SP1. The server where SP2 is located is a linked server with Query Timeout configured. What I want to be able to do is to catch the attention event in SP2 when the execution of SP2 expires timeout in SP1, and the attention signal (event) is sent to the linked server. Why… well I want to be able to “terminate” SP2 in a certain way if it is “canceled” / timed out from SP1
In my tests I’m not able to get the Catch block of a try-catch construction in SP2 to execute when attention event is issued while SP2 is still in the Try block. It simply stops executing doing rollback of everything accomplished when it received the attention event .
November 16, 2011 at 10:53 am
Was anyone able to come up with a solution to this? I am having the same problem.
We know the problem and we are developing a solution for the timeouts, but in the meantime I want to be able to catch the timeouts and return a message to the user.
November 16, 2011 at 11:03 am
You'll have to catch it in the application. To SQL Server, it's not an error. It's the same as clicking 'stop' in management studio.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 16, 2011 at 1:34 pm
Thanks, unfortunetly I am calling a stored procedure within a stored procedure. Is there anyway to get that message to return to the other stored procedure so I can handle it?
November 16, 2011 at 1:46 pm
Again, SQL doesn't consider this an error, so there's nothing to catch here. The application is the one that knows it's a timeout (as opposed to say a developer clicking the stop button in SSMS), so the app is the one that place that will handle it.
When the app times out, it says to SQL 'Stop what you're doing', so SQl stops the query at the next point it could do so. It's not going to pass execution to some other piece of T-SQL code to go and do who knows what, when the client's instructions were 'stop as soon as possible'
The application error that you get 'Timeout occurred' (or similar) is generated by the application, not by SQL Server. It's not SQL Server that throws a time out, it's application that decides that the query has taken too long.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply