CODEWITHSUNDEEP
sqlsql-serverlinked-server
Keith Adler
Keith Adler

Reputation: 21178

Call a Sproc on another SQL Server without being linked via TSQL

I want to call a sproc on server B from server A in TSQL without linking the servers. Is it possible to use something like a connection string to execute this sproc? The return will be a single nvarchar value.

Regards.

Upvotes: 5

Views: 2293

Answers (2)

gbn
gbn

Reputation: 432210

To avoid "linked servers", you'd normally use OPENDATASOURCE

After comment:

EXEC OPENDATASOURCE('SQLNCLI', 'Data Source=London\Payroll;Integrated Security=SSPI').remoteDB.remoteSchema.remoteProc @param1, @param2,...

Simple 4 part naming convention. The whole OPENDATASOURCE simply replaces the linked server name...

Note: you may have issues with "adhoc access"

Upvotes: 11

Don Dickinson
Don Dickinson

Reputation: 6248

i know of no way of doing it without ...

  1. creating an extended stored proc to do it for you
  2. perhaps using xp_cmdshell to use isql to execute your stored proc .. however, getting the result might be tricky (perhaps write the result to a table on your current server in the same sql file that isql is reading)

-don

Upvotes: 0

Related Questions