Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
vk59
Starting Member
38 Posts |
 Posted - 2005-03-09 : 06:33:30
|
| Hi,I have two SQL servers Server A and Server B.In server A I have table Project . Whenever a row is Inserted/Modfied/Deleted in Project table of Server A then a trigger needs to be fired and the same will be reflected in the Project table of Server B.Both tables are of same schema. The trigger is as followsCREATE TRIGGER [TrigOnProject] ON [dbo].[Project] FOR INSERT,DELETE,UPDATEASBEGIN SET XACT_ABORT ON DECLARE @PName VARCHAR(100) DECLARE @PNo INT IF EXISTS (SELECT * FROM Inserted) AND NOT EXISTS (SELECT * FROM Deleted) BEGIN INSERT [ServerB].[EmployeeDB].[dbo].[Project] SELECT Project_Id,Project_Name FROM Inserted END SET XACT_ABORT OFF ENDINSERT INTO dbo.Project(project_id,project_name,project_isongoing)VALUES(250,'Test',1)When we try to execute above insert query on the project table from query analyzer the following error message appeared. The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].Thanks in advance..` |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-03-09 : 06:55:26
|
| Dunno about fixing the Distributed Transaction error, but I'm not sure your trigger is going to do what you expect.Why not just make it a FOR INSERT trigger, instead of using:IF EXISTS (SELECT * FROM Inserted) AND NOT EXISTS (SELECT * FROM Deleted)(Actaully I can't see how this would get out of wack, but its a strange way to perform the logic and I reckon it will lead to maintenance bugs downstream, at the very least)Kristen |
 |
|
|
jhocutt
Constraint Violating Yak Guru
385 Posts |
|
|
|
|
|
|
|
Distributed Transactions in Trigger giving error.