Pass a table type parameter to sql server via powershell

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.


$servers = gc -Path 'C:\Servers.txt'
$cnString = 'Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DatabaseName;Data Source=ServerName'

$PerfQry = "
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'


	$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.SqlConnection]$c = New-Object('')
		$c.connectionstring = $cnString;
		$dr = $srvConn.ExecuteReader($PerfQry);
		$cmd.Connection = $c;
		$cmd.CommandType = [System.Data.CommandType]::StoredProcedure
		$cmd.commandText = "dbo.InsertPerfCounters"
		$serverIDParam = New-Object('')
		$perfResultsParam = New-Object('')
		#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
	$_ | fl -Force

One thought on “Pass a table type parameter to sql server via powershell

  1. 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?

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.