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

J P 1 Reputation point
2021-03-02T14:47:28.4+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,677 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 40,736 Reputation points
    2021-03-02T15:28:18.553+00:00

    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

    0 comments
  2. Erland Sommarskog 100.9K Reputation points MVP
    2021-03-02T23:01:52.893+00:00

    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.

    0 comments
  3. Erland Sommarskog 100.9K Reputation points MVP
    2021-03-04T22:21:13.42+00:00

    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.

    0 comments