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?
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??
"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?
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.
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.
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.