Home Forums SQL Server 2014 Administration - SQL Server 2014 Store PowerShell result in a table RE: Store PowerShell result in a table

  • March 5, 2018 at 1:46 pm

    #1982012

    ragupta 10363 - Monday, March 5, 2018 12:57 PM

    I am trying to write a script for gathering session information for all the SQL server instances in our environment and then store the result in a SQL server table. I am using the following query:

    SELECT host_name,COUNT(*) AS [Connections] 
      FROM sys.dm_exec_sessions 
      WHERE database_id > 0 
      GROUP BY host_name;"

    Can someone help with a PowerShell script for this so that I can store the result for this query for all my SQL server instances into 1 single table.

    If you are using the SQLServer module, after you create a table for the results, you can do something like:
    Invoke-Sqlcmd -Query "
    SELECT host_name,COUNT(*) AS [Connections]
    FROM sys.dm_exec_sessions
    WHERE database_id > 0
    GROUP BY host_name;" -OutputAs DataTables |    
    Write-SqlTableData -ServerInstance ServerName -DatabaseName DatabaseName -SchemaName TableSchema -TableName TableName -Force

    Sue