Delen via

  • Artikel

Working with Data Sources (DSO)

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.

Collections of data sources (that is, MDStore objects of ClassType clsDataSource) are contained in MDStore objects of ClassType clsDatabase, clsCube,and clsPartition. Each object's data source specifies an external database that will be used as the source of data.

A database can contain multiple data sources in its DataSources collection. However, each cube and partition contains only a single data source.

The two examples in this topic demonstrate how to list and add a data source to the database's DataSources collection.

List Data Sources

The easiest way to list data sources is to iterate through the DataSources collection of an MDStore database object, as shown in the following code example which lists the Name and ConnectionString properties of each data source for every database on a given Analysis server.

The following code example loops through the DataSources collection of each database on the local Analysis server, printing some of the basic properties for each data source in the Immediate window:

Private Sub ListDataSources()
    Dim dsoServer As New dso.Server
    Dim dsoDB As dso.MDStore
    Dim dsoDS As dso.DataSource

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

    ' Step through the databases in the
    ' MDStores collection of the server.
    For Each dsoDB In dsoServer.MDStores

        ' Print the name & description of the database.
        Debug.Print "DATABASE: " & dsoDB.Name & " - " & _
            dsoDB.Description

        ' Determine whether the database has data sources.
        If dsoDB.DataSources.Count = 0 Then
            Debug.Print "    Data source: None"
        Else
            ' Iterate through and print the data source
            ' information.
            For Each dsoDS In dsoDB.DataSources
               Debug.Print "    Data source: " & dsoDS.Name
               Debug.Print "         Valid?:" & dsoDS.IsValid
            Next
        End If
    Next

End Sub

Add a Data Source

The process to add a new data source is similar to the process for adding a new database. The AddNew method of the DataSources collection for a given database creates a new data source for the database.

The following code example adds a data source named FoodMart to the TestDB database object on the local Analysis server:

Private Sub AddDataSource()
    Dim dsoServer As New DSO.Server
    Dim dsoDB As DSO.MDStore
    Dim dsoDS As DSO.DataSource

    Dim strDBName As String
    Dim strDSName As String
    Dim strDSConnect As String

    ' Initialize variables for the database name,
    ' data source name, and the ConnectionString property
    ' for the data source.
    strDBName = "TestDB"
    strDSName = "FoodMart"
    strDSConnect = "Provider=SQLOLEDB.1;" & _
        "Integrated Security=SSPI;" & _
        "Persist Security Info=False;" & _
        "Initial Catalog=FoodMart;" & _
        "Data Source=FoodMartServer;" & _
        "Connect Timeout=15"

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

    ' Locate the database first.
    If dsoServer.MDStores.Find(strDBName) Then
        Set dsoDB = dsoServer.MDStores(strDBName)

        ' Check to see whether the data source already exists.
        If dsoDB.DataSources.Find(strDSName) Then
            MsgBox "Data source " & strDSName & _
                " already exists for database " & strDBName
        Else
            ' Create a new data source.
            Set dsoDS = dsoDB.DataSources.AddNew(strDSName)
            ' Add the ConnectionString properties
            dsoDS.ConnectionString = strDSConnect
            ' Update the data source.
            dsoDS.Update
            ' Inform the user
            MsgBox "Data source " & strDSName & _
                " has been added to database " & strDBName
        End If
    Else
        MsgBox strDBName & " is missing."
    End If

End Sub