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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-08-19 : 07:46:25
|
| shanly writes "I tried to link two sql server 2000 edition, MS SQL Server 2000 standard edition on windows 2003 server standard edition and MS Desktop edition 2000 on windows XP OS using linked server by Microsoft OLE DB Provider, also direct SQL Server option. I configure the liked server on MS SQL Server 2000 Standard Edition. Everything worked perfect. I can see the remote server tables in MS SQL Server 2000 Standard Edition as well as I can select data from remote server,i can insert data from sqlserver 2000 standard edition to desktop edition also the stored procedure also work fine. But I need to run a trigger on MS Sqlserver 2000 Standard Editin to synchronize the data. I can create the trigger but when i tried to insert the data it shows me a error shown below..Created trigger is... create trigger triggername on tablefor insertasset XACT_ABORT onselect * from openquery(linkedserver,'select * from username.tablename') select * from insertedgo gives this errorServer: Msg 7391, Level 16, State 1, Procedure tt, Line 6The 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].THE MSDTC services is running on both servers. please help me in this. I am stuck with this.." |
|
|
Thrasymachus
Constraint Violating Yak Guru
483 Posts |
Posted - 2005-08-19 : 09:29:23
|
| First I do not see how your trigger accomplishes what you are looking for. This will only return a result set. It will not move data to another server.Second, the BOL description for the error is "This error can occur while processing an INSERT, UPDATE, or DELETE statement inside an explicit or implicit transaction. This indicates that the OLE DB provider does not support distributed transactions, which is needed for data modification statements inside an explicit or implicit transaction. A data modification statement can be executed against such a provider only in the case where the statement is a transaction by itself."Can you fire your select statement directly over the linked server outside of the trigger. If so, you may want to read the last sentence of the error message.Another issue may be permissions. Have you done any sp_addlinkedsrvlogin's.====================================================Regards,Sean RoussyThank you, drive through |
 |
|
|
|
|
|
|
|
Administration