locked
sp_dbmmonitorupdate syntax error RRS feed

  • Question

  • I set up mirroring for a database running on SQL 2005 SP1.  The mirroring itself works fine, but the automatically created Database Mirroring Monitor job fails with the following syntax error:

     

    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near 'Data'.
    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.

     

    The same error occurs if I run sp_dbmmonitorupdate interactively. The mirrored database is named [Business Data].  I suspect that the blank space in the database name may be the source of the problem.  Can anyone confirm this?  Is it a known bug?

     

    Thanks!

    Tuesday, May 1, 2007 9:28 PM

Answers

  • Yes, the space in the database name is the issue.  The code isn't expecting to have spaces in database names and so does not account for adding in the brackets.  (I won't even get started on how I hate spaces in object names.....)  Don't know if it is a known bug or not.  I'd have to shove a database with a blank into an SP2 + hotfix server to see if it still throws an error.  From looking at the code, it doesn't appear that it's still a problem, because it relies on the database ID and a variable instead, but I'd still have to test it explicitly.
    Wednesday, May 2, 2007 8:11 PM

All replies

  • Yes, the space in the database name is the issue.  The code isn't expecting to have spaces in database names and so does not account for adding in the brackets.  (I won't even get started on how I hate spaces in object names.....)  Don't know if it is a known bug or not.  I'd have to shove a database with a blank into an SP2 + hotfix server to see if it still throws an error.  From looking at the code, it doesn't appear that it's still a problem, because it relies on the database ID and a variable instead, but I'd still have to test it explicitly.
    Wednesday, May 2, 2007 8:11 PM
  • Thanks for the information.  I'm not wild about spaces in object names, either, but it wasn't my choice ...   I tested it on an SP2 box and still got the error, but I haven't loaded any hotfixes yet.
    Thursday, May 3, 2007 1:30 PM
  • Just adding to this for anyone else who reads this forum...

     

    A bug has already been filed for this issue. 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.

     

    Cheers

     

    Rolf

    Monday, November 12, 2007 6:35 AM
  • Any Idea if this has been fixed by now...
    I'm looking forward for solution urgently otherwise I'll have to go by Old logshipping method...

    :-(
    Friday, May 8, 2009 12:42 PM