Re: Triggers from one MSDE to another using DTC
From: Wayne Snyder (wayne.nospam.snyder_at_mariner-usa.com)
Date: 08/06/04
- Next message: Wayne Snyder: "Re: Hosting website on SQL Sever."
- Previous message: Wayne Snyder: "Re: master db"
- In reply to: Jon Bishop: "Triggers from one MSDE to another using DTC"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 6 Aug 2004 07:31:25 -0400
I agree with Mary's assessment... However to specifically answer your
question. In order to do a Distributed Transaction you must SET XACT_ABORT
ON ie see below
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jon Bishop" <JonBishop@discussions.microsoft.com> wrote in message
news:B475361A-02AE-4FD8-9AE1-0BE27A28CC89@microsoft.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
Set Xact_abort on
> INSERT into JON1.ISR_DB.dbo.associate
> select * from inserted
if @@error!=0
begin
raiserror(.....yada yada
rollback tran
return
end
> 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
> --
> Software Developer
> UK
- Next message: Wayne Snyder: "Re: Hosting website on SQL Sever."
- Previous message: Wayne Snyder: "Re: master db"
- In reply to: Jon Bishop: "Triggers from one MSDE to another using DTC"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
