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

  • 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

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

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