Save SP to table

Question

Post reply

Save SP to table

stryk

Ten Centuries

Points: 1172
More actions
March 14, 2013 at 2:21 am

#151210

Hi there!
Hmmm ... actually I read a lot of articles about how to accomplish that, and indeed I could make this work in another case ... but this time I always get an error like "you cannot have nested INSERT EXEC commands" or something (the original message is in German).
This is what I try:
CREATE TABLE #mirroring_stats
(
[database_name] SYSNAME
,[role] INT
,[mirroring_state] INT
,[witness_status] INT
,[log_generation_rate] INT
,[unsent_log] INT
,[send_rate] INT
,[unrestored_log] INT
,[recovery_rate] INT
,[transaction_delay] INT
,[transactions_per_sec] INT
,[average_delay] INT
,[time_recorded] DATETIME
,[time_behind] DATETIME
,[local_time] DATETIME
)
INSERT INTO #mirroring_stats
(
[database_name]
,[role]
,[mirroring_state]
,[witness_status]
,[log_generation_rate]
,[unsent_log]
,[send_rate]
,[unrestored_log]
,[recovery_rate]
,[transaction_delay]
,[transactions_per_sec]
,[average_delay]
,[time_recorded]
,[time_behind]
,[local_time]
)
EXEC msdb..sp_dbmmonitorresults
@database_name = 'NAV_Mirroring'
,@mode = 0
,@update_table = 1
SELECT * FROM #mirroring_stats
DROP TABLE #mirroring_stats
So actually I want to save the output of sp_dbmmonitorresults into a temporary table ...
What am I doing wrong???
Any help appreciated!
Cheers,
Jörg
Jörg A. Stryk
MVP - MS Dynamics NAV

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

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

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 1

Does this return anything?

IF OBJECT_ID ( N'msdb.dbo.dbm_monitor_data', N'U' ) IS NULL

SELECT 'No data'

ELSE

SELECT * FROM msdb.dbo.dbm_monitor_data

or this?

SELECT

*

FROM

msdb.dbo.dbm_monitor_data

WHERE

database_id = OBJECT_ID(''NAV_Mirroring'')

Maybe there's no data to read? On my local machine these commands return null.

If I run the procedure

EXEC msdb..sp_dbmmonitorresults

@database_name = 'LocalTestDB'

,@mode = 0

,@update_table = 1

I get this error:

Msg 32039, Level 16, State 1, Procedure sp_dbmmonitorupdate, Line 99

The database 'LocalTestDB' is not being mirrored. No update of the base table was done.

I also noticed that the mirroring stats values as declared inside msdb..sp_dbmmonitorresults are TINYINT rather than INT on a few of the variables. I don't see how that would make any difference, but it's just an observation.

Sorry I can't offer any more help than this.

stryk Ten Centuries Points: 1172 More actions · Answer 2

Thanks for your reply. Yes, I have create a test scenario making sure there is some data; your queries give indeed some result.

Also when I just execute this ...

EXEC msdb..sp_dbmmonitorresults

@database_name = 'NAV_Mirroring'

,@mode = 0

,@update_table = 1

... I get/see the desired data. But now I want to save this into a #table; but when I try this, I get this "nesting INSERT EXECUTE" error ...

But maybe there would be a different way? Actually all this is to finally read the "time_behind"!

Is there an easier way to retrieve this info?

Jörg A. Stryk
MVP - MS Dynamics NAV

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

The system procedure msdb.sys.sp_dbmmonitorresults calls msdb.sys.sp_dbmmonitorupdate. Within this procedure there is an insert into @results exec (@command). As such, you use insert into #mirroring_stats exec msdb..sp_dbmmonitorresults @database_name = 'NAV_Mirroring', @mode = 0, @update_table = 1.

stryk Ten Centuries Points: 1172 More actions · Answer 4

Lynn, you saved my day

OK, now I run the "update" separately ...

EXEC msdb.sys.sp_dbmmonitorupdate @database_name = 'Navision-SQL'

... and then I execute the "results" without the update:

EXEC msdb..sp_dbmmonitorresults

@database_name = 'Navision-SQL'

,@mode = 0

,@update_table = 0

Now everything could be inserted into that table for further processing :w00t:

Thank you very much for your help!

Jörg A. Stryk
MVP - MS Dynamics NAV

Save SP to table

Cookies on SQLServerCentral