Query Timeout Expired.... Message 7412. How To Catch This Error??

Hi All,

I have the same question and error that Chopaka is getting:

"I have a SQL 2005 job that calls a stored proc. The job step returns the message "Query timeout expired....Message 7412...The step succeeded." The proc never actually ever did anything due to the query timeout, and the job continued on to other steps. I'm going to address the timeout issue eventually, but first I'd like to trap the timeout problem and force the job to end.

It appears that the query timeout isn't really an error, just a message. I've tried TRY-CATCH in the SP but the situation isn't caught, again probably due to the interpretation that it isn't an error."


I have reduced the "Remote Query time out" to 1 sec, in order to catch the error and to prevent the job from running, but the error is not caught.


Is there a way to catch this in the SQL or in the job step to prevent the job from continuing?

This is the script that I'm using without any luck
BEGIN TRY
BEGIN Transaction
Create table #tmpSummaryTable
(
)
insert into #tmpSummaryTable
select * from CDRServer01.iXtemp.dbo.gx_tbFTRSummary_test

COMMIT Transaction
END TRY

BEGIN CATCH
DECLARE @err int
SELECT @err = @@error
PRINT '@@error: ' + ltrim(str(@err))

SELECT ERROR_NUMBER() ERNumber,


ERROR_MESSAGE() Error_Message
ROLLBACK
Return

END CATCH