SQL 2012 ERROR - The metadata could not be determined because statement contains dynamic SQL

Question

SQL 2012 ERROR - The metadata could not be determined because statement contains dynamic SQL

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

GonnaCatchIT SSCrazy Points: 2797 More actions · Answer 1

OLEDB Provider for SQL -- does this work with provider 'MSOLEDBSQL.1' ?

Robin Wilson SSCertifiable Points: 5007 More actions · Answer 2

I have been using OPENQUERY and WITH RESULTS SETS which has been working well but now I have hit an 8,000 character limit with the column definition and am not sure what to do as all solutions online point to using EXEC to execute the code dynamically but that isn't possible in a view.

Once you exceed 8,000 characters (or about 7,500 when I count them) it fails with this error:

Msg 103, Level 15, State 1, Line 13
The character string that starts with '
SET FMTONLY OFF; 
EXEC EPNE.dbo.SPR_4CA_4Cast2020Data
WITH RESULT SETS
(
(
TeamCode VARCHAR(20),
TeamNam' is too long. Maximum length is 8000.

Does anyone know any solution to this?

Thanks

Robin

Robin Wilson SSCertifiable Points: 5007 More actions · Answer 3

It's ok I have solved it now.

I needed to create a wrapper stored procedure where I define the Result Set and then can remove this from the view and call the wrapper stored procedure instead and this removes the need for OPENQUERY and the 8,000 character limit.