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:
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)) Else Err.Clear MsgBox "Connection Failed!" & vbCrLf & vbCrLf & "Check parameters and try again.", vbCritical, "Error" ToggleConnectControlsEnabled False End If
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:
oCurDB.ExecuteImmediate ("sp_updatestats") oCurDB.ExecuteImmediate (strTSQL)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("Properties").Execute oSQLNSObj.Commands("Data Export").Execute 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.
oSQLServer.Close 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.