Click here to Skip to main content
15,445,242 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I'm having a little problem executing a batch file in SQL Server 2008.
I've created a job and I want to zip a file using 7zip and then move that zip to an FTP server. I have a batch file to do this for me, but I want to run it in a job.

So first I create a job and then I create a step. The type of the step goes to Operating System (CmdExec). It runs as the SQL Server Agent Service Account (which is admin, same as my login). And then in the Command box I put my command text.

And it works great, except that nothing happens... So it's going wrong, but it's not giving me an exception.

When I run my batch file directly from explorer everything works fine. If I set up my job to do the following:
cmd.exe /c "MyFile.cmd"
everything goes fine.

Why can't I just put the contents of my file inside the Command box in my step and execute it (after all, that's what it does when I Open a file from that window too)?
Thanks.
Posted
Comments
Richard Deeming 24-Feb-15 10:17am     CRLF
What's the contents of the batch file? There should be an option on the job step to append the step output to the log. That would at least let you see any errors generated by the step.
Sander Rossel 24-Feb-15 11:03am     CRLF
That would be useful! How does that work? The file currently looks like this: %BACKUP_DIR%7za.exe a %BACKUP_DIR%zips\%DATABASE_BACKUP_NAME%.zip %BACKUP_DIR%%DATABASE_BACKUP_NAME% %BACKUP_DIR%winscp.exe /script=%BACKUP_DIR%winscp-synchronize-script.txt /parameter %BACKUP_DIR%zips\ %SFTP_ROOTDIR%/SQLServer The variables are simply set at the top of the file. I've never worked with this before and I simply want to zip and move a database backup to an FTP. I'm currently just calling the script from my job, but I'm still curious why I can't just use those commands directly.
Richard Deeming 24-Feb-15 11:10am     CRLF
The "advanced" page of the job step properties has options to log to a file, log to a table, or include the output in the job history: Semi-Advanced Logging Options for SQL Server Agent Jobs[^] When you view the history of the job, you can expand to see the details of the individual steps. With the "include in history" option selected, you should then see at least part of the output under the relevant step.
Sergey Alexandrovich Kryukov 24-Feb-15 10:42am     CRLF
In addition to potentially abusive use of a batch in SQL, you also do ridiculous thing cmd.exe /c "MyFile.cmd" instead of "MyFile.cmd" CMD.EXE /c does nothing, it transparently carries out the command. Why? —SA
Sander Rossel 24-Feb-15 10:58am     CRLF
I figured that out. I already deleted that part. I did that because I have no clue how this stuff works and I found it on the internet. If I knew I wouldn't be here ;-)

Please, read my comment to the question.

As per i understand you're trying to run batch file from SQL Job Agent to zip files and put it an archive file onto ftp server.. Please, follow below links:
How to: Create a CmdExec Job Step (SQL Server Management Studio)
How to run multiple Operating System commands using CmdExec step in SQL Agent jobs
 
Share this answer
 
Comments
Sergey Alexandrovich Kryukov 24-Feb-15 14:21pm     CRLF
5ed. —SA
Maciej Los 24-Feb-15 14:47pm    
Thank you, Sergey ;)
Sander Rossel 25-Feb-15 3:54am     CRLF
Thanks for your answer. Unfortunately it did not solve my problem. I tried using && between commands and putting them all on the same line, but it said the syntax was not valid. Perhaps I should use a proxy account? Too much hassle... I'll stick with executing a seperate batch file. That seems to work...
do zip by sql agent job only
see link:
http://searchsqlserver.techtarget.com/tip/ZIP-files-with-a-stored-procedure
 
Share this answer
 
Comments
Sander Rossel 25-Feb-15 7:47am    
Thanks for the link, but it's not quite what I'm looking for :-)
I've gone around the problem by creating a cmd file and calling it from my job like: "MyFile.cmd".
I've added some parameters too: "MyFile.cmd param1 param2"...
Not quite what I had in mind, but works like a charm nonetheless.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900