May 21, 2008 at 4:12 am
Dear All,
One of our SQL Servers had suddenly lost all SQL Job Step Types except "Transact-SQL Script (TSQL)". The other Replication, ActiveX and in particular "Operating System Command (CmdExec)" are all now absent from the list. Nothing has changed from a SQL point of view (no configuration changes or new hotfixes etc applied) and our Tech guys assure me that nothing has changed server side or with my permissions to the server.
Has anyone ever seen this before? All our other servers are fine and still show the full list of available types so I'm convinced that someone has done something and is not owning up to it.
We had several scheduled DTS Packages which used the CmdExec step type which are now failing as they've reverted to the TSQL type and syntax is now obviously wrong. I've worked around it by scheduling a stored procedure to run the DTS packages but I'd like to try and understand what has happened and if there is anyway to get them back again?
Kind Regards,
Nick
May 21, 2008 at 12:13 pm
Check the contents of this table:
[msdb].[dbo].[syssubsystems]
Check this directory:
Program Files\Microsoft SQL Server\MSSQL.(*)\MSSQL\binn
or this one:
Program Files\Microsoft SQL Server\90\COM
It sounds like stuff got removed from one of these locations.
"Got no time for the jibba jabba!"
-B.A. Baracus
May 22, 2008 at 3:03 am
Hi,
Thanks for your reply. I have compare the contents of these locations against some backups and there are no changes. However, I have since found another post from a user a few years ago who had the same problem...
http://www.sqlservercentral.com/Forums/Topic187545-110-1.aspx
...it would appear that the problem lies in the registry. If I try to access the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent\ key I get an error message. I've passed this over to our Tech guys who are looking into it.
Thanks,
Nick
May 27, 2008 at 10:28 am
Interesting problem, I look forward to hearing what your systems peoples find to resolve it.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
May 28, 2008 at 4:05 am
Hi,
The problem is now all fixed. We scheduled some time over the weekend to take the server down. As soon as we stopped the SQL Services the registry key came back and the various types were available again in the drop down boxes. They checked against a previous registry backup and everything looked good. I can find no explanation as to why the key became corrupted, I can only assume that the service had some kind of lock on it, but at least we know what to do if it happens again.
Regards,
Nick
May 28, 2008 at 7:31 am
Interesting, thanks for posting final outcome.
"Got no time for the jibba jabba!"
-B.A. Baracus
May 28, 2008 at 8:22 am
It's amazing (and sad and disgusting!) how many problems a server reboot can fix. We rarely can say "this is what's wrong and this is how to fix it", we just most often see if a reboot fixes it and go from there.
At least a DBCC points a more accurate finger most of the time when a problem occurs.
Congrats on your problem being resolved! 🙂
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply