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
