An INSERT EXEC statement cannot be nested

  • PRAMANA.DBA

    SSCertifiable

    Points: 5507

    Hi All,

    i am trying to post sp result to temp table then am getting this error, How can i resolve this issue?.

    Msg 8164, Level 16, State 1, Procedure Rep_Accuracy_Achievement_Analysis_Report_PRR, Line 461

    An INSERT EXEC statement cannot be nested.

    .

    Ex:

    insert into #Result(ID,Name,Count)

    exec [Rep_Accuracy_Achievement_Analysis_Report]

  • SrcName

    Default port

    Points: 1429

    if you can post procedure code.

    First error is:

    In your stored procedure [Rep_Accuracy_Achievement_Analysis_Report]

    you have insert data from another stored procedure in temp table. that is not allowed and you attempt to bubbleup data from a chain in stored procedures. Only one insert into exec is allow at time.

    You can solve this with: create procedure with output parameter, create table valued function or with global temp table e.t.c

    second error is consequence of first,

  • Kenny Jozi

    SSCrazy

    Points: 2004

    create a temp table and openrowset your stored procedure data into it

    INSERT INTO #tmp_table

    SELECT *

    FROM

    OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off EXEC [ServerName].dbo.[StoredProcedureName] 1,2,3')

    SQL 2000/2005/2008/2012 DBA - MCTS/MCITP

  • michal.lisinski

    SSCertifiable

    Points: 6873

    hi

    Imho the [Rep_Accuracy_Achievement_Analysis_Report] returns different number of columns.

    Br.

    Mike

  • Mark Cowne

    One Orange Chip

    Points: 26684

    Great article here

    http://www.sommarskog.se/share_data.html

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • enriquezreyjoseph

    Ten Centuries

    Points: 1231

    PRR.DB (9/25/2013)


    Hi All,

    i am trying to post sp result to temp table then am getting this error, How can i resolve this issue?.

    Msg 8164, Level 16, State 1, Procedure Rep_Accuracy_Achievement_Analysis_Report_PRR, Line 461

    An INSERT EXEC statement cannot be nested.

    Msg 213, Level 16, State 7, Procedure Rep_Accuracy_Achievement_Analysis_Report_PRR, Line 793

    Column name or number of supplied values does not match table definition.

    Ex:

    insert into #Result(ID,Name,Count)

    exec [Rep_Accuracy_Achievement_Analysis_Report]

    please let us see the code?..thanks...hehe

  • PRAMANA.DBA

    SSCertifiable

    Points: 5507

    Code is too big, it having around 900 lines .

    but inner side also post data to temp table from sp result.

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

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