Site icon Parenting Voice

“The multi-part identifier could not be bound” When using PowerShell to run a working SSMS query

admin

Below is a working SSMS query, meaning it will run and produce the requested results in SSMS with no problems. But, I am trying to get this query to run in PowerShell in order to create a daily automated script. PowerShell simply puts the output of this SSMS query into an array, then transfers it to a .txt file. When it is run in PowerShell, it kicks back:

Exception calling "Fill" with "1" argument(s)
"The multi-part identifier "l.cust_login" could not be bound"
"The multi-part identifier "l.user_id" could not be bound"
"The multi-part identifier "c.cust_full_name" could not be bound"
"The multi-part identifier "a.login_type" could not be bound"
"The multi-part identifier "a.flag_login" could not be bound"
"The multi-part identifier "a1.flag_login" could not be bound"
"The multi-part identifier "a2.flag_login" could not be bound"

Here is the query that mind you, works in SSMS just fine!

    SELECT
    rtrim(l.cust_login)+':'+
    rtrim(l.user_id)+':'+
    rtrim(c.cust_full_name)+':'+
    rtrim(a.login_type)+':'+
    
    CASE
    WHEN a1.flag_login IS NOT NULL THEN 'flagged'
    ELSE ''
    END+':'+

    CASE
    WHEN a2.flag_login IS NOT NULL THEN 'co-flagged'
    ELSE ''
    END
    
    FROM accounts a
    INNER JOIN customer c ON a.owner_id = c.owner_id
    INNER JOIN logins l ON a.cust_login = l.cust_login

    --problematic joins
    LEFT OUTER JOIN accounts a1 ON (a.cust_login = a1.cust_login AND a1.flag_login = 22)
    LEFT OUTER JOIN accounts a2 ON (a.cust_login = 
    a2.cust_login AND a2.flag_login IN (45,46))
    
    WHERE a.flag_login = 55
    ORDER BY 1

Now, ive been able to find where PowerShell gets upset, and its with the recursive table joins. If I leave them out, then the query runs fine in PowerShell. Below is an example query that I made that does not include the needed JOINS and CASE

    SELECT
    rtrim(l.cust_login)+':'+
    rtrim(l.user_id)+':'+
    rtrim(c.cust_full_name)+':'+
    rtrim(a.login_type)+':'
    
    --missing CASE statements here
    
    FROM accounts a
    INNER JOIN customer c ON a.owner_id = c.owner_id
    INNER JOIN logins l ON a.cust_login = l.cust_login
    
    --missing outer joins here
        
    WHERE a.flag_login = 55
    ORDER BY 1

I actually had the bright idea of converting the problematic query to include temp tables, I sadly have the same issue! Even when I define separate temp tables in place of the recursive joins. As well as CTEs!

I suppose my main question is, does anyone know a workaround that powershell may like for the problematic outer joins. As with the error, it seems as if powershell is having trouble distinguishing between accounts a, accounts a1, and accounts a2. Even though when ran solely in SSMS, there is no issue.

I know this is a lot, I added the powershell script due to request. Sorry for the long post.

PowerShell SQL module:

function Invoke-table1SQL {
    param ([string] $FileLocation)

    if (!(Test-Path $FileLocation)) {
        Write-Host -BackgroundColor:Black -ForegroundColor:Red "File does not exist: $FileLocation"
        exit
    }

    $config_file = "$PSScriptRoot\ConnectionStrings.config"
    [xml] $appSettings = (Get-Content $config_file)
    $connectionString = $($appSettings.connectionStrings.add | Where-Object { $_.name -eq "ConnectionString" }).connectionString
    
    #db connection
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = $connectionString
    
    #run sql cmd
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandTimeout = 1200
    $SqlCmd.Connection = $SqlConnection
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlCmd.CommandText = get-content $FileLocation
    $SqlAdapter.SelectCommand = $SqlCmd
    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet)
    
    return $DataSet
}

export-modulemember -function Invoke-table1SQL

And here is the actual script using the module:

Import-Module $PSScriptRoot\Invoke-table1SQL.psm1 -Force

#declare
$queryLocation = "[location of query]"
$results = Invoke-table1SQL -FileLocation $queryLocation

#return index '0' in $dataset - $results and clean up output file
$outfile = "[txt file output location] " 
$results.Tables[0].Rows | Format-Table -HideTableHeaders | Out-File -filePath $outfile
Exit mobile version