Run a console application from SQL Server after table update trigger asynchronously?

sql sql-server xp-cmdshell
kds6253 picture kds6253 · Dec 27, 2011 · Viewed 8.3k times · Source

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.

Answer

Andriy M picture Andriy M · Dec 27, 2011

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.

Related questions

How to delete files on the directory via MS SQL Server

I am trying to delete a file from a directory inside windows using the following query, exec xp_cmdshell 'del "C:\root\sfd_devtracker\'+@deletefile + '"'; When i execute this command it gives the following error, Incorrect syntax …

Read More
SQL xp_cmdshell copy files between servers

I am trying to move all .zip in a specific folder to another folder. the source folder is located on another server, currently i am using EXECUTE xp_cmdshell 'copy \\server1\e$\ETL\*.zip \\server2\e$\ETL\' GO Which …

Read More
How do I check if a directory exists using SQL Server?

I know this command will create a directory: EXEC master.sys.xp_create_subdir 'C:\testing\' But how do I check whether 'C:\testing\' exists? IF EXISTS(...

Read More