The metadata could not be determined because statement

  • 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

  • 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

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

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