How to execute a SP using OpenQuery

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

How to execute a SP using OpenQuery

Author

Topic

sradez
Starting Member

6 Posts

Posted - 2008-06-12 : 22:01:41

Hi,

I am trying to execute a ServerB stored Procedure which takes Int as paramter using OpenQuery in ServerA.But this doesnt seems to working.
Please Help !!!!

DECLARE @param1 INT
SET @param1 1

SELECT *
FROM OPENQUERY(ServerB,'DBNAME.dbo.SP_NAME ''@param1''')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-13 : 00:02:25

SELECT *
FROM OPENQUERY(ServerB,'EXEC DBNAME.dbo.SP_NAME @param1=''Value1''')

sradez
Starting Member

6 Posts

Posted - 2008-06-13 : 09:40:32

Hi Visakh,

I missed the Exec last time in my query that i pasted but tried again with the query what you sent.But this is not working either..
I wonder if we can implement anything like this i.e passing parameterized SPs to openrowset or openquery

Thanks!!!

sradez
Starting Member

6 Posts

Posted - 2008-06-13 : 10:45:45

This is working!!!!

declare @v varchar(200)
Declare @param1 INT
SET @param1 = 100

set @v='
SELECT *
FROM OPENQUERY(SEVERB, ''EXEC DBNAME.dbo.spname ' + @param1 + ''')'
exec(@v)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources