Store PowerShell result in a table

  • 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.

  • 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

  • Thanks for your reply.
    I wrote the following script:
    $server = get-content "C:\Users\sql_dba_test\Desktop\servers.txt"
    Foreach ($s in $server)
    {

    Invoke-Sqlcmd -Query "
    SELECT host_name,COUNT(*) AS [Connections]
    FROM sys.dm_exec_sessions
    WHERE database_id > 0
    GROUP BY host_name;" | 
    Write-SqlTableData -ServerInstance "HQ-SQLDEVAPP00\DB06" -DatabaseName "SQL_App_Repository" -SchemaName "dbo" -TableName "sql_sessions" -Force
     
    }

    But I am getting the following error:
    "Write-SqlTableData : The term 'Write-SqlTableData' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again"

    Is there something I need to install first?

  • ragupta 10363 - Tuesday, March 6, 2018 9:43 AM

    Thanks for your reply.
    I wrote the following script:
    $server = get-content "C:\Users\sql_dba_test\Desktop\servers.txt"
    Foreach ($s in $server)
    {

    Invoke-Sqlcmd -Query "
    SELECT host_name,COUNT(*) AS [Connections]
    FROM sys.dm_exec_sessions
    WHERE database_id > 0
    GROUP BY host_name;" | 
    Write-SqlTableData -ServerInstance "HQ-SQLDEVAPP00\DB06" -DatabaseName "SQL_App_Repository" -SchemaName "dbo" -TableName "sql_sessions" -Force
     
    }

    But I am getting the following error:
    "Write-SqlTableData : The term 'Write-SqlTableData' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again"

    Is there something I need to install first?

    Doesn't look like you have the SQLServer module. Are you using SQLPS?

    Sue

  • I install SQLServer module and tried again but getting the same error: See below:

    PS SQLSERVER:\> Get-Module SqlServer -ListAvailable

      Directory: C:\Program Files\WindowsPowerShell\Modules

    ModuleType Version  Name           ExportedCommands
    ---------- -------  ----           ----------------
    Script  21.0.17224 SqlServer

    Do i need to reboot my machine after installing the module?

  • ragupta 10363 - Tuesday, March 6, 2018 11:46 AM

    I install SQLServer module and tried again but getting the same error: See below:

    PS SQLSERVER:\> Get-Module SqlServer -ListAvailable

      Directory: C:\Program Files\WindowsPowerShell\Modules

    ModuleType Version  Name           ExportedCommands
    ---------- -------  ----           ----------------
    Script  21.0.17224 SqlServer

    Do i need to reboot my machine after installing the module?

    You need to import the module for your session. In Powershell, execute this:
    import-module sqlserver
    It's generally better to use that module (Sqlserver)  since it is the replacement for SQLPS.

    Sue

  • This fixed the issue.
    Thanks a lot for your help.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply