locked
Msg 8164, Level 16, State 1, Procedure sp_MSload_tmp_replication_status, Line 80 An INSERT EXEC statement cannot be nested. RRS feed

  • Pergunta

  • I am trying to insert replication-publication status into temp table, but am facing issues.

     

    create table #1(
    publisher_db sysname,
    publication sysname,
    publication_id int,
    publication_type int,
    status int,
    warning int,
    worst_latency int,
    best_latency int,
    average_latency int,
    last_distsync datetime,
    retention int,
    latencythreshold int,
    expirationthreshold int,
    agentnotrunningthreshold int,
    subscriptioncount int,
    runningdistagentcount int,
    snapshot_agentname sysname,
    logreader_agentname sysname,
    qreader_agentname sysname,
    worst_runspeedPerf int,
    best_runspeedPerf int,
    average_runspeedPerf int,
    retention_period_unit int)
    
    
    


    insert into #1
    exec sp_replmonitorhelppublication @@SERVERNAME,null
    

    But I get the below error

    "Msg 8164, Level 16, State 1, Procedure sp_MSload_tmp_replication_status, Line 80
    An INSERT EXEC statement cannot be nested."

    I would be grateful if someone can help me finding the solution for this error at the earliest.

     

    sexta-feira, 2 de setembro de 2011 13:48

Respostas

  • Hi All

    Here is Something Strange When I Am Executing in Following Way I Got Error

     

    insert into #1
    exec sp_replmonitorhelppublication @@SERVERNAME,null
    
    

     

    But When I Execute In This Way It Works

     

    exec sp_replmonitorhelppublication @@SERVERNAME,null
    
    insert into #1
    exec sp_replmonitorhelppublication @@SERVERNAME,null
    
    


     


    Mark as Answer If Reply Was Helpful Thanks Kuldeep Bisht Technical Lead @ Simplion Technologies http://mssqlguide.kuldeepbisht.com
    • Editado Kuldeep Bisht quarta-feira, 7 de setembro de 2011 16:24
    • Sugerido como Resposta SpizardMac quinta-feira, 8 de setembro de 2011 22:32
    • Marcado como Resposta Alex Feng (SQL) terça-feira, 13 de setembro de 2011 08:52
    quarta-feira, 7 de setembro de 2011 16:24

Todas as Respostas

  • There is NO linked server here. Everything is in 1 server. But still having issues :(
    San...
    sexta-feira, 2 de setembro de 2011 13:52
  • If the stored procedure has INSERT EXEC statement, then you will not be able to do this as Insert Exec statements can not be nested. I don't see a simple resolution for your problem, unfortunately.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Editado Naomi N terça-feira, 17 de março de 2020 13:38
    sexta-feira, 2 de setembro de 2011 13:58
  • Check if the SP, sp_replmonitorhelppublication inturn contains another INSERT INTO...EXEC statement. If it does, then you wont be able to use the above logic. Workaround for this problem might be to replace the nested stored procedures calls to an OPENROWSET method.

     

    sexta-feira, 2 de setembro de 2011 14:03
  • The procedure sp_replmonitorhelppublication is not having any INSERT INTO...EXEC statement....  :(

    San...
    sexta-feira, 2 de setembro de 2011 14:11
  • You can use the SSIS Import/Export Wizard with your query as source:

    http://www.sqlusa.com/bestpractices2008/administration/ssiswizardicon/

     


    Kalman Toth, SQL Server & Business Intelligence Training; sqlusa.com
    quarta-feira, 7 de setembro de 2011 15:34
  • May be that is a good idea, but here in my current requirement, am planning to capture the replication (publication/subscription) status in an stored proc...

    San...
    quarta-feira, 7 de setembro de 2011 15:36
  • Hi All

    Here is Something Strange When I Am Executing in Following Way I Got Error

     

    insert into #1
    exec sp_replmonitorhelppublication @@SERVERNAME,null
    
    

     

    But When I Execute In This Way It Works

     

    exec sp_replmonitorhelppublication @@SERVERNAME,null
    
    insert into #1
    exec sp_replmonitorhelppublication @@SERVERNAME,null
    
    


     


    Mark as Answer If Reply Was Helpful Thanks Kuldeep Bisht Technical Lead @ Simplion Technologies http://mssqlguide.kuldeepbisht.com
    • Editado Kuldeep Bisht quarta-feira, 7 de setembro de 2011 16:24
    • Sugerido como Resposta SpizardMac quinta-feira, 8 de setembro de 2011 22:32
    • Marcado como Resposta Alex Feng (SQL) terça-feira, 13 de setembro de 2011 08:52
    quarta-feira, 7 de setembro de 2011 16:24
  • Kuldeep,

    Thanks!  I have no idea why running the exec prior to the insert/exec works, but, it does for me as well.  No errors and I get the results as expected.

    Very curious....

     

     

    quinta-feira, 8 de setembro de 2011 22:29
  • Yes, this way am able to insert into table, but it would be nice to know the reason.
    San...
    terça-feira, 13 de setembro de 2011 08:59
  • Hello - have you resolved this problem?

    Are you using BEGIN/END in your stored procedure? I did a test with BEGIN/END and I get the error. I removed the BEGIN/END within the stored procedure and the issued resolved. 

    The BEGIN/END creates the 'nesting' within the stored procedure.

    Hope this helps.

    Kalman Kiss

    terça-feira, 17 de março de 2020 10:25