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

Question

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.

Answer 1

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

Answer 2

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"