Export (0) Print
Expand All

Events (DSO)

NoteNote

  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.

The only object in Decision Support Objects (DSO) that directly supports event trapping is the Database object. This object fires events for all of its child objects including shared dimensions, cubes, partitions, aggregations, and data mining models.

The following table lists the events that this object supports.

Event

Description

ReportAfter

Called whenever a processing action on an object in the database has finished executing

ReportBefore

Called before a processing action on an object in the database

ReportError

Called whenever an error occurs during a processing action

ReportProgress

Called to report the progress of an action during a processing

Each event reports the status of the processing action. This processing action is represented by integer constants. The tense for each processing action depends on the event being trapped. For instance, the merge action (mdactMerge) reports that two partitions or aggregations will bemerged when trapped in the ReportBefore event. In contrast, this same action reports that two partitions or aggregations have been merged when trapped in the ReportAfter event.

The following is a list of actions that are supported by the database object events:

Action

Constant

Description

Process

mdactProcess

Indicates that the object referred to by obj has been processed.

Merge

mdactMerge

Reports that two partitions/aggregations have been merged.

Delete

mdactDelete

Indicates that an object has been deleted.

Delete Old Aggregations

mdactDeleteOldAggregations

Indicates that the existing relational OLAP (ROLAP) aggregations of a partition have been deleted.

Rebuild

mdactRebuild

Indicates that the definitions of an object have been rebuilt.

Commit

mdactCommit

Indicates that a transaction has been committed on the database.

Rollback

mdactRollback

Reports that a transaction has been rolled back on the database.

Create Indexes

mdactCreateIndexes

Indicates that indexes for a ROLAP aggregation have been created.

Create Table

mdactCreateTable

Reports that the aggregation table for the ROLAP aggregation has been created.

Insert Into

mdactInsertInto

Indicates that the aggregation table for the ROLAP partition has been populated.

Transaction

mdactTransaction

Reports that a transaction has been started, completed, or has encountered an exception.

Initialize

mdactInitialize

Indicates that the object referred to by the obj parameter has been initialized.

Create View

mdactCreateView

Reports that an aggregation view has been created for the ROLAP aggregation. This action is only valid when processing a ROLAP cube with Microsoft® SQL Server™ 2005 using indexed views.

Write Data

mdactWriteData

Data has been written to the disk.

Read Data

mdactReadData

Data has been read from the disk.

Aggregate

mdactAggregate

Aggregations are being built.

Execute SQL

mdactExecuteSQL

An SQL statement has been executed.

Now Executing SQL

mdactNowExecutingSQL

An SQL statement is executing that can be canceled.

Executing Modified SQL

mdactExecuteModifiedSQL

A modified SQL statement has been executed.

Rows Affected

mdactRowsAffected

Reports number of rows affected by an SQL statement.

Error

mdactError

Indicates that an error has occurred during processing.

Write Aggregations and Indexes

mdactWriteAggsAndIndexes

Indexes and aggregations will be written to the disk.

Write Segment

mdactWriteSegment

Segments will be written to the disk.

Data Mining Model Processed Percentage

mdactDataMiningProgress

The status of the completion of processing for a data mining model in percentage terms.

For more information about the Database object, see clsDatabase.

The following tutorial demonstrates trapping processing events. In examples A through C, a Microsoft Visual Basic® project file is set up that contains all of the information needed to use the rest of the examples. Examples D through G demonstrate trapping each of the events that are available from the database object.

A. Setting up the Project File

  1. Start Visual Basic and create a new project called Project1.

  2. In the Project References dialog box, select the Microsoft Decision Support Objects check box.

  3. In the Project Components dialog box, click Microsoft Windows Common Controls 6.0 (SP3).

  4. Create a new module called Module1.

  5. Create a new form called Form1.

  6. Add a text box to the form called Text1.

  7. Add a button called Command1 to the form and label it "Process".

  8. Add another button called Command2 to the form and label it "Cancel".

  9. Add a progress bar control named ProgressBar1.

  10. In the form's general declarations section, add the following code:

    Option Explicit
    
    'Declare a database object with events.
    Public WithEvents dsoDb As DSO.Database
    
    'Declare some useful variables and constants.
    Public gCubeMaxRows As Long
    Public gbCancel As Boolean
    Private Const SERVER_NAME = "LocalHost"
    Private Const DATABASE_NAME = "FoodMart 2000"
    
    

B. Adding the Form_Load Event and Button Click Events

  • Add the following code to the form:

    Private Sub Command1_Click()
      gbCancel = False
      ProcessDatabase DATABASE_NAME
    End Sub
    
    Private Sub Command2_Click()
      gbCancel = True
    End Sub
    
    Private Sub ProcessDatabase(strDBName As String)
    Dim dsoServer As New DSO.Server
    Dim dsoDatabase As DSO.MDStore
    Dim dsoCube As DSO.MDStore
    Dim dsoMiningModel As DSO.MiningModel
    
      Screen.MousePointer = vbArrowHourglass
      'Connect to the server.
      dsoServer.Connect (SERVER_NAME)
      
      'Get a reference to the database.
      Set dsoDatabase = dsoServer.MDStores(strDBName)
      'Copy the database reference.
      Set dsoDb = dsoDatabase
      
      'Process each of the cubes in the database.
      For Each dsoCube In dsoDatabase.MDStores
        Text1.Text = Text1.Text & "Processing Cube " & dsoCube.Name & vbCrLf
        gCubeMaxRows = dsoCube.EstimatedRows
        dsoCube.Process
        Text1.Refresh
      Next
      Screen.MousePointer = vbNormal
    End Sub
    
    

C. Adding the ProcessDatabase Subroutine

  • Add the following code to the form:

    Private Sub ProcessDatabase(strDBName As String)
    Dim dsoServer As New DSO.Server
    Dim dsoDatabase As DSO.MDStore
    Dim dsoCube As DSO.MDStore
    
      Screen.MousePointer = vbArrowHourglass
      'Connect to the server.
      dsoServer.Connect (SERVER_NAME)
      
      'Get a reference to the database.
      Set dsoDatabase = dsoServer.MDStores(strDBName)
      'Copy the database reference.
      Set dsoDb = dsoDatabase
      
      'Process each of the cubes in the database.
      For Each dsoCube In dsoDatabase.MDStores
        Text1.Text = Text1.Text & "Processing Cube " & dsoCube.Name & vbCrLf
        gCubeMaxRows = dsoCube.EstimatedRows
        dsoCube.Process
        Text1.Refresh
      Next
      Screen.MousePointer = vbNormal
    End Sub
    
    

D. Adding the ReportBefore Event Handler

  • Add the following code to the form:

    Private Sub dsoDb_ReportBefore(obj As Object, ByVal Action As Integer, Cancel As Boolean, Skip As Boolean)
    Dim strNew As String
      
     
      strNew = strNew & " Beginning Action = " & ConvertAction(Action) & " - on object "
      
      'What if the object doesn't have a name property?
      On Error Resume Next
      'Get the name of the object.
      strNew = strNew & obj.Name & "."
      
      Text1.Text = Text1.Text & vbTab & strNew & vbCrLf
      Form1.Refresh
    End Sub
    
    

