sql - Exec Stored Procedure using OpenRowSet

Question

i have this query and what i want to do is to execute a stored procedure from another server to get results.

i need to pass a paramenter in the sp.

my problem is that is not working and i don´t know what i have to do put this to work.

hope that someone can help me out with this.

this is my code:

 DECLARE @EXECSQL3 VARCHAR(500), @a VARCHAR(300), @b INT

SET @a = 'test'
Set @b = 100

   SET @EXECSQL3 = 'SELECT * FROM OPENROWSET
         (''xxxxx'',''xxxxxx'';''xxxxxxx'';''xxxxxxx'',
         ''[DB].[dbo].[MyStoredProcedure]  @a, @b'')'

   EXEC(@EXECSQL3)

If its a linked server can't you just execute it directly?

Alex K.
– Alex K.

2014-02-26 17:21:38 +00:00
Commented Feb 26, 2014 at 17:21 — Alex K.
– Alex K., Commented Feb 26, 2014 at 17:21

John · Accepted Answer · 2014-02-26 18:37:53Z

2

SELECT *
FROM OPENQUERY(OtherServer, 'EXEC db.dbo.MyStoredProcedure ''test'', 100')

I've never used OpenRowSet... but I've used OPENQUERY. I prefer it over just querying the server directly in a linked server scenario, because the query runs on the remote server, and only the results are returned, which is usually much faster.

edited Feb 26, 2014 at 18:37

answered Feb 26, 2014 at 18:13

John

1865 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Wesley Bland Over a year ago

Can you give a little more explanation? In general, code-only answers are frowned upon as they are very specific and often hard to understand later.

Collectives™ on Stack Overflow

sql - Exec Stored Procedure using OpenRowSet

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related