Database Mirroring sys.sp_dbmmonitorupdate stored procedure fails with error "Incorrect syntax near '-'"
Recently got stuck with the stored procedure sys.sp_dbmmonitorupdate which is used to monitor the database mirroring session. As usual you could do using the GUI tool, but sometimes using TSQL methods is good enough to see what is happening on your system.
On the system with SQL 2005 Service pack2 when executing this stored procedure getting the error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Also you could try running, exec @retcode = sys.sp_dbmmonitorMSgetthelatestlsn @database_name, @end_of_log_lsn output
As referred the SQL Server agent executes this stored procedure whenever the Database Mirroring monitor job requires the update or requested by the user. To fix this a bug has been raised on Connect site with the ID: 546359. The bug says there is a problem when the database name has either a SPACE or a HYPHEN.
As far as a fix is concerned, there is none in sight.
Options -
1) Change the DB Name
2) Roll your own version of the mirror monitor job/procedure and skip all those databsaes with SPACE or HYPHEN.
The msdb.sys.sp_dbmmonitorupdate can be called with a DB Name parameter - so it can be run in a wrapper for specific databases.
Further information on monitoring a Database Mirroring session refer to http://www.microsoft.com/technet/prodtechnol/sql/2005/mirroringevents.mspx link.
**__________________________________**
SQL Server MVP, Sr. DBA & industry expert.
-
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.
