Solved

SQL 2008 job not running

Posted on 2016-11-09
5
25 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 8

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 8

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
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…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

690 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