September 9, 2009 at 11:08 am
I have a test environment where I need to restore a database and then restart the application
I can start my app succesfully with EXEC master..xp_cmdShell 'C:\startup.bat' but I can't move on to other tsql statements since QA ( I am in a MSSQL 2000 environment) just reports 'executing query batch...' in the status bar.
The batch is vendor supplied so i'd rather not mess with it.
Is there any way I can proceed here?
thanks,
Emily
September 9, 2009 at 12:30 pm
There is no way to tell, the information provided allows for no conclusions since we don't know anything about what startup.bat or the script you are talking about does.
Sorry.
CEWII
September 9, 2009 at 12:55 pm
Elliott W (9/9/2009)
Sorry.
Wow thanks for the helpful reply. Are you the the designated spokesperson for the forum? I am looking for tsql control flow commands which would allow my tsql script to proceed. If such commands exist it doesn't matter what the the batch or my script does.
September 9, 2009 at 1:04 pm
emily (9/9/2009)
Elliott W (9/9/2009)
There is no way to tell, the information provided allows for no conclusions since we don't know anything about what startup.bat or the script you are talking about does.Sorry.
Wow thanks for the helpful reply. Are you the the designated spokesperson for the forum? I am looking for tsql control flow commands which would allow my tsql script to proceed. If such commands exist it doesn't matter what the the batch or my script does.
Why don't you drop the sarcasm, it doesn't motivate anyone to help you, and no I am not the designated spokeman, I'm just a guy who answers a lot of questions for people who ask for help. And when they don't provide enough information to make any determinations they get told that too. Your original question even with the additional comments about flow control is not particularly clear. Should you post again please clearly state your question, the more detail the better.
I can start my app succesfully with EXEC master..xp_cmdShell 'C:\startup.bat' but I can't move on to other tsql statements since QA ( I am in a MSSQL 2000 environment) just reports 'executing query batch...' in the status bar.
1. What other statements?
2. What is the determiner to move on?
3. Is this all in a single script that you want to execute?
There is flow control using IF
IF ( @SomePreviouslyDeclaredVariable = 1 )
BEGIN
-- Do some more work here
END
GO
CEWII
September 9, 2009 at 1:21 pm
OK Sarcasm dropped. Thanks for helping.
>>1. What other statements?
Insert and updates
>>2. What is the determiner to move on?
Simply that the xp_cmdShell command was executed
<<3. Is this all in a single script that you want to execute?
Single
--My sample script
EXEC master..xp_cmdShell 'C:\startup.bat'
--Then do the following UPDATE command even though the previous command didnt return anything
UPDATE.....
September 9, 2009 at 1:30 pm
emily (9/9/2009)
OK Sarcasm dropped. Thanks for helping.>>1. What other statements?
Insert and updates
>>2. What is the determiner to move on?
Simply that the xp_cmdShell command was executed
<<3. Is this all in a single script that you want to execute?
Single
--My sample script
EXEC master..xp_cmdShell 'C:\startup.bat'
--Then do the following UPDATE command even though the previous command didnt return anything
UPDATE.....
By default it should drop to the next statement when the xp_cmdshell completes. What that lead me to believe is that xp_cmdshell is NOT completing. Does it EVER finish?
Are there any commands in that bat file that might be expecting user input like "Are you sure (y/n)", that is one of the most common reasons that xp_cmdshell gets hung up.
How long should that batch file take to run? Do you ever get any output in the results window from the xp_cmdshell?
CEWII
September 9, 2009 at 2:01 pm
>>By default it should drop to the next statement when the xp_cmdshell completes. What that lead me to believe is that xp_cmdshell is NOT completing. Does it EVER finish?
No, it starts an application that runs continuosly.
>>Are there any commands in that bat file that might be expecting user input like "Are you sure (y/n)", that is one of the most common reasons that xp_cmdshell gets hung up.
There is no user interaction.
>>How long should that batch file take to run?
Until the application closes.
>>Do you ever get any output in the results window from the xp_cmdshell?
No
September 9, 2009 at 2:36 pm
Ok, then you have a problem. Because xp_cmdshell never returns the script can NOT go on to the next items until it comes back.
The statements that follow the xp_cmdshell, do you want them to run WHILE its running or after it HAS run.
If after then there should be no problem, as soon as it completed it will go on. But if during you are talking about something more asynchronous, and more tricky. The easiest was to do that is for a script or SQL Agent job to call another job since the job start is asynchronous. How this works in that you have one job that does the start and another job that runs the other commands. You have either a script or another job start the execution of the first job and when it comes back to start the execution of the second. since the start is asynchronous SQL Agent is now in charge of the jobs. If Job 2 needs to wait a bit before execution you can add either a job step or in the calling script a WAITFOR DELAY command.
CEWII
September 10, 2009 at 8:47 am
Thanks, I'll look at the SQL server agent options.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply