sp_execute_remote error capture

grajee 331 Reputation points


The results returned by SP executed via sp_execute_remote can be captured via a table variable. However, how does one capture the error?

I have to execute an INSERT statement against an external database using sp_execute_remote. The INSERTs works but when there is an error I'm not able to retrieve the error (for example duplicate key) though the error shows up in the message pane.

Is there a way to capture the errors?


Azure SQL Database
{count} votes

1 answer

Sort by: Most helpful
  1. SUNOJ KUMAR YELURU 13,236 Reputation points MVP


    Retrieving detailed information on the error
    You can take advantage of various functions inside the CATCH block to get detailed information about an error.

    These functions include the following:

    ERROR_MESSAGE() - you can take advantage of this function to get the complete error message.
    ERROR_LINE() - this function can be used to get the line number on which the error occurred.
    ERROR_NUMBER() - this function can be used to get the error number of the error.
    ERROR_SEVERITY() - this function can be used to get the severity level of the error.
    ERROR_STATE() - this function can be used to get the state number of the error.
    ERROR_PROCEDURE() - this function can be used to know the name of the stored procedure or trigger that has caused the error.

    Refer below websites explained in detailed.
    Error Handling in SQL Server with Try Catch

    Catch the Error Message from shard Database- AzureSqlDB


    Please don’t forget to "Accept the answer" and “up-vote” wherever the information provided helps you, this can be beneficial to other community members.