Need Help in executing a Stored Procedure using Op

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need Help in executing a Stored Procedure using Op

Author

Topic

sradez
Starting Member

6 Posts

Posted - 2008-06-12 : 16:12:01

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)

Thanks in Advance!!!

sradez
Starting Member

6 Posts

Posted - 2008-06-13 : 09:42:59

There got to be some solution to this!!!
Any Help would be appreciated!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-13 : 10:37:24

DECLARE @TSQL varchar(8000), @DBNAME char(20)
SELECT @DBNAME = 'DB1'

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-13 : 11:21:28

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=104772

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources