Solved

SQL 2008 job not running

Posted on 2016-11-09
5
19 Views
Last Modified: 2016-11-16
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.
0
Comment
Question by:SBSWIZARD
  • 3
  • 2
5 Comments
 
LVL 7

Expert Comment

by:Dung Dinh
ID: 41881551
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
 

Author Comment

by:SBSWIZARD
ID: 41882421
If I run the SQL script on its own, it works successfully.

If I try to run the .bat file, nothing.
0
 
LVL 7

Assisted Solution

by:Dung Dinh
Dung Dinh earned 500 total points
ID: 41883255
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
 

Accepted Solution

by:
SBSWIZARD earned 0 total points
ID: 41884112
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
 

Author Closing Comment

by:SBSWIZARD
ID: 41889430
The suggestion provided by Dung Dinh led me to the correct resolution.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question