Path: csiph.com!eternal-september.org!feeder.eternal-september.org!news.eternal-september.org!.POSTED!not-for-mail
From: Erland Sommarskog <esquel@sommarskog.se>
Newsgroups: microsoft.public.sqlserver.programming
Subject: Re: logging stored procedure fails when inserting via linked server
Date: Wed, 24 May 2017 20:37:12 +0200
Organization: Erland Sommarskog
Lines: 63
Message-ID: <XnsA77FD1C246C82Yazorman@127.0.0.1>
References: <14264424-14c3-48c6-828b-4c6a15cb903b@googlegroups.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 8bit
Injection-Info: mx02.eternal-september.org; posting-host="d1d1865120a768cedd901835da1c2146"; logging-data="13060"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX18bgcgBgUZBnwWehER5WvTt"
User-Agent: Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32)
Cancel-Lock: sha1:Ywg8OIJZoG+FpHACRrQsBgb70XE=
Xref: csiph.com microsoft.public.sqlserver.programming:31319

> 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