Powershell前端SQL Server查询随机整理DataSet

Question

我需要制作一个简单的内务脚本，检查14个Server的表。最后，我使用了来自Adam's问题的this脚本。

但是，在运行时，它跳过对任意数量的服务器发布结果，然后将其查询转储到一个表中，然后单独执行任何剩余的服务器。我做错了什么？(因为我不能对亚当的回答发表评论)

我不介意整理表中的所有数据并在最后转储，但是如果这更容易实现，则使用列来显示服务器名称和结果。

这是我的脚本，下面是示例输出；

$year = Get-Date -Format yyyy
$servers = gc 'ServerList.txt'

foreach ($server in $servers) 
{
    try
    {
        $ServerName = "$server"
        $DatabaseName = "DatabaseName"
        $Query = "select count(*) from Alarm_Activations where Activation_Date not like '%$year%'"
        #$ds = $null -Tried adding this to fix the issue

        #Timeout parameters
        $QueryTimeout = 120
        $ConnectionTimeout = 30

        Write-Host ""
        Write-Host ""
        Write-Host "  Checking $server "

        #Action of connecting to the Database and executing the query and returning results if there were any.
        $conn = New-Object System.Data.SqlClient.SQLConnection
        $ConnectionString = "Data Source=$server\sqlexpress;Initial Catalog=DatabaseName;Connect Timeout=30; Integrated security=true;"
        $conn.ConnectionString = $ConnectionString

        $conn.Open()

        $cmd = New-Object system.Data.SqlClient.SqlCommand($Query,$conn)
        $cmd.CommandTimeout = $QueryTimeout

        $ds = New-Object system.Data.DataSet
        $da = New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
        [void]$da.fill($ds)
        $conn.Close()

        Write-Host "Number of activations older than $year :"
        $ds.Tables
    } 
    catch 
    {
        Write-Error "Something went wrong. Seems you have to do it manually!"
        return
    }
}

示例输出

转储表的目的似乎是随机的，但通常超过一半的服务器。

我见过最早的是Server-5，最新的；Server-14；

& CheckAlarmActivation.ps1


  Checking Server-1
Number of activations older than 2019 :



  Checking Server-2
Number of activations older than 2019 :


  Checking Server-3
Number of activations older than 2019 :


  Checking Server-4
Number of activations older than 2019 :


  Checking Server-5
Number of activations older than 2019 :


  Checking Server-6
Number of activations older than 2019 :


  Checking Server-7
Number of activations older than 2019 :


  Checking Server-8
Number of activations older than 2019 :


  Checking Server-9
Number of activations older than 2019 :


  Checking Server-10
Number of activations older than 2019 :
Column1
-------
      0
      0
      0
   3318
   1069
   4375
      8
   9357
     74
   1735


  Checking Server-11
Number of activations older than 2019 :
   7917


  Checking Server-12
Number of activations older than 2019 :
   3583


  Checking Server-13
Number of activations older than 2019 :
   5622


  Checking Server-14
Number of activations older than 2019 :
   4166

Answer 1

与AdminOfThings注释一样，使用Write-Output而不是Write-Host。因为您使用的是Write-Host流，并且只是输出一个变量(该变量转到输出流)($ds.Tables)，所以不能同时在控制台上看到这两个流。

除了使用Write-Output之外，我还会将DB调用的结果(以及希望从它们返回的任何结果)存储到集合中，然后在遍历所有服务器之后，将输出格式化(用于控制台、生成csv/html报告等)，从而解决这个问题。这种方法的好处是，如果其中一个服务器出现故障，您的脚本就不会失败。

编辑--我更新了catch块，以正确的方式获取异常。我还更新了从数据集中获取值的位置，以便展开，这样就没有数据行对象。如果有多个列，或者希望根据某些列创建计算属性，则可以使用更高级的Select-Object逻辑。SO post with more info。在您的情况下，您只想得到记录的计数。

$serverResults = @()
$servers = "srv1","srv2"

foreach ($server in $servers) 
{
  # Create an instance of a custom object that will hold the results of your query. Add properties as needed, they can be other complex objects, or simple types. 
   $serverResultInstance = New-Object PSObject -Property @{
   ServerName = $server
   DatabaseName = $DatabaseName 
   Tables = $null
   Exception = $null
   }
 try{
    #Connect to DB like you do (Don't use Write-Host)
    #Add your dataset for this instance 
    $ServerName = "$server"
        $DatabaseName = "dbName"
        $Query = "select count(*) from sys.columns"
        #$ds = $null -Tried adding this to fix the issue

        #Timeout parameters
        $QueryTimeout = 120
        $ConnectionTimeout = 30

        Write-Host ""
        Write-Host ""
        Write-Host "Checking $server"

        #Action of connecting to the Database and executing the query and returning results if there were any.
        $conn = New-Object System.Data.SqlClient.SQLConnection
        $ConnectionString = "Data Source=$server;Initial Catalog=$DatabaseName;Connect Timeout=$ConnectionTimeout; Integrated security=true;"
        $conn.ConnectionString = $ConnectionString

        $conn.Open()

        $cmd = New-Object system.Data.SqlClient.SqlCommand($Query,$conn)
        $cmd.CommandTimeout = $QueryTimeout

        $ds = New-Object system.Data.DataSet
        $da = New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
        [void]$da.fill($ds)

        # Instead of keeping the whole table/row object, expand out the column you want. If you have multiple columns or tables in your dataset you would need to change these values. 
    $serverResultInstance.Tables = $ds.Tables[0] | select -ExpandProperty Column1
 }
 catch{
    #This should have been $_.Exception
    $serverResultInstance.Exception = $_.Exception
 }
 finally{
    $conn.Close()
    $serverResults  += $serverResultInstance
 }
}
foreach($result in $serverResultInstance){
# Format your output here. Make a csv, send an email, etc. Or just:
 Write-Output "Number of activations older than $year :"
 Write-Output $result.Tables

}