export sql agent jobs no access via SSMS

Question

export sql agent jobs no access via SSMS

Bryden Murdoch

Grasshopper

Points: 15
More actions
December 21, 2010 at 11:12 am

#244646

have a SQL server that in the SSMS just displays Agent XP's Disabled alonside the agent entry.
have tried enabling the Xp's but cannot get the agent to expand in the SSMS.
So I was wondering if there is an sp_ to script each job or someone knows of a way since I dont have the ability to right click an script.
Thanks
Bryden

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Meet George Jetson SSChampion Points: 11636 More actions · Answer 1

Are there any messages in the SQL Agent Log, SQL Server Log, Error Log files? Typically the SQL Agent Log will tell you precisely why.

Have you attempted to enable Agent jobs? Please run the following:

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'Agent XPs', 1;

GO

RECONFIGURE

GO

Chris Powell

George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.

magasvs SSCertifiable Points: 7660 More actions · Answer 2

After "Agent XPs" has been enabled - start SQL Server Agent service.

Bryden Murdoch Grasshopper Points: 15 More actions · Answer 3

ran the sql and restarted the SQL agent, no affect the agent is running but still unavailable in SSMS.

the SQL ran without any errors, there are no errors in either of the LOG files, but interestingly running exec sp_configure after starting the agent shows that the XP's have again been disabled.

If I run the TSQL with the agent running it also has no affect.

any more ideas for me to try

cheers

Bryden

Meet George Jetson SSChampion Points: 11636 More actions · Answer 4

Have you performed a migration from one SQL Server to a different SQL Server?

Can you run the following against the msdb database and verify subsystem dlls are pointing to the correct location on your server?

select *

FROM syssubsystems

Chris Powell

George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.

Bryden Murdoch Grasshopper Points: 15 More actions · Answer 5

executed your suggested TSQL and all dll's listed point to the current installation location and all dll's listed are present.

next please

cheers

Bryden

Meet George Jetson SSChampion Points: 11636 More actions · Answer 6

What version of SQL Server with SP are you running?

Please post the results of the following:

select @@version

Chris Powell

George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.

magasvs SSCertifiable Points: 7660 More actions · Answer 7

Try to use Surface Area Configuration tool to start the SQL Server Agent service.