hi all,
I have a trigger on a table that inserts data in a external table, referenced through an OPENDATASOURCE call.

The trigger reads :
CREATE TRIGGER whatever On whatevertable
FOR INSERT
AS
insert OPENDATASOURCE('MSDASQL','DSN=Copy;Uid=x;Pwd=y').g db.dbo.Table1
select blabla from inserted

When running this, the INSERT in the original table whatevertable fails giving me a "Distributed Transaction Error"

I can insert the data using OPENDATASOURCE from the Query Analyser.

Any ideas ?

guido

Any ideas

If the remote table will always be there, it would be best to create a Linked Server for this instead of using OpenDataSource. Has the extra benefit of NOT listing login info in clear code...

--Jeff Moden

So I defined an linked server called "TEST" - it uses the Micorsoft OLE DB for ODBC drivers.

So how do I select the data from this linked table ? Select * from TEST.tablename fails ?

thanks