September 21, 2005 at 10:54 am
I am trying to run a batch file using a stored procedure. Here is the stored procedure:
CREATE PROCEDURE reload_mission @mid varchar(10) AS
SET NOCOUNT ON
declare @cmd varchar(200)
IF @mid is not null or @mid <> ''
BEGIN
BEGIN
SET @cmd = 'c:\ReloadMission\Rmission.bat ' + @mid
PRINT @cmd
EXEC master..xp_cmdshell @cmd
END
IF @@ERROR is NULL
BEGIN
PRINT 'Mission' + @mid + 'was successfully reloaded'
RETURN(0)
END
ELSE
BEGIN
PRINT 'The mission did not load'
RETURN(-1)
END
END
GO
and the batch file (Rmission.bat):
::
@echo off
cls
echo.
if "%1"=="" goto usage
goto copy
:usage
echo Must enter a valid Mission Name Parameter (e.g. rmission.bat BKNL05-123)...
pause
goto finish
:copy
echo.
cls
echo You entered mission ID %1. If you see any errors below, please check
echo the existence of the files manually or check the mission name.
echo.
echo.
xcopy /Q "c:\Inetpub\ftproot\adam\MisBack\Bais\%1.AIS" "c:\Inetpub\ftproot\adam\Mission"
xcopy /Q "c:\Inetpub\ftproot\adam\MisBack\Bcps\%1.CPS" "c:\Inetpub\ftproot\adam\Mission"
xcopy /Q "c:\Inetpub\ftproot\adam\MisBack\Bdet\%1.DET" "c:\Inetpub\ftproot\adam\Mission"
xcopy /Q "c:\Inetpub\ftproot\adam\MisBack\Bdtl\%1.DTL" "c:\Inetpub\ftproot\adam\Mission"
xcopy /Q "c:\Inetpub\ftproot\adam\MisBack\Bedt\%1.EDT" "c:\Inetpub\ftproot\adam\Mission"
xcopy /Q "c:\Inetpub\ftproot\adam\MisBack\Bflt\%1.FLT" "c:\Inetpub\ftproot\adam\Mission"
xcopy /Q "c:\Inetpub\ftproot\adam\MisBack\Bmis\%1.MIS" "c:\Inetpub\ftproot\adam\Mission"
xcopy /Q "c:\Inetpub\ftproot\adam\MisBack\Bnav\%1.NAV" "c:\Inetpub\ftproot\adam\Mission"
echo.
echo.
:finish
::
It seems as though the files are not being copied to the appropriate directory. I ran the stored procedure using exec reload_mission 'BKSF05-254' (which it should accept) both on the server and locally but it would not copy the files. I also printed out the value of @cmd in the stored procedure and it was c:\ReloadMission\Rmission.bat BKSF05-254. If you take that same line and type at the command prompt, then everything works fine. What could the problem be? Any help would be greatly appreciated!!
September 21, 2005 at 11:04 am
Is the C drive on the database server ? All reference to relative paths are from the point of view of the SQL Server not from where the stored procedure is invoked.
Is the bat file on the database server ?
Are you connected to SQL Server with an account that has system adminstrator rights ? Only a system administrator has rights to execute xp_cmdshell.
SQL = Scarcely Qualifies as a Language
September 21, 2005 at 11:04 am
when you run it manually, you are running it on your machine, otherwise on the server!
What happens if you run it from the server's command line?
September 21, 2005 at 11:14 am
Sara, what exactly do you mean by run "it" from the command line? If you mean the line of code,
c:\ReloadMission\Rmission.bat BKSF05-254 , then as stated previously, it does work from the server's command line.
Carl, glad to provide the additional information. Yes, the C drive is on the database server. And the stored procedure is on the same server. Yes, the bat file is on the same server as well. I am using the local administrator account to run the stored procedure. I have other stored procedures that do basically the same thing and they all work fine. I even tried logging into the server using the domain username and password and it still won't work.
Any further suggestions would be greatly appreciated.
September 22, 2005 at 4:02 am
try:
SET @cmd = 'c:\ReloadMission\Rmission.bat ' + @mid + ' >> c:\miss.log'
or
SET @cmd = 'c:\ReloadMission\Rmission.bat ' + @mid + ' >> c:\miss.log 2>&1'
and look at c:\miss.log on the server...
karl
Best regards
karl
September 22, 2005 at 6:51 am
You entered mission ID BKSF05-252. If you see any errors below, please check
the existence of the files manually or check the mission name.
September 22, 2005 at 6:56 am
Try adding backslash to the end of the xcopy to tell the system the output refers to a directory
eg
xcopy /Q "c:\Inetpub\ftproot\adam\MisBack\Bais\%1.AIS" "c:\Inetpub\ftproot\adam\Mission"
to
xcopy /Q "c:\Inetpub\ftproot\adam\MisBack\Bais\%1.AIS" "c:\Inetpub\ftproot\adam\Mission\"
Far away is close at hand in the images of elsewhere.
Anon.
September 22, 2005 at 7:02 am
try adding the path to xcopy, and no /Q option
c:\winnt\system32\xcopy.exe "c:\Inetpub\ftproot\adam\MisBack\Bais\%1.AIS" "c:\Inetpub\ftproot\adam\Mission"
Best regards
karl
September 22, 2005 at 8:55 am
Tried both Dave and Karl's suggestions and neither worked. Still getting the same entry in the miss.log file. Any further suggestions?
September 22, 2005 at 9:03 am
Looks like your script is producing a prompt which cannot be catered for when using xp_cmdshell and will give you the symtoms described
Do the files already exist in the destination directories if so xcopy will produce a prompt, try assing /Y parameter to the xcopy.
Far away is close at hand in the images of elsewhere.
Anon.
September 22, 2005 at 9:26 am
The files don't already exist in the destination directory. I tried using the /Y switch:
C:\winnt\system32\xcopy.exe /Y "c:\Inetpub\ftproot\adam\MisBack\Bais\%1.AIS" "c:\Inetpub\ftproot\adam\Mission\"
and that did not work. When I run the batch file from the command line on the server, the copy takes place. I don't understand why the call to xcopy (or a copy) won't work.
September 22, 2005 at 9:29 am
Is it possible that one or more of the files will exist in the target directory already? If so then XCOPY will prompt you to overwrite the file in the batch. You can get around this by changing your xcopy command to look something like this:
echo Y | xcopy /Q "c:\Inetpub\ftproot\adam\MisBack\Bais\%1.AIS" "c:\Inetpub\ftproot\adam\Mission"
The Y from the echo command will be piped into XCOPYs standard input where it will be consumed as the reply to the prompt. If there is no pre existing file in the target directory it gets thrown away when the XCOPY finishes so you don't have to worry about extra stuff in your keyboard buffer. I use this all the time in my batch files.
I'd also recommend that you always log the processing from these batch files as Karl suggested. You can learn a lot from having the messages and errors logged. This cool little bit is another batch file gem that is worth keeping handy "2>&1" as it will put the errors in the file right after the command that generated them.
SET @cmd = 'c:\ReloadMission\Rmission.bat ' + @mid + ' >> c:\miss.log 2>&1'
Bill
September 22, 2005 at 9:31 am
Put
ECHO Finished
after last xcopy and before :Finish label and see if the text 'Finished' appears in the miss.log
Far away is close at hand in the images of elsewhere.
Anon.
September 22, 2005 at 9:49 am
Thanks all. This is what was logged as a result of the previous two suggestions:
You entered mission ID BKSF05-253. If you see any errors below, please check
the existence of the files manually or check the mission name.
File not found - BKSF05-253.CPS
0 File(s) copied
File not found - BKSF05-253.DET
0 File(s) copied
File not found - BKSF05-253.DTL
0 File(s) copied
File not found - BKSF05-253.EDT
0 File(s) copied
File not found - BKSF05-253.MIS
0 File(s) copied
File not found - BKSF05-253.NAV
0 File(s) copied
Finished
But the files do exist in the source directory, I checked and double-checked. Should the file extensions be lower case if thay are on the server? I didn't think that would make a difference. Suggestions?
September 22, 2005 at 9:53 am
Sorry, this is the actual result:
You entered mission ID BKSF05-253. If you see any errors below, please check
the existence of the files manually or check the mission name.
File not found - BKSF05-253.cps
0 File(s) copied
File not found - BKSF05-253.det
0 File(s) copied
File not found - BKSF05-253.dtl
0 File(s) copied
File not found - BKSF05-253.edt
0 File(s) copied
File not found - BKSF05-253.mis
0 File(s) copied
File not found - BKSF05-253.nav
0 File(s) copied
The process tried to write to a nonexistent pipe.
Finished
using:
echo Y | xcopy /Q "c:\Inetpub\ftproot\adam\MisBack\Bcps\%1.cps" "c:\Inetpub\ftproot\adam\Mission\"
echo Y | xcopy /Q "c:\Inetpub\ftproot\adam\MisBack\Bdet\%1.det" "c:\Inetpub\ftproot\adam\Mission\"
echo Y | xcopy /Q "c:\Inetpub\ftproot\adam\MisBack\Bdtl\%1.dtl" "c:\Inetpub\ftproot\adam\Mission\"
echo Y | xcopy /Q "c:\Inetpub\ftproot\adam\MisBack\Bedt\%1.edt" "c:\Inetpub\ftproot\adam\Mission\"
echo Y | xcopy /Q "c:\Inetpub\ftproot\adam\MisBack\Bmis\%1.mis" "c:\Inetpub\ftproot\adam\Mission\"
echo Y | xcopy /Q "c:\Inetpub\ftproot\adam\MisBack\Bnav\%1.nav" "c:\Inetpub\ftproot\adam\Mission\"
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply