Update another SQL Server

[jamey]

Is it possible to update a table on one SQL Server based on an update of a table on another SQL Server?

Using SQL Server only.

Both are on the same domain.

 

[tlbroadbent]

If you are using SQL 7 or higher you can add a linked server in the first server. Create an update trigger on the table in the first server. Have the trigger update the table on the linked server.

Create Trigger On table1
For Update As

Update server2.dbbase2.dbo.table2
Set Col1=i.ColA, Col2=i.ColD, ...
From server2.dbbase2.dbo.table2 As t
Inner Join inserted As i
On d.keycolD=i.keycolI

I've not used a trigger to update a linked server but believe it should work. Let me know if it does. Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[jamey]

Thanks for your answer. However I was not able to get the trigger to work on a linked server. I suspect it is my linked server. How can I tell if my linked server was registared properly?

 

[tlbroadbent]

Try running a select statemnt against a table in a database on the linked server. Confirm that security is properly set up and the login used for linking has necessary permissions on the linked server. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.