functions/resolve-axtablefieldids.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136

<#
    .SYNOPSIS
        Fix table and field ID conflicts
         
    .DESCRIPTION
        Fixes both table and field IDs in the AX SqlDictionary (data db) to match the AX code (Model db)
         
        Useful for after a database has been restored and the table or field IDs do not match
        Run this command instead of letting the database synchronization process drop and recreate the table
         
        Before running:
        Stop the AOS
        Always take the appropriate SQL backups before running this script
         
        After running:
        Start the AOS
        Sync the database within AX
         
        Note:
        Objects that are new in AOT will get created in SQL dictionary when synchronization happens
         
    .PARAMETER DatabaseServer
        Server name of the database server
         
        Default value is: "localhost"
         
    .PARAMETER DatabaseName
        Name of the database
         
        Default value is: "MicrosoftDynamicsAx"
         
    .PARAMETER ModelstoreDatabase
        Name of the modelstore database
         
        Default value is: "MicrosoftDynamicsAx_model"
         
    .PARAMETER SqlUser
        User name of the SQL Server credential that you want to use when working against the database
         
    .PARAMETER SqlPwd
        Password of the SQL Server credential that you want to use when working against the database
         
    .PARAMETER Force
        Instruct the cmdlet to overwrite any existing bak (backup) tables from previous executions
         
    .PARAMETER OutputCommandOnly
        When provided the SQL is returned and not executed
         
        Note: This is useful for troubleshooting or providing the script to a DBA with access to the server
         
    .EXAMPLE
        PS C:\> Resolve-AxTableFieldIDs
         
        This will execute the cmdlet with all the default values.
        This will work against the SQL server that is on localhost.
        The database is expected to be "MicrosoftDynamicsAx_model".
         
    .NOTES
        Author: Dag Calafell, III (@dodiggitydag)
        Reference: http://calafell.me/the-ultimate-ax-2012-table-and-field-id-fix-for-synchronization-errors/
#>

Function Resolve-AxTableFieldIDs {
    [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseSingularNouns', '')]
    [CmdletBinding()]
    [OutputType([System.String], ParameterSetName = "Generate")]
    Param(
        [string] $DatabaseServer = $Script:ActiveAosDatabaseserver,

        [string] $DatabaseName = $Script:ActiveAosDatabase,

        [string] $ModelstoreDatabase = $Script:ActiveAosModelstoredatabase,

        [string] $SqlUser,

        [string] $SqlPwd,

        [Switch] $Force,

        [Parameter(ParameterSetName = "Generate")]
        [Switch] $OutputCommandOnly
    )

    Invoke-TimeSignal -Start

    $baseParams = Get-DeepClone $PSBoundParameters
    $baseParams.Add("TrustedConnection", $true)

    $UseTrustedConnection = Test-TrustedConnection $baseParams

    $SqlParams = @{ DatabaseServer = $DatabaseServer; DatabaseName = $DatabaseName;
        SqlUser = $SqlUser; SqlPwd = $SqlPwd
    }

    $forceParameterValue = "0"

    if ($Force) { $forceParameterValue = "1" }

    $sqlCommand = Get-SqlCommand @SqlParams -TrustedConnection $UseTrustedConnection

    $commandText = (Get-Content "$script:ModuleRoot\internal\sql\resolve-sqldictionaryids.sql") -join [Environment]::NewLine
    
    $sqlCommand.CommandText = $commandText.Replace('@DatabaseName', $DatabaseName).Replace('@ModelDatabaseName', $ModelstoreDatabase).Replace("@ForceValue", $forceParameterValue)

    if ($OutputCommandOnly) {
        (Get-SqlString $sqlCommand)
    }
    else {
        $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] { param($sender, $event) Write-PSFMessage -Level Host -Message $($event.Message) -Target $($event.Message) }
        $sqlCommand.Connection.add_InfoMessage($handler)
        $sqlCommand.Connection.FireInfoMessageEventOnUserErrors = $true;

        try {
            Write-PSFMessage -Level InternalComment -Message "Executing a script against the database." -Target (Get-SqlString $sqlCommand)
            
            $sqlCommand.Connection.Open()
            $sqlCommand.ExecuteNonQuery()

            Write-PSFMessage -Level Verbose -Message "SQL query executed."
        }
        catch {
            Write-PSFMessage -Level Host -Message "Something went wrong while working against the database" -Exception $PSItem.Exception
            Stop-PSFFunction -Message "Stopping because of errors"
            return
        }
        finally {
            if ($sqlCommand.Connection.State -ne [System.Data.ConnectionState]::Closed) {
                $sqlCommand.Connection.Close()
            }
    
            $sqlCommand.Dispose()
        }
    }

    Invoke-TimeSignal -End
}