question

DharmanDave avatar image
DharmanDave asked

How to execute a batch file from sql server 2008 job ?

The batch file is running properly when executed separately on windows. When the same is put as a Job step, the job executes successfully but there is no output.
sql-server-2008-r2command-line
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DharmanDave avatar image DharmanDave commented ·
Hi all... Im trying to download a file from an ftp using psftp.exe along with credentials. When batch file is executed, the expected output is that the required file should be downloaded to a path specified. This expected output i'm not getting using the sql job.
0 Likes 0 ·
Sacred Jewel avatar image Sacred Jewel commented ·
As I said in my answer....should be the permissions issue. Use a proxy account (recommended) or change the owner of the job to a user which have sufficient rights to create files in the destination.
0 Likes 0 ·
DharmanDave avatar image DharmanDave commented ·
The owner of the job is part of the administrators group and the SQL agent service is also running under the same account.
0 Likes 0 ·
Sacred Jewel avatar image Sacred Jewel commented ·
Can the same user execute the batch file successfully?....have you tried the job output file to detect any errors?...
0 Likes 0 ·
DharmanDave avatar image DharmanDave commented ·
'PSFTP.EXE' is not recognized as an internal or external command, operable program or batch file. This is one of the error messages. Also i get this error msg when i give only the path of the batchfile in the command window of the sql job. when i write down the commands explicitly in the command window, it executes properly but doesnt give output.
0 Likes 0 ·
Sacred Jewel avatar image Sacred Jewel commented ·
That indicates that you are not in the correct directory while executing the command..... Change your working directory to one where PSFTP.EXE resides.... Another option could be to add the PSFTP.EXE path in the LOCAL PATH environment variable...
0 Likes 0 ·
Sacred Jewel avatar image
Sacred Jewel answered
This is the problem with cmdline batch files..... SQL Server cannot catch the exceptions from there....You need to check the errors in the batch file and then handle it accordongly... My wild guess... it could be permiissions issues.... You may need to setup a proxy account or make sure the owner of the job have sufficient rights to execute the batch properly... Another way to troubleshoot in this scenrio is to have an job output file.... The job output file could reflect the errors may occured...
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
What results are you expecting? There is no 'window' for the results to be shown on screeen anywhere when the SQL Agent job runs the batch file. Have you tried piping your results into a txt file? e.g. Dir > C:\ MyDirectoryList.txt or Dir >> C:\ MyDirectoryList.txt a single > will create/overwrite the output file, a double >> will append results to an existing file [Edit - after more info on question] Your SQL Agent service account will have to have permission to write to the location chosen in the FTP process. check this and then llok into handling the error within the batch file so that at least the error details are passed into a file to help debugging.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DharmanDave avatar image
DharmanDave answered
Error handling in the job output helped. The path for the psftp.exe was being passed incorrectly. Thanks everyone for your responses. Cheers !
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.