Working with Databases
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.
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
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
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
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.