0

I created a small logging framework so I can easily call a stored procedure like spLog or spLogError.

The stored procedures and tables all reside in a separate database called Logging.

Now the code that I use looks as follows:

if OBJECT_ID('dbo.testLogProcLinkedServer') is not null
    drop procedure dbo.testLogProcLinkedServer;
go

CREATE PROCEDURE [dbo].[testLogProcLinkedServer]
AS
BEGIN
    set nocount on;

    declare @msg nvarchar(max),
            @dbId int = db_id();

    begin try
        declare @i int;

        set @i = 1 / 0;
    end try
    begin catch
        exec [MyLinkedServer].[Logging].dbo.spLogError  @objectId = @@PROCID, 
                                                        @databaseId = @dbId, 
                                                        @message = N'Testing catch errors, check field ErrorMessage for more information.';
    end catch
end
go

exec dbo.testLogProcLinkedServer;

Now the thing is that ERROR_MESSAGE() does not get into the spLogError, it is NULL. But when I remove MyLinkedServer (on my development environment, the databases are on the same server so there I am able to do this), the ERROR_MESSAGE() is available and all is working as intended.

When looking up "tsql catch linked server" on Google. I find this SO question: Catching Errors through a linked server with severity < 20

But the main difference with this question and mine, is that the most topics are refering to catching an error from a remote procedure call, but I don't want to catch something from a remote procedure call. I would like to send it my error data and write the corresponding table.

Can anyone point me to what is causing this?

Vincent Verweij
  • 85
  • 1
  • 8
  • 1
    I don't see `ERROR_MESSAGE()` in your code. Is that it in the remote `spLogError` proc? If so, you need to instead get the value in the code before calling the remote proc and pass the value to the remote proc (e.g. as part of the `@message` parameter). – Dan Guzman Jun 04 '19 at 10:49
  • Hi Dan, yes it is part of the stored proc spLogError. You are right in that I should pass it with the @message parameter. But why is my question. The procedure is inside the catch scope, so shouldn't that procedure be aware of what Error_message() is? – Vincent Verweij Jun 04 '19 at 11:13
  • The error context is out of scope in the remote proc. Error context is only available on the local server and not serialized for access in remote calls. – Dan Guzman Jun 04 '19 at 11:31

1 Answers1

1

ERROR_MESSAGE() and other error context functions are only available in the local CATCH block. The remote procedure is oblivious to the fact it's called from a CATCH block and doesn't have access to the error context.

You'll need to pass the value to the remote spLogError as a parameter value for logging purposes.

BEGIN CATCH
    @msg = N'Testing catch errors, ErrorMessage value is: '
        + ERROR_MESSAGE();
    EXEC [MyLinkedServer].[Logging].dbo.spLogError  @objectId = @@PROCID, 
                                                    @databaseId = @dbId, 
                                                    @message = @msg;
END CATCH;
Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
  • Thanks for the clarification. Is it stated anywhere in the docs? As I was not able to find it on Google. Normally such things pop up immediately. – Vincent Verweij Jun 04 '19 at 11:34
  • 1
    I'm not aware of this being called out in the docs but it makes sense when you think about it. There's nothing special about `ERROR_MESSAGE()`. It's that the remote server has no knowledge of the calling session context (sans maybe distributed transaction info) and all system functions that return session context information (e.g. `@@SPID`, `HOST_NAME()`, et.al.) return information about the session of the instance running the code. The calling server would need to pass all that information in order for it to be available remotely. – Dan Guzman Jun 04 '19 at 12:04