Build the Utility
Although it is possible to reproduce any and all of the features of the SQL Server Enterprise Manager, our Admin Utility will include only the following functions:
In order to accomplish the above tasks, we will need to make a project reference to the SQL DMO and SQL Namespace object libraries.
- Show/Edit Database Properties
- Update Database Statistics
- Run the DTS Import/Export Wizard
- Manage Login Security
- Execute Ad-Hoc SQL Statements
If the SQL Server client utilities have not been installed on your computer, then you will have to copy the necessary files to the appropriate folders and register them manually. (For more information see the heading Installation Issues and Appendix A, which contains the text of the Redist.txt file that ships with SQL Server.)
Our Admin Utility is simple, consisting of only one form named frmAdminMain. The form contains 4 text boxes for collecting login parameters, namely server name, database name, user name and password. Command buttons allow the user to connect and disconnect from the data source. Once connected, the Admin functions become enabled, as does a text entry box for submitting ad-hoc T-SQL statements for execution. It should be noted that the login supplied by the user must map to a valid SQL Server login with sysAdmin privileges, or much of the functionality will not be available.
Module level variables and constants are all declared with private scope.
' Database Connection Variables
Private strServer As String
Private strDatabase As String
Private strUID As String
Private strPWD As String
' SQL DMO & SQL NS Module Level Variables
Private oSQLServer As SQLDMO.SQLServer
Private oCurDB As SQLDMO.Database
Private oSQLNSObj As SQLNamespaceObject
Private oSQLNS As SQLNamespace
Private nsArray(10) As Long
Private varConnect As Variant
The following code is executed when the user clicks the Connect button and creates the SQL DMO and SQL Namespace objects. Note that the user supplied login parameters are passed in the SQL DMO server Connect method.
' Set SQLDMO object
Set oSQLServer = New SQLDMO.SQLServer
oSQLServer.LoginTimeout = 10
oSQLServer.ODBCPrefix = False
'Attempt a connection, then fill the properties stuff
oSQLServer.ApplicationName = "SQL-DMO Explorer"
oSQLServer.Connect strServer, strUID, strPWD
oSQLServer.Application.GroupRegistrationServer = ""
' Set Current SQLDMO Database object
Set oCurDB = oSQLServer.Databases(strDatabase)
Having attempted to create the SQL DMO server and database objects, you should now check the Errors Collection. If the requested database (or server) doesn't exist, an error will be returned. If no error has occurred, then continue the connect process by creating Namespace Object.
If Err.Number = 0 Then
' Create SQL Namespace Object and initialize it
Set oSQLNS = New SQLNamespace
varConnect = "Server=" & strServer & ";UID=" & strUID & ";pwd=" & strPWD & ";"
oSQLNS.Initialize "EMS Admin Namespace", SQLNSRootType_Server, varConnect, hWnd
' Get a root object of type Server and walk down the hierarchy from there
nsArray(0) = oSQLNS.GetRootItem
' Get first level server->databases
nsArray(1) = oSQLNS.GetFirstChildItem(nsArray(0), SQLNSOBJECTTYPE_DATABASES)
' Get second level server->databases->database('pubs')
nsArray(2) = oSQLNS.GetFirstChildItem(nsArray(1), SQLNSOBJECTTYPE_DATABASE, strDatabase)
' Get a SQLNamespaceObject to execute commands against on the wanted level
Set oSQLNSObj = oSQLNS.GetSQLNamespaceObject(nsArray(2))
MsgBox "Connection Failed!" & vbCrLf & vbCrLf & "Check parameters and try again.", vbCritical, "Error"
At this point, assuming that the user has provided valid login parameters, you have now instantiated the two objects necessary to accomplish any administrative task. Namely,
SQLDMO Database Object: oCurDB
SQLNS Namespace Object: oSQLNS
The SQLDMO database object can be used to execute any stored procedures, DML or DDL SQL statements. Consider the following syntax:
The first statement runs the system stored procedure called sp_updatestats, which updates index statistics for all tables in the database. The second example runs a custom T-SQL statement passed in the string variable, strTSQL.
The QueryResults object is used in connection with the ExecuteWithResults() method to return a recordset. From this recordset object you may extract both metadata about the structure of the recordset and the data itself.
Dim qryResult As QueryResults
Set qryResult = oCurDB.ExecuteWithResults(strTSQL)
The SQLDNS Namespace object can be used to launch any of the standard Enterprise Manager dialog boxes and wizards. Some examples are listed below and a complete list of command arguments is supplied in the sidebar, "SQL Namespace Command Arguments.
oSQLNSObj.Commands("Manage SQL Server Security").Execute
The one last bit of business is to remember to unload all the objects you created in order to recover memory resources. I created a subroutine that is called from both the Disconnect button click and the Form_Unload events. The code simply closes the server object and sets all the objects to nothing.
Set oSQLServer = Nothing
Set oCurDB = Nothing
Set oSQLNSObj = Nothing
Set oSQLNS = Nothing
Because these dialogs and wizards are identical to those exposed through the Enterprise Manager, they encapsulate all the same functionality. You can provide your users with the ability to increase the size of the database and log files, as well as shrink, backup or restore the database. With this object model you can manage user logins and create maintenance jobs. All of this rich functionality is exposed with just a few simple lines of code!
The complete code listing for this utility is available for download at VB6msde001.zip. The finished utility includes code for toggling the enabled property for each control, depending on whether or not you are connected to a SQL Server database. Additional logic is provided to do some simple syntax checking for SQL statements submitted by the user to the SQL DMO ExecuteImmediate() method. Since testing text for malformed SQL statements could be an article in itself I will leave that for another discussion.
Page 4: Installation Issues