Powershell将不会运行嵌套的SQL查询 [英] Powershell won't run nested SQL Query

问题描述

SQL Server 2012

SQL Server 2012

我有运行查询并输出到EXCEL的Powershell脚本

I have Powershell script that runs queries and outputs to EXCEL

如果我执行以下操作

$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 
 }

有效

但是如果我使用另一个SQL语句，即

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"

它不起作用.我在SQL Management Studio中运行了此查询，它按预期运行.

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

我什至使用了另一个SQL语句并运行了脚本，即

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"

它有效.

为什么我尝试执行Powershell脚本时会挂起

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"

是因为存在嵌套的SELECT吗?该如何解决?

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

推荐答案

即使您没有告诉我，我想的问题是您正在使用ODBC从Excel连接到SQL Server.

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不支持嵌套查询.您需要使用其他方式才能从Excel访问SQL Server.可以使用OLE DB驱动程序吗?

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?

您还可以修改查询，使其不使用子查询(我看不到如何执行查询).您还可以在服务器上创建视图，以便excel的查询不使用子查询.

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.

请看到此SO答案:您可以在联接内使用子查询，并根据联接的值进行过滤.

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

这篇关于Powershell将不会运行嵌套的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！