Triggers from one MSDE to another using DTC

[Jonathan Bishop]

Hi.

We are using MSDE2000 on a Point of Sale application. We need to keep a copy
of a few key tables as up to date as possible for backup purposes.
We are looking at using triggers over the source tables to update the target
tables using a linked servers setup. Distributed Transaction Coordinator is
running on both source and target servers.

This following create command:

CREATE TRIGGER JonsTrigger
ON associate
FOR INSERT
AS
INSERT into JON1.ISR_DB.dbo.associate
select * from inserted
GO
will generate the following error:

Server: Msg 7395, Level 16, State 2, Procedure JonsTrigger, Line 5
Unable to start a nested transaction for OLE DB provider 'SQLOLEDB'. A
nested transaction was required because the XACT_ABORT option was set to
OFF.
[OLE/DB provider returned message: Cannot start more transactions on this
session.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionLocal::StartTransaction returned 0x8004d013: ISOLEVEL=4096].

OK fine, as the error says, use XACT_ABORT ON around the trigger INSERT and
all works well and the insert gets fired over to the target machine. I
believe we need this set as SQL doesnt support nested transactions ? Any way
, so far so good . In principle it will work......

The issue we have is that if the trigger fails ( target machine not
available etc etc ) the whole transaction is rolled back including the
originating transaction that the trigger was fired from. Problem is that we
dont want the original insert to fail. How can we isolate the two. Original
insert must occur no matter what. The trigger will do the best it can to
update the backup DB but we dont want the whole thing to fail if it cant.

I have looked around BEGIN DISTRIBUTED TRANSACTION and couldnt get it to
make a difference. Have looked at removing the XACT_ABORT but cant get the
update to succeed if this is missing. Have thought about using a stored
procedure in some way but not quite sure how !

Can anyone shed any light on this please.

Thanks

Jon

[Simon Hayes]

"Jonathan Bishop" <Jo*************@BTInternet.com> wrote in message news:<ce**********@sparta.btinternet.com>...

Hi.

We are using MSDE2000 on a Point of Sale application. We need to keep a copy
of a few key tables as up to date as possible for backup purposes.
We are looking at using triggers over the source tables to update the target
tables using a linked servers setup. Distributed Transaction Coordinator is
running on both source and target servers.

<snip>

You might find the information here useful:

http://www.sommarskog.se/error-handling-I.html

Personally, I would avoid the trigger solution completely, and look at
an alternative approach. Specifically, log shipping and replication
are good ways to maintain an up to date copy of a production database
on a different server. Log shipping is only available in Enterprise
Edition, however implementing your own solution in MSDE should be
quite straightforward:

http://www.winnetmag.com/Article/Art...231/23231.html
http://sqlguy.home.comcast.net/logship.htm
http://www.sql-server-performance.co...g_shipping.asp

Or you could look at replication, which would replicate all
transactions from your source server to the target server. While log
shipping copies all changes, replication can copy changes made to
specific tables only. This is quite powerful, but it adds a certain
amount of complexity to your environment, which may or may not be a
good trade-off for you. See Books Online for more information, and
also this article:

http://support.microsoft.com/default...;en-us;Q324992

Simon

[Erland Sommarskog]

Jonathan Bishop (Jo*************@BTInternet.com) writes:

The issue we have is that if the trigger fails ( target machine not
available etc etc ) the whole transaction is rolled back including the
originating transaction that the trigger was fired from. Problem is that
we dont want the original insert to fail. How can we isolate the two.
Original insert must occur no matter what. The trigger will do the best
it can to update the backup DB but we dont want the whole thing to fail
if it cant.

If you want the insert to always succeed, you cannot perform the insert
on the remote table in the trigger. In SQL Server a DML statement -
INSERT, UPDATE or DELETE - is always atomic. That is, it is always all
or nothing - and that includes the trigger.

One solution would be to insert to a local table, and then have another
process to perform the replication. Simon suggested using replication
or log shipping, but this may a little heavy-duty. And I don't know
how much of that you can do in MSDE anyway.

OK, so there is a possible wya to use a trigger after all:

CREATE TRIGGER JonsTrigger
ON associate
FOR INSERT
AS
COMMIT TRANSACTION
INSERT into JON1.ISR_DB.dbo.associate
select * from inserted
BEGIN TRANSACTION
GO

But this would be very bad practice. And it would not work if the INSERT
statement is part of a multi-statement transaction. Also, if the INSERT
into the remote statement fails, the batch will still be terminated,
which means that any other statements in the batch are not run. The
closing BEGIN TRANSACTION is there, as else the batch would be terminated
because of the @@trancount in the trigger.

Again, I like to stress that this is a wretched solution, and I don't
know if Microsoft even considers it supported, and thus it could break
in the next version of SQL Server.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Jonathan Bishop]

Thanks for the input gentlemen....

We are indeed looking at another solution ! We will probably settle on a
scheduled job to just update based on a status flag, rather than a trigger.
It should be about 10 mins up to date which is close enough.

Thanks again.

Jon.

"Jonathan Bishop" <Jo*************@BTInternet.com> wrote in message
news:ce**********@sparta.btinternet.com...

Hi.

We are using MSDE2000 on a Point of Sale application. We need to keep a copy of a few key tables as up to date as possible for backup purposes.
We are looking at using triggers over the source tables to update the target tables using a linked servers setup. Distributed Transaction Coordinator is running on both source and target servers.

This following create command:

CREATE TRIGGER JonsTrigger
ON associate
FOR INSERT
AS
INSERT into JON1.ISR_DB.dbo.associate
select * from inserted
GO
will generate the following error:

Server: Msg 7395, Level 16, State 2, Procedure JonsTrigger, Line 5
Unable to start a nested transaction for OLE DB provider 'SQLOLEDB'. A
nested transaction was required because the XACT_ABORT option was set to
OFF.
[OLE/DB provider returned message: Cannot start more transactions on this
session.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionLocal::StartTransaction returned 0x8004d013: ISOLEVEL=4096].

OK fine, as the error says, use XACT_ABORT ON around the trigger INSERT and all works well and the insert gets fired over to the target machine. I
believe we need this set as SQL doesnt support nested transactions ? Any way , so far so good . In principle it will work......

The issue we have is that if the trigger fails ( target machine not
available etc etc ) the whole transaction is rolled back including the
originating transaction that the trigger was fired from. Problem is that we dont want the original insert to fail. How can we isolate the two. Original insert must occur no matter what. The trigger will do the best it can to
update the backup DB but we dont want the whole thing to fail if it cant.

I have looked around BEGIN DISTRIBUTED TRANSACTION and couldnt get it to
make a difference. Have looked at removing the XACT_ABORT but cant get the
update to succeed if this is missing. Have thought about using a stored
procedure in some way but not quite sure how !

Can anyone shed any light on this please.

Thanks

Jon