Can a trigger update a different MS SQL server/version?

[mbrickley]

Is it possibe to set up a trigger in SQL Server 2000 that will update a table on another server that is in a SQL Server 6.5 database? If so, what is the procedure/syntax?

Thanks

 

[NoCoolHandle]

There are a number of options...

Set up the 6.5 box as a linked server
Use OA_Create to have a com object do it
Use the SQL openrowset function
Buffer the changes in a table and use a scheduled DTS package


All solutions will have possible +'s and -'s
THe last option will probably have less of an impact on the current transaction, but will have the worst latency..

My 2c linked servers with early versions of SQL tend to run slow...

COM Objects have there own set of problems and might cause some latency/concurency problems and impact the number of current transactions...

The big question I would ask is how quickly do you want the data to change on the 6.5 box. DOes it need to be real time or real enough time??


Rob

 

[mbrickley]

"real-enough" time is sufficient. I have linked the 6.5 server and tried to use servername.databasename.tablename in the trigger but can't get it to work. Is it not possible to do it in a trigger?

Thanks

 

[osjohnm]

Hi

Did you attempt to create the trigger in Enterprise Manager?
If so then look at the following thread where I showed someone else how to get around this. This problem applies to procedures, triggers and views that use linked servers.

thread183-503708

Here is a link to a Microsoft support article about this problem.

http://support.microsoft.com/default.aspx?scid=kb;en-us;296769

I would also recommend that you run the instcat.sql script mentioned in BOL that allows OLE DB connections between 6.5 and 2000. This is under "linked servers" or "adding a linked server" in BOL.

Hope this helps.

John