Still don’t like the title, but I’m pushing it anyway. Here is how to pass a DataTable to a Sql Server stored procedure via powershell. In my case, I’m gathering a boat-load of data from the sys.dm_os_performance_counters DMV for numerous server to be queried and reported upon. First I query the target server, get the results into a datareader, load said datareader into a DataTable and then pass the DataTable as a parameter to a stored proc to be saved off. Use at your own risk, this has been only minimally tested.
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
[void][reflection.assembly]::LoadWithPartialName("System.data")
$servers = gc -Path 'C:\Servers.txt'
$cnString = 'Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DatabaseName;Data Source=ServerName'
$PerfQry = "
SELECT
[OBJECT_NAME],
counter_name,
instance_name,
cntr_value,
cntr_type
FROM sys.dm_os_performance_counters
WHERE object_name NOT IN(
'SQLServer:Deprecated Features',
'SQLServer:Database Replica',
'SQLServer:Memory Broker Clerks',
'SQLServer:User Settable',
'SQLServer:Broker Statistics',
'SQLServer:Availability Replica',
'SQLServer:Access Methods',
'SQLServer:Broker/DBM Transport',
'SQLServer:Broker Activation',
'SQLServer:Broker TO Statistics')
AND instance_name != 'mssqlsystemresource'
"
try{
$servers | %{
#$srvName = $_
$srvConn = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection"
$srvConn.ServerInstance = $_
[System.Data.DataTable]$perfTable = New-Object('system.Data.DataTable')
[System.Data.SqlClient.SqlCommand]$cmd = New-Object('system.data.sqlclient.sqlcommand')
[System.Data.SqlClient.SqlConnection]$c = New-Object('system.data.sqlclient.sqlconnection')
$c.connectionstring = $cnString;
$c.open();
$dr = $srvConn.ExecuteReader($PerfQry);
$perfTable.load($dr);
$cmd.Connection = $c;
$cmd.CommandType = [System.Data.CommandType]::StoredProcedure
$cmd.commandText = "dbo.InsertPerfCounters"
$serverIDParam = New-Object('system.data.sqlclient.sqlParameter')
$perfResultsParam = New-Object('system.data.sqlclient.sqlparameter')
#serverid param
$serverIDParam.ParameterName = "ServerID"
$serverIDParam.SqlDBtype = [System.Data.SqlDbType]::Int
$serverIDParam.Direction = [System.Data.ParameterDirection]::Input
$serverIDParam.value = 1
#sql table type param
$perfResultsParam.ParameterName = "PerfCounters"
$perfResultsParam.SqlDBtype = [System.Data.SqlDbType]::Structured
$perfResultsParam.Direction = [System.Data.ParameterDirection]::Input
$perfResultsParam.value = $perfTable
$cmd.parameters.add($serverIDParam);
$cmd.parameters.add($perfResultsParam);
$cmd.executeNonQuery();
}
}
catch{
$_ | fl -Force
}
finally{
$c.close();
}
Sql Server Powershell 
Hello,
I m struggling on passing datetime in in table value type parameter of stored procedure using powershell
I use same technique and also set datetime for the sqldatacolumn for the datetime column of the table value type. I get error ‘operand type clash between datetime2 and int’
Any suggestions?