SQL 2008 job not running

Trying to resolve an issue with a user's SQL job.

BankJob runs daily and extracts data from one database and dumps it into a table on another database. If the table already exists, it is supposed to drop the table and create a new table.

This job stopped working all of a sudden. When checking the Job History, it shows the Job running successfully with no errors.

The Job calls a .bat file. The .bat file calls the SQL script and and also dumps the results into a .txt file. If I run the SQL script on its own, it works successfully. The script pulls the correct information and creates a table in the appropriate database.

This is the syntax of the .bat file:

sqlcmd  -S SERVERNAME\SQL  -i "C:\Jobs\OperationReports\BankJob\EXE\spBankJob.sql" ^
-o "C:\Jobs\OperationReports\BankJob\logs\JspBankJob_log.txt"

I checked the SQL Agent and it is running. Restarted the service just in case.
Checked eventvwr logs and didnt see any errors for SQL and have also checked if there were any system changes during the time the job stopped working - nothing.
I've checked the SQL Reporting Accounts and the SQL Agent accounts and they all have SYSADMIN rights to the databases. The user has the rights as well and to the location of where the job is being stored on the server.

At a loss as to why the job stopped working, but the sp works fine.
SBSWIZARDAsked:
Who is Participating?
 
SBSWIZARDConnect With a Mentor Author Commented:
Thanks. You pointed me in the right direction.

Not sure if there was an update done to the server, but the sqlcmd would not run in command prompt. It was giving a command not recognized error.

I had to add the path for sqlcmd to Environment Variables to get it working. bat file ran fine afterwards.
0
 
Dung DinhDBA and Business Intelligence DeveloperCommented:
Could you run the job manually to see what will happen? If nothing, continue running  the .bat file manually. If the .bat file works, it seems that your job can not execute this file. Otherwise, there is an issue in your bat file.
0
 
SBSWIZARDAuthor Commented:
If I run the SQL script on its own, it works successfully.

If I try to run the .bat file, nothing.
0
 
Dung DinhConnect With a Mentor DBA and Business Intelligence DeveloperCommented:
It seems there was an issue of .bat file
sqlcmd  -S SERVERNAME\SQL  -i "C:\Jobs\OperationReports\BankJob\EXE\spBankJob.sql" -o "C:\Jobs\OperationReports\BankJob\logs\JspBankJob_log.txt"

Open in new window

- From your command lines, I understand that you are using Window Authentication by default. Please check the log file to see if your account has rights to access and execute the SQL script. Otherwise, you should provide -U <user name> -P <password> in the command lines
- After fixing .bat file, make sure that you can run .bat file successfully, then run the SQL Agent Job. I am assuming that SQL Agent Account has rights on all databases that you want to execute SQL script.
0
 
SBSWIZARDAuthor Commented:
The suggestion provided by Dung Dinh led me to the correct resolution.
0
All Courses

From novice to tech pro — start learning today.