Powershell won't run nested SQL Query

SQL Server 2012

I have Powershell script that runs queries and outputs to EXCEL

If I execute the following

$SQL9 = "SELECT *
FROM dbo.Computers
WHERE Date_of_Record = CAST(GETDATE() AS DATE)
AND dbo.Computers.COMPUTER_NAME LIKE '%s001'
AND dbo.Computers.IP_Address LIKE '%.100'
ORDER BY Computer_Name"

$ws = $wb.Worksheets.Item(9)
$ws.name = "GUP"

$qt = $ws.QueryTables.Add("ODBC;DSN=$DSN3;UID=$username;PWD=$password", $ws.Range("A1"), $SQL9)

if ($qt.Refresh()){
    $ws.Activate()
    $ws.Select()
    $excel.Rows.Item(1).HorizontalAlignment = $xlCenter
    $excel.Rows.Item(1).VerticalAlignment = $xlTop
    $excel.Rows.Item("1:1").Font.Name = "Calibri" 
    $excel.Rows.Item("1:1").Font.Size = 11 
    $excel.Rows.Item("1:1").Font.Bold = $true 
 }

It works

But if I use another SQL statement, i.e

$SQL9 = "SELECT Date_of_Record, Computer_Name, IP_Address, Agent_Version
FROM dbo.Computers
WHERE Computer_Name in (SELECT Computer_Name
                        FROM dbo.Computers
                        GROUP BY Computer_Name
                        HAVING COUNT(DISTINCT Agent_Version) > 1)
AND dbo.Computers.COMPUTER_NAME LIKE '%s001'
AND dbo.Computers.IP_Address LIKE '%.100'
ORDER BY Computer_Name, Date_of_Record"

It does not work. I ran this query in SQL Management Studio and it works as expected.

I even used another SQL statement and ran the script, i.e.

$SQL9 = "SELECT Computer_Name, IP_Address, COUNT(*) AS Number_of_Days_Checked_Past_Month
FROM dbo.Computers
WHERE Date_of_Record > GETDATE() - 30
AND dbo.Computers.COMPUTER_NAME LIKE '%s001'
AND dbo.Computers.IP_Address LIKE '%.100'
Group BY Computer_Name, IP_Address
ORDER BY Number_of_Days_Checked_Past_Month DESC"

and it works.

Why is the powershell script hanging when I attempt to execute

$SQL9 = "SELECT Date_of_Record, Computer_Name, IP_Address, Agent_Version
FROM dbo.Computers
WHERE Computer_Name in (SELECT Computer_Name
                        FROM dbo.Computers
                        GROUP BY Computer_Name
                        HAVING COUNT(DISTINCT Agent_Version) > 1)
AND dbo.Computers.COMPUTER_NAME LIKE '%s001'
AND dbo.Computers.IP_Address LIKE '%.100'
ORDER BY Computer_Name, Date_of_Record"

Is it because there is a nested SELECT? How to fix this?

Even if you have not told it, I guess the problem is that you're using ODBC to connect to SQL Server from Excel.

ODBC doesn't support nested queries. You need to use a differnt means to acces from Excel to SQL Server. Can you use OLE DB drivers?

You can also modify your query so that it doesn't use subqueries (I can't see how to do it). You can also create a view on the server, so that the query from excel doesn't use subqueries.

Please, see this SO answer: you can use the subquery inside a join, and filter on the joined value.