Job Details Vanish in SQL Server Management Studio

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 we’d recently published a blog by Bob Jackson 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
(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.

End of Technical Article

For further essential guides, technical articles, blogs, newsgroups, forums, links and more, please visit the SQL Server Club site at www.sqlserverclub.com