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

  • GonnaCatchIT

    SSCrazy

    Points: 2797

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

  • robinwilson16

    SSCarpal Tunnel

    Points: 4314

    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

  • robinwilson16

    SSCarpal Tunnel

    Points: 4314

    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.

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

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