• Article

Creating SQL Server Components Using SQL-DMO Objects

This feature will be removed in the next version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Using SQL Distributed Management Objects (SQL-DMO) to define new Microsoft SQL Server components is always a three-step process. The application:

  1. Requests a new object from SQL-DMO.

  2. Configures the object to reflect the desired attributes of the SQL Server component.

  3. Adds the appropriately configured object to the containing collection.

For most administrative tasks automated with SQL-DMO, the simple, three-step process is quickly evident.

The Microsoft Visual Basic example shows adding a computed row total column:

Dim oColumn As New SQLDMO.Column

oColumn.Name = "SubTotalNoDiscount"
oColumn.Datatype = "money"
oColumn.ComputedText = "CONVERT(money,  Quantity * UnitPrice)"
oColumn.IsComputed = True

oSQLServer.Databases("Northwind").Tables("[Order Details]").Columns.Add oColumn

The complexity of a DBMS implementation may sometimes obscure this simple process. For example, to define a SQL Server database using SQL-DMO:

  • Request a new Database object from SQL-DMO.

  • Configure the Database object by:

    • Setting Database properties.

    • Requesting a new DBFile object from SQL-DMO.

    • Configuring the DBFile object.

    • Adding the DBFile object to the DBFiles collection of the FileGroup object named PRIMARY.

    • Requesting a new LogFile object from SQL-DMO.

    • Configuring the LogFile object.

    • Adding the LogFile object to the LogFiles collection of the Database object.

  • Add the Database object to the Databases collection of a SQLServer object.

The database is created by successively applying nested iterations of the three-step process. This example is still simple, and does not include details such as multiple filegroups with multiple database files or multiple log files.

For more information about the details of creating a specific SQL Server component using a SQL-DMO object, see Objects.