来自SQL数据库的IP地址查询列表

Question

我正在使用powershell

1. 从IP地址列表中获取IP地址
2. 将其插入SQL查询中。
3. 输出结果

到目前为止，只有当我包含一个IP地址时，代码才能工作。如果我包含多个脚本，脚本将无限期挂起。

这个代码似乎是正确的，应该修正什么？

$file = Get-Content C:\list.txt

$conn.Open()

foreach ($k in $file){
    write-host $k

    $sql = "SELECT dbo.sem_computer.COMPUTER_NAME,[IP_ADDR1_TEXT],(dbo.SEM_AGENT.FREE_DISK/1073741824)as 'Free Disk Space (GB)', 
    (dbo.SEM_COMPUTER.DISK_TOTAL/1073741824) as 'Total Disk Space (GB)',
    (dbo.SEM_AGENT.FREE_MEM/1073741824) as 'Free Memory (GB)', (dbo.SEM_COMPUTER.MEMORY/1073741824) as 'Total Memory (GB)', 
    dbo.SEM_COMPUTER.OPERATION_SYSTEM,NAME, SEM_AGENT.MAJOR_VERSION
    FROM dbo.sem_computer, [dbo].[V_SEM_COMPUTER], dbo.IDENTITY_MAP, dbo.SEM_CLIENT, dbo.SEM_AGENT
    WHERE [dbo].[V_SEM_COMPUTER].COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
    AND dbo.SEM_CLIENT.GROUP_ID = IDENTITY_MAP.ID
    AND dbo.SEM_CLIENT.COMPUTER_ID = dbo.SEM_COMPUTER.COMPUTER_ID
    AND dbo.SEM_COMPUTER.COMPUTER_ID = dbo.SEM_AGENT.COMPUTER_ID
    AND [IP_ADDR1_TEXT] = '$k'"

    $cmd = New-Object System.Data.SqlClient.SqlCommand($sql,$conn)

    do{
    try{
        $rdr = $cmd.ExecuteReader()

        # Read Computer Name, Computer ID, IP address, Domain, and Operating System from the Database into a multidimensional array

        while ($rdr.read()){
            $sql_output += ,@($rdr.GetValue(0), $rdr.GetValue(1), $rdr.GetValue(2), $rdr.GetValue(3), $rdr.GetValue(4), $rdr.GetValue(5), $rdr.GetValue(6), $rdr.GetValue(7), $rdr.GetValue(8))
        }
        $transactionComplete = $true
    }
    catch{
        $transactionComplete = $false
    }
    }until ($transactionComplete)
}

Write-host $sql_output

$conn.Close()  

Answer 1

显然，这就是答案(来自评论)：

考虑过在for循环中构建Sql连接吗？另外，如果向$sql_output写入错误，您将永远运行。为了排除故障，我会编写主机$sql，以确保文本是正确的，然后花时间确保您正确地与读者一起工作。

Answer 2

我可能会先测试一下它是否有效：

$file = Get-Content C:\list.txt

$conn.Open()

foreach ($k in $file){
    write-host $k

    $sql = "SELECT dbo.sem_computer.COMPUTER_NAME,[IP_ADDR1_TEXT],(dbo.SEM_AGENT.FREE_DISK/1073741824)as 'Free Disk Space (GB)', 
    (dbo.SEM_COMPUTER.DISK_TOTAL/1073741824) as 'Total Disk Space (GB)',
    (dbo.SEM_AGENT.FREE_MEM/1073741824) as 'Free Memory (GB)', (dbo.SEM_COMPUTER.MEMORY/1073741824) as 'Total Memory (GB)', 
    dbo.SEM_COMPUTER.OPERATION_SYSTEM,NAME, SEM_AGENT.MAJOR_VERSION
    FROM dbo.sem_computer, [dbo].[V_SEM_COMPUTER], dbo.IDENTITY_MAP, dbo.SEM_CLIENT, dbo.SEM_AGENT
    WHERE [dbo].[V_SEM_COMPUTER].COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
    AND dbo.SEM_CLIENT.GROUP_ID = IDENTITY_MAP.ID
    AND dbo.SEM_CLIENT.COMPUTER_ID = dbo.SEM_COMPUTER.COMPUTER_ID
    AND dbo.SEM_COMPUTER.COMPUTER_ID = dbo.SEM_AGENT.COMPUTER_ID
    AND [IP_ADDR1_TEXT] = '$k'"

    $command = $conn.CreateCommand()
    $command.CommandText = $sql
    $sql_output = $command.ExecuteReader()
}

Write-host $sql_output

$conn.Close()  

然后在后面添加错误处理(尝试/捕获)。