November 16, 2002 at 5:08 pm
I'd like to have a Stored Procedure call an EXE program on the SQL Server. That EXE program will access the SQL Data and edit a row or two on the SQL Server and then quit. How do I call this program, and how do I know when it is done. Can the SP "wait" while polling a row in the database?
Thanks in advance,
Student of SQL and Golf, Master of Neither
Student of SQL and Golf, Master of Neither
November 16, 2002 at 5:23 pm
This is typically done using a mechanism such as xp_cmdshell. The stored procedure will wait until xp_cmdshell completes, because xp_cmdshell does return back a return set.
However, using techniques like xp_cmdshell can represent security risks. By default, xp_cmdshell is restricted to sysadmin access only for these reasons.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
November 16, 2002 at 5:24 pm
The easiest way is to use xp_cmdshell in yuor SP. However launching your app is designed to write it's responses back to the cmd shell it may be created and put in it's own thread, other conditions can cause it to remain type to the cmd sheel until it completes, but usually cmd shell will launch and respond done, but the app is still running in memory. You have to test this. Once cmdshell is done with it your SP considers it complete. If you own the source and it is in C++ or you can convert it, you could make an extended stored procedure that SQL can utilize.
November 17, 2002 at 9:15 pm
I find it easier to have the proc initiate a job that runs the app - ok as long as async completion is what you want. Another alternative is to package as a com dll and run using sp_oa (or the job approach will work here too).
Andy
November 18, 2002 at 6:44 am
quote:
I find it easier to have the proc initiate a job that runs the app - ok as long as async completion is what you want.Andy
The external EXE generates access codes for my customer's products. The SP that will invoke it may be called from inside the company using a program, or outside the company using a web page. What we want is for a SP to cause the EXE file to run in any reliable manner. The customer doesn't want any of the code in the external EXE to be moved.
I could deal with asynch execution (I didn't initially realize I had a choice) and let the user interface programs poll for results.
I'm just looking for a reliable, secure method to get that EXE file run from SQL Server. I don't need result codes passed back in (I'll have to do a read to get all the answers anyway), but it would be nice if the SP could 'hang' waiting for the EXE to be done. Conversely, the SP that calls the EXE could also do the polling, to the user it would look the same.
Any other comments? Thanks again for all your thoughts.
Student of SQL and Golf, Master of Neither
Student of SQL and Golf, Master of Neither
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply