0

I have a stored procedure proc_abc. In this stored procedure, I have few insert statements where I am calling other stored procedures and getting the results into temp tables, at the end of the stored procedure I return final results in a temp table #rset.

Now when I use openquery to run proc_abc it returns the error

Msg 208, Level 16, State 1, Procedure '', Line 67
Invalid object name '#rset'.

Problem is I can not change the stored procedure, is there way to still be able to run it using openquery and get the stored procedures out put in my temp table?

Please guide.

Thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user1063108
  • 662
  • 1
  • 10
  • 24
  • Solution is here http://stackoverflow.com/questions/14293865/using-openquery-exec-stored-procedure-to-create-new-temporary-table-fails-with – neer Jul 23 '16 at 21:01
  • Thanks for your quick response, however it didn't seem to do the trick, Here is the code "select t.* from OPENQUERY(myserver,'SET FMTONLY OFF;exec mydb.dbo.proc_abc') t;" But this returns another error which can not be true as I know sp returns data and I do have permissions on the system, Here is the error "indicates that either the object has no columns or the current user does not have permissions on that object." – user1063108 Jul 23 '16 at 21:32

1 Answers1

0

This is because #rset does not exist in the database. May be you are not creating #rset temp table inside the Stored procedure. If can give the SP query, I can give you better answer.

Rakesh Pattanayak
  • 19
  • 8