my job will not run

[OhioSteve]

I have quite a bit of experience with sql but I am just starting to learn about jobs. I have created several test jobs but unfortunately they never work when I schedule them. Here are the details:

1. I am working on my own computer with a database that I created. Permissions should be no problem.
2. I am using SQL Server 2008. I have already created a db with a table. In my job, I will insert one record into this table. So the sql will be easy.
3. In Management Studio, I go to the "SQL Server Agent" node.
4. SQL Server Agent *is* running.
5. I right-click the jobs folder and choose "new job".
6. In the general tab I choose a name for the job.
7. On the steps tab, I add a step, input sql for that step, and save the step.
8. On the schedule tab I schedule it to run one time in the near future.
9. I click "okay" to finish creating the new job.
10. I wait until the appropriate time but the job does not execute.

Here's the kicker...if I right-click the job and run it manually it works. But a job you cannot schedule is really pointless. What am I overlooking???

 

[gmmastros]

I'm not too familiar with jobs, so this advice may not help you.

If I'm not mistaken, it's the SQL Agent that executes jobs. So, it's the security context of the SQL Agent that the job is run under. When you manually run a job (in SQL Server Management Studio) it runs under the security context of the login you used to start SSMS. These logins can be (and probably are) different.

If I am not mistaken, SQL Agent connects with the login used to start the SQL Agent service. To see what this is.... click start, run, type "services.msc", hit enter, scroll to SQL Agent, right click, properties, click "log on" tab.

Of course, I could be completely wrong about this, but it is the first thing that comes to mind.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLBill]

Some things to check/do...

1. The job may be enabled but the schedule disabled - or vice versa. Check that both are enabled.

2. This is a biggie...in the Advanced area, create an ouput file (I usually name mine as the jobname followed by _output.txt - ie. myjob_output.txt). Then I check that when a job fails.

3. Check the job history to see if it shows the job as successful or failed. If it is successful, something is wrong in the job code. If it failed, look for the reason for the failure. If there is nothing in the history, it probably never ran. Check the schedule -is it enabled? Is the start date/time correct?

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[OhioSteve]

I changed the logon property of the SQL Server Agent Service to my own domain account, then restarted the service. Afterwards, my job executed at the scheduled time!

Then to verify my diagnosis, I switched back to "Local System Account" and restarted the service. I was disapointed when my job STILL executed at the scheduled time.

So my new question is...why did my job execute even after I removed the appropriate account?

 

[gmmastros]

I dunno.

Maybe either account is fine, but a restart was all you really needed.

BTW... you shouldn't use a "user" domain account for any service, ever. Obviously, testing is fine, but don't use it long term. If you ever need to change the logon account, you should create a new domain account just for the service.

About 8 years ago I had used my domain account for the service, and then promptly forgot about it. Several months later, I changed my password. When the service was restarted (probably because of a power failure), the SQL service wouldn't start. It took me a long time to figure out why. The other issue is... user accounts usually have permissions to things that the SQL Server service should NOT have access to. By creating a new domain account just for the service, you can configure the password to never expire and you can set the permissions as narrowly as possible to get the job done.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom