Execute stored procedure in another server from sql agent job

Question

I am trying execute stored procedure in remote server in sql agent job using sqlserver 2008 r2

 select * from openquery([1.1.1.1],'exec [DB_Name].[dbo].[sp_ProcName]')

Or

EXEC [1.1.1.1].[DB_Name].[dbo].[sp_ProcName]

but I got the folowing error:

Could not find server '1.1.1.1' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

(I type the correct IP) Thanks!

Answer 1

As the error message suggests, OPENQUERY looks for a linked server. If you don't have that set up, and don't have the rights to get that set up, then perhaps using an OPENROWSET query might be the way to go. This will allow you to connect to a datasource on a more ad-hoc basis, by writing a query which contains a full connection string.

In your case, you may get the results by using something like:

SELECT spresults.* 
FROM OPENROWSET('SQLNCLI', 'Server=1.1.1.1;Trusted_Connection=yes;',
 'EXEC [db_name].dbo.[sp_ProcName]') AS spresults

CAVEAT: This will fail at runtime if your SP returns multiple resultsets.

CAVEAT 2: This will also fail if Ad Hoc queries haven't been allowed. To sort that out, you need to run the appropriate sp_configure statement(s):

exec sp_configure 'show advanced options', 1
reconfigure
exec sp_configure 'ad hoc distributed queries', 1
reconfigure

Of course, in order to run sp_configure, you'll need the appropriate rights in SQL Server. According to the "Permissions" section of the sp_configure documentation, that's "ALTER SETTINGS", which is by default part of the sysadmin and serveradmin roles, but may be assigned to other roles too.

• mich: I got the folowing error: SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server.
• Thomas Rushton: Yes, sorry, should have mentioned that. Hang on.
• Thomas Rushton: @Aaron Bertrand - thanks for the "exec" edit. Brain was definitely not in gear this morning!