CODEWITHSUNDEEP
mysqlsqlsql-serverstored-procedures
user2232273
user2232273

Reputation: 4964

sql - Exec Stored Procedure using OpenRowSet

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)

Upvotes: 0

Views: 1178

Answers (1)

John
John

Reputation: 186

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.

Upvotes: 2

Related Questions