xp_sqlagent_enum_jobs Problem - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

xp_sqlagent_enum_jobs Problem

Author

Topic

pacolim
Starting Member

11 Posts

Posted - 2010-05-13 : 05:52:31

I want to access linked server and execute xp_sqlagent_enum_jobs to check on current job status on that server. I am able to execute command below in local server:

EXEC master..xp_sqlagent_enum_jobs 1,garbage

But when I try to execute it to linked server using open query method:

SELECT *
FROM OPENQUERY([LINKSERVERA], 'EXEC master.dbo.xp_sqlagent_enum_jobs 1,garbage')

It return me the following error:

Cannot process the object "EXEC master.dbo.xp_sqlagent_enum_jobs 1,garbage". The OLE DB provider "SQLNCLI" for linked server "LINKSERVERA" indicates that either the object has no columns or the current user does not have permissions on that object.

If directly execute:
EXEC [LINKSERVERA].master..xp_sqlagent_enum_jobs 1,garbage

It given this error:
Server 'LINKSERVERA' is not configured for RPC.

Not sure what is RPC, preferrably don't want to change this setting in the linked server.

Any one have any idea why xp_sqlagent_enum_jobs is not working for linked server? Is there any other way to get particular job status (by providing job name) from linked server if I can't use xp_sqlagent_enum_jobs?

Thanks in advanced.

pacolim
Starting Member

11 Posts

Posted - 2010-05-13 : 22:01:19

can anyone help on this ?

PhillyJoe
Starting Member

1 Post

Posted - 2010-06-14 : 15:47:05

Right click your linked server and select 'Properties'.
Then select the menu item on the top left titled 'Server Options'.
Set 'RPC' and 'RPC Out' to 'True'.

If you continue to get the error message, you need to go to the server you are trying to link to.
Log in to that sql server and right click the server name (at the very top of sql's 'Object Explorer'). Select 'Properties' and then select 'Connections'. Select the checkbox 'Allow remote connections to this server'.

Please respond in case this didn't answer your question.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources