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.
You should not include the instance name when specifying the port, so instead of
Source : sat\sql2012,5000
destination:tak3\war2012,55750
You should use
Source : sat,5000
destination:tak3,55750
** Note: following queries about this, I can find no documentation to support my answer. Consider it to be my personal experience only.
There is some detailed information on connection string attributes at the following URL,
Using Connection String Keywords With SQL Native Client
which defines the syntax for server addressing as:
[protocol:]Address[,port |\pipe\pipename] for an "Address"
or
Server= [protocol:]Server[,port] for a "Server"
Best Answer
I don't think there's a built-in way to do this, but here are a few alternatives:
Use
OPENDATASOURCElike this:Note that using
OPENDATASOURCErequires you to turn on theAd hoc Distributed Queriessp_configuresetting (as noted in the Remarks section of the documentation) even though you won't be writing code that accesses another server (SQL Server doesn't know that).Create a wrapper SSIS package that specifies the application name in a Connection Manager, and then uses an Execute SQL task to run the stored procedure.
Create a wrapper command-line app that takes a connection string and a command as parameters; execute it using an Operating System (cmdexec) job step.