5

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.

gbn
  • 422,506
  • 82
  • 585
  • 676
Keith Adler
  • 20,880
  • 28
  • 119
  • 189

2 Answers2

11

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"

gbn
  • 422,506
  • 82
  • 585
  • 676
  • +1 but there should be a triple bonus score if you can provide an example of it using a sproc, since msdn doesn't give one – Nick Kavadias Oct 06 '09 at 06:15
  • wow weirdness, i can't upvote you again, my click-happy fingers managed to unclick my vote & i can't vote up again.. wow. that sucks! – Nick Kavadias Oct 06 '09 at 16:50
0

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

Don Dickinson
  • 6,200
  • 3
  • 35
  • 30