"An Insert EXEC statment cannot be nested" problem

Question

I have to create a report that shows latencies on our mirroring system(We are using SLQ SERVER 2008 R2)I have created the following SP,but when I run it sometimes I recieve the following error message:

An Insert EXEC statment cannot be nested

I completely understand the rpoblem ,but is there any work around for this?

Thanks

create 

procedure [dbo].[sdcp_DBAU_GetDBMirroringLatency]

@DatabaseName

varchar(50)

as

begin

set nocount on

create table #MirrorStats

(

database_name

sysname, -- Name of database

role tinyint, -- 1 = Principal, 2 = Mirror

mirroring_state

tinyint, -- 0 = Suspended, 1 = Disconnected, 2 = Synchronizing, 3 = Pending Failover, 4 = Synchronized

witness_status

tinyint, -- 1 = Connected, 2 = Disconnected

log_generation_rate

int null, -- in kb / sec

unsent_log

int, -- in kb

send_rate

int null, -- in kb / sec

unrestored_log

int, -- in kb

recovery_rate

int null, -- in kb / sec

transaction_delay

int null, -- in ms

transactions_per_sec

int null, -- in trans / sec

average_delay

int, -- in ms

time_recorded

datetime,

time_behind

datetime,

local_time

datetime -- Added for UI

)

insert into

#MirrorStats

exec msdb.sys.sp_dbmmonitorresults @DatabaseName ,3,1

Select

database_name

,

max(unsent_log) unsent_log,

max(time_recorded) time_recorded

from

#MirrorStats

where unsent_log > 200

group by database_name,convert(varchar(2),time_recorded,108)

order by database_name,unsent_log desc

end

---

A.G

Answer 1

First, the web UI includes a button with two arrows pointing from each other. This secret button permits you to insert code without the web UI mangling the code completely.

Next, yes, there is a solution: write a CLR stored procedure that calls the procedure and the writes the data to the temp table. Quite a bit of a kludge. I have an example of this in my article "How to share data between stored procedures", http://www.sommarskog.se/share_data.html

---

Erland Sommarskog, SQL Server MVP, [e-mail]