sql - Powershell Script Array Error - Must Declare Scalar Variable "@" - Stack Overflow

admin管理员组

文章数量:1346203

I have a Powershell script to export data from tables to .csv files, and I want to filter out certain datatypes from the .csv export; however, I get a warning error and no data in the 3 generated .csv files.

When I run the script, it generates 3 empty .csv files, and it gives me the following errors/warnings:

WARNING: [13:57:30][Connect-DbaInstance] Additional parameters are passed in, but they will be ignored WARNING: [13:57:30][Invoke-DbaQuery] [COP-CPB-DAT1] Failed during execution | Must declare the scalar variable "@".

Here is my code (below):

function Exclude-ColumnsByType {
    param(
        [Parameter(Mandatory=$true, ValueFromPipeline=$true)]
        $InputObject,
        [Parameter(Mandatory=$true)]
        [string[]]$ExcludeType
    )
    
    $propertiesToInclude = $InputObject | Get-Member -MemberType Properties | Where-Object {$ExcludeType -notcontains $_.TypeNameOfValue} | Select-Object -ExpandProperty Name
    
    $InputObject | Select-Object $propertiesToInclude
}



#Initiate a connection
$MyConnection = Connect-DbaInstance -SqlInstance LOCALHOST\MSSQLSERVER -TrustServerCertificate
#Define a list of tables
$Tables = @("aaaa1_table4", "aaaa1_table5", "newspic")
#Iterate through the list
foreach ($Table in $Tables) {
     #Build the query
     $Query = "SELECT * FROM $Table;"


$filteredData = $Query | Exclude-ColumnsByType -ExcludeType "System.String", "System.DateTime"

     #Build the output file path and remove the schema, dot, and v from the file name
     #and append the .csv extension
     $CsvPath = "C:\CSVOut\b\" + $Table + ".csv"
     #Run the query and export results to CSV
     Invoke-DbaQuery -SqlInstance $MyConnection -Database cop `
         -Query "$filteredData" |  Export-Csv -Path "$CsvPath" `
         -Delimiter "|" -NoTypeInformation -Encoding unicode 
 }

When I run the code without the filtering function, it generates 3 .csv files populated with correct content, although it still gives the first warning:

WARNING: [13:57:30][Connect-DbaInstance] Additional parameters are passed in, but they will be ignored

That code, which generates the populated .csv files, (without the filter function) is here:

#Initiate a connection
$MyConnection = Connect-DbaInstance -SqlInstance LOCALHOST\MSSQLSERVER -TrustServerCertificate
#Define a list of tables
$Tables = @("aaaa1_table4", "aaaa1_table5", "newspic")
#Iterate through the list
foreach ($Table in $Tables) {
     #Build the query
     $Query = "SELECT * FROM $Table;"
     #Build the output file path and remove the schema, dot, and v from the file name
     #and append the .csv extension
     $CsvPath = "C:\CSVOut\" + $Table + ".csv"
     #Run the query and export results to CSV
     Invoke-DbaQuery -SqlInstance $MyConnection -Database cop `
         -Query "$Query"  |  Export-Csv -Path "$CsvPath" `
         -Delimiter "|" -NoTypeInformation -Encoding unicode 
 }

I am wondering if the error is in the array object somewhere (although it runs in the second example)? Thanks for any leads.

本文标签： sqlPowershell Script Array ErrorMust Declare Scalar Variable quotquotStack Overflow