According to the Microsoft Office 2000/Visual Basic Programmer's Guide MSDE (Microsoft Data Engine) is a new technology that provides local data storage compatible with Microsoft SQL Server 7.0. This article discusses how to create your own administration tool for MSDE.
Page 2: How is MSDE administered?
What is MSDE
According to the Microsoft Office 2000/Visual Basic Programmer's Guide MSDE (Microsoft Data Engine)
is a new technology that provides local data storage compatible with Microsoft SQL Server 7.0. You
can think of MSDE as a client/server database engine alternative to the file-server Microsoft Jet
database engine. MSDE runs under Windows NT 4.0 or later and Windows 95 or later. It is designed and
optimized for use on smaller computer systems, such as a single-user computer or small-workgroup server.
MSDE doesn't limit the number of users who can connect to its database, but is optimized for five users.
Because MSDE is based on the same database engine as SQL Server, most Microsoft Access projects or
client/server applications can run unchanged on either version. However, unlike SQL Server 7.0, MSDE
has a 2-gigabyte database size limit, supports up to 2 processors for Symmetrical Multiprocessing (SMP),
and in a replicated database environment cannot be a replication publisher for transactional replication
(although it can act as a replication subscriber for both transactional and merge replication, and as a
replication publisher for merge replication).
For the developer, this means that you can now create applications that leverage the power of SQL Server
while providing your users with a robust, royalty free database. All of the familiar features of SQL
Server are available in MSDE: tables, views, stored procedures, rules, constraints, triggers and even
the security SQL Server model.
Page 3: Build the Utility
How is MSDE administered?
Application developers commonly use the Enterprise Manager and Query Analyzer utilities to
create and modify database objects. This is an excellent solution in the development environment,
but your end users will not likely have access to these tools. There are also some compelling
reasons not to install SQL Server client utilities on an end user's machine. Although you as a
developer may be accustom to the complex interface, your clients are not. There is a steep
learning curve and the Enterprise Manager's interface can be intimidating, even fear inspiring,
for the first time user.
Wouldn't it be a better solution if you could provide a subset of the myriad of DBA services
exposed through the SQL Server client utilities? Why overwhelm your users with a massive
administration utility when all they really need to do is edit database properties, do backups
or restores, shrink database files and update index statistics?
Well, it is possible to roll your own Admin Utility. Microsoft makes available two type libraries
with automation interfaces for the administration of SQL Server databases. They are the SQL
Distributed Management Objects (SQL-DMO) and SQL Namespace (SQL-NS) COM libraries.
These APIs expose all the features, functionality, dialogs and wizards of the SQL Enterprise
Manager to the application developer. As a result, you can create an application that completely
shields your users from many of the complexities of SQL Server administration and still provides
an interface for performing the necessary database maintenance. The benefits of creating your own
Admin Utility include:
For example, the application that was the inspiration for this article required some custom
administrative services that were best performed from SQL Server. The application administrator
needed the ability to read a list of user names and e-mail addresses from the tblUsers table and
send mail messages to all users informing them of upcoming events, such as upgrades, or system
maintenance. Additionally, by reading from the tblTasks table, the admin could determine who was
currently logged on and either send mail messages to those individuals, or if necessary, log them
out for maintenance purposes. The Admin could then lock users out of the application until maintenance
was finished. Of course, all of these actions could be accomplished from the Enterprise Manager and
Query Analyzer, but for end users, checking a checkbox and clicking a button is more intuitive than
typing text at a command prompt.
- Provides a simple interface to accomplish necessary database maintenance.
- Allows you to conceal potentially dangerous actions, like the ability to drop (delete) tables,
stored procedures or even the database itself.
- Makes it possible to add custom functions that are peculiar to your application's requirements.
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
The complete list of files required to install SQL DMO and SQL Namespace listed in
Appendix A were taken from the Redist.txt file distributed with SQL Server. Pay
special attention to the following instructions about the placement and registration
of some of the key library files:
Installation Notes for Distributed Management Objects (DMO)
The sqldmo.dll file must be registered using the regsvr32.exe utility.
Example: regsvr32 \mssql7\binn\sqldmo.dll)
The sqlwoa.dll file should reside in the system folder
(i.e., \winnt\system32 or \windows\system).
Installation Notes for SQL Namespace (SQLNS) Files
The following files must be registered using the regsvr32.exe utility:
Example: regsvr32 \mssql7\binn\sqlns.dll
It has been my experience that these instructions need to be followed exactly.
Placing even a single file in the wrong directory or forgetting to register any
of the above mentioned files will cause a failure when trying to instantiate a
SQL Namespace object. It seems that SQL DMO is more forgiving, but I still
suggest that you distribute these components exactly as suggested by Microsoft.
The Microsoft Data Engine (MSDE) provides a robust alternative for desktop
applications currently using the JET database, file-server option. By designing
applications for MSDE you pave the way for a more painless, hassle free upsize
to SQL Server when that time comes. Thanks to the SQL DMO and SQL Namespace COM
libraries, developers now have the ability to include all the administrative
utilities necessary for your client to manage the database without requiring
years of training or hours of coding.
In a future article, I will discuss what is required to perform a remote,
unattended install of the MSDE database engine on a client machine.
See All Articles by Columnist Danny Lesandrini