Get-DbaDbStoredProcedure When | Write-DbaDbTableData Parameters doesn't populate

[Sirwill1968]

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

When running Get-DbaDbStoredProcedure When | Write-DbaDbTableData in the reulting table there is a parameter column it is either null or if params it has this in the field instead of a list of parameters. Microsoft.SqlServer.Management.Smo.StoredProcedureParameterCollection

Steps to Reproduce

# provide your command(s) executed pertaining to dbatools
# please include variable values (redacted or fake if needed) for reference

Get-DbaDbStoredProcedure -SqlInstance Server1-ExcludeSystemSp |Write-DbaDbTableData -SqlInstance Server1 -Database BB -Table StoredProcedures -Truncate

Please confirm that you are running the most recent version of dbatools

2.1.22 same problem in 2.1.17

Other details or mentions

No response

What PowerShell host was used when producing this error

PowerShell Core (pwsh.exe), Windows PowerShell (powershell.exe), Windows PowerShell ISE (powershell_ise.exe)

PowerShell Host Version

Name Value

---

PSVersion 7.4.2 PSEdition Core GitCommitId 7.4.2 OS Microsoft Windows 10.0.14393 Platform Win32NT PSCompatibleVersions {1.0, 2.0, 3.0, 4.0…} PSRemotingProtocolVersion 2.3 SerializationVersion 1.1.0.1 WSManStackVersion 3.0

and

Name Value

---

PSVersion 5.1.14393.6343
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.14393.6343
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

SQL Server Edition and Build number

Microsoft SQL Server 2019 (RTM-CU26) (KB5035123) - 15.0.4365.2 (X64) Mar 29 2024 23:02:47 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor)

.NET Framework Version

.NET 8.0.4

[rickloveslamp]

I'm not sure this is really a bug. The Parameters property is an array of objects, each with their own properties. It doesn't really have a ToString() property that would create the output that you are looking for automatically that the Write-DbaDbTableData command could use. If you want to create your own list of parameters you can use something like this. It removes the original parameters property and puts in a new expression with the same name.

Get-DbaDbStoredProcedure -SqlInstance Server1 -ExcludeSystemSp | select -ExcludeProperty Parameters -Property *, @{"name"="Parameters"; "expression"={($_.Parameters | % {$out = $_.name + " (" + $_.datatype; if ($_.DefaultValue){$out += ", Default = " + $_.DefaultValue}; if ($_.IsOutputParameter){$out +=", Output"}; $out += ")"; $out } ) -join ", "}} | Write-DbaDbTableData -SqlInstance Server1 -Database BB -Table StoredProcedures -Truncate

Or a less squished version might use a function like this:

function paramsToString($parameters) {
    $outList = @()
    foreach ($parameter in $parameters | Sort-Object -Property id) {
        $out = $parameter.name + " (" + $parameter.datatype
        if ($parameter.DefaultValue) {
            $out += ", Default = " + $parameter.DefaultValue
        }
        if ($parameter.IsOutputParameter) {
            $out += ", Output"
        }
        $out += ")"
        $outList += $out
    }
    return $outList -join ", "
}
Get-DbaDbStoredProcedure -SqlInstance Server1 -ExcludeSystemSp | Select-Object -ExcludeProperty Parameters -Property *, @{ "name" = "Parameters"; "expression" = { paramsToString($_.Parameters) } }  | Write-DbaDbTableData -SqlInstance Server1 -Database BB -Table StoredProcedures -Truncate

[Sirwill1968]

All 5 of these columns have the smo object info in it instead of populating the column with the values. Why write the columns at all if not populate them. It's also interesting that your code to populate the parameters, the column ends up as the last column in the table (Had to do an auto create because the columns were different)

[Parameters] --Microsoft.SqlServer.Management.Smo.StoredProcedureParameterCollection [Events] --Microsoft.SqlServer.Management.Smo.StoredProcedureEvents [ParentCollection] --Microsoft.SqlServer.Management.Smo.StoredProcedureCollection [Properties] --Microsoft.SqlServer.Management.Smo.SqlPropertyCollection [ExecutionManager] --Microsoft.SqlServer.Management.Smo.ExecutionManager