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

1. 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

1. 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

1. 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

1. 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

1. 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

1. 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

1. 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