Unanswered: sql 6.5 triggers to write to sql 2k database
hi sql gurus,
i have run into this problem where i have to use triggers residing on
ms sql 6.5 database to updqte/insert table values on sql 2k database. i tried to use remote servers(only thing that 6.5 supports), and remote
servers can support only running remote stored procedures. Is there a
way i can set up a linked server from 6.5 to 2000? any other suggestions
as to how to achieve this, wil really help..
6.5 doesn't support linked servers so I think the only method would be to creatre the remote server and an SP on the 2000 side.
Personally I think this was a good thing as it made people think about the boundaries between servers and the consequences of interfacing between them - you get a lot of developers now thinking that you can use a linked server just like another database on the local server.
You could create a SP on the 2000 server which receives a sql string to execute - the trigger could then just build the string to call it.
I would do this by inserting into a staging table(s) on the 6.5 server, transferring the data to the 2000 server (by a scheduled task on the 2000 server) and actioning it there. In this way if the connection to the 2000 server gets lost the 6.5 one still works, the 2000 will catch up automatically when it comes back and you get a record in the staging tables of what has happenned.