CODEWITHSUNDEEP
sqlmetadatasql-server-2014openrowset
Val K
Val K

Reputation: 331

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

Upvotes: 6

Views: 18708

Answers (2)

Chung
Chung

Reputation: 1

Please remember that the "WITH RESULT SETS", in accepted answer, which's columns definition must follow the return column order. Instead of mapping with column name, they are mapped by the sequence. And you can give the alias via changing the name in "WITH RESULT SETS" clause.

--From whom spent half a day for finding the root cause of data type error

Upvotes: 0

Alex
Alex

Reputation: 21766

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

Upvotes: 11

Related Questions