Events (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.

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

Processing Actions

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.

Tutorial - Trapping Database Events

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