An INSERT EXEC statement cannot be nested error

Question

Post reply

An INSERT EXEC statement cannot be nested error

DBA-640728

SSChampion

Points: 12898
More actions
January 28, 2010 at 6:41 pm

#144562

Hello guys,
I want to monitor replication using sp_replmonitorhelpsubscription system proc. So want to get the resultset of this proc into a temporary table:
create table #repmonitor (
[status] int null,
warning int null ,
subscriber sysname null ,
subscriber_db sysname null ,
publisher_db sysname null ,
publication sysname null ,
publication_type int null ,
subtype int null ,
latency int null ,
latencythreshold int null ,
agentnotrunning int null ,
agentnotrunningthreshold int null ,
timetoexpiration int null ,
expirationthreshold int null ,
last_distsync datetime null ,
distribution_agentname sysname null ,
mergeagentname sysname null ,
mergesubscriptionfriendlyname sysname null ,
mergeagentlocation sysname null ,
mergeconnectiontype int null ,
mergePerformance int null ,
mergerunspeed float null ,
mergerunduration int null ,
monitorranking int null ,
distributionagentjobid binary(30) null ,
mergeagentjobid binary(30) null ,
distributionagentid int null ,
distributionagentprofileid int null ,
mergeagentid int null ,
mergeagentprofileid int null ,
logreaderagentname sysname null
)
insert into #repmonitor exec distribution..sp_replmonitorhelpsubscription @Publisher=' ',@publication_type=0
but when i execute this, i get the error below:
"Msg 8164, Level 16, State 1, Procedure sp_MSload_tmp_replication_status, Line 80
An INSERT EXEC statement cannot be nested."
the weird part is that it only happens sometimes, sometimes i get results with no errors and sometimes i do get the errror.
since it is using INSERT EXEC nested .
I have done some research and i found a workaround using
openrowset('SQLOLEDB','SERVER=(local);Trusted_Connection=yes;',
'
SET FMTONLY OFF;.....
but my prod server is not configured to use remote connections or use adhoc distributed queries and i really don't want to make these changes. Is there another workaround to get the resultset of the proc in some temp table? :blink:

Viewing 11 posts - 1 through 10 (of 10 total)

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

DBA-640728 SSChampion Points: 12898 More actions · Answer 1

i also saw that there is a way to fix this error by making the proc into a function, has anyone ever done that?

ken-688296 SSC Enthusiast Points: 114 More actions · Answer 2

Did you find an answer to this yet? I'm doing something very similar. What I've found out so far is that the proc sp_replmonitorhelpsubscription has an INSERT-EXEC in it, so we cannot use our own INSERT-EXEC. One per customer!

ken-688296 SSC Enthusiast Points: 114 More actions · Answer 3

This worked for me:

INSERT INTO MyDB.dbo.replStatus

SELECT a.*

FROM OPENROWSET

('SQLNCLI', 'Server=DBServer;Trusted_Connection=yes;',

'SET FMTONLY OFF; exec distribution..sp_replmonitorhelpsubscription

@publisher = DBServer,

@publication_type = 2,

@publication=MyPublication') AS a;

DBA-640728 SSChampion Points: 12898 More actions · Answer 4

Hi thanks for your reply, but as I said in the original post, I can't use openrowset, to use this I have to change some configuration in my server that is not feasible at this time, I read that maybe using XML could be another way to bypass this error.....

DBA-640728 SSChampion Points: 12898 More actions · Answer 5

DBA-640728

SSChampion

Points: 12898

February 14, 2010 at 9:47 pm

#1118654

Not yet, still with that problem 🙁

Paul White SSC Guru Points: 150468 More actions · Answer 6

You could write a CLR procedure to work around this restriction.

Or, if the server happens to be already running MSDTC, you could use a loopback linked server.

INSERT...EXECUTE (4-part name) works but required a distributed transaction (so MSDTC is needed).

If you are running 2008 MSDTC is not required for the loopback linked server if you configure it correctly.

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 7

You could write you own version of sp_replmonitorhelpsubscription , its not an encrypted procedure.

Clear Sky SQL
My Blog[/url]

Paul White SSC Guru Points: 150468 More actions · Answer 8

Dave Ballantyne (2/16/2010)
You could write you own version of sp_replmonitorhelpsubscription , its not an encrypted procedure.

Nice lateral thinking Dave.

If you do this, be sure to check your implementation after upgrades and service packs etc. to make sure nothing important has changed.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 9

Paul White (2/16/2010)
If you do this, be sure to check your implementation after upgrades and service packs etc. to make sure nothing important has changed.

Good Catch.

TBH im surprised that there are no more replication DMV's

http://msdn.microsoft.com/en-us/library/ms176053.aspx

Clear Sky SQL
My Blog[/url]

Paul White SSC Guru Points: 150468 More actions · Answer 10

Dave Ballantyne (2/16/2010)
TBH im surprised that there are no more replication DMV's
http://msdn.microsoft.com/en-us/library/ms176053.aspx

True. It seems to be taking even longer to get DMVs for replication than for full-text search!

It may be just me, but replication has always felt just a bit 'unfinished' to me, and quite inconsistent in places.

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi