sp_execute_remote error capture

Question

sp_execute_remote error capture

grajee 331

All,

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?

Thanks,
grajee

SUNOJ KUMAR YELURU 13,236 Reputation points MVP

2021-01-09T05:13:35.227+00:00

Let us know if anything required.

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.
SUNOJ KUMAR YELURU 13,236 Reputation points MVP

2021-01-14T00:35:23.093+00:00

To follow-up, Please let us know if you have further query on this.
Please don’t forget to Accept the answer

SUNOJ KUMAR YELURU 13,236 Reputation points MVP

2021-01-09T05:13:35.227+00:00

Let us know if anything required.

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.
SUNOJ KUMAR YELURU 13,236 Reputation points MVP

2021-01-14T00:35:23.093+00:00

To follow-up, Please let us know if you have further query on this.
Please don’t forget to Accept the answer

Answer 1

@grajee

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.

grajee 331 Reputation points

2020-12-30T17:30:04.033+00:00

Consider the below sql:

EXEC sp_execute_remote @alrt _source_name = N'AICorp_Staging_DB',
@stmt = N'INSERT INTO dbo.stgLoaddatafile (
File_ID,
File_Type
)
VALUES (
@_File_ID,
@_File_Type
);',
@params = N'@_File_ID VARCHAR(64), @_File_Type AS CHAR(3)',
@_File_ID = 'AICorpAct1', @_File_Type = 'CSV';

Suppose the INSERT fails due to duplicate Primary Key (in this case File_ID), there is no way to capture the error.

Ofcourse, I can check for the value before Inserting. However, I cannot account for all the various types of errors this way. Better would be to check if an error occurred and code accordingly.

I cannot create a table variable and INSERT which would be something like 'INSERT @Table EXEC sp_execute_remote" since this works only for rows returned with successful execution

Thanks,
grajee

sp_execute_remote error capture

1 answer

Activity