Try not to use Distributed Transaction unless really needed.

Test:

insert into server3.databasename.dbo.tablename
values(1,2,3)

-----Original Message-----
From: BChacko@d... [mailto:BChacko@d...]
Sent: Friday, November 08, 2002 8:44 AM
To: sql language
Subject: [sql_language] RE: Distributed Transaction MS SQL 2K



Hi Denis,

Yes the DTC service is running all of the machines using the Local System
Account.

Thanks,
Benny



 

                    "Denis Gobo"

                    <dgobo@w...       To:     "sql language"
<sql_language@p...>                        
                    com>                 cc:

                                         Subject:     [sql_language] RE:
Distributed Transaction MS SQL 2K         
                    11/08/2002

                    08:42 AM

                    Please respond

                    to "sql

                    language"

 

 





Is Distributed Transaction Coordinator running??

-----Original Message-----
From: BChacko@d... [mailto:BChacko@d...]
Sent: Friday, November 08, 2002 11:33 AM
To: sql language
Subject: [sql_language] Distributed Transaction MS SQL 2K


I would like to run an insert statement in SQL Server 2K to update a
table
on three separate servers from a trigger.  I have a linked server
connection to all of the machines.  Do I need to call any stored
procedure
or write any statement before  running the following code?  I keep
getting
the following error and I really don't know what it could be.  The DTC
Service has been started on all of the machines and the linked server
connection appears to be working fine.  I can't move on in my code until
I
can figure this out.  Can someone please help?  I really appreciate it.
Thanks!

ERROR MESSAGE:
[Microsoft][ODBC SQL Server Driver][SQL Server] The operation could not
be
performed because the OLE DB provider 'SQLOLEDB' was unable to begin a
distributed transaction.
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
message: New transaction cannot enlist in the specified transaction
coordinator.]

MY CODE:

CREATE TRIGGER INSERT
on Changetable
for insert
as
SET XACT_ABORT ON

Declare @SiteValue VARCHAR(5)
select @SiteValue= OrigSite from inserted

IF (@SiteValue = 'ARA' )
insert into server1.databasename.dbo.tablename
...
ELSE IF (@SiteValue = 'PTS')
insert into server2.databasename.dbo.tablename
....
ELSE IF (@SiteValue = 'DLK')
insert into server3.databasename.dbo.tablename


Thanks for the help,
Benny











This electronic message transmission, including any attachments, contains information from PacifiCare Health Systems Inc. which may
be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not
the intended recipient, be aware that any disclosure, copying, distribution or use of the contents of this information is
prohibited.  

If you have received this electronic transmission in error, please notify the sender immediately by a "reply to sender only" message
and destroy all electronic and hard copies of the communication, including attachments.