June 2, 2006 at 12:14 pm
MSSQL 2000
I have two SQL Servers, Server1 and Server2. Server2 is in a remote location, however shares a constant, high speed network connection. Both servers have a database with table, tblLOGINS.
I created tblLOGINSUMMARIES on Server1. Whether changes are made to tblLOGINS on either server, I want it to immediately update tblLOGINSUMMARIES (on Update and Insert).
I created a Trigger on tblLOGINS on Server1, which works flawlessly. I applied the same trigger on Server2 with respective modifications to reflect the linked server. Thereafter, if changes are made the tblLOGINS on Server2, it throws an error about requiring ANSI_NULLS and ANSI_WARNINGS for the connection. I have in fact set them within the code so I don't know what the problem is.
Before trouble-shooting these errors, I wanted to find out whether this is the preferred method. I know there are other options, such as distributed transactions, but I have not yet worked with those.
tblLOGINSUMMARIES could grow to 200K and will continue to grow fairly slowly. It only contains a dozen or so fields which all are relatively small, under 20 chars. Edits to the tblLOGINS could be 30 per minute, with 1/4 of those coming from the remote location (Server2).
Any direction or help would be greatly appreciated!
June 3, 2006 at 9:36 am
This is an odd behaviour but i think your problem is the ANSI_NULLS and ANSI_WARNINGS setting under which the trigger was created and not the setting as set within the code. Drop your trigger and recreate ensuring you set the the correct settings for he connection which creates the trigger. Strange but that should fix it
You might want to look as using replication rather than triggers to keep these tables in synch especially if the the requirement is likely to get more complex ie more tables.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply