4

I am migrating from SQL Server 2005 to SQL Server 2014 and one of the queries stopped working in SQL Server 2014:

select * 
from openrowset ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;',' exec [MyDatabase].[dbo].[MyTable]')

I get the following error message:

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 "#MyTempTable" because it does not exist or you do not have permissions.

dbo.MyTable and #MyTempTable are not real names.

What could cause this error? Any help would be appreciated.

Thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Val K
  • 301
  • 3
  • 9
  • 22
  • I realize I'm necroing a 3 year old post but wonder what you expected to happen when you try to run EXEC on a TABLE (MyTable) instead of a stored procedure or a SELECT. I suspect that's the real problem here. – Jeff Moden May 07 '19 at 12:45

1 Answers1

10

From SQL Server 2012 onwards, you need to use WITH RESULT SETS to explicitly describe the result set:

EXEC('exec [MyDatabase].[dbo].[StoredProcedure] WITH RESULT SETS (( val SMALLINT));')
Alex
  • 21,273
  • 10
  • 61
  • 73
  • I'm pretty sure that EXEC isn't going to work against a table. It should be a stored procedure or embedded SELECT statement, the latter of which would make little sense here. – Jeff Moden May 07 '19 at 12:47
  • The answer is based on the question that includes `exec [MyDatabase].[dbo].[MyTable]`. As this was working in SQL2005, I can only assume it was a stored procedure in the first place, however, to clarify my answer I have changed it to `[StoredProcedure]` – Alex May 07 '19 at 15:02
  • Thanks for the feedback, Jaco. I figured you figured that but had to say it out loud in case a newbie was reading this forum. I also forgot to take my hat off to you for the WITH RESULT SETS part of your answer. – Jeff Moden May 08 '19 at 18:25
  • In case this helps anyone in the future, I've wasted a couple days battling with this with a system extended procedure, till I found that you can use "WITH RESULT SETS NONE" too... – Zalakain Jun 22 '20 at 16:25