Problem in exec sp_enum_sqlagent_subsystems

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Problem in exec sp_enum_sqlagent_subsystems

Author

Topic

YogeshDesai
Posting Yak Master

136 Posts

Posted - 2007-04-11 : 03:03:48

Presently we are facing some strange issue related to SQL server jobs. We found that the list of “type” is only showing the “Transact-SQL script(TSQL)” Other options are not visible.
On further investigation after running the stored proc exec sp_enum_sqlagent_subsystems in MSDB database returning only a single record.
This ideally should return below given rows

ActiveScripting Active Scripting Subsystem
CmdExec Command-Line Subsystem
Distribution Replication Distribution Subsystem
LogReader Replication Transaction-Log Reader Subsystem
Merge Replication Merge Subsystem
QueueReader Replication Transaction Queue Reader Subsystem
Snapshot Replication Snapshot Subsystem
TSQL Transact-SQL Subsystem

But the actual output is

TSQL Transact-SQL Subsystem

The subsystem type in the sysjobsteps is still stored as CmdExec for old jobs. When you try to create a new job you won’t get this option in the drop box as mentioned earlier. When open the job steps it won’t show the CmdExec option it is automatically changed to TSQL.

The supporting DLL files [ SQLATXSS.DLL, SQLCMDSS.DLL, SQLREPSS.DLL] are exists in the C:\Program Files (x86)\Microsoft SQL Server\MSSQL\Binn folder.

This procedure fetches data from windows registry. When I tried to open the SQL Server agent Key from the windows registry id thrown the below given error
" Cannot open Sql Server Agent:Error while opening key"

Kindly suggest the method to make those options available. Few of our jobs are directly running by CmdExec options.

Yogesh V. Desai. | SQLDBA|

gvphubli
Yak Posting Veteran

54 Posts

Posted - 2008-01-31 : 19:08:53

Shutdown the SQL Agent and then try to open the registery keys and verify the values. If values are missing, try to re-register the DLL again. And even restart may solve issue sometime...so give it a try.

if all this does not help, try making an entry in the registery your self manually for one of the item and see if that works. but carefully while messing with registery.

TechnologyYogi
http://gvphubli.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources