The sample downloadable solution (zip) is entirely developed using Visual Studio.NET 2003 Enterprise Architect on Windows Server 2003 Standard Edition together with SQL Server 2000 Enterprise Edition. But, I am confident that it would work with other versions of Windows (which support .NET 1.1) as well.
WMI with SQL Server 2000?
Recently, I received a question from one of the readers about whether WMI can be used with the SQL Server 2000 database. That question really brought me up on my toes, so I immediately started an article to achieve the same. Not every developer would know that we can manage SQL Server 2000 using WMI. Another question you might want to ask would be “Is it really necessary to work with WMI?” The answer would be that it is not quite necessary. You have SQL-DMO, which is quite well matured as compared with WMI (of course it cannot even compare). I already introduced SQL-DMO at the following link.
SQL-DMO is generally used only by SQL server developers and not by system administrators. What if the system administrator wants to access SQL server database information using a common programming model? The answer would be WMI, because WMI is specially made with them (and also with WBEM) in mind. And I introduced WMI with Visual Basic.NET at the following link.
As everyone knows by now, WMI is the initiative for WBEM (Web Based Enterprise Management) to fulfill Microsoft's vision. It has nearly entered into the market of system administrators at the moment. But, in the future, there is no doubt that it would further penetrate into the developer’s world as well. WMI is mainly used to manage tasks. The tasks may not be related to a single piece of software or component. WMI almost works with every Microsoft product.
Just imagine that you have Microsoft products already designed into about 15 to 20 servers (for various scenarios) and you would like to manage several instances of all of them using a common “language.” And WMI achieved it. WMI can work with almost all of them (as almost all of those servers support WMI internally).
And another exciting point about WMI is that you can work with it using either VBScript at the command prompt (or ASP for the web) together with WSH (Windows Script Host), or you can develop very effective applications using the .NET framework. The .NET framework fully supports WMI natively (which is also good news for ASP.NET developers who develop web applications).
As I already introduced the concept of WMI in several articles at this site, I am not going to cover any fundamentals of WMI in this article. In this article, I mainly focus on programming WMI using Visual Basic.NET, with the target server as SQL Server 2000 (which is not well-known to many developers).
First of all, you need to install “WMI SQL Server Administration Provider” on an existing instance of SQL Server 2000 (if not installed). This is not part of the default or custom installation. You need to install it separately from SQL server installations. You can find the “WMI SQL Server Administration Provider” installation files at the “x86\other\wmi” path of your SQL Server 2000 installation CD. To be frank, I really didn’t check this on SQL Server 7.0. So, my focus will always be on SQL Server 2000 in this article.
Once it is installed (which is as simple as winzip installation), you can directly work with my downloadable solution. The first button (captioned “version”) within the form tries to give you the SQL server version you installed on your computer. If you can get it, you can confirm that WMI works with your version. Let us go through the code I wrote:
Private Sub btnCaption_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCaption.Click
Dim PrivateLateBoundObject As ManagementObject
PrivateLateBoundObject = New ManagementObject(Nothing, New ManagementPath("\\server\root\MicrosoftSQLServer: MSSQL_SQLServer.Name=""(local)"""), Nothing)
From the code above, you can understand that I am creating an object (PrivateLateBoundObject) based on the “ManagementObject” class. The “ManagementPath” starts with “\\yourhostname”. There exists a WMI class called “MSSQL_SQLServer” within the “MicrosoftSQLServer” namespace (which comes onto the scene after you installed the “WMI SQL Server Administration Provider”).
The “(local)” says that I am trying to connect to my “local instance” within the same host name I provided. There exists a WMI property called “VersionString” which is used to retrieve the version information of SQL Server 2000 you installed on your computer. You should get an output (Message box) something like the following figure.
Now we come to dealing with SQL Server information. How do we retrieve the “databases” available in SQL Server 2000 using WMI? Actually it is simpler than you might have expected. Let us go through the following code:
Private Sub btnListDatabases_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnListDatabases.Click
Dim PrivateLateBoundClass As ManagementClass
PrivateLateBoundClass = New ManagementClass(Nothing, New ManagementPath("\\server\root\MicrosoftSQLServer: MSSQL_Database"), Nothing)
Dim dbs As ManagementObjectCollection = PrivateLateBoundClass.GetInstances()
Now, I am working on the “ManagementClass” rather than the “ManagementObject”. Even though I didn’t specify that it needs to connect to “(local)”, it would automatically get connected to the local database!
“MSQL_Database” is the second WMI class I used now (in the above code), to retrieve all of the database names existing in the local SQL Server 2000 instance. As there may exist more than one database, I need to work with the “collection” (which is why I used “ManagementObjectCollection”). The following is the loop, which helps us to retrieve all the database names automatically.
Dim dbs As ManagementObjectCollection = PrivateLateBoundClass.GetInstances()
“GetInstances” now would retrieve all “database” instances. The loop iterates through each database instance, and appends the value of “SQLServerName” and “Name” (database name), which are WMI properties, to the string “sdb”. Finally, it displays a message box with the string (list of database names) we framed.
Now we come to dealing with SQL Server table information. How do we retrieve the “tables” available in SQL Server 2000 database using WMI? Here, in this example, I am trying to filter based on a particular “database”. Let us go through the following code:
Private Sub btnListTables_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnListTables.Click
Dim PrivateLateBoundClass As ManagementClass
PrivateLateBoundClass = New ManagementClass(Nothing, New ManagementPath("\\server\root\MicrosoftSQLServer: MSSQL_Table"), Nothing)
Dim tables As ManagementObjectCollection = PrivateLateBoundClass.GetInstances()
Now, I am working on “ManagementClass” rather than “ManagementObject”. Even though, I didn’t specify that it needs to connect to “(local)”, it would automatically get connected to the local database!
“MSQL_Table” is the third WMI class I used now (in the above code), to retrieve all of the table names existing in the local SQL Server 2000 database. As there may exist more than one table in a single database (or more databases), I need to work with the “collection” (which is why, I used “ManagementObjectCollection”). The following is the loop, which helps us to retrieve all the table names of a particular database “Northwind” automatically.
Dim tables As ManagementObjectCollection = PrivateLateBoundClass.GetInstances()
“GetInstances” now would retrieve all “tables” available. The loop iterates through each table or WMI instance, and appends the value of “SQLServerName” , “DatabaseName” and “Name” (table name), which are WMI properties, to the string “sTable”. Finally, it displays a message box with the string (list of table names) we framed. You can also observe that I filtered the tables of a particular database using an “IF” condition.
Summary:
I hope now you can confirm that we can use WMI to interact with SQL Server 2000 together with the .NET framework. As this is an introductory article, I could not go up to the level of “managing SQL Server 2000”. But you can expect a few more articles on this subject soon. Check regularly on this site or sign up for the newsletter.
You can further enhance the coding I provided with list boxes, dropdown lists, and so forth. You can also shift this code to ASP.NET. And that gives us a way to manage SQL Server 2000 using a web application with WMI!
Any comments, suggestions, bugs, errors, feedback etc. are highly appreciated at jag_chat@yahoo.com.
Visual Basic.NET 
ASP.NET Export to Excel and Word using VB.NET and C#
