0

My stored procedure emit reults set which has dynamic columns, sometimes it has 10 and sometimes it has more 15 Also My stored procedure has temporary table that is return to calling app.

select a.* 
into [tempdb].[dbo].[tempBase]
from openrowset('SQLNCLI', 'Server=.;Trusted_Connection=Yes;Integrated     Security=SSPI', 'SET NOCOUNT ON;SET FMTONLY OFF ; exec  LIQ_TradeBustTrader5895.dbo.price_report @loginID = ''ALL'', @tableName = ''order_msgs'', @firmname = ''ALL'', @AssetClass = ''All'',  @OutputBustRecords=0, @ManualOrder=0, @TimeFrom=''0:0'', @TimeTo=''23:59'', @ExcludeSimulatedTrades=0, @SingleTable =1 ') AS a;

Now I get these errors:

Msg 11529, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because every code path results in an error; see previous errors for some of these.

Msg 4902, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
Cannot find the object "#Tethys_ReportGenerator_Tbl" because it does not exist or you do not have permissions.

Community
  • 1
  • 1
Neeraj Dubey
  • 4,401
  • 8
  • 30
  • 49
  • did you tried using WITH RESULT SETS as here : http://stackoverflow.com/questions/35951765/the-metadata-could-not-be-determined-because-every-code-path-results-in-an-error – Malcolm Jun 23 '16 at 11:56
  • Proc return dynamic column which is not known before executes,hence can not use `WITH RESULT SETS`. – Neeraj Dubey Jun 23 '16 at 13:25
  • How many different possible result sets does your sproc have? Unless it's dozens, a rewrite shouldn't be too much work. `OPENROWSET(Server=.)` is a dirty hack in the first place. – Jeroen Mostert Jun 23 '16 at 13:54
  • Its around more than `300` resultset and its increasing.Please suggest some other way by I can achieve my work. – Neeraj Dubey Jun 23 '16 at 14:14
  • As far as I can tell from all materials describing workarounds, you're hosed. Push whatever processing logic you're doing externally up the client, or down to `LIQ_TradeBustTrader5895.dbo.price_report`, [use a different way of sharing data](http://www.sommarskog.se/share_data.html), or don't upgrade to any version of SQL Server beyond 2008 R2. – Jeroen Mostert Jun 24 '16 at 13:26

1 Answers1

0

Few troubleshooting options..

1.Your sp seems to be having issues ,so try running your sp and see if you are able to run it to completion..

Cannot find the object "#Tethys_ReportGenerator_Tbl"

2.Once your sp completes successfully and if you are still having issues,try running below SP which SQL uses internally to know metadata of result set..

exec [sys].sp_describe_first_result_set "your sp"
TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
  • `exec [sys].sp_describe_first_result_set "your sp"` is also gives error as my proc using temp table inside and return reults-set. – Neeraj Dubey Jun 23 '16 at 13:42