SQL Server - TRY/CATCH does not work in certain casesLinked server error not caught by TRY-CATCH“Request to run job dbxxxxx refused” error in merge replicationMake SQL Server Agent retry failed script with Try/CatchWhy TRY CATCH does not suppress exception in triggerGetting a “Request to run job refused because the job is already running” When no job is running?Why do I get “The initial snapshot for publication is not yet available.”?connection pooling, transactions, nested transaction and rollbackHow to delete the remote files older than N days on schedule from SQL Server 2012 (with SqlServerAgent job)?SQL Server Try Catch TransastionSQLServerAgent Error: The process cannot access the file because it is being used by another processSQL Server TRY..CATCH resume on terminating error
How can select a specific triangle in my Delaunay mesh?
How frequently do Russian people still refer to others by their patronymic (отчество)?
Is の方 necessary here?
Convert integer to full text string duration
Park the computer
Does 5e have an equivalent of the Psychic Paper from Doctor Who?
What does "in the course of" mean?
Was I wrongfully denied boarding for having a Schengen visa issued from the second country on my itinerary?
Lie bracket of vector fields in Penrose's abstract index notation
2000s (or earlier) cyberpunk novel: dystopia, mega storm, omnipresent noise
How to supply water to a coastal desert town with no rain and no freshwater aquifers?
/api/sitecore is not working in CD server
Bypass with wrong cvv of debit card and getting OTP
Shipped package arrived - didn't order, possible scam?
Is this standard Japanese employment negotiations, or am I missing something?
How did Einstein know the speed of light was constant?
Sleepy tired vs physically tired
What is the difference between an "empty interior" and a "hole" in topology?
What is the shape of the upper boundary of water hitting a screen?
Why did Super-VGA offer the 5:4 1280*1024 resolution?
soda water first stored in refrigerator and then outside
Does the "divide by 4 rule" give the upper bound marginal effect?
Is conquering your neighbors to fight a greater enemy a valid strategy?
Why would "dead languages" be the only languages that spells could be written in?
SQL Server - TRY/CATCH does not work in certain cases
Linked server error not caught by TRY-CATCH“Request to run job dbxxxxx refused” error in merge replicationMake SQL Server Agent retry failed script with Try/CatchWhy TRY CATCH does not suppress exception in triggerGetting a “Request to run job refused because the job is already running” When no job is running?Why do I get “The initial snapshot for publication is not yet available.”?connection pooling, transactions, nested transaction and rollbackHow to delete the remote files older than N days on schedule from SQL Server 2012 (with SqlServerAgent job)?SQL Server Try Catch TransastionSQLServerAgent Error: The process cannot access the file because it is being used by another processSQL Server TRY..CATCH resume on terminating error
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
How can I write guarded code that invokes the CATCH
code block in every expected* case (without doing funky junk like wrapping everything in dynamic SQL)?
For example, this does not work:
- Define a SQL Agent job with no steps
Try to start the job inside a
TRY/CATCH
BEGIN TRY
EXEC msdb.dbo.sp_start_job @job_name = 'my_empty_job'
END TRY
BEGIN CATCH
SELECT [MyError] = 'Error caught: ' + ISNULL(ERROR_MESSAGE(), 'NULL')
END CATCH
Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job my_empty_job (from User xyz) refused because the job has no job steps.
Note that its error severity is 16 so it should not have bypassed the CATCH block. wtf?!?
It also bypasses my CATCH
block if the job is busy handling a request (I'm spamming stop and start requests in my testing).
Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job my_simple_job (from User xyz) refused because the job already has a pending request from User xyz.
Related case: invalid linked server reference
P.S. Please do not assume I just want a narrow-scope solution for handling job invocation. I am seeking a generic solution that I can reuse in the future.
P.P.S. I am able to catch the case of trying to start/stop a job which doesn't exist ("The specified @job_name ('missing_job') does not exist."). Why does that behave differently?
*What TRY/CATCH Doesn’t Handle (tl;dr error severities outside the range (10, 20)
)
sql-server sql-server-2014 error-handling
add a comment |
How can I write guarded code that invokes the CATCH
code block in every expected* case (without doing funky junk like wrapping everything in dynamic SQL)?
For example, this does not work:
- Define a SQL Agent job with no steps
Try to start the job inside a
TRY/CATCH
BEGIN TRY
EXEC msdb.dbo.sp_start_job @job_name = 'my_empty_job'
END TRY
BEGIN CATCH
SELECT [MyError] = 'Error caught: ' + ISNULL(ERROR_MESSAGE(), 'NULL')
END CATCH
Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job my_empty_job (from User xyz) refused because the job has no job steps.
Note that its error severity is 16 so it should not have bypassed the CATCH block. wtf?!?
It also bypasses my CATCH
block if the job is busy handling a request (I'm spamming stop and start requests in my testing).
Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job my_simple_job (from User xyz) refused because the job already has a pending request from User xyz.
Related case: invalid linked server reference
P.S. Please do not assume I just want a narrow-scope solution for handling job invocation. I am seeking a generic solution that I can reuse in the future.
P.P.S. I am able to catch the case of trying to start/stop a job which doesn't exist ("The specified @job_name ('missing_job') does not exist."). Why does that behave differently?
*What TRY/CATCH Doesn’t Handle (tl;dr error severities outside the range (10, 20)
)
sql-server sql-server-2014 error-handling
add a comment |
How can I write guarded code that invokes the CATCH
code block in every expected* case (without doing funky junk like wrapping everything in dynamic SQL)?
For example, this does not work:
- Define a SQL Agent job with no steps
Try to start the job inside a
TRY/CATCH
BEGIN TRY
EXEC msdb.dbo.sp_start_job @job_name = 'my_empty_job'
END TRY
BEGIN CATCH
SELECT [MyError] = 'Error caught: ' + ISNULL(ERROR_MESSAGE(), 'NULL')
END CATCH
Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job my_empty_job (from User xyz) refused because the job has no job steps.
Note that its error severity is 16 so it should not have bypassed the CATCH block. wtf?!?
It also bypasses my CATCH
block if the job is busy handling a request (I'm spamming stop and start requests in my testing).
Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job my_simple_job (from User xyz) refused because the job already has a pending request from User xyz.
Related case: invalid linked server reference
P.S. Please do not assume I just want a narrow-scope solution for handling job invocation. I am seeking a generic solution that I can reuse in the future.
P.P.S. I am able to catch the case of trying to start/stop a job which doesn't exist ("The specified @job_name ('missing_job') does not exist."). Why does that behave differently?
*What TRY/CATCH Doesn’t Handle (tl;dr error severities outside the range (10, 20)
)
sql-server sql-server-2014 error-handling
How can I write guarded code that invokes the CATCH
code block in every expected* case (without doing funky junk like wrapping everything in dynamic SQL)?
For example, this does not work:
- Define a SQL Agent job with no steps
Try to start the job inside a
TRY/CATCH
BEGIN TRY
EXEC msdb.dbo.sp_start_job @job_name = 'my_empty_job'
END TRY
BEGIN CATCH
SELECT [MyError] = 'Error caught: ' + ISNULL(ERROR_MESSAGE(), 'NULL')
END CATCH
Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job my_empty_job (from User xyz) refused because the job has no job steps.
Note that its error severity is 16 so it should not have bypassed the CATCH block. wtf?!?
It also bypasses my CATCH
block if the job is busy handling a request (I'm spamming stop and start requests in my testing).
Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job my_simple_job (from User xyz) refused because the job already has a pending request from User xyz.
Related case: invalid linked server reference
P.S. Please do not assume I just want a narrow-scope solution for handling job invocation. I am seeking a generic solution that I can reuse in the future.
P.P.S. I am able to catch the case of trying to start/stop a job which doesn't exist ("The specified @job_name ('missing_job') does not exist."). Why does that behave differently?
*What TRY/CATCH Doesn’t Handle (tl;dr error severities outside the range (10, 20)
)
sql-server sql-server-2014 error-handling
sql-server sql-server-2014 error-handling
edited 8 hours ago
Elaskanator
asked 9 hours ago
ElaskanatorElaskanator
2821 silver badge12 bronze badges
2821 silver badge12 bronze badges
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
How can I write guarded code that invokes the CATCH code block in every expected* case (without doing funky junk like wrapping everything in dynamic SQL)?
You can't, unfortunately.
SQL Server MVP Erland Sommarskog has a series of brief* articles on the subject of error handling in SQL Server that starts here: Error and Transaction Handling in SQL Server
The issue you're running into with the Agent job is that it calls an extended stored procedure, which is what raises the error. In particular, that error comes from master.dbo.xp_sqlagent_notify
.
Erland covers that issue here:
SQL Server still ships with quite a few extended stored procedures, whereof some are documented and others are only intended for the tools that ship with SQL Server. With regards to error handling, all bets are off with these guys. There is no consistent behaviour, and about every XP has its own twist.
* brief, ha!
1
I wonder if this is why my Ola's jobs fail without error sometimes...
– Jacob H
8 hours ago
After more testing I discovered that a start request can be created (after verifying the job is inactive) while one is already pending, causing one of my uncatchable errors above ("...the job already has a pending request...").
– Elaskanator
7 hours ago
add a comment |
Your Answer
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f242014%2fsql-server-try-catch-does-not-work-in-certain-cases%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
How can I write guarded code that invokes the CATCH code block in every expected* case (without doing funky junk like wrapping everything in dynamic SQL)?
You can't, unfortunately.
SQL Server MVP Erland Sommarskog has a series of brief* articles on the subject of error handling in SQL Server that starts here: Error and Transaction Handling in SQL Server
The issue you're running into with the Agent job is that it calls an extended stored procedure, which is what raises the error. In particular, that error comes from master.dbo.xp_sqlagent_notify
.
Erland covers that issue here:
SQL Server still ships with quite a few extended stored procedures, whereof some are documented and others are only intended for the tools that ship with SQL Server. With regards to error handling, all bets are off with these guys. There is no consistent behaviour, and about every XP has its own twist.
* brief, ha!
1
I wonder if this is why my Ola's jobs fail without error sometimes...
– Jacob H
8 hours ago
After more testing I discovered that a start request can be created (after verifying the job is inactive) while one is already pending, causing one of my uncatchable errors above ("...the job already has a pending request...").
– Elaskanator
7 hours ago
add a comment |
How can I write guarded code that invokes the CATCH code block in every expected* case (without doing funky junk like wrapping everything in dynamic SQL)?
You can't, unfortunately.
SQL Server MVP Erland Sommarskog has a series of brief* articles on the subject of error handling in SQL Server that starts here: Error and Transaction Handling in SQL Server
The issue you're running into with the Agent job is that it calls an extended stored procedure, which is what raises the error. In particular, that error comes from master.dbo.xp_sqlagent_notify
.
Erland covers that issue here:
SQL Server still ships with quite a few extended stored procedures, whereof some are documented and others are only intended for the tools that ship with SQL Server. With regards to error handling, all bets are off with these guys. There is no consistent behaviour, and about every XP has its own twist.
* brief, ha!
1
I wonder if this is why my Ola's jobs fail without error sometimes...
– Jacob H
8 hours ago
After more testing I discovered that a start request can be created (after verifying the job is inactive) while one is already pending, causing one of my uncatchable errors above ("...the job already has a pending request...").
– Elaskanator
7 hours ago
add a comment |
How can I write guarded code that invokes the CATCH code block in every expected* case (without doing funky junk like wrapping everything in dynamic SQL)?
You can't, unfortunately.
SQL Server MVP Erland Sommarskog has a series of brief* articles on the subject of error handling in SQL Server that starts here: Error and Transaction Handling in SQL Server
The issue you're running into with the Agent job is that it calls an extended stored procedure, which is what raises the error. In particular, that error comes from master.dbo.xp_sqlagent_notify
.
Erland covers that issue here:
SQL Server still ships with quite a few extended stored procedures, whereof some are documented and others are only intended for the tools that ship with SQL Server. With regards to error handling, all bets are off with these guys. There is no consistent behaviour, and about every XP has its own twist.
* brief, ha!
How can I write guarded code that invokes the CATCH code block in every expected* case (without doing funky junk like wrapping everything in dynamic SQL)?
You can't, unfortunately.
SQL Server MVP Erland Sommarskog has a series of brief* articles on the subject of error handling in SQL Server that starts here: Error and Transaction Handling in SQL Server
The issue you're running into with the Agent job is that it calls an extended stored procedure, which is what raises the error. In particular, that error comes from master.dbo.xp_sqlagent_notify
.
Erland covers that issue here:
SQL Server still ships with quite a few extended stored procedures, whereof some are documented and others are only intended for the tools that ship with SQL Server. With regards to error handling, all bets are off with these guys. There is no consistent behaviour, and about every XP has its own twist.
* brief, ha!
answered 8 hours ago
Josh DarnellJosh Darnell
10.2k3 gold badges23 silver badges51 bronze badges
10.2k3 gold badges23 silver badges51 bronze badges
1
I wonder if this is why my Ola's jobs fail without error sometimes...
– Jacob H
8 hours ago
After more testing I discovered that a start request can be created (after verifying the job is inactive) while one is already pending, causing one of my uncatchable errors above ("...the job already has a pending request...").
– Elaskanator
7 hours ago
add a comment |
1
I wonder if this is why my Ola's jobs fail without error sometimes...
– Jacob H
8 hours ago
After more testing I discovered that a start request can be created (after verifying the job is inactive) while one is already pending, causing one of my uncatchable errors above ("...the job already has a pending request...").
– Elaskanator
7 hours ago
1
1
I wonder if this is why my Ola's jobs fail without error sometimes...
– Jacob H
8 hours ago
I wonder if this is why my Ola's jobs fail without error sometimes...
– Jacob H
8 hours ago
After more testing I discovered that a start request can be created (after verifying the job is inactive) while one is already pending, causing one of my uncatchable errors above ("...the job already has a pending request...").
– Elaskanator
7 hours ago
After more testing I discovered that a start request can be created (after verifying the job is inactive) while one is already pending, causing one of my uncatchable errors above ("...the job already has a pending request...").
– Elaskanator
7 hours ago
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f242014%2fsql-server-try-catch-does-not-work-in-certain-cases%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown