April 27, 2004 at 10:20 am
Hi,
We are in process of moving some of our databases to another server. Have linked server setup to run cross server queries. I am new to this setup. We have cross database triggers which now need to run across server. Can anyone help with what are the settings that need to be done to run distributed queries/cross server triggers to execute successfully. I came to know of SET XACT_ABORT ON, but the update trigger gets hung doing nothing. Any help with regards to cross server triggers or any other method that can be suggested is more then welcome.
If anyone has any other way of have the trigger fuctionality implemented across 2 servers, please let me know. Thanks in advance for your help.
April 27, 2004 at 12:52 pm
If you are trying to get the changes made at your server applied also to the new server, instead of triggers you can use replication.
April 27, 2004 at 1:05 pm
The issue is we have cross database triggers. Since we are moving couple of databases to the other server, we need to be able to have the triggers working. Replication cannot be the solution as there are cascade updates and inserts that go across number of databases. Insert trigger works, but update trigger doesn't. It just gets hung and the process cannot be killed. Just looking if i am missing at SET commands for distributed queries. The update runs fine from QA, but when fired within a trigger it just keeps running doing nothing, and to kill the process we need to restart/reboot the box. Any idea what i might be missing for the updates for not working???
April 27, 2004 at 2:08 pm
- Because your goal is to move the db to a new server and postpone application-adaption, maybe you can consider to move the db's and at the old location only alter all the old stuff (tb/vw/proc) with views /procs that access the remote db's through linked server(s).
- or setup two-way replication if you can.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 28, 2004 at 9:24 am
Have you set up Cross DB Ownership Chaining on your database, this option is turned off in SP3
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply