Locking up on Trigger call
-
(Also posted under General)
Hello all.
I've been tasked with creating a flat-file based upon database events that occur in a legacy system.
I've written an application that is a command-line .exe using MFC. Its job is simple. When an insert occurs in a given table, I'm trying to execute the .exe.
Here is my trigger code:
CREATE TRIGGER trg_CallFileBuilder
ON dbo.SomeTable
AFTER UPDATE
AS
EXEC master..xp_cmdshell 'start C:\FileBuilder.exe', NO_OUTPUT
Now, the executable (FileBuilder.exe) does a query on SomeTable. I don't care what's updated. I simply want the FileBuilder to do its thing.
But it locks up when I 'update SomeTable', causing me to have to start and stop the SQL service. When I run FileBuilder.exe by itself (say from the command line), it runs and produces results as expected.
Can someone tell this SQL idiot (me) what I'm doing wrong?
Thanks!
-
why do u need to use trigger to call an exe?
what happens when exe fails.(your DB is not updated as trigger failed)
If your exe queries the same table that it updated/inserted u will have a dead lock issue.
why can't u run filebuilder on it own and that can check for updates/inserts?
-
Hello, thank you for your fast response.
1) I want the flatfiles to contain the most-current-possible information from the legacy system. I can, but would rather not, have the FileBuilder.exe poll for new data. Long story there.
2) SQL simply locks up.
3) Indeed, the .exe calls a stored procedure that queries the same table that was updated. If the extended sproc 'xp_cmdshell' executes a start, I was hoping that the executable would have a different process space. If this is an issue, I can write my own extended sproc and insure that a new process space is created for FileBuilder.exe.
-
Welcome to the club!
Have been there, so can probably explain.
Trigger is a transaction. It waits utill all its commands completed to commit or, if something failed, rollback.
One of the statements inside trigger starts a new process - C:\FileBuilder.exe from cmd shell. This process waits for the trigger to finish and release locks on the table to proceed.
And Trigger (remember?) still waits for that process to complete to either commit or rollback transaction.
I would recomment not to start new processes from triggers.
But if you insist on this one you must direct new data to another table (kind of temporary storage, but not # table!), than (after FileBuilder.exe has completed its job) you may access that table from trigger, work out data and truncate that temporary storage table.
_____________
Code for TallyGenerator -
Nicasio,
In order for the EXE to release it's grip, it must close connections, destroy the connections, AND successfully exit the command processor window you've invoked. When you run it as a standalone from a CMD window, does it automatically close the window at completion? If not, that's your problem. And, don't look for the window while you're trying to run it from SQL... you won't see it except maybe in the Task Mangler... er... uh... Task Manager
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply