Chapter 35 - Using Visual Basic to Remotely Manage SQL Server 2000

In a world where data must be available 24 hours a day, 7 days a week, database administrators (DBAs) are in demand. It is not always possible to staff data operation centers with fully-qualified SQL DBAs. This chapter shows you how to construct such a tool using SQL Database Management Objects (SQL-DMO) and SQL Namespace (SQL-NS) objects with Microsoft® Visual Basic®.

SQL-DMO and SQL-NS are COM interfaces you can access from Visual Basic or any other COM-speaking language. SQL-DMO is a collection of objects that encapsulate SQL Server's database, scheduling, and replication management. SQL-NS is a collection of objects, accessed slightly differently, that encapsulate the functionality in SQL Server Enterprise Manager.

One advantage of passing SQL Server management tasks through a COM interface is the extra layer of protection it provides. For example, if several people in your organization need remote SQL Server administrative capability, but you want to maintain specific control of what they can do or access after they are inside the system, SQL-DMO can provide a solution. This chapter demonstrates a sample Visual Basic application, the SQL Junior Administrator, that provides administrative capability without granting total access.

As mentioned, SQL-DMO exposes SQL Server as a series of objects in collections. The Application object contains all the SQL Server objects, each of which contains a collection of Database objects. Each Database object holds a group of collections, such as Tables, each of which is made up of a series of objects, such as Column objects, each of which has a series of properties. There are different building blocks for each of the collections but they share the same general architecture. SQL-DMO allows programmatic access to every layer.

Using SQL-DMO and SQL-NS, you can perform almost any administrative or management task through simple dot notation coding. The major difference between SQL-DMO and SQL-NS is in the way they allow access to the server objects. SQL-DMO uses a collection of collections, and SQL-NS uses a tree-and-node model. Although you can manipulate SQL Server through either one, most of this chapter will focus on SQL-DMO. For more information about SQL-DMO and SQL-NS, see SQL Server Books Online.

The sample application discussed in this chapter uses only a few of the ways that you can manipulate a SQL Server database using SQL-DMO. In fact, almost every property of every object in the database can be manipulated including logins, indexes, relationships, foreign keys, and triggers.

Inside the SQL Junior Administrator Application

The SQL Junior Administrator sample application is provided on the accompanying CD. The code is immediately usable, and it can be easily modified or upgraded for more secure or thorough functionality.

User Interface

The application provides a simple graphical user interface, through which users can log in, view existing data objects, create new data objects, and access a subset of the SQL Server tools.

Log In

Open SQLDMO.exe on the accompanying CD. The drop-down box displays any SQL Server instances available on your network or local computer and allows for login.

Note This sample application contains only basic error handling functionality.

Setting Permissions

Successful login brings you to the main screen. You can create and view server logins without selecting a database, but if you want to perform database-specific actions involving tables or stored procedures, you must select a database.

To create a login, on the Create menu click Login.

After you enter the login information, a list box appears, displaying the names of the databases found on that server instance. To add user permissions to an individual database, select the database in that list and then confirm the choice in the dialog box that appears.

The application automatically adds DB_DDLADMIN and DB_DATAWRITER permissions to the chosen database. These permissions allow users to create and modify new tables as well as view and edit data in existing tables.

To view existing logins, click Logins on the View menu. To delete a login, select it in the list box, and then confirm your choice.

Viewing Existing Data Objects

After the user has been given the correct permissions, existing tables and stored procedures within that database are available through the View menu. The stored procedures themselves cannot be modified, but the columns within a chosen table can be seen and added to (although the properties of existing fields cannot be changed from this application). For example, to view existing data objects, click Table on the View menu.

After you select a table, two buttons appear, offering a choice to add a new column or to view existing columns. Clicking Add Column causes several fields to appear on the form in which the basic properties of a new column can be set. (Only the properties required for the creation of a new column are present here, though every column property can be set programmatically.) To create the new column, set the property values and then click Add Column. Clicking View Columns produces a list box showing all available columns. After you select a column, fields appear, containing the existing column properties.

Note It is not possible to modify existing column properties using this application.

Creating New Data Objects

You can also create new data objects from this interface. For example, to create a new table in a database, click Table on the Create menu and then fill in the form fields. After you enter the name for the new table, you can use the controls that appear to enter property information for new columns. (The TableName_ID column, which is an ID column, is created automatically.)

Using SQL Server Tools

The Application tab provides access to the administrative functionality, such as wizards and backup, you can use through SQL-DMO.

The list box on the left shows all existing logs in that instance of SQL Server, the list box on the right shows all existing backup devices, and the Change SQLServers button opens the main form and allows you to log into a different SQL Server database.

This form only allows you to view existing objects, such as backup devices. If you want to add functionality to create new objects, follow this sample application's model for creating tables and columns. You just need to provide the code to set or access the correct properties within the appropriate collection. SQL-DMO does the rest.

Note that frmApp provides a higher level of access than other parts of this application. The DB Wizards button allows direct access to the SQL Server Enterprise Manager wizards. These wizards allow the user to implement a wide variety of administrative tasks. Clicking this button opens the master wizard, from which any of the other wizards can be selected and put into action. The full functionality of these wizards is too extensive to be covered here, but it includes all of the individual processes shown on frmApp. For more information about the SQL Server Enterprise Manager wizards, see SQL Server Books Online

Note The SQL Server Enterprise Manager wizards are accessed through SQL-NS, not SQL-DMO. For more information, see "Application Tasks" in this chapter.

Visual Basic Code

By exploring the code behind the user interface, you can learn more about using SQL-DMO and SQL-NS to implement custom Visual Basic solutions. This section follows the path a user might follow, starting with login and progressing through different tasks. Here is the code behind the first login screen:

Private Sub Form_Load()

On Error GoTo ErrorHandler

Dim SQL7 As New SQLDMO.Application
Dim NameList As SQLDMO.NameList

Set NameList = SQL7.ListAvailableSQLServers
' Show all available servers running SQL Server
Dim x As Integer
For x = 0 To NameList.Count
Me.lstSrvrs.AddItem NameList(x)
Next

' Clean up
Set SQLInstance = Nothing
Set NameList = Nothing

Exit Sub

ErrorHandler:

MsgBox "Unable to get list of servers."

End Sub

This subroutine uses the ListAvailableSQLServers method of the Application object to populate the NameList DMO object when the form loads. The NameList object is a collection object, which the code iterates through to produce a list box that contains all the instances of SQL Server that are available to that computer.

The following subroutine records the user-entered information and attempts to log in to the selected instance of SQL Server through the Connect property of the instance. Each required layer of the SQL-DMO nest must be instantiated.

Private Sub btnGo_Click()

On Error GoTo ErrorHandler

' Set application variables for login
Dim SQL7 As New SQLDMO.Application
Dim SQLInstance As SQLDMO.SQLServer
Set SQLInstance = New SQLDMO.SQLServer
' Log in
SQLInstance.Connect Me.lstSrvrs.Text, Me.txtLogin.Text, Me.txtPassword.Text

' If OK then
Set SQLInstance = Nothing
Set SQL7 = Nothing
' Set global variables
ServerName = Me.lstSrvrs.Text
User = Me.txtLogin.Text
Password = Me.txtPassword.Text
' Open the body of the program
Load frmMain
Me.Hide
frmMain.Show

Call HideOthers("frmMain")

Exit Sub

ErrorHandler:

MsgBox "Unable to log you in to " & ServerName & "."

End Sub

The final, one-line subroutine sets the cursor into the login box automatically after a SQL Server instance is selected:

Private Sub lstSrvrs_Click()

Me.txtLogin.SetFocus

End Sub

Note Global variables, as set in the btnGo_Click subroutine after the successful login, are rarely the best way to store user information. On the other hand, the global module may be the perfect place to initialize application and server instances, which are required for every task in SQL-DMO, although those initializations are included in every subroutine here. This particular application has been written to be immediately usable and at the same time easily modified or upgraded for more secure or thorough functionality.

The HideOthers function called from the btnGo_Click subroutine can be found in the global module. It takes the name of one form (the one the user has selected to see) as a parameter and hides the rest, with the exception of the MDI parent form in which most of the application windows live:

Public Sub HideOthers(ByVal FormName As String)

Dim Form As Form
For Each Form In Forms

If Form.Name <> FormName And Form.Name <> "frmMDIMain" Then Form.Hide

Next

End Sub

Main

The MDI parent (frmMDIMain) opens after successful login, and it initially contains the Main form (frmMain) with the database list box discussed earlier in this chapter. The MDI form has some code behind it, but it only refers to the menu controls present on the form, such as the On_Load event that disables some of them initially:

Private Sub MDIForm_Load()

Me.cmdTable.Enabled = False
Me.cmdViewTable.Enabled = False
Me.cmdViewSP.Enabled = False
mnuApp.Enabled = False

End Sub

The same menu provides basic program functionality, such as exiting the application:

Private Sub cmdClose_Click()

'Exit the program
Dim Form As Form
For Each Form In Forms
Unload Me
Next

End Sub

The following code provides one example of program navigation, coded similarly throughout the application (only the menu choice name and the form name change):

Private Sub cmdViewTable_Click()

Load frmViewTable
frmViewTable.Show

Call HideOthers("frmViewTable")

End Sub

The Main form is straightforward. The two read-only text boxes display the server name and ConnectionID value, which is the value SQL-DMO uses to attach you to the correct SQL Server instance. A list box shows each of the databases on that SQL Server in much the same way as the SQLServers collection was generated on the login screen.

The only thing your code must do is instantiate a SQL-DMO object for each layer of the nest you want to access. In this case, the user will access one layer deeper than from the login screen; the list box here will show the databases inside the previously selected server instance.

Note The base index value for collections in SQL-DMO is 1 instead of 0 (which is the usual index value of the first object in a Visual Basic collection or array). Because of this, any counter variables must be initialized.

Public Sub GetDBList()

'This subroutine retrieves a list of databases
'that reside on the server specified by name
'in the Connect property of the instance of SQL Server 

On Error GoTo ErrorHandler

' Create a SQL-DMO application and SQL Server 2000 instance
Dim SQL2000 As New SQLDMO.Application
Dim SQLInstance As SQLDMO.SQLServer
Dim DB As SQLDMO.Database

' Instance variables
Dim SQLSrvrID As Long

Set SQLInstance = New SQLDMO.SQLServer
' Connect to the server by name (from global variable)
SQLInstance.Connect ServerName, User, Password
' Set the instance to that server ID
SQLSrvrID = SQLInstance.ConnectionID
Set SQLInstance = SQL2000.SQLServers.ItemByID(SQLSrvrID)

' Display the available databases in a list box
Dim x As Integer

x = 1

For Each DB In SQLInstance.Databases

'Do not add system databases to the list
If Not SQLInstance.Databases(x).SystemObject Then
Me.lstDBs.AddItem (SQLInstance.Databases(x).Name)
End If

x = x + 1

Next

' Show the connection ID
Me.txtCnxnID = SQLSrvrID

' Clean up
Set SQLInstance = Nothing
Set SQL2000 = Nothing

Exit Sub

ErrorHandler:

MsgBox "Unable to get database list."

End Sub

The ConnectionID value is also the value required to execute the ItemByID method of the SQLServers collection in the Application object:

SQLSrvrID = SQLInstance.ConnectionID
Set SQLInstance = SQL2000.SQLServers.ItemByID(SQLSrvrID)

These two lines assure connection to the instance of SQL Server while you work with the database objects inside.

In the For Each loop that produces the list box of databases, take note of this line:

If Not SQLInstance.Databases(x).SystemObject Then

The SystemObject property (which holds a Boolean value) of each database within the Databases collection allows you to display only the available non-system databases. This is an example of the additional security that SQL-DMO can provide while still allowing some administrative tasks on the server to be accomplished.

The final subroutine in the form enables the balance of those tasks after a database has been selected:

Private Sub lstDBs_Click()

frmMDIMain.cmdTable.Enabled = True
frmMDIMain.cmdViewTable.Enabled = True
frmMDIMain.cmdViewSP.Enabled = True
frmMDIMain.mnuApp.Enabled = True 

End Sub

Create Login

Adding permissions for someone to work within that instance of SQL Server is a two-step process. In this application, both processes are taken care of in the same form (frmCreateLogin). The first step is to create a login to the server itself. To do this, add a Login to the Logins collection of the server instance and then assign the correct server permissions to that login:

Private Sub CreateLogin(ByVal NewLoginID As String, ByVal NewPassword As String)

On Error GoTo ErrorHandler

Dim SQL2000 As New SQLDMO.Application
Dim SQLInstance As SQLDMO.SQLServer
Dim SQLSrvrID As Long
Dim Login As SQLDMO.Login

' User objects
Dim NewLogin As SQLDMO.Login
Dim ServerRole As SQLDMO.ServerRole
Dim ServerRole2 As SQLDMO.ServerRole

Set SQLInstance = New SQLDMO.SQLServer
' Connect to the server by name (from global variables)
SQLInstance.Connect ServerName, User, Password
SQLSrvrID = CLng(frmMain.txtCnxnID)
Set SQLInstance = SQL2000.SQLServers.ItemByID(SQLSrvrID)

' Create the login
Set NewLogin = CreateObject("SQLDMO.Login")
' Server roles for the login
Set ServerRole = SQLInstance.ServerRoles("dbcreator")
Set ServerRole2 = SQLInstance.ServerRoles("diskadmin")

' Check to see if that login already exists
Dim x As Integer
For Each Login In SQLInstance.Logins
If Login.Name = NewLoginID Then
MsgBox "That login already exists in " & ServerName & "."
Exit Sub
End If
Next

' If not then assign login values
NewLogin.Type = SQLDMOLogin_Standard
NewLogin.SetPassword "", NewPassword
NewLogin.Name = NewLoginID

' Add the new login to the correct roles
SQLInstance.Logins.Add NewLogin
ServerRole.AddMember (NewLoginID)
ServerRole2.AddMember (NewLoginID)

MsgBox "New login added to " & ServerName & "."
' Show database user fields
Me.lblDB.Visible = True
Me.lstDBs.Visible = True
Exit Sub

' Clean up
Set SQLInstance = Nothing
Set SQL2000 = Nothing
Set ServerRole = Nothing
Set ServerRole2 = Nothing 
Set Login = Nothing
Set NewLogin = Nothing
Exit Sub

ErrorHandler:

MsgBox "Unable to add login successfully."

End Sub

After all of the objects and variables have been initialized, the login is checked against all of the existing logins in the Logins collection using this code:

Dim x As Integer
For Each Login In SQLInstance.Logins
If Login.Name = NewLoginID Then
MsgBox "That login already exists in " & ServerName & "."
Exit Sub
End If
Next

Then the Login object itself is created and assigned values for the required properties:

Set NewLogin = CreateObject("SQLDMO.Login")
NewLogin.Type = SQLDMOLogin_Standard
NewLogin.SetPassword "", NewPassword
NewLogin.Name = NewLoginID

The first argument contains an empty string because there is no previous password for this login. You can also use the SetPassword method to change existing passwords.

Permissions must also be set for new logins. In SQL Server, permissions are often granted through server roles. The SQL Junior Administrator application creates two server roles, DBCREATOR and DISKADMIN:

Set ServerRole = SQLInstance.ServerRoles("dbcreator")
Set ServerRole2 = SQLInstance.ServerRoles("diskadmin")

ServerRoles is a collection contained in each SQL Server instance. For more information about security, see Chapter 10, "Implementing Security," and SQL Server Books Online.

After the roles are created, use the following code to add the new login to members of the Logins collection of the server instance:

SQLInstance.Logins.Add NewLogin
ServerRole.AddMember (NewLoginID)
ServerRole2.AddMember (NewLoginID)

Next, grant users access to individual databases. Creating these permissions is similar to adding logins to a server instance. Instead of adding a login to the Logins collection of the server instance, add a user to the Users collection of each database and assign that user the appropriate database roles:

Private Sub CreateDBUser(ByVal DBName As String, ByVal NewLoginID As String, ByVal NewPassword As String)

On Error GoTo ErrorHandler

Dim SQL2000 As New SQLDMO.Application
Dim SQLInstance As SQLDMO.SQLServer
Dim SQLSrvrID As Long
Dim DB As SQLDMO.Database

' User objects
Dim DBUser As SQLDMO.User
Dim Role As SQLDMO.DatabaseRole
Dim Role2 As SQLDMO.DatabaseRole

Set SQLInstance = New SQLDMO.SQLServer
' Connect to the server by name (from global variables)
SQLInstance.Connect ServerName, User, Password
SQLSrvrID = CLng(frmMain.txtCnxnID)
Set SQLInstance = SQL2000.SQLServers.ItemByID(SQLSrvrID)

' Add the login as a user to the database
' with the appropriate permissions
Set DBUser = CreateObject("SQLDMO.User")
DBUser.Name = NewLoginID
DBUser.Login = NewPassword

Set DB = SQLInstance.Databases(DBName)
Set Role = DB.DatabaseRoles("DB_DDLADMIN")
Set Role2 = DB.DatabaseRoles("DB_DATAWRITER")

DB.Users.Add DBUser
Role.AddMember NewLoginID
Role2.AddMember NewLoginID

MsgBox "New user permissions to create database objects and to read and write data " & vbCrLf _
& "have been added to " & DBName & "."

' Clean up
Set SQLInstance = Nothing
Set SQL2000 = Nothing
Set DB = Nothing
Set DBUser = Nothing
Set Role = Nothing
Set Role2 = Nothing
Exit Sub

ErrorHandler:

MsgBox "Unable to add database user."

End Sub

The newly created login now has permissions in the selected databases to create and modify tables and to read and write data.

Create Table

Creating a table is similar to creating a user; both are objects (and members of collections) within a database. Only the collection references and required property settings change.

Private Sub AddTable(ByVal DBName As String, ByVal TableName As String)

On Error GoTo ErrorHandler

Dim SQL2000 As New SQLDMO.Application
Dim SQLInstance As SQLDMO.SQLServer
Dim CnxnID
' Database objects
Dim DB As SQLDMO.Database
' Table objects
Dim NewTable As SQLDMO.Table
Dim NewColumn As SQLDMO.Column

Set SQLInstance = New SQLDMO.SQLServer
' Connect to the server by name (from global variable)
SQLInstance.Connect ServerName, User, Password
CnxnID = frmMain.txtCnxnID
Set SQLInstance = SQL2000.SQLServers.ItemByID(CnxnID)

' Create table and column
Set NewTable = CreateObject("SQLDMO.Table")
Set NewColumn = CreateObject("SQLDMO.Column")

' Name the table and give ownership to the dbo
NewTable.Name = TableName
NewTable.Owner = "dbo"

' Add the first column as identity - 
' at least one column must be present before you add a table
NewColumn.Name = TableName & "_ID"
NewColumn.Datatype = "int"
NewColumn.Identity = True
NewColumn.IdentitySeed = 0
NewColumn.IdentityIncrement = 1

'Add it to the Columns collection
NewTable.Columns.Add NewColumn

'Add the whole thing to the Tables collection
Set DB = SQLInstance.Databases(DBName)
DB.tables.Add NewTable

MsgBox "table added to " & DBName

' Show the column controls
Me.lblAddColumns.Visible = True
Me.lblColName.Visible = True
Me.lblDataType.Visible = True
Me.lblOr.Visible = True
Me.lblDefault.Visible = True
Me.lblNulls.Visible = True
Me.lblLength.Visible = True

Me.txtColName.Visible = True
Me.lstDT.Visible = True
Me.chkNulls.Visible = True
Me.txtDefault.Visible = True
Me.txtLength.Visible = True
Me.btnCreateColumn.Visible = True

Exit Sub

ErrorHandler:

MsgBox "Unable to add table."

End Sub

Note The previous subroutine contains code to make certain controls appear only after the table creation process has actually begun. This is not required; it is there to keep the form uncluttered.

The only difference between creating a table object and creating a user object is the addition of the first Column object in the table. This is required by the Add method of the Tables collection. The previous code defines the first column it creates as an identity column. (Only one identity column is allowed in each table.)

NewColumn.Name = TableName & "_ID"
NewColumn.Datatype = "int"
NewColumn.Identity = True
NewColumn.IdentitySeed = 0
NewColumn.IdentityIncrement = 1

The following line of code sets the owner of the table:

NewTable.Owner = "dbo"

Here, ownership rights have been given to the database owner. If your code does not do this, the Owner property of the object is automatically mapped to the current login. This can create problems if you try to remove that login from the system.

To add columns, access the Columns collection in the table and add a column to it:

Private Sub AddColumn(ByVal DBName As String, ByVal TableName As String, ByVal ColumnName As String)

On Error GoTo ErrorHandler

Dim SQL2000 As New SQLDMO.Application
Dim SQLInstance As SQLDMO.SQLServer
Dim CnxnID As Long
' Database object
Dim DB As SQLDMO.Database
' Table objects
Dim table As SQLDMO.table
Dim NewColumn As SQLDMO.Column

Set SQLInstance = New SQLDMO.SQLServer
' Connect to the server by name (from global variable)
SQLInstance.Connect ServerName, User, Password
CnxnID = CLng(frmMain.txtCnxnID)
Set SQLInstance = SQL2000.SQLServers.ItemByID(CnxnID)
Set DB = SQLInstance.Databases(DBName)

' Get the table
Set table = DB.Tables(TableName)
' Create the column
Set NewColumn = CreateObject("SQLDMO.Column")

' Add the column with basic properties
NewColumn.Name = ColumnName
NewColumn.Datatype = Me.lstDT.Text
NewColumn.Length = Me.txtLength.Text
If Me.chkNulls.Value = 1 Then NewColumn.AllowNulls = True
NewColumn.Default = Me.txtDefault.Text

'Add it to the Columns collection
table.Columns.Add NewColumn

MsgBox "Column " & ColumnName & " added to " & TableName & "."

' Reset the form
Me.txtColName = ""
Me.txtDefault = ""
Me.txtLength = ""
Me.chkNulls.Value = 0
Me.txtColName.SetFocus

Exit Sub

ErrorHandler:

MsgBox "Unable to add column."

End Sub

As shown in the two previous subroutines, certain properties of the column must be set for the Add method of the Columns collection to work. For example, the Datatype property must be set, so that all of the existing data types are listed in a list box on frmCreateTable. To do this, loop through each member of the SystemDataTypes collection of the database (using a variant to hold each value).

Private Sub GetDataTypes()

On Error GoTo ErrorHandler

Dim SQL2000 As New SQLDMO.Application
Dim SQLInstance As SQLDMO.SQLServer
Dim CnxnID
' Database objects

Dim DB As SQLDMO.Database
Dim DBName As String

Set SQLInstance = New SQLDMO.SQLServer
' Connect to the server by name
SQLInstance.Connect ServerName, User, Password
CnxnID = frmMain.txtCnxnID
Set SQLInstance = SQL2000.SQLServers.ItemByID(CnxnID)

' Get the database by name
DBName = frmMain.lstDBs.Text
Set DB = SQLInstance.Databases(DBName)

Dim dt
Dim x As Integer

x = 1

For Each dt In DB.SystemDatatypes

Me.lstDT.AddItem DB.SystemDatatypes.Item(x).Name
x = x + 1

Next

' Clean up
Set DB = Nothing
Set SQLInstance = Nothing
Set SQL2000 = Nothing
Exit Sub

ErrorHandler:

MsgBox "Unable to get list of data types."

End Sub

This application sets only the properties required to make the called method work correctly. However, you can use SQL-DMO to manipulate all column properties, just as if you were within SQL Server Enterprise Manager.

Note You do not have to memorize all of the properties and methods, because the standard dot notation statement completion works with SQL-DMO. When you dot any correctly instantiated object, a list of available properties and methods appears.

View Table

Retrieving the existing columns for any tables in the database is a simple two-step process that begins with iterating through the Tables collection added to in the previous example. In the GetTableList subroutine shown here, the results of the loop are again placed in a list box:

Private Sub GetTableList(ByVal SQLSrvrID As Long, ByVal DBName As String)

' Application objects
Dim SQL2000 As New SQLDMO.Application
Dim SQLInstance As SQLDMO.SQLServer
' Database object
Dim DB As SQLDMO.Database
' Table and column objects
Dim table As SQLDMO.Table
Dim NewColumn As SQLDMO.Column

Set SQLInstance = New SQLDMO.SQLServer
' Connect to the server by name (from global variable)
SQLInstance.Connect ServerName, User, Password
Set SQLInstance = SQL2000.SQLServers.ItemByID(SQLSrvrID)
Set DB = SQLInstance.Databases(DBName)

' Show the tables one at a time
Dim x As Integer
For x = 1 To DB.Tables.Count
' But do not show system tables
If Not DB.Tables(x).SystemObject Then
Me.lstTables.AddItem DB.Tables(x).Name
End If
x = x + 1
Next

End Sub

The second part of the operation, the listing of the columns themselves, can be completed after the table has been selected:

Private Sub GetFieldList(ByVal SQLSrvrID As Long, ByVal DBName As String, ByVal TableName As String)

' Application objects
Dim SQL2000 As New SQLDMO.Application

Dim SQLInstance As SQLDMO.SQLServer
' Database object
Dim DB As SQLDMO.Database
' Table and column objects
Dim table As SQLDMO.table
Dim Column As SQLDMO.Column

Set SQLInstance = New SQLDMO.SQLServer
' Connect to the server by name (from global variable)
SQLInstance.Connect ServerName, User, Password
Set SQLInstance = SQL2000.SQLServers.ItemByID(SQLSrvrID)
Set DB = SQLInstance.Databases(DBName)
Set table = DB.Tables(TableName)

' Show the columns one at a time
Dim x As Integer

x = 1
For x = 1 To table.Columns.Count
Me.lstFields.AddItem table.Columns(x).Name
x = x + 1
Next

End Sub

In this application, the capability to add a column to an existing table is present in the same form (frmViewTable).

View Login

The process for seeing existing logins for a server is similar to the process for seeing databases on the server. To view existing logins, use the Logins collection. (This is the same collection used to add a new login and to loop through to see if a login already exists by accessing the Name property.) The following code adds logins to a list box:

Sub GetLogins(ByVal SQLSrvrID As Long)

On Error GoTo ErrorHandler

Dim SQL2000 As New SQLDMO.Application
Dim SQLInstance As SQLDMO.SQLServer
' User objects
Dim Login As SQLDMO.Login

Set SQLInstance = New SQLDMO.SQLServer
' Connect to the server by name (from global variable)
SQLInstance.Connect ServerName, User, Password
Set SQLInstance = SQL2000.SQLServers.ItemByID(SQLSrvrID)

' Fill the list box
Dim x
x = 1
For Each Login In SQLInstance.Logins
Me.lstLogins.AddItem SQLInstance.Logins(x).Name
x = x + 1
Next

' Clean up
Set SQLInstance = Nothing
Set SQL2000 = Nothing

Exit Sub

ErrorHandler:

MsgBox "Unable to get login list."

End Sub

A common reason for retrieving a list of logins is so you can delete someone's login. This is a two-step process that is the reverse of adding a login. You must remove the login as a user from all appropriate databases before you remove it as a server login. You can accomplish this in one procedure, using the Remove method of the Users and Logins collections:

Sub DeleteLogin(ByVal SQLSrvrID As Long, ByVal Login As String)

On Error GoTo ErrorHandler

Dim SQL2000 As New SQLDMO.Application
Dim SQLInstance As SQLDMO.SQLServer
Dim DB As SQLDMO.Database

Set SQLInstance = New SQLDMO.SQLServer
' Connect to the server by name (from global variable)
SQLInstance.Connect ServerName, User, Password
Set SQLInstance = SQL2000.SQLServers.ItemByID(SQLSrvrID)

' Remove the user from all databases
For Each DB In SQLInstance.Databases
If DB.IsUser(Login) Then DB.Users.Remove (Login)
Next

' Remove the user from the server
SQLInstance.Logins.Remove (Login)

' Show confirmation
MsgBox Login & " has been removed from " & ServerName

' Fill the list box again
Dim x As Integer
x = 1
Dim UserName As SQLDMO.Login

Me.lstLogins.Clear
For Each UserName In SQLInstance.Logins
Me.lstLogins.AddItem SQLInstance.Logins(x).Name
x = x + 1
Next

' Clean up
Set UserName = Nothing
Set SQLInstance = Nothing
Set SQL2000 = Nothing
Set DB = Nothing

Exit Sub
ErrorHandler:

MsgBox "Unable to delete login."

End Sub

The IsUser method removes the user from the Users collection in only the appropriate databases in the Database object:

For Each DB In SQLInstance.Databases
If DB.IsUser(Login) Then DB.Users.Remove (Login)
Next

Failing to use this method results in an error for any database if the chosen login is not currently shown as a member of that database's Users collection. The previous procedure also automatically refills the logins list box once the login is removed from the system.

Changing permissions for an existing login or user requires only slight modification of the previous procedures.

View Stored Procedures

The viewing of stored procedures is functionality added to the SQL Junior Administrator with Visual Basic and Active Server Pages (ASP) developers in mind. You must usually know the exact names of the stored procedures you want to execute programmatically. Getting all of the existing stored procedures in a database by name and displaying them conveniently is easy in SQL-DMO:

Private Sub GetSPList(ByVal SQLSrvrID As Long, ByVal DBName As String)

On Error GoTo ErrorHandler

' Application objects
Dim SQL2000 As New SQLDMO.Application
Dim SQLInstance As SQLDMO.SQLServer
' database object
Dim DB As SQLDMO.Database

Set SQLInstance = New SQLDMO.SQLServer
' Connect to the server by name (from global variable)
SQLInstance.Connect ServerName, User, Password
Set SQLInstance = SQL2000.SQLServers.ItemByID(SQLSrvrID)
Set DB = SQLInstance.Databases(DBName)

' Show the stored procedures one at a time
Dim x As Integer
For x = 1 To DB.StoredProcedures.Count
Me.lstSPs.AddItem DB.StoredProcedures(x).Name
Next

Set SQL2000 = Nothing
Set SQLInstance = Nothing
Set DB = Nothing
Exit Sub

ErrorHandler:

MsgBox "Unable to retrieve stored procedures."

End Sub

Note Because SQL syntax checking is not provided, you cannot use this application to view and edit the actual SQL code within the stored procedure.

Application Tasks

You can begin to explore the full functionality of SQL-DMO by using this sample application to retrieve (but not modify) two other collections in SQL Server. These collections, Backup Devices and Database Logs, are geared more directly toward actual administrative tasks and represent only a small subset of the accessible collections.

You can use similar code to complete both retrieval tasks. This code shows how to retrieve the logs:

Sub GetLogs(ByVal SQLSrvrID As Long, ByVal DBName As String)

' Application objects
Dim SQL7 As New SQLDMO.Application
Dim SQLInstance As SQLDMO.SQLServer
' Database object
Dim DB As SQLDMO.Database

Set SQLInstance = New SQLDMO.SQLServer
' Connect to the server by name (from global variable)
SQLInstance.Connect ServerName, User, Password
Set SQLInstance = SQL7.SQLServers.ItemByID(SQLSrvrID)
Set DB = SQLInstance.Databases(DBName)

' Show the log files one at a time
Dim x As Integer
For x = 1 To DB.TransactionLog.LogFiles.Count
Me.lstLog.AddItem DB.TransactionLog.LogFiles(x).Name
x = x + 1
Next

' Clean up
Set DB = Nothing
Set SQLInstance = Nothing
Set SQL7 = Nothing

End Sub

