Re: Use replication + DTS - Referred to Hilary Cotter or Paul Ibis

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Paul Ibison (Paul.Ibison_at_Pygmalion.Com)
Date: 10/26/04 

Date: Tue, 26 Oct 2004 21:58:16 +0100

Matt,

can you test each part separately:

(1) do the same type of trigger, but locally create ordersnew and use that
  CREATE TRIGGER copy_Order ON [dbo].[orders]
  FOR INSERT
  AS insert into databasename.dbo.ordersnew(idOrder,
 orderDateEntered)
 select idOrder, orderDateEntered from inserted

(2) try a distributed transaction using similar syntax:
begin distributed transaction
do a local insert
insert into server.databasename.dbo.orders(idOrder,
 orderDateEntered)
values(....)
commit transaction

Let's see if both of these work
  HTH,
  Paul Ibison (SQL Server MVP)

  (recommended sql server 2000 replication book:
  http://www.nwsu.com/0974973602p.html)

Relevant Pages

  • Re: Triggers from one MSDE to another using DTC
    ... In order to do a Distributed Transaction you must SET XACT_ABORT ... Wayne Snyder, MCDBA, SQL Server MVP ... > OK fine, as the error says, use XACT_ABORT ON around the trigger INSERT ...
    (microsoft.public.sqlserver.server)
  • Re: UPDATE Statement Involving Tables in Different Databases
    ... 'SQLOLEDB' was unable to begin a distributed transaction. ... OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ... If the databases are on the same instance of SQL Server, ...
    (comp.databases.ms-sqlserver)
  • RE: Remote stored procedure
    ... "The problem occurs because Microsoft Distributed Transaction Coordinator ... settings of MS DTC are disabled on new installations of SQL Server 2000 on ... Author of "SQL Server 2000 Fast Answers..." ...
    (microsoft.public.sqlserver.server)
  • Re: LInked Servers and Triggers...
    ... "AnthonyThomas" wrote: ... > distributed transaction unless you explicitly override the creation of one. ... > when I have a trigger setup to update a Pervasive table from MSSQL. ... > if I run those queries in the query anaylzer they run fine. ...
    (microsoft.public.sqlserver.server)
  • Re: Creating a Trigger which updates a linked server
    ... But the problem is not the @@error message or else!!! ... the trigger start and i wait until the connection is ... > BEGIN DISTRIBUTED TRANSACTION ... >> COMMIT TRAN ...
    (microsoft.public.sqlserver.programming)