Working with Databases

Note

  This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible.

Each Analysis server contains an MDStores collection of database objects (that is, objects of ClassType clsDatabase). A database in Decision Support Objects (DSO) contains dimensions and their subordinate levels, data sources, roles, and commands. Each database object also contains an MDStores collection of cube objects (that is, objects of ClassType clsCube.)

The following examples discuss the methods used to list, add, and delete databases on an Analysis server.

List Databases

The following code example connects to the specified DSO server and iterates through all of the databases on that server, using the MDStores collection for the Analysis server object.

When executed, the following code example prints the name and description of every database defined for the specified Analysis server:

Private Sub ListDatabases()
    Dim dsoServer As New DSO.Server
    Dim dsoDB As DSO.MDStore

    ' Connect to the server.
    dsoServer.Connect "LocalHost"

    ' For each MDStore database object on the server,
    ' print its name.
    For Each dsoDB In dsoServer.MDStores
        Debug.Print "Database: " & dsoDB.Name & _
            " - " & dsoDB.Description
    Next

End Sub

Create a New Database

To create an MDStore object of ClassType clsDatabase on the server, the AddNew method of the Server.MDStores database object collection is used in the following code example.

The following code example creates a new database, TestDB, on the local Analysis server:

Private Sub CreateDatabase()
    Dim dsoServer As New DSO.Server
    Dim dsoDB As DSO.MDStore

    Dim strDBName As String
    Dim strDBDesc As String

    ' Create a connection to the Analysis server.
    dsoServer.Connect "LocalHost"

    ' Initialize the string variables for the
    ' new database name and description.
    strDBName = "TestDB"
    strDBDesc = "Test Database"

    ' Is there already a database by this name?
    If dsoServer.MDStores.Find(strDBName) Then
        MsgBox strDBName & " already exists."
        Exit Sub
    End If

    ' Add new database to server object collection.
    ' Using the AddNew method from MDStores.
    Set dsoDB = dsoServer.MDStores.AddNew(strDBName)

    'Assign the description to the MDStore's
    'Description property, and then call the Update method.
    dsoDB.Description = strDBDesc
    dsoDB.Update

    'Inform the user that the database was added to the server.
    MsgBox (strDBName & " added to server " & dsoServer.Name)

End Sub

Remove a Database

To remove a database, use the Remove method of the Server.MDStores collection as demonstrated in the following code example.

The following code example removes the TestDB database from the local Analysis server:

Private Sub RemoveDatabase()
    Dim dsoServer As New DSO.Server
    Dim dsoDB As DSO.MDStore

    Dim strDBName As String
    Dim blnResult As Boolean

    ' Create a connection to the Analysis server.
    dsoServer.Connect "LocalHost"

    ' Set the database name variable to TestDB.
    strDBName = "TestDB"

    ' Check to make sure that the TestDB database is on 
    ' the Analysis server.
    If dsoServer.MDStores.Find(strDBName) Then
        ' The database was on the server.

        ' Delete the TestDB database.
        dsoServer.MDStores.Remove strDBName

        ' Inform the user.
        MsgBox strDBName & " removed from server " & dsoServer.Name
    Else
        ' The database was not on the server.

        ' Inform the user.
        MsgBox strDBName & " not found on server " & dsoServer.Name
    End If

End Sub

Note

  The TestDB database is referred to in other examples. Use the code example provided earlier in this topic to re-create the database for other examples.