Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1896
  • Last Modified:

SQL Server SMO exception when checking if SQL Server Agent is running.

I have an application built in C#  that uses SQL Server SMO library to push out SQL Server code. One of the functions I have (attached code snippet) checks if SQL Server Agent is running on a specific server. I have the name of the server to the function and it returns True or False.

We're getting the following error when attempting to call this function on a SQL Server 2000 box.

Microsoft.SqlServer.Management.Smo.SmoException: SQL Server WMI provider is not available on DRGP1. ---> System.Management.ManagementException: Invalid namespace
   at System.Management.ManagementScope.Initialize()
   at System.Management.ManagementScope.Connect()
   at Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer.TryConnect()
   --- End of inner exception stack trace ---
   at Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer.TryConnect()
   at Microsoft.SqlServer.Management.Smo.Wmi.WmiSmoObject.get_Proxy()
   at Microsoft.SqlServer.Management.Smo.Wmi.WmiSmoObject.EnumChildren(String childTypeName, WmiCollectionBase coll)
   at Microsoft.SqlServer.Management.Smo.Wmi.ServiceCollection.InitializeChildCollection()
   at Microsoft.SqlServer.Management.Smo.Wmi.ServiceCollection.get_Count()

Also does anyone know if there is an issue if to ManagedComputer I pass a server name that includes the SQL Server instance name too, this is not the case for the server that's throwing the exception but wanted to know anyways.
public static bool IsAgentRunning(string strServer)
        {
            bool bRunning = false;
 
            try
            {
                ManagedComputer mc = new ManagedComputer(strServer);
 
                //traverse every running service on the server
                for (int nIndex = 0; nIndex < mc.Services.Count; nIndex++)
                {
                    Service s = mc.Services[nIndex];
 
                    //if the service is out sql server agent
                    if (s.DisplayName.StartsWith("SQL Server Agent", StringComparison.OrdinalIgnoreCase))
                    {
                        //if sql server agent is not running
                        if (s.ServiceState != ServiceState.Running)
                        {
                            s.Start(); //try starting the agent
                            s.Alter();
                        }
 
                        System.Threading.Thread.Sleep(10000);
                        s.Refresh(); //refreshes the service
 
                        //if the SQL Server agent servce is running
                        if (s.ServiceState == ServiceState.Running)
                        {
                            bRunning = true;
                        }
 
                        break;
                    }
                }
            }
            catch (Exception ex)
            {
                bRunning = true;
                GlobalFunc.Log(ex.ToString(), "GlobalFunc.IsAgentRunning()");
            }
 
            return bRunning;
        }

Open in new window

0
nyphalanx
Asked:
nyphalanx
  • 2
1 Solution
 
MohammedUCommented:
SMO is introduced in SQL 2005 and I believe it works in 2005 and later not in 2000...
For SQL 2000 you have to use DMO...
0
 
nyphalanxAuthor Commented:
No, SMO is also compatible with SQL Server 2000. I've used it without a problem in the past on SQL Server 2000. I think this is related to the ManagedComputer object.
0
 
MohammedUCommented:
My bad...you are right... you can use SMO to connect to SQL 7 and 2000 also.... However, there are some objects and properties that only work with SQL Server 2005.
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
This error is related to not having the WMI provider on the SQL 2000 box.  The Windows Management Instrumentation framework must be enabled and started on that machine.  You also have to have rights on that box as well.  This looks to me like the SQL 2000 server is not the same server that you execute this code on (physically).
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now