sql数据集

laik7k3q  于 2021-07-24  发布在  Java
关注(0)|答案(1)|浏览(356)

这个问题在这里已经有了答案

powershell输出在函数之间交叉(1个答案)
9个月前关门了。
我有一个powershell脚本,它在3个服务器的列表中循环。sql脚本与一起运行 Invoke-Sqlcmd 结果集存储到变量中 $DS . 在循环结束时,我返回带有 $DS.Tables.Rows .
但是结果集正在混合在一起。我试着用 Write-Host 消息来分解结果。但他们还是混在一起了。
为什么结果在输出中混合在一起?
如何在每个循环之间分离输出?
谢谢


对象类型

$DS | gm ............... TypeName: System.Data.DataSet

$DS.Tables | gm ........ TypeName: System.Data.DataTable

$DS.Tables.Rows | gm ... TypeName: System.Data.DataRow

脚本


######################### >

# SQL servers

$PCList= @("GCOD139","GCOD039","GCOP039") 
Write-Host ($PCList -join ", ") 

# Query multiple servers

foreach ($PC in $PCList) {

    Write-Host ($PC + "...") -ForegroundColor Yellow

    # SQL parameters
    $Params = @{
        'ServerInstance' = $PC;  
        'Database' = 'master';
    #   'Username' = 'svcBIPOC';
    #   'Password' = 'bipoc2020*';
    #   'InputFile' = "C:\ScriptFolder\TestSqlCmd.sql"
        'Query' = '
            SELECT 
                [Server]= @@SERVERNAME
                --MB to GB
                , REPLACE(name, ''MB'', ''GB'')
                ,[value]= CAST(value as int)/1000
                , [value_in_use]= CAST(value_in_use as int)/1000
                --, value, value_in_use, [description]
            FROM sys.configurations
            WHERE name like ''%server memory%''
            ORDER BY name desc 
            OPTION (RECOMPILE);
        '
    }

    # Capture SQL Dataset
    # (Get-Date).ToSTring('s')  + " SQL query start..."
    $DS = Invoke-Sqlcmd @Params -As DataSet
    #(Get-Date).ToSTring('s')  + " SQL query end..."

    Write-host "-----"
    Write-host "SQL"

    sleep -Seconds 5
    $DS.Tables.Rows
    sleep -Seconds 5

}

######################### 
efzxgjgh

efzxgjgh1#

停止使用 Write-Host 传达进度信息-使用 Write-Progress 为了这个!

$PCList= @("GCOD139","GCOD039","GCOP039") 
Write-Progress -Activity "Query servers" -Status "About to query: $($PCList -join ", ")"

# Query multiple servers

foreach ($PC in $PCList) {

    Write-Progress -Activity "Query servers" -Status "Querying: $PC"

    # SQL parameters
    $Params = @{
        'ServerInstance' = $PC;  
        'Database' = 'master';
    #   'Username' = 'svcBIPOC';
    #   'Password' = 'bipoc2020*';
    #   'InputFile' = "C:\ScriptFolder\TestSqlCmd.sql"
        'Query' = '
            SELECT 
                [Server]= @@SERVERNAME
                --MB to GB
                , REPLACE(name, ''MB'', ''GB'')
                ,[value]= CAST(value as int)/1000
                , [value_in_use]= CAST(value_in_use as int)/1000
                --, value, value_in_use, [description]
            FROM sys.configurations
            WHERE name like ''%server memory%''
            ORDER BY name desc 
            OPTION (RECOMPILE);
        '
    }

    # Capture SQL Dataset
    $DS = Invoke-Sqlcmd @Params -As DataSet
    $DS.Tables.Rows
}

Write-Progress -Activity "Query servers" -Completed

现在进度消息不会干扰函数的实际输出

相关问题