Interesting issue with INSER-EXEC statement

• SQL Severe DBA

  Hall of Fame

  Points: 3579

  More actions

  June 23, 2010 at 11:46 pm

  #140878

  Hi guys ,

  I am working Replication monitoring scripts and scripted code for replication latency output .

  The interesting part is I am able to get the output most of the time and some time it fails i can

  3/10 failure .The main issue is occuring while executing the below part of code .

  DECLARE replmonitor CURSOR FOR

  select b.srvname,

  a.publisher_db,

  a.publication

  from distribution.dbo.MSpublications a,

  master.dbo.sysservers b

  where a.publisher_id=b.srvid

  OPEN replmonitor FETCH NEXT

  FROM replmonitor into @srvname,@pub_db,@pubname WHILE @@FETCH_STATUS = 0

  BEGIN

  insert into #replmonitor exec distribution.dbo.sp_replmonitorhelpsubscription

  @publisher = @srvname ,

  @publisher_db = @pub_db ,

  @publication = @pubname ,

  @publication_type = 0

  FETCH NEXT FROM replmonitor

  into

  @srvname,

  @pub_db,

  @pubname

  END

  CLOSE replmonitor DEALLOCATE replmonitor

  Error output is :

  Msg 8164, Level 16, State 1, Procedure sp_MSload_tmp_replication_status, Line 80

  An INSERT EXEC statement cannot be nested.

  I will highly appreciate If some one really has solution for this .

  Thanks

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  June 24, 2010 at 1:35 am

  #1184353

  Duplicate post.

  Replies here, please:

  http://www.sqlservercentral.com/Forums/FindPost942232.aspx

  -- Gianluca Sartori