Digging into SQL Server 2000 with WMI Using Visual Basic.NET and VBScript - How to add a primary key to a table using WMI

(Page 5 of 6 )

This aspect is a bit different from all of the above, but interesting. To add a new primary key to an existing table, we need to provide some of the parameters, such as server name, database name, table name, column name, name of the primary key, and so forth.  It would involve some input parameters to pass, along with invoking a WMI method.  Let us have the code first.

Try
        Dim classInstance As New ManagementClass( _
            "root\MicrosoftSQLServer", _
            "MSSQL_PrimaryKey", Nothing)

        Dim inParams As ManagementBaseObject = _
                    classInstance.GetMethodParameters("Create")

        inParams("Clustered") =  true
        inParams("ColumnName") =  "empno"
        inParams("DatabaseName") =  "employee"
        inParams("Name") =  "pk_emp_empno"
        inParams("SQLServerName") =  "(local)"
        inParams("TableName") =  "emp"

        Dim outParams As ManagementBaseObject = _
                    classInstance.InvokeMethod("Create", inParams, Nothing)
    Catch err As ManagementException
        MessageBox.Show("Error: " & err.Message)
    End Try

The most important class note from the above code fragment is “MSSQL_PrimaryKey”, which is a WMI class especially available for SQL Server 2000.  It has several properties to give us increasing amounts of information.  In this scenario, however, I am not retrieving any information.  Instead, I am trying to execute a method against the SQL Server database using WMI.

“inParams” (which is of type System.Managment.ManagmentBaseObject) is mainly used to pass parameters (input parameters) to the “Create” method (WMI method) dynamically.  Similarly, “outParams” generally contains the result of the method execution. 

In the above script, I worked with “InvokeMethod”, which is used to execute a WMI method dynamically. 

The VBScript version of the same would be as follows:

strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\MicrosoftSQLServer")
Set objShare = objWMIService.Get("MSSQL_PrimaryKey")
Set objInParam = objShare.Methods_("Create"). _
    inParameters.SpawnInstance_()
objInParam.Properties_.Item("Clustered") =  true
objInParam.Properties_.Item("ColumnName") =  "empno"
objInParam.Properties_.Item("DatabaseName") =  "employee"
objInParam.Properties_.Item("Name") =  "pk_emp_empno"
objInParam.Properties_.Item("SQLServerName") =  "(local)"
objInParam.Properties_.Item("TableName") =  "emp"
Set objOutParams = objWMIService.ExecMethod("MSSQL_PrimaryKey", "Create", objInParam)

Next: Can we rename a database using WMI? >>
 

More MS SQL Server Articles
More By Jagadish Chaterjee

Comments On: Digging into SQL Server 2000 with WMI Using Visual Basic.NET and VBScript

· hello guys...WMI with SQL Server has gone to further depth..have the article and...    · I want to connect to remote sql server by using vb.net code. I am using...   >>> Post your comment now!