June 18, 2002 at 1:24 pm
I have a question on trigger and linked servers. I have a linked server created and a link defined between two servers. The trigger (for update, delete or insert) gets fired and syncs up the other server database tables.
My issue is that if the other server (server 2) is down for some reason, the trigger fails and hence the insert statement/update statement/the delete statements to the server 1 also fails.
How can I trap the error and even if the server 2 is down, bypass it and allow the insert in server 1 database tables to succeed ?
I have run the delete statement by itself but it does not return and error message (through @@error statement)....the only thing it returns is:
Server: Msg 17, Level 16, State 1, Line 1
SQL Server does not exist or access denied.
Please advise.
Thanks,
June 18, 2002 at 3:25 pm
And the reason why it will not return an error message in this case is that at compile time itself, MSDTC notices that the destination server does not exist and hence never even goes to @@error because of that.
The work-around that I had was to check through xp_cmdshell and osql whether the linked server is available or not. If not, then just return and exit the trigger indicating success...
If you guys have a better way, please do let me know.
Thanks
Rahul Sharma
Senior Database Administrator
Manhattan Associates
Atlanta, GA
June 18, 2002 at 3:32 pm
I've had this same issue on my list for a couple weeks hoping to get to it. Best work around I've though of so far is to use replication - either make the linked server a subscriber which gives you some tolerance with regard to it being offline, or if that seems awkward, replicate the table from the linked server to the main server and use queued updates to push the changes back.
Andy
June 18, 2002 at 4:01 pm
The problem in this case though is that these are MSDE databases and is being used for the caching of some of the tables on the web server....I have configured replication many times with sql server but never with MSDE and wanted to stay away from it because of the connections and performance issues in MSDE.
Have implemented poor-man's replication using the work-around that I mentioned above.
Thanks Andy.
Rahul Sharma
Senior Database Administrator
Manhattan Associates
Atlanta, GA
June 18, 2002 at 5:13 pm
Only problem I see with that is you have to keep track of which changes got propagated and which didnt. Think replication would really be that bad? You're limited in connections anyway.
Andy
June 19, 2002 at 6:52 am
I have merge replication running on 25 MSDE systems (2.0 or 2000 version), synchronizing with one central publisher. It works great. Performance is fine, but these are all single user systems (field rep notebooks). If exceeding 5 connections is a concern, why not upgrade it to a standard installation?
Jay Madren
June 19, 2002 at 6:57 am
Sorry about the duplicate posts. My mouse finger got a little trigger happy. (-;
Jay Madren
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply