Piotr Rodak

Last week I came across an interesting issue.

If you have a FOR INSERT trigger on a table and you want to store some information on a linked server, the transaction the trigger is running within is automatically expanded to distributed mode. This creates some problems. First, you have to have Distributed Transaction Coordinator running on your machine. Second, even this does not mean you will succeed, as not all providers support distributed transactions protocols. We will see this further in this post.

First, create a linked server that will point to an Excel workbook on local disk, for simplicity sake.

DECLARE @RC int

DECLARE @server nvarchar(128)

DECLARE @srvproduct nvarchar(128)

DECLARE @provider nvarchar(128)

DECLARE @datasrc nvarchar(4000)

DECLARE @location nvarchar(4000)

DECLARE @provstr nvarchar(4000)

DECLARE @catalog nvarchar(128)

-- Set parameter values

SET @server = 'XLTEST'

SET @srvproduct = 'Excel'

SET @provider = 'Microsoft.Jet.OLEDB.4.0'

SET @datasrc = 'c:\LSBook.xls'

SET @provstr = 'Excel 8.0'

EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider,

@datasrc, @location, @provstr, @catalog

Create LSBook.xls file on your drive first. In first row, in first two columns write afield and bfield:

Now, create a test table and trigger:

use testdb

go

if object_id('dbo.testtable') is not null

      drop table dbo.testtable

go

create table dbo.testtable

(

      afield int identity(1, 1),

      bfield varchar(20) not null

)

go

create trigger trg_testtrg1 on dbo.testtable

for insert

as

begin

insert xltest...sheet1$ (afield, bfield)

select afield, bfield

from inserted

print 'inserted ' + convert(varchar, @@rowcount) + ' row(s)'

end

Finally, execute some statements to see what happens:

insert into dbo.testtable values('somevealue1')

insert into dbo.testtable values('somevealue2')

go

select * from dbo.testtable

And the result is..

Msg 8501, Level 16, State 3, Procedure trg_testtrg1, Line 6

MSDTC on server 'VAMILOXP' is unavailable.

afield      bfield

----------- --------------------

(0 row(s) affected)

Oops, MSDTC is not running on the machine.. Start it. Note that you may have to install it if you are running Windows 2003 Server.

Msg 7390, Level 16, State 2, Procedure trg_testtrg1, Line 6

The requested operation could not be performed because OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "xltest" does not support the required transaction interface.

afield      bfield

----------- --------------------

(0 row(s) affected)

So, as you see, MSDTC did not help us too much. The provider I used in this case does not support distributed transactions. What we can do to be able to insert information to linked server data store?

There is an article in MSDN that mentions that you can COMMIT transaction in trigger before you call linked server. However this causes a batch terminating error. Let's modify trigger code to commit transaction and see the results:

create trigger trg_testtrg1 on dbo.testtable

for insert

as

begin

--commit transaction

commit

--call linked server

insert xltest...sheet1$ (afield, bfield)

select afield, bfield

from inserted

Will the code above work? No! Commiting transaction makes inserted table empty, so we will not have anything to store! Here's modification what overcomes this little issue:

create trigger trg_testtrg1 on dbo.testtable

for insert

as

begin

--declare temp variable and store rows to insert to linked server table

declare @tmp table (a int, b varchar(20))

insert @tmp (a, b) select afield, bfield from inserted

--commit current tran to avoid escalation

commit

--this will work, but the batch will be broken.

insert xltest...sheet1$ (afield, bfield)

select a, b

from @tmp

end

Let's see results of this code:

insert into dbo.testtable values('somevealue1')

insert into dbo.testtable values('somevealue2')

go

--there is only one record in the table

select * from dbo.testtable

As you see there is only one record in the table - the batch was broken before second insert in the code. Obviously, this is not what we want.

I don't actually understand, why Microsoft decided to throw batch breaking error when a commit in trigger is called. Perhaps to prevent poor coding practices? Anyway, this error is not thrown when you add begin tran statement AFTER your linked server query:

create trigger trg_testtrg1 on dbo.testtable

for insert

as

begin

--declare temp variable and store rows to insert to linked server table

declare @tmp table (a int, b varchar(20))

insert @tmp (a, b) select afield, bfield from inserted

--commit current tran to avoid escalation

commit

--this will work, but the batch will be broken.

insert xltest...sheet1$ (afield, bfield)

select a, b

from @tmp

begin tran

end

So far, so good. In some cases this will work.

afield      bfield

----------- --------------------

1           somevealue1

2           somevealue2

But how about this code? What if you decide to roll back some of the work done due to an error?

begin tran

insert into dbo.testtable values('somevealue1')

insert into dbo.testtable values('somevealue2')

rollback

Well.. we are in trouble now:

select * from dbo.testtable

This way the linked server functionality is called outside of transaction boundaries, which does not upset DTC and the main transaction flow remains consistent. The issues with this approach are: you have to have proper credentials to call sp_start_job and if the insert statement is executed too often, you may get following error:

Msg 22022, Level 16, State 1, Line 0

SQLServerAgent Error: Request to run job SaveToLinkedServer (from User VAMILOXP\rogas) refused because the job already has a pending request from User VAMILOXP\rogas.

You may get rid of this error by checking job status before calling sp_start_job, you may also configure this job to run periodically and remove the sp_start_job call from the trigger at all.

References:

http://msdn2.microsoft.com/en-us/library/ms187844.aspx

My first blog post. As someone said somewhere, sharing information forces you to look at it from every side, ensure that it is really correct. I will do my best to make so.

This blog is going to be about... SQL Server :). I am database developer and from time to time I come across quite interesting problems, up with (brilliant ) ideas, have some observations that I would like to share. Some of them will not be revolutionary but I hope that some of them will allow you to look at questions you have from different angle, which is always helpful, I noticed.

My main areas of interest are performance, programming, some SSIS, some c# will perhaps find their way here as well. We'll see.