March 14, 2013 at 2:21 am
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
March 14, 2013 at 6:24 am
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.
March 14, 2013 at 8:52 am
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
March 14, 2013 at 9:13 am
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.
March 14, 2013 at 9:33 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply