July 18, 2008 at 7:58 am
We have a JOB that runs every day on certain time, it reads Table data and based on that it updates our Production Server and there are triggers on that Table which updates other servers.
We setup up MS DTC Services to run queries directly (INSERT, UPDATE OR DELETE) from triggers activity.
The Job's Stored procedure when updates our production Table we setup Triggers that updates changes to other servers. Problem occurs when a Linked Server Failed, meaning it becomes un available due to network issue or if its physically down. Now our triggers are failing to accept changes including proudction table. The job that send changes on our production table is giving following error:
Executed as user: NT AUTHORITY\SYSTEM. SQL Server does not exist or access denied. [SQLSTATE 42000] (Error 17) 139641541 [SQLSTATE 01000] (Error 0). The step failed.
I would like to know if there is any way that production Table may grab/accept all changes (INSERT, UPDATE AND DELETE), but if the other servers are not not available it does not matter. if they are available then it work as usual.
If some one has faced this issue
Shamshad Ali.
July 18, 2008 at 8:03 am
First of all it is not a good practice to have code in triggers that depend on services/resources outside the database or databases on the same server for exactly the issue you are facing. In a case like yours the best thing is to log the changes to a table or database on the local server then have a job or windows service that makes those changes on the linked servers.
You should also investigate using replication as well.
If you have to make the changes to the other servers through a trigger you could do something like this:
If Exists(select * from linked_server.database.schema.table)
Begin
--insert data into linked server
End
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 18, 2008 at 10:53 pm
You are saying Correct that its not good practice to write such kind of logic in Triggers, but the reason is i have to write changes at the same time when the local database has any DML activity (INSERT, UPDATE OR DELETE).
Its our primary responsibility, incase the other server is not available, which is not our responsibility, (sending data change is our responsibilty, but keeping other servers up and running is not our responsibility). In this case I would like to get more suggestions if possible.
Your help is really appreciated. Incase physically if the server is not on network how can i skip those
inserts?
is there any way to try ... catch to check the remote sever is live or dead? has some faced this issue before and fixed?
My another problem is the linked server is dead, and I'm trying to update trigger with following code as you said to use
IF EXISTS (select col from linkedserver.database.dbo.table)
BEGIN
MY insert code here
END
But the trigger is checking my query and trying to reach and verify linked server, as it is dead it won't get ALTER TRIGGER command with following message:
Server: Msg 17, Level 16, State 1, Procedure trg_DEL_Schedule, Line 106
SQL Server does not exist or access denied.
I am using SQL Server 2000 SP4....
Please advise.
Thanks
Shamshad Ali.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply