Sql server 20000 linked server

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.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Sql server 20000 linked server

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-08-18 : 07:59:53

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 table
for insert
as
set XACT_ABORT on
select * from openquery(linkedserver,'select * from username.tablename') select * from inserted
go

gives this error

Server: Msg 7391, Level 16, State 1, Procedure tt, Line 6
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].

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-18 : 08:21:26

I think you want some more like so...

CREATE TRIGGER MyTrigger ON MyLocalTable
AFTER INSERT
AS
INSERT INTO LinkedServerName.DatabaseName.Owner.RemoteTableNAme(Column1,Column2)
SELECT Column1,Column2
FROM INSERTED

etc....

====================================================
Regards,
Sean Roussy

Thank you, drive through

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources