functions/get-axparametersniffingsetting.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

<#
    .SYNOPSIS
        Get the parameter sniffing configuration
         
    .DESCRIPTION
        Get the parameter sniffing value from the database that has been released by Microsoft for AX 2012
         
    .PARAMETER DatabaseServer
        Server name of the database server
         
        Default value is: "localhost"
         
    .PARAMETER DatabaseName
        Name of the database
         
        Default value is: "MicrosoftDynamicsAx"
         
    .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
         
    .EXAMPLE
        PS C:\> Get-AxParameterSniffingSetting
         
        This will query the database for the parameter sniffing settings.
        The result will be displayed along with a ShouldBe value to easily tell you if something is off.
         
    .NOTES
        Author: Mötz Jensen (@Splaxi)
         
#>

function Get-AxParameterSniffingSetting {
    [CmdletBinding()]
    #[OutputType()]
    param (
        [Parameter(Mandatory = $false, ValueFromPipelineByPropertyName = $true, Position = 1)]
        [string] $DatabaseServer = $Script:ActiveAosDatabaseserver,

        [Parameter(Mandatory = $false, ValueFromPipelineByPropertyName = $true, Position = 2)]
        [string] $DatabaseName = $Script:ActiveAosDatabase,

        [Parameter(Mandatory = $false, Position = 3)]
        [string] $SqlUser,

        [Parameter(Mandatory = $false, Position = 4)]
        [string] $SqlPwd
    )

    Invoke-TimeSignal -Start

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

    $UseTrustedConnection = Test-TrustedConnection $baseParams

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

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

    $sqlCommand.CommandText = (Get-Content "$script:ModuleRoot\internal\sql\get-axparametersniffingsetting.sql") -join [Environment]::NewLine

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

        $sqlCommand.Connection.Open()

        $reader = $sqlCommand.ExecuteReader()

        while ($reader.Read() -eq $true) {
            [PSCustomObject]@{
                Name = "$($reader.GetString($($reader.GetOrdinal("Name"))))"
                Value = "$($reader.GetString($($reader.GetOrdinal("Value"))))"
                ShouldBe = "1"
            }
        }
    }
    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
}