Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.sqlserver.programming > #31319

Re: logging stored procedure fails when inserting via linked server

Show key headers only | View raw

> I've created a stored procedure that inserts into a table in my current
> database using a loopback linked server, this was done because i need
> the logging transaction to be independent of the current active
> transaction. 
> 
>
> i call my logging proc directly from the code and the logging proc then
> executes the following dynamic sql: 

I would recommend that you write a stored proceedure that writes to the 
log table, and then you call it this way:

SELECT @spname = quotename(@loopbackserver) + '.' + 
                  quotename(db_name()) + '.dbo.your_sp'
EXEC  @spname @LogDate       = @now
                ,                  @RowCount      = @RowCount
                ,                  @Severity      = @Severity
                ,                  @LogLevel      = @LogLevel
                ,                  @MessageID     = @MessageID
                ,                  @ProcName      = @ProcName
                ,                  @Message       = @Message
                ,                  @ExtractSession= @ExtractSession


EXEC accepts a variable for the procedure name, so that works fine.

I don't really know why you get the error you do, but I would execpt
the above to work. If you don't want a separate SP, you can still do:

DECLARE @DMLVars nvarchar(1024)
        @sp_executesql nvarchar(1023)
SET @DMLVars = '@LogDate datetime, @RowCount int, 
                @Severity int, @ExtractSession uniqueidentifier, 
                @LogLevel int, @MessageID int, @ProcName nvarchar(128), 
                @Message nvarchar(max)'

SET @sp_executesql = quotename(@loopbackserver) + '.' + 
                     quotename(db_name()) + '.sys.sp_executesql'

EXEC @sp_executesql N'INSERT INTO [Log].[BIInfoLog] 
                      ([LogDate]      , [RowCount]     , [Severity], 
                       [LogLevel]     ,[MessageID], [SPName]      ,                        
                      [Message], [ExtractSession] ) 
  values (@LogDate, @RowCount, @Severity, @LogLevel, @MessageID, @ProcName, 
          @Message, @ExtractSession )
                
>                --SET ANSI_WARNINGS OFF -- don't warn about truncation
>                --set ANSI_NULLS OFF

Never touch these SET options! Never! They are pure legacy, and will only
cause you pain. Specifically, they must be on when you are dealing with
linked servers.

A completely different take on this is to write a CLR procedure to
do the loopback. It will need to be installed with EXTERNAL_ACCESS
permission, but I think this is the better option. You can read
this chapter in my series on Error and Transaction Handling in SQL
Server where I discuss an error-logging facility not unlike what you
are trying to achieve.


-- 
Erland Sommarskog, Stockholm, esquel@sommarskog.se

Back to microsoft.public.sqlserver.programming | Previous | Next — Previous in thread | Find similar

Thread

logging stored procedure fails when inserting via linked server lachlann@gmail.com - 2017-05-24 09:00 -0700
  Re: logging stored procedure fails when inserting via linked server lachlann@gmail.com - 2017-05-24 11:24 -0700
    Re: logging stored procedure fails when inserting via linked server Erland Sommarskog <esquel@sommarskog.se> - 2017-05-24 20:46 +0200
      Re: logging stored procedure fails when inserting via linked server lachlann@gmail.com - 2017-05-24 12:05 -0700
        Re: logging stored procedure fails when inserting via linked server Erland Sommarskog <esquel@sommarskog.se> - 2017-05-24 23:06 +0200
      Re: logging stored procedure fails when inserting via linked server lachlann@gmail.com - 2017-05-24 12:05 -0700
  Re: logging stored procedure fails when inserting via linked server Erland Sommarskog <esquel@sommarskog.se> - 2017-05-24 20:37 +0200

csiph-web