Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL 2008 job not running

Posted on 2016-11-09
5
Medium Priority
?
30 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 2000 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

618 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