locked
WMI Query for Database Mirroring RRS feed

  • Question

  • I am executing the WMI query below on powershell to check the Database Mirroring Status but not getting any sought of output.

    gwmi -namespace "root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER" -Query "Select * from database_mirroring_state_change"

    I have tried this from SQL server thru creating the Alerts but no result as well. Here i am getting the below error

    " The @wmi_query could not be executed in the @wmi_namespace provided". Although i have assigned the proper rights to this namespace \\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER but still no luck.

    Service Broker is also enabled on msdb DB.

    Could someone help me why i am not able to get thru either from SQL server or Powershell.
    Wednesday, January 27, 2010 4:30 PM

Answers

  • Resolved the issue by adding SQL server agent account to sysadmin server role.
    • Marked as answer by SNITINKUMAR Tuesday, February 2, 2010 5:53 PM
    Tuesday, February 2, 2010 5:53 PM

All replies

  • I am sure there are no tables with the name 'database_mirroring_state_change' , i think your SQL query should read as


    select
    mirroring_state_desc from sys.database_mirroring


    Thanks, Leks
    Thursday, January 28, 2010 10:48 PM
    Answerer
  • Hi,

    The following KB article reports the issue and provides the hotfix which is included in the Service Pack 1 for SQL Server 2008:
    http://support.microsoft.com/kb/960573

    If there are any more questions, please let me know.
    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Friday, January 29, 2010 9:08 AM
  • I am running with SP1 on SQL 2008 but still having an issue.

    I have given full admin rights to the ROOT folder under WMI control and also to their namespaces but still having an issue.

    The problem is whenever i fired the above query from Powershell , the query is executing without an error but does not come up with any sought of result rather blank screen . At the same time if i fire the same query from SQL server while creating the alert its giving me  " The @wmi_query could not be executed in the @wmi_namespace provided".

    I have also checked the same with wbemtest.exe.
    Friday, January 29, 2010 11:08 AM
  • Yes , you are right there is no table in the SQL server but here i am talking about WMI Query which is having so many classes and DATABASE_MIRRORING_STATE_CHANGE is one of them to check the Database mirroring status.
    • Marked as answer by SNITINKUMAR Tuesday, February 2, 2010 5:52 PM
    • Unmarked as answer by SNITINKUMAR Tuesday, February 2, 2010 5:52 PM
    Friday, January 29, 2010 11:10 AM
  • Resolved the issue by adding SQL server agent account to sysadmin server role.
    • Marked as answer by SNITINKUMAR Tuesday, February 2, 2010 5:53 PM
    Tuesday, February 2, 2010 5:53 PM
  • Resolved the issue by adding SQL server agent account to sysadmin server role.

    Thanks for your sharing! It's comprehensive.
    Saturday, January 22, 2011 4:43 AM