question

JP-2470 avatar image
0 Votes"
JP-2470 asked ·

SQL Server 2017: Error handling for example: sys.xp_create_subdir

Hello! Is there a way to "try and catch" a system procedure like sys.xp_create_subdir. This command does not trigger our try and catch construct. Is there any solution for this? Thank you! Ceoolc

sql-server-general
· 2
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @JP-2470, why did you want to try and catch a system procedure? is there have some errors occur?

0 Votes 0 ·

Hi, we have not received a reply from you. Any update for this?

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered ·

That has less to do which SP/XP you call, but with the raised error and it's severity

See TRY...CATCH (Transact-SQL) => Errors Unaffected by a TRY...CATCH Construct


·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ·

TRY-CATCH and extended stored procedures is not a fantastic story. I have written about it here: http://www.sommarskog.se/error_handling/Part2.html#XPs

But I have not tried this particular XP.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ·

It occurred to me that I describe some possible alternatives elsewhere in my series on Errors and Transaction Handling in SQL Server: http://www.sommarskog.se/error_handling/Part3.html#CMDcatchhandler

Not of these are very palatable. The least kludgy approach is probably the tip I got from Dave Mason, and which I discuss in the section after the one I linked you to. However, his approach does not work here: an error in an XP does not trigger the extended event. Bleech!

So that leaves you with my CMD Catch Handler. Or orchestrate what you want to do from a client-side program.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.