Insert Distribution in PostSQL Macro Error

I tried to run the Insert Distribution in PostSQL macro within the Physical Modeling Macros\MS SQL Server folder.

I changed the code by adding lines for MsgBox to try and determine where the code is abending.  The macro is abending on the following line:

        If MyEntity.PostSQL Is Nothing Then

The error that I am received is:

Below is the code for the macro:

'MACRO TITLE:  INSERT DISTRIBUTION IN POSTSQL.BAS
'DESCRIPTION:  This Macro will add the WITH DISTRIBUTION to a specified table.
'The macro will prompt the user with a dialog to choose the table.
'The synonym code will be inserted into the PostSQL of the specified table.

Sub Main
    Dim MyModel As Model
    Dim MyDiagram As Diagram
    Dim MyEntity As Entity
    Dim MyView As View
    Dim Distribution As String

    'Current Model has to be physical


    'set ER variables
    Set MyDiagram = DiagramManager.ActiveDiagram
    Set MyModel = MyDiagram.ActiveModel


    If MyModel.Logical = True Then

        MsgBox("Current Model has to be Physical.")

    ElseIf MyModel.MajorPlatform <> "Microsoft SQL Server" Then
        MsgBox ("Current model has to be Microsoft SQL Server.")

    Else

    'initialize ObjectArray that holds tables and views
    'for the Dialog list
    Dim ObjectCount As Integer

' Get count of number of tables
    ObjectCount = MyModel.Entities.Count

' Get count of number of tables and views
'   ObjectCount = MyModel.Entities.Count + MyModel.Views.Count

    ReDim ObjectArray( 0 To ObjectCount ) As String

    Dim count As Integer

    count = 0

    'insert tables and views into the ObjectArray for the dialog list
    For Each MyEntity In MyModel.Entities
        ObjectArray(count) = MyEntity.EntityName
        count = count + 1
    Next

'   For Each MyView In MyModel.Views
'       ObjectArray(count) = MyView.Name
'       count = count + 1
'   Next

    Begin Dialog UserDialog 510,224,"Distribution Editor" ' %GRID:10,7,1,1
        GroupBox 20,7,470,161,"Choose the Table that you would like to add the distribution for:",.GroupBox1
'       GroupBox 20,7,470,161,"Choose the Table or View that you would like to add the distribution for:",.GroupBox1
        CancelButton 350,182,130,28
        DropListBox 40,28,220,168,ObjectArray(),.ObjectList
        OKButton 200,182,130,28
'       TextBox 40,140,220,21,.SynName
'       OptionGroup .Group1
'           OptionButton 40,91,60,14,"Yes",.yes
'           OptionButton 110,91,60,14,"No",.no
'       Text 30,70,430,14,"Do you want the Synonym accessible to all users (ie, PUBLIC)?",.Text2
'       Text 30,119,370,14,"Please give a name for your synonym.",.Text1
    End Dialog
    Dim dlg As UserDialog


    If Dialog(dlg) = -1 Then

            Distribution = "WITH " & vbCrLf
            Distribution = Distribution & "  ( " & vbCrLf
            Distribution = Distribution & "   DISTRIBUTION = ROUND_ROBIN REPLICATE HASH ( distribution_column_name ), " & vbCrLf
            Distribution = Distribution & "   CLUSTERED COLUMNSTORE INDEX " & vbCrLf
            Distribution = Distribution & "  ) " & vbCrLf
            MsgBox (Distribution)

'       If dlg.group1 = 0 Then
'
'           Distribution = "WITH " & vbCrLf
'           Distribution = Distribution & "  ( " & vbCrLf
'           Distribution = Distribution & "   DISTRIBUTION = ROUND_ROBIN REPLICATE HASH ( distribution_column_name ), " & vbCrLf
'           Distribution = Distribution & "   CLUSTERED COLUMNSTORE INDEX " & vbCrLf
'           Distribution = Distribution & "  ) " & vbCrLf
'
''          Synonym = "CREATE PUBLIC SYNONYM " & dlg.SynName & vbCrLf
''          Synonym = Synonym & vbTab & "FOR " & ObjectArray(dlg.objectlist) & vbCrLf & ";" & vbCrLf
'
'       Else
'
'           Distribution = "WITH " & vbCrLf
'           Distribution = Distribution & "  ( " & vbCrLf
'           Distribution = Distribution & "   DISTRIBUTION = ROUND_ROBIN REPLICATE HASH ( distribution_column_name ), " & vbCrLf
'           Distribution = Distribution & "   CLUSTERED COLUMNSTORE INDEX " & vbCrLf
'           Distribution = Distribution & "  ) " & vbCrLf
''          Synonym = "CREATE SYNONYM " & dlg.SynName & vbCrLf
''          Synonym = Synonym & vbTab & "FOR " & ObjectArray(dlg.objectlist) & vbCrLf & ";" & vbCrLf
'
'       End If

        Dim ObjName As String

        ObjName = ObjectArray(dlg.objectlist)

        Set MyEntity = MyModel.Entities.Item(ObjName)

'       Set MyView = MyModel.Views.Item(ObjName)

'       MsgBox (MyEntity.PostSQL)

'       If MyModel.Entities.Item(ObjName).PostSQL Is Nothing Then
'           MyModel.Entities.Item(ObjName).PostSQL = Distribution
'       Else
'           MyModel.Entities.Item(ObjName).PostSQL = MyEntity.GenPostSQL & Distribution
'       End If

'       OLD CODE
        If MyEntity.PostSQL Is Nothing Then
            MyEntity.PostSQL = Distribution
        Else
            MyEntity.PostSQL = MyEntity.PostSQL & Distribution
        End If

'       If MyView Is Nothing Then
'           MyEntity.PostSQL = MyEntity.PostSQL & Distribution
'       Else
'           MyView.PostSQL = MyView.PostSQL & Distribution
'       End If

    End If 'dialog

    End If 'Physical check

End Sub

Any thoughts on how to resolve the problem.  Other macros that I have reviewed seem to indicate that the code should work.

Thanks in advance for the assistance

Parents Reply Children
No Data