Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dodi
Starting Member
9 Posts |
 Posted - 2003-11-12 : 12:08:24
|
| Hi allI wrote a Store Proc that needs to run an exe in vb 6.0 with a command line (Int,datetime,datetime)CREATE PROCEDURE FromRawToStation @@nType int=1, @@nStation int=0,@@dStartdate datetime ='00:00:00',@@dStopdate datetime ='00:00:00' as EXEC master..xp_cmdshell 'c:\calcavg\FromRawToStation.exe @@nType,@@nStation,@@dStartdate,@@dStopdate'when i am running the store proc the vb prog freezess what's wron with the Store procthanks |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-11-12 : 12:34:49
|
| There's nothing wrong with the stored procedure, it's probably your VB app. You cannot run an application that requires user input via xp_cmdshell. The program must terminate automatically or it will hang SQL Server. Notepad, Word, Excel are out. Command-line utilities like copy, bcp, dtsrun are OK. If your VB program runs as a form, that is out. It must be written to run from a Sub Main() construct that exits without user intervention. |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-11-12 : 12:37:24
|
| First, use local variable @x int, ect, not @@x int...second can you run the xp_cmdshell from QA? Does it run?third, is the vb a gui, or a batch vb...if it's a gui it won't run and most likley blow up sql server....Brett8-)EDIT: SnipedAren't you at PASS? |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-11-12 : 13:24:32
|
Just another note on this Dodi. I've done some similar things in the past where a stored proc or trigger called a EXE. It worked great, until it had one little issue, and like Brett said, it stopped a good portion of our SQL based application. From the BOL:quote:
xp_cmdshell operates synchronously. Control is not returned until the command shell command completes.
The app had one issue, and the statement was hung, blocking other queries from running. You might want to think about moving to a "queue based" system where you drop a record in some sort of processing queue table, and your app polls that table periodically and does what it needs to do. Not quite as effecient, but it doesn't affect SQL server as much as the app stopping after a xp_cmdshell.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-11-12 : 13:28:15
|
| Yeah,good point....what are you trying to do with vb that can't be done in t-sql?Isn't much you can't do.....I mean getting data in and out, but then there bcp...never had a problem with it...Brett8-) |
|
|
|
dodi
Starting Member
9 Posts |
Posted - 2003-11-13 : 01:17:42
|
| What i am going to try is calling a different Exe that he will only call the working ExeHope that will solve my problem |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2003-11-13 : 05:47:09
|
| if SQL calling programa is a problemthen SQL calling programb calling programa is not going to solve it.do try to see if you can use a different approach (as advised above) to solving your business problem. |
|
|
|
|
|
|
|
|
Running Exe with command line