November 13, 2007 at 7:07 am
Using SQL Server 2005
I have two linked servers, server1 and server 2, linked to each other
I have server1.db1.table1 and server2.db2.table2, both with triggers that insert/update each other, based on status columns
server1.db1.table1 has an individual insert and an individual update trigger:
[1A]insert trigger inserts a record in server2.db2.table 2, status 1, which subsequently triggers [2A]
[1B]update trigger fires when status is '1307' (see [2B]) and updates status to '1304', and then selects the data from the tables in server2.db2 updated in [2A] and updates other tables in server1.db2 (same server, different db)
server2.db2.table2 has an insert and an update as well as another update trigger:
[2A]insert and update fires an .exe program designed perform an external process, and then to update other tables in server2.db2. Status is a number between 1-14, but will only fire off when status is 1-13.
[2B] once status reaches 14 (within 40 sec or less), update trigger updates server1.db1.table1 to status '1307', thereby firing off trigger [1B]
The basic order is 1A, 2A, 2B, 1B
Everything works fine as long as I simply update the status in both tables. I get a .Net "Transfer Context" error when I try to implement both the .exe in [2A] and the updating of tables in [1B]
Any ideas anyone? I can get more explicit if need be...
Any comments would be greatly appreciated!!!
Thanks in advance
Robert
November 13, 2007 at 11:06 am
Calling an external EXE inside a trigger is never recommended. I think you may do it in another way, for example, insert a record to a "control" table, which will be scanned by a job every minute, once the job detects your command in the "control" table, the job will start the external EXE.
HTH,
J.Y
November 13, 2007 at 11:17 am
yes...in fact, table 2 is our job table.
What would be the impact of running a job on the server every minute? We run a lot of processes, including decrypt searches, which can tie up various tables for a long time, so we are reluctant to schedule jobs every minute.
November 13, 2007 at 11:57 am
Running job every minute really does not matter as I have seen lots of production systems that have jobs running every minute. However, here the assumption is the job can be finished within one minute interval. Otherwise, it makes no sense.
HTH,
J.Y
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply