call an exe from a stored proc 1

[ktucci]

does anyone know the best way if any to call an exe(notepad.exe) from a stored proc on sql server 2000...

thanks in advance

keith

 

[williamu]

Use xp_cmdshell but to execute commands like this, but I wouldn't recommend it, since control won't return to the procedure until the command that was called is finished.

And if you run notepad on a server, how will the client know that it's running if they can't see the server!

Approach with caution.


William.

 

[ktucci]

my reasoning was more along the way of calling an exe that is a server based application that needs to execute if a specific query returns any records

mi was thinking building a job that ran a stored proc that returned a recordset, and if the recordset is greater than 0 then execute the executable

does this make any sense

thanks

keith

 

[williamu]

Yes, oddly enough it does!

With that said xp_cmdshell is what you want then.

William
Software Engineer

 

[ktucci]

one more question...if my job calls a stored proc that calls xp_cmdshell and the it runs the exec and the exec make launch and run for a hour, will the job continue to run its schedule even though the the executable is still running, and assume the job runs a few as every few secs if possible or every minute

thanks for the help

 

[williamu]

Sorry I don't quite follow. If you are going to run an .exe that will possibly run for 1 hour+ then why would you schedule the job every few seconds?

Depending on what your exe does you may need to ensure that there is only one instanace of it running at any one time.

William
Software Engineer

 

[ktucci]

assume the exe can run more then one instance, for that matter the exe will be a dll...the checking every few seconds or every minute is simply looking for an updated record in this table...upon finding this updated record it needs to run this exe or dll...but the job needs to keep checking for updated records while the first instance of the exe or dll is still running

currently i have a exe that runs a timer and runs a stored proc every 10 secs upon finding a match it runs a dll that possibly could run for an hour and the timer meanwhile keeps running that stored proc checking for updated records...my main goal was to move the time piece into sql server and still call the exe or dll that will actually do all of the processing

thanks

keith

 

[williamu]

Ok then if I understand this correctly then the smartest (IMHO) would be to use an update trigger on the table that you currently use the SP on, that way you can dump the timer altogether.

Since I don't know what your DLL does you may need to be aware of possible contentions caused when you call the DLL code on subsequent updates. But this depends on A.) what the DLL does and B.) how the DLL is constructed.




William
Software Engineer

 

[ktucci]

that was exactly what i was thinking regarding update triggers...

can update triggers be configured to only wait for an update on specific field with one specific value

eg: i only want the trigger to execute when col1 in tbl_test is changed to 3...col1 can be changed to 1, 2, or 4 as well but i would want those changes to be ignored...

sorry for the simple question, i just have not worked with update triggers at all

thanks

keith

 

[williamu]

Yes.

William
Software Engineer

 

[ktucci]

thanks for your help...i think you put me on the right track...

thanks again

keith