SQL Server Club
Home Articles Essential Guides Blogs Links Twitter Facebook Join Contact About
Job Details Vanish in SQL Server Management Studio
Web Monkey About the Author

With security clearance and well over a decade of experience with SQL Server, Web Monkey is the resident DBA at Norb Technologies and a real database guru.

A regular contributor to SQL Server Club, his background includes software development, hardware, a smattering of essential networking knowledge and an unhealthy interest in web security. He has an obsession with automating repetitive tasks, but has a tendency to forget where he left his last open bag of fruit and nut...

Typepad Profile RSS Feed Web Monkey on Twitter
Send to a Friend Printer Friendly Version

Recently I was running some tests on the scheduler code in one of our home-grown applications and needed to check that the job schedule set up by our software exactly matched the details held in SQL Server Management Studio (SSMS).

Now this code has been run and tested many times before without problems in SSMS and has been meticulously hand-tested. Yet on this occasion, I opened up a job I had just created to find there was no entry in the Job window for its name, the category into which it had been created or certainly any details in the Steps or Schedules pages.

My first reaction was that something was wrong with the code. I opened up another job to find the same problem. All job properties were absent. I raced through another half dozen jobs to find that they were all showing blank entries. This was on my local development machine, so I connected to another test server to see if it was local to me. Nope. I found the same problem again on the test server.

I opened up the Job Activity Monitor window and saw a list of all my jobs. Double-clicking to open up the Job Properties window showed all the details of the job, exactly as specified by the application.

Interestingly I eventually noticed that when I clicked on a job under the Jobs folder of SQL Server Agent in Management Studio, instead of opening the Job Properties window, it opened a New Job window. I guess this explains why all my job details disappeared. The progress bar at the bottom left hand corner of the window also showed an error occurred.

Ok, so some consolation that this was a Microsoft coding error, not one of ours. Just to prove it was Microsoft at fault and not our code, I also ran:

use msdb go select * from sysjobs
This confirmed all the jobs existed and the schedules and steps were correct.

I remembered that I’d recently published a blog entitled Re-writing History with SQL Server 2005 SP2a and that there were some issues with Service Pack 2 and datetime settings in maintenance plans. My first thought was to check the service packs of my machine and the test server.

I ran a select @@version on my development machine and got the following results:

Microsoft SQL Server 2005 - 9.00.1406.00 (Intel X86)
Mar 3 2007 18:40:02
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Taking a quick peek at our SQL Server Versions article, I was even more surprised that version 9.00.1406.00 didn’t seem to exist. Now this was really getting interesting. I had installed SQL Server from a DVD in our MSDN subscription, so it’s straight from Microsoft, nothing dodgy there.

Running select @@version against the test server I had the following result:

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

Looking it up against the SQL Server Version article I found this to be the base release of SQL Server, so no Service Packs yet.

A quick search on the web for my unusual version of SQL Server brought up few links, but I found this one on SQL Team. And it seemed to be another Service Pack issue.

After installing SQL Server Service Pack 2, I returned to Management Studio and opened my jobs to find all the details in there correctly. So if you have any problems with schedules or jobs in SSMS, check which service packs you are running and if necessary upgrade your client tools first. If this doesn’t fix it, you may want to look at service packing your other SQL Servers if you haven’t already.

If you found this article useful, please visit our sponsors who keep us going...

SQL Enterprise Monitor 2009
This tool gives you unlimited server monitoring at a really great price!

Ensure critical services are running, compare enterprise-wide security and configuration settings, find troublesome queries and resource-intense database activities.
Download
Send to a Friend Printer Friendly Version
Top of Page
© Norb Technologies 2007-2010 Privacy Policy Norb Technologies Devdex Feedback Home
Feedback Form