[SOLVED] The metadata could not be determined because every code path results in an error; see previous errors for some of these

The metadata could not be determined because every code path results in an error; see previous errors for some of these

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

Solution

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));')