This code shows how to retrieve the list of backup devices:

Sub GetBackupDvcs(ByVal SQLSrvrID As Long)

' Application objects
Dim SQL7 As New SQLDMO.Application
Dim SQLInstance As SQLDMO.SQLServer
' Database object
Dim DB As SQLDMO.Database

Set SQLInstance = New SQLDMO.SQLServer
' Connect to the server by name (from global variable)
SQLInstance.Connect ServerName, User, Password
Set SQLInstance = SQL7.SQLServers.ItemByID(SQLSrvrID)

Dim x As Integer
If SQLInstance.BackupDevices.Count = 0 Then
Me.lstBUDvc.AddItem "No Backup Devices"

Else
' Show the log files one at a time
For x = 1 To SQLInstance.BackupDevices.Count
Me.lstBUDvc.AddItem SQLInstance.BackupDevices(x).Name
x = x + 1
Next

End If

' Clean up
Set DB = Nothing
Set SQLInstance = Nothing
Set SQL7 = Nothing

End Sub

You will probably use SQL-DMO to construct applications to offer only limited capability to manage administrative tasks in SQL Server. However, if you want to offer a more robust interface to SQL Server, SQL-NS may be the better solution. Although the overall capabilities of SQL-DMO and SQL-NS are similar, SQL-NS allows you to access the SQL Server Enterprise Manager wizards. The SQL Server Enterprise Manager wizards provideremote administrative capabilities.

The DB Wizards button invokes SQL-NS to call the various wizards in SQL Server Enterprise Manager. You can use SQL-NS to allow full remote administrative access to the database. Every wizard is accessible through this simple piece of code:

Private Sub GetWiz()

Dim NS As SQLNS.SQLNamespace
Dim NSO As SQLNS.SQLNamespaceObject
Dim Srvr As Long
Dim strCnxn As Variant


Set NS = CreateObject("SQLNS.SQLNamespace")

strCnxn = String(255, 0)
strCnxn = "Server="
strCnxn = strCnxn & ServerName & ";"
strCnxn = strCnxn & "UID=" & User & ";"
strCnxn = strCnxn & "pwd=" & Password & ";"

MsgBox "Your connection string is:" & vbCrLf & strCnxn
NS.Initialize "SQLDMO", SQLNSRootType_Server, strCnxn, frmMDIMain.hWnd

Srvr = NS.GetRootItem
If Srvr <> 0 Then
Set NSO = NS.GetSQLNamespaceObject(Srvr)
NSO.ExecuteCommandByID (SQLNS_CmdID_WIZARDS)
Else
MsgBox "No server"
End If

End Sub

When you use SQL-NS, objects are instantiated to create a node-and-tree object structure (as opposed to collections as in SQL-DMO):

Dim NS As SQLNS.SQLNamespace
Dim NSO As SQLNS.SQLNamespaceObject

Connections are also created differently, although the server name, user ID, and password are all still required. For example, a specific cast is required instead of simple string variable creation before the connection string is assembled.

Dim strCnxn As Variant
strCnxn = String(255, 0)
strCnxn = "Server="
strCnxn = strCnxn & ServerName & ";"
strCnxn = strCnxn & "UID=" & User & ";"
strCnxn = strCnxn & "pwd=" & Password & ";"

The connection is then established through the Initialization property (not the Connect property) of the NameSpace object:

NS.Initialize "SQLDMO", SQLNSRootType_Server, strCnxn, frmMDIMain.hWnd

In the previous line of code, the first argument is the requesting application, the second is the root type, the third is the connection string, and the last is the handle of the requesting MDI form within the application that was passed in the first parameter.

Finally, object references are also different in SQL-NS and SQL-DMO. In SQL-NS, server objects are accessed through the appropriate root node:

Srvr = NS.GetRootItem
If Srvr <> 0 Then
Set NSO = NS.GetSQLNamespaceObject(Srvr)
NSO.ExecuteCommandByID (SQLNS_CmdID_WIZARDS)
Else
MsgBox "No server"
End If

This is the line of code that retrieves the wizard:

NSO.ExecuteCommandByID (SQLNS_CmdID_WIZARDS)

In this case, the constant that shows all of the other wizards (SQLNS_CmdID_WIZARDS) was selected, but each of the following wizards is available by passing the appropriate CommandID constant.

For more information, see SQL Server Books Online.

Top of page

Summary

The SQL Junior Administrator sample application demonstrates the use of SQL-DMO. It provides access to the various collections within SQL Server and manipulates those collections and the objects contained in them. The sample also takes advantage of the power and convenience of SQL-NS, which can allow programmatic access to the SQL Server Enterprise Manager wizards.

Top of page