locked
Database Mirroring Monitor Job Error RRS feed

  • Question

  • We have recently set up two SQL Server 2005 Standard Edition servers with database mirroring.  The mirrors function and fail over without a problem, but the Database Mirroring Monitor Job fails every time with the following error message:

     

    Incorrect syntax near '.'. [SQLSTATE 42000] (Error 102)  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. [SQLSTATE 42000] (Error 319).  The step failed.

     

    Both SQL Servers are running SP2 with the latest patches. 

     

    Can you please let me know what we have to do to resolve this issue?

     

    Thank you!

    Bosko

    Monday, April 9, 2007 4:46 AM

All replies

  • Is this been like this since the invocation of Database mirroring on these instances?

    If not see what has been changed since the last successful occurence.

    Sunday, April 15, 2007 6:46 PM
  • The problems started upon implementation of the database mirroring
    Monday, April 16, 2007 4:19 AM
  • I'm also facing the same issue.

    Did anyone find a solution or workaround?

     

     

    Thursday, September 20, 2007 5:59 PM
  •  

    I posted this in a couple different forums/newsgroups and never got an answer.  We are still having this problem.

     

    Bosko

    Thursday, September 20, 2007 6:09 PM
  • Not sure of your specific situation but there is an issue with the monitor job when the database name has a SPACE or HYPHEN in the name.

     

    A bug has already been filed for this issue: bug ID: 546359.

     

    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 and skip those with space or hyphen.

     

    Hope this helps?

     

    - Rolf

     

    Monday, November 19, 2007 12:54 AM
  • Hi Rolf,

     

    Thank you for the information, but none of our databases have hyphens or spaces in them.  Are there any other reasons that the problem would arise?

     

    Thanks!

    Bosko

     

    Monday, November 19, 2007 6:11 AM
  • I've come across the same probem. I found that when I initially setup the Mirroring. This job was working without error. Its after I initialised the Failover from one server to the other (Changing the roles of the principle and the Mirror) this occured.

     

    I even swapped them back and still have the issue. I even ended mirroring. Droped the jobs and then re-started mirroring and it still failed with the same error.

     

    I posted a new thread today and then found this one.

     

    If anyone has any ideas, please let me know.

     

    I can monitor through the GUI ok. But that means I have leave it running along with SSMS. Something I dont want to do.

     

    If I find a solution I will post it. If anyone else finds it, please post it.

     

     

     

    Tuesday, June 3, 2008 3:19 PM
  • Hi All

     

    Following up for this.

     

    I installed SQL 2005 Cumulative Update 5 for SP2 and it resolved the SPACE and HYPHEN issue in the database names. It may solve your specific issue as well - though I have not experienced that one on any of my installs so cannot be sure.

     

    http://support.microsoft.com/kb/943656

     

    Suggest to give it a try on a test SQL box and check out if it resolves the problem.

     

    Cheers - Rolf

     

    Thursday, June 5, 2008 8:43 AM