| Author |
Topic |
|
samtoffa
Yak Posting Veteran
60 Posts |
 Posted - 2004-06-23 : 08:25:51
|
| Hi,I have a number of applications originally written in VB6 that perform 'housekeeping' tasks on the server. I would like to know how to call one of these (compiled .exe) files from within a stored procedure AND have the stored procedure wait until the VB application has finished executing BEFORE the rest of the code in the stored procedure continues. I don't want to leave the housekeeping applications running in the background all the time that SQL Server is running.Any help appreciated, and sorry if this is a trivial question!!Sam |
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2004-06-23 : 08:37:22
|
| You can call xp_cmdshell from sql (This executes a dos command).Look it up in books online - Hopefully this helps :)Duane. |
 |
|
|
samtoffa
Yak Posting Veteran
60 Posts |
Posted - 2004-06-23 : 08:46:46
|
| Thanks Duane. But will execution of the stored proc wait until the VB application has finished?If this is in books online then please ignore me....I'll take a better look.Sam |
|
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2004-06-23 : 09:03:57
|
| why dont you test it and see what happens :)Duane. |
|
|
|
samtoffa
Yak Posting Veteran
60 Posts |
Posted - 2004-06-23 : 09:10:48
|
| That's far too sensible!!!!! :-) |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-06-23 : 17:35:01
|
| Yes, it will wait. If your EXE gets hung, it will hang SQL server though. Be very careful and have good error handling in that EXE.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-23 : 17:47:19
|
| What kind of housekeeping does it do? Can you move that code into the stored procedure?Tara |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-06-23 : 18:35:08
|
| I'm with Tara. After my last experience with xp_cmdshell I avoid it at all costs (it locked my server up bigtime).Another solution might be to write a Windows Service (.Net Windows service or VC++). That way, you can have the service perform the action, and then Sleep for some period of time, thus not using the CPU.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
samtoffa
Yak Posting Veteran
60 Posts |
Posted - 2004-06-24 : 07:06:37
|
| The applications mainly just parse large text files (~100MB) and my main reasons for using them were (a)to use existing VB development talent and (b)my ,perhaps wrong,assumption that these small executables had less overheads in terms of system resources. However, Mischael's point about potential system hangs is a very strong argument for re-writing the apps as stored procedures. Thanks all for your input.Sam :-) |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-06-24 : 14:42:17
|
| Depending on what you are doing exactly, the stored Proc way will probably be much faster than the VB6 way.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
|
Controlling Visual Basic Applications