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?
