Execute a Stored Procedure using OpenQuery

• sradez_DBA

  SSC Veteran

  Points: 290

  More actions

  June 12, 2008 at 10:48 am

  #96314

  Hi All,

  I am trying to execute a "ServerB" Stored Procedure in "ServerA".This SP is in Multiple DB's in ServerB.

  I am trying to use Openquery and Dynamic SQL to do that.But I am having issues.

  Intially i am trying to pass just one DBname as parameter..if it returns values then i can use cursor or other options to retrieve for multiple DB's

  Please Help!!!

  Ex:

  DECLARE @TSQL varchar(8000), @DBNAME char(20)

  SELECT @DBNAME = 'DB1'

  SELECT @TSQL = 'SELECT * FROM OPENQUERY(serverB'+','+''exec '' + @DBNAME + ''.dbo.sp_StoredProcedure''+')'

  EXEC (@TSQL)

• rahulnadkarni

  SSC Enthusiast

  Points: 113

  More actions

  June 13, 2008 at 4:23 am

  #828346

  Hi,

  You can execute the stored procedure as following

  exec [Linkedserver].databasename.dbo.usp_testing

  I am getting problem inexecuting the query thro OPENQUERY

  SELECT *

  FROM OPENQUERY([Linkedserver],databasename.dbo.usp_testing)

  So yOu can write a SQL Function Object on the top of the procedure

  and call the function thro linked server.

  select * from OpenQuery(LinkeServername,'select * from batabase.dbo.funcname()')

  In this case the function will return a TABLE data type which you

  can access in your code.

  Thanks,

  Rahul