1

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!

1 Answer 1

2

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.

3
  • 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.
    – mich
    Apr 13, 2014 at 7:58
  • Yes, sorry, should have mentioned that. Hang on. Apr 13, 2014 at 8:03
  • @Aaron Bertrand - thanks for the "exec" edit. Brain was definitely not in gear this morning! Apr 13, 2014 at 20:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.