How to modify a table outside of the current transaction ? ( with rollback and using linked server )

Question

How to modify a table outside of the current transaction ? ( with rollback and using linked server )

Tomasz M

SSC-Addicted

Points: 408
More actions
May 5, 2011 at 2:02 pm

#250389

Hi
I have read somewhere that is is possible to define a loopback linked server ( pointing to the same source db )
and use in auditing scenarios to modify data outside of the currently running transaction.
This was an alternative to using table variable and generating events by I cannot get this to work.
I remember that there was so setting responsible for not starting a distributed transaction for this linked server and probably the linked server has to be using ODBC drivers.
Does anyone here knows about this approach, or is it just a myth ?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

mutobo (5/5/2011)
Hi
I have read somewhere that is is possible to define a loopback linked server ( pointing to the same source db )
and use in auditing scenarios to modify data outside of the currently running transaction.
This was an alternative to using table variable and generating events by I cannot get this to work.
I remember that there was so setting responsible for not starting a distributed transaction for this linked server and probably the linked server has to be using ODBC drivers.
Does anyone here knows about this approach, or is it just a myth ?

Well creating the Loopback Linked server is no big deal, but I don't see how this would help you at all;

DECLARE @server SYSNAME

set @server=@@SERVERNAME

EXEC master.dbo.sp_addlinkedserver @server = N'MyLoopbackServer',

@srvproduct = N'',

@datasrc = @@SERVERNAME,

@provider = N'SQLOLEDB',

@catalog='SandBox';

SP_TABLES_EX MyLoopbackServer

EXEC dbo.sp_DropServer 'MyLoopbackServer','DropLogins'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Tomasz M SSC-Addicted Points: 408 More actions · Answer 2

Actually I got this working by changing the linked server option:

EXEC master.dbo.sp_serveroption @server=N'LOOPBACK', @optname=N'remote proc transaction promotion', @optvalue=N'false'

and using the "EXEC() at" syntax:

exec ('update top(1) db_name.schema.table

set col = value

where col2= 2=value2') at LOOPBACK

but still would like to create a synonym to the table on the linked server and use line casual DML statement:

CREATE SYNONYM linkedtablename FOR LOOPBACK.db_name.schema.table

update top(1) linkedtablename

set col = value

where col2= 2=value2

but that does not work:

Msg 3910, Level 16, State 2, Line 1

Transaction context in use by another session.

Paul White SSC Guru Points: 150467 More actions · Answer 3

The remote transaction promotion technique is described in more detail here:

http://blogs.msdn.com/b/mssqlisv/archive/2008/08/22/how-to-create-an-autonomous-transaction-in-sql-server-2008.aspx

I used the same trick with sequence tables:

http://sqlblog.com/blogs/paul_white/archive/2010/10/19/sequence-tables.aspx

But it's hard to recommend this technique (it's quite slow, and there are many 'issues' waiting for you down this path). Why not use the traditional table variable technique?

CREATE TABLE dbo.Test (A INT NOT NULL);

BEGIN TRANSACTION;

DECLARE @Audit TABLE (A INT NOT NULL);

INSERT dbo.Test (A)

OUTPUT

inserted.A

INTO @Audit (A)

VALUES

(999);

ROLLBACK TRANSACTION;

SELECT * FROM dbo.Test;

SELECT * FROM @Audit

DROP TABLE dbo.Test;

Notice the audit data survives the rollback.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi