Post reply

The metadata could not be determined because statement

  • September 28, 2021 at 1:02 am

    #3933742

    Hi everyone,

    I am running into a \n issue and it seems like none of the solutions I found online  helped me to fix it.

    So basically I want to have the result of a stored procedure into a temp table. I cannot use functions as it gets complex.  so My first solution didn't work as I have a temp table inside this sproc:

    create table #CodeSecCode 
    ( 
    WS_PERM_ID_CO VARCHAR(9)
    , SecCode float
    , StartDate datetime
    , EndDate datetime
    )

    Insert into #CodeSecCode 
    (
    WS_PERM_ID_CO
    , SecCode 
    , StartDate 
    , EndDate 
    )
    EXEC [dbo].[spGetCodeSecCode]

    I get an error here An INSERT EXEC statement cannot be nested.

    Now, some of you would recommend a table variable but the temp table could be big and I may run in performance issue so prefer to keep the temp table. I tried the second approach :

     SELECT a.*
    FROM OPENROWSET('SQLNCLI',
    'SERVER=myserver;DATABASE=mydb;Trusted_Connection=Yes;',
    'EXEC [dbo].[spGetCodeSecCode]  AS a

    and this again fails with an error

    The metadata could not be determined because statement.

    I see there are different post talking about it but still do not see a solution. has anyone seen this issue before? Any help would be appreciated.

    I am using

    Microsoft SQL Server 2016 (SP2-CU4) (KB4464106) - 13.0.5233.0 (X64) Nov 3 2018 00:01:54 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

    Thanks,

    Ro

    • This topic was modified 2 years, 7 months ago by  rohitkocharda.

  • September 28, 2021 at 3:34 am

    #3933759

    Create the temp table before calling the proc.  Have a flag that is passed in to "tell" the proc to load its results into the temp table, or you could even pass in the temp table name itself.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Viewing 2 posts - 1 through 1 (of 1 total)

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