6

I am having trouble with finding the best way to solve my issue, please keep in mind I am open to better ways of going about this task.

What I need to do is, after a row's value in my table is updated, I need to use 2 fields from that table as parameters for a console application. Right now I can accomplish this by setting a trigger on the table and then using xp_cmdshell to run the application with the parameters. However I need to do this asynchronously so my stored procedure doesn't hang while it waits for the console application to finish.

Maybe I am going about this the wrong way.

I'm using SQL Server 2008

EDIT - The answer by Andriy M seems to be the best currently but as stated in the comments I need a way to make this happen "Instantly". Is it possible to call a job from a SP or a Trigger? or maybe another way to achieve a similar result?

thanks for the help everyone.

EDIT - I choose he answer below because it helped me the most come to a better solution. What i end up doing was create a job that just queries my table against another which keeps track of updated rows. then when i have the rows i need to update i use xp_cmdshell to run my application with the specified parameters. this solution appears to be working smoothly so far.

kds6253
  • 835
  • 1
  • 12
  • 17

4 Answers4

5

There is another disadvantage to running your application directly from the trigger. It has to do with the fact that generally there can be more than one row updated. To account for that in your trigger, you'd probably have to organise a loop over the updated rows and run the application for each individually. Cursors are typically considered as a last resort, and those in a trigger even more so.

In a situation like this I would most probably consider creating a SQL Agent job which would read the updated values from a dedicated table populated by a trigger. The job would still have to use a cursor, I think, but your trigger wouldn't, and the main point is, running the application from the job wouldn't stop your main working process.

Andriy M
  • 76,112
  • 17
  • 94
  • 154
  • This seems like the best solution. The trigger calls the job which should query the table and get the information for the parameters and then run my app.exe from xp_cmdshell within the job. – kds6253 Dec 27 '11 at 19:37
  • Does anyone see any problems with this logic or possible improvements? – kds6253 Dec 27 '11 at 19:37
  • @kds6253: I am not very proficient in dealing with SQL jobs, in particular I've never heard about invoking jobs from triggers. I was only thinking about a trigger that would merely populate a certain table, nothing more. The job I mentioned would run on a schedule and poll the table. After encountering new rows it would start invoking the application for every row, one after another. If a trigger can *directly* start the job, I would see that as a nice bonus. – Andriy M Dec 27 '11 at 20:16
  • I see you point, That will be an issue as my useres would like the action to happen "instantly" after update. It doesnt seem practical to have a job running basically all the time. – kds6253 Dec 27 '11 at 20:27
  • @kds6253: SQL Agent is a separate Windows service and, consequently, a separate process. Polling a table, even if done every minute, doesn't seem a big deal to me, but you know your environment better than I do, so you may well have serious reasons to object against a solution I've suggested. Maybe you should withdraw your accept then and, if you are unsure, give this idea a try, then decide whether my answer worth accepting ultimately. That would only be fair as well as useful for others in the long run. – Andriy M Dec 27 '11 at 20:50
  • 1
    If he's running `xp_cmdshell` from a trigger, the presence of a cursor in the same trigger is the least of his worries. – mwigdahl Dec 27 '11 at 22:21
  • Another possibility is to use the Service Broker to trigger the CmdExec, that way it's ALMOST instant, and the process still occurs asynchronously from the insert/update/delete – Roger Willcocks Jun 25 '15 at 23:12
  • @RogerWillcocks That's along the lines of [Ben Thul's answer](http://stackoverflow.com/a/8650386/297408), I believe. – Andriy M Jun 26 '15 at 05:04
  • @Andriy M It is, but interestingly I didn't see that yesterday. – Roger Willcocks Jun 27 '15 at 06:36
1

In your trigger, put a message onto a Service Broker queue for each row that got updated. Write a stored procedure that processes messages off of the queue. Set the stored procedure as the activation stored procedure for the queue.

Ben Thul
  • 31,080
  • 4
  • 45
  • 68
0

I think you should develop an extended stored procedure (DLL) instead of calling a console application using xp_cmd_shell.

Baatar
  • 221
  • 2
  • 9
0

I would suggest using a CLR procedure, as it gives you much more control over the process. But you can do this with xp_cmdshell.

To do this you can write a batch file that you will invoke with xp_cmdshell. Inside the batch file, kick off the console application with the proper parameters using the START command. This will fire off your process asynchronously. The batch file, and the xp_cmdshell invocation, will return immediately.

mwigdahl
  • 16,268
  • 7
  • 50
  • 64
  • A full example of writing and registering a CLR procedure is more than I can do here. There are great examples online. If the `START` trick isn't working you could also try the `AT` program. This can be used to schedule a job to execute in the future. If you set it up for, say, a second or two in the future it should work. I stress, though, that any of these `xp_cmdshell` approaches would be a hack. You should really look at the fundamental design of the issue if you can. – mwigdahl Dec 27 '11 at 19:38