April 11, 2006 at 9:05 am
The companies system of record is a 1500+ table DB in 6.0 compliant mode on a SQL 2000 machine (MAS 500 is the application if it helps). I want to mirror about 30 tables to another DB on another computer. (Not keen on replication because we only want to mirror a few tables and maybe write to/alter the mirrored table) Much of the DB is managed with triggers so my plan was to commit the transaction and just before closing, delete from linked server where primary key in inserted.primarykey and insert into linked server from inserted. In order to save the trigger, we turn on ansi_nulls and ansi_warnings on the trigger itself when we save it. The trigger saves just fine. Now, irregardless if I delete the trigger and recreate with ansi_nulls and ansi_warnigns or just run an update statement, the applicaiton throws the ansi_nulls error even though the trigger was saved just fine.
I tried changing the system of record DB's Ansi_Nulls and Ansi_warnings ON in the DB settings and the application freezes up w/out verbal errors. I tried changing the target tables in various ways and no luck. I even tried to send to a test SQL 05 db to see if the error remained. It did.
What are we missing? I know a workaround is to cursor on inserted (or a temp-table while-oop, but on two or three record updates, i don’t know if its worth creating a temp table vs a cursor), and then I can send the primary keys to a procedure on the linked server to pull the data over, no problems there. But the efficiency of writing the two statements not only is MUCH cleaner but would be much faster, plus some of the primary keys of these tables is four fields, not always integers so it would be one ugly proc to process or one proc per table.
Any ideas? Are we stuck with the cursor? And if so, is it worth throwing records into a temp table to loop through 3 or 4 records and not use a cursor?
Thanks for reading this and for any comments or advice.
Jason
April 11, 2006 at 5:16 pm
Do you have triggers on targeted tables as well?
You cannot change ANSI_NULLS settings on run time. You need to create trigger with proper ANSI_NULLS set.
_____________
Code for TallyGenerator
April 16, 2006 at 3:53 pm
No, there are no triggers on the targeted tabled. The triggers were created with ANSI_NULLS set (otherwise it wouldn't even save them). But when I insert/update through the program, I get the ANSI_NULLS error.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply