Cross Server Procedure Exection

• M Chapman

  SSC Enthusiast

  Points: 192

  More actions

  June 24, 2004 at 11:55 am

  #360160

  I need to use OPENROWSET() to execute a stored procedure on a separate, linked server. Any Ideas?

• Peter Tillotson

  Hall of Fame

  Points: 3325

  More actions

  June 25, 2004 at 11:11 am

  #512011

  If the server is a 'linked' server you don't need to use OPENROWSET(), you just refer to the stored procedure using the full 4 part reference. eg

  exec linkedservername.database.dbo.storedprocname

  The interesting thing is that the stored procedure actually runs on the linked server.

  If the other server isn't linked, the format for OPENROWSET() is:

  Using ODBC:

  SELECT * FROM OPENROWSET('MSDASQL',   'DRIVER={SQL Server};SERVER=servername;UID=userid;PWD=password',   databasename.dbo.tablename)

   

  I am not sure if you can change this to run a stored procedure.

   

  Using OLEDB:

  SELECT 'SomeText', A.*     FROM OPENROWSET('SQLOLEDB','servername';'userid';'password',  'exec databasename.dbo.storedprocname') AS A

   

  substitute the servername, username and password as required.

   

  Hope this helps

   

  Peter

• M Chapman

  SSC Enthusiast

  Points: 192

  More actions

  June 25, 2004 at 11:15 am

  #512013

  thanks pete...that's what worked (full four part reference of the table)