Solved

SQL 2008 job not running

Posted on 2016-11-09
5
16 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

776 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