Store PowerShell result in a table

Question

Store PowerShell result in a table

ragupta 10363

SSC Veteran

Points: 245
More actions
March 5, 2018 at 12:57 pm

#403159

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.

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

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

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

ragupta 10363 SSC Veteran Points: 245 More actions · Answer 2

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?

Sue_H SSC Guru Points: 90860 More actions · Answer 3

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

ragupta 10363 SSC Veteran Points: 245 More actions · Answer 4

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?

Sue_H SSC Guru Points: 90860 More actions · Answer 5

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

ragupta 10363 SSC Veteran Points: 245 More actions · Answer 6

ragupta 10363

SSC Veteran

Points: 245

March 6, 2018 at 2:33 pm

#1982154

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