E. Adding the ReportAfter Event Handler

  • Add the following code to the form:

    Private Sub dsoDb_ReportAfter(obj As Object, ByVal Action As Integer, ByVal success As Boolean)
    Dim strNew As String
      
      'What if the object doesn't have a name property?
      On Error Resume Next
      'Get the name of the object.
      strNew = "Processing object """ & obj.Name & """"
      
      strNew = strNew & " Action = " & ConvertAction(Action) & " - "
      
      'Determine the success of the operation.
      If success = True Then
        strNew = strNew & " was successful."
      Else
        strNew = strNew & " was unsuccessful."
      End If
      
      Text1.Text = Text1.Text & vbTab & strNew & vbCrLf
      Form1.Refresh
      
    End Sub
    
    

F. Adding the ReportProgress Event Handler

  • Add the following code to the form:

    Private Sub dsoDb_ReportProgress(obj As Object, ByVal Action As Integer, Counter As Long, Message As String, Cancel As Boolean)
    Dim strNew As String
      
     'See if the user has canceled.
     Cancel = gbCancel
     
      strNew = strNew & vbTab & " Progress of Action " & ConvertAction(Action)
      'What if the object doesn't have a name property?
      On Error Resume Next
      'Get the name of the object.
      strNew = strNew & "on object " & obj.Name & ". "
      strNew = strNew & Counter & " - " & Message
      
      'Update the progress bar.
      ProgressBar1.Max = gCubeMaxRows
      ProgressBar1.Value = Counter
        
      Text1.Text = Text1.Text & vbTab & strNew & vbCrLf
      Form1.Refresh
    End Sub
    
    

G. Adding the ReportError Event Handler

  • Add the following code to the form:

    Private Sub dsoDb_ReportError(obj As Object, ByVal Action As Integer, ByVal ErrorCode As Long, ByVal Message As String, Cancel As Boolean)
    Dim strNew As String
      
     
      strNew = strNew & " ERROR #" & ErrorCode
      
      'What if the object doesn't have a name property?
      On Error Resume Next
      'Get the name of the object.
      strNew = strNew & " on object " & obj.Name & ". "
      
      strNew = strNew & " - " & Message
      
      Text1.Text = Text1.Text & strNew & vbCrLf
      Form1.Refresh
    End Sub
    
    

H. Adding the ConvertAction function

  • Add the following code to the module:

    Public Const mdactProcess = 1
    Public Const mdactMerge = 2
    Public Const mdactDelete = 3
    Public Const mdactDeleteOldAggregations = 4
    Public Const mdactRebuild = 5
    Public Const mdactCommit = 6
    Public Const mdactRollback = 7
    Public Const mdactCreateIndexes = 8
    Public Const mdactCreateTable = 9
    Public Const mdactInsertInto = 10
    Public Const mdactTransaction = 11
    Public Const mdactInitialize = 12
    Public Const mdactCreateView = 13
    
    Public Const mdactWriteData = 101
    Public Const mdactReadData = 102
    Public Const mdactAggregate = 103
    Public Const mdactExecuteSQL = 104
    Public Const mdactNowExecutingSQL = 105
    Public Const mdactExecuteModifiedSQL = 106
    Public Const mdactConnecting = 107
    Public Const mdactRowsAffected = 108
    Public Const mdactError = 109
    Public Const mdactWriteAggsAndIndexes = 110
    Public Const mdactWriteSegment = 111
    Public Const mdactDataMiningProgress = 112
    
    ' Warnings
    Public Const mdwrnSkipped = 901
    Public Const mdwrnCubeNeedsToProcess = 902
    Public Const mdwrnCouldNotCreateIndex = 903
    Public Const mdwrnTimeoutNotSetCorrectly = 904
    Public Const mdwrnExecuteSQL = 905
    Public Const mdwrnDeletingTablesOutsideOfTransaction = 906
    Public Const mdwrnCouldNotProcessWithIndexedViews = 907
    
    
    Public Function ConvertAction(ByVal Action As Integer) As String
        Dim strReturn As String
        
        Select Case Action
            Case mdactProcess
                strReturn = "Process"
            Case mdactMerge
                strReturn = "Merge"
            Case mdactDelete
                strReturn = "Delete"
            Case mdactDeleteOldAggregations
                strReturn = "Delete old aggregations"
            Case mdactRebuild
                strReturn = "Rebuild"
            Case mdactCommit
                strReturn = "Commit"
            Case mdactRollback
                strReturn = "Rollback"
            Case mdactCreateIndexes
                strReturn = "Create Indexes"
            Case mdactCreateTable
                strReturn = "Create Table"
            Case mdactInsertInto
                strReturn = "Insert Into"
            Case mdactTransaction
                strReturn = "Transaction"
            Case mdactInitialize
                strReturn = "Initialize"
            Case mdactCreateView
                strReturn = "Create View"
            Case mdactWriteData
                strReturn = "Write Data"
            Case mdactReadData
                strReturn = "Read Data"
            Case mdactAggregate
                strReturn = "Aggregate"
            Case mdactExecuteSQL
                strReturn = "Execute SQL"
            Case mdactNowExecutingSQL
                strReturn = "Now Executing SQL"
            Case mdactExecuteModifiedSQL
                strReturn = "Execute Modified SQL"
            Case mdactConnecting
                strReturn = "Connecting"
            Case mdactRowsAffected
                strReturn = "Rows Affected"
            Case mdactError
                strReturn = "Error"
            Case mdactWriteAggsAndIndexes
                strReturn = "Write aggregations & indexes"
            Case mdactWriteSegment
                strReturn = "Write segment"
            Case mdactDataMiningProgress
                strReturn = "Data mining progress"
            ' Warnings
            Case mdwrnSkipped
                strReturn = "Warning: action skipped"
            Case mdwrnCubeNeedsToProcess
                strReturn = "Warning: cube needs to process"
            Case mdwrnCouldNotCreateIndex
                strReturn = "Warning: could not create index"
            Case mdwrnTimeoutNotSetCorrectly
                strReturn = "Warning: timeout not set correctly"
            Case mdwrnExecuteSQL
                strReturn = "Warning: error while executing SQL"
            Case mdwrnDeletingTablesOutsideOfTransaction
                strReturn = "Warning: deleting tables outside of transaction"
            Case mdwrnCouldNotProcessWithIndexedViews
                strReturn = "Warning: could not process with indexed views"
            Case Else
                strReturn = "Unknown action or warning"
        End Select
        
        ConvertAction = strReturn
    End Function
    
    
    

Community Additions

ADD
Show:
© 2014 Microsoft