April 17, 2007 at 1:59 pm
Hello,
Here is one where I am not even sure where to begin.
We have a scheduled job that runs every 30 minutes. When I check the job history, it says that the job has run successfully. However, the job is supposed to load a table with data, and when I check the table it is empty. So even though the job process ran successfully at the scheduled time, for all intents and purposes it has failed.
So I have run the job manually. When I check the job listing and the job history, it says that the job has run successfully. This time, when I go check the table, it has data in it.
At the next scheduled run time, the table is left empty again.
I do not want to be stuck running this job by hand every 30 minutes....
Does anyone know why running the same job manually would work when running it automatically does not?
Might restarting SQL Server Agent help? Or does it sound like some other problem?
Thanks for any help.
----
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
April 17, 2007 at 2:12 pm
Look at ou job definition. In the past I made the mistake of having two databases (one for UAT and one for Prod) on the same server and ran into this. Issue turned out I was pointing the Job to the wrong database. Odds are you have something in your code that may work but is not where you expect it to be going.
April 17, 2007 at 2:22 pm
Thanks, that is a good suggestion. I checked the job properties, but it does appear that the job is pointing to the correct database on the production server. Also, since the data goes to the right table when I run the job manually, that is also a sign that the correct database is listed in the properties.
I have tried restarting SQL Server Agent to see if that helps. In the meantime, if you think of any other possibilities, let me know.
One final thing is that when I run the job manually, it runs under my user name. I don't know how that figures in, but it is a difference between the scheduled and the manual runs.
Thanks again.
---
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
April 17, 2007 at 3:29 pm
Have you tried running it manually from the server itself? This sounds like either a permissions, or a scope (i.e., getting data from the local drive) issue, but without knowing your job, I have no idea if it would still show success if it ran into such an issue.
April 17, 2007 at 3:38 pm
When you run it, you run it under your id. When it runs automaticaly, it runs under the id you have set up for the SQL Server Agent. Make sure that the agent id has the permissions required to do the update.
Is there a trigger on the table that may be backing out the update for some reason?
Is there a log from the job? Does is show an 'affected rows' count?
April 18, 2007 at 5:43 am
You may check the owner of the job
April 18, 2007 at 7:15 am
Thanks to everyone for the replies. Modifying the owner worked. I made a copy of the job and set the owner to the generic account the previous DBA created for running jobs, disabled the original job, and watched the scheduled runs. The copy of the job worked completely.
Thanks again!
---
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
April 23, 2007 at 5:39 pm
As a cleanup activity you might want to check the default database of the first userid that was executing the JOB. You may have some table cleanup in that database or even the master database !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply