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.

[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();
}

One response to “Pass a table type parameter to sql server via powershell”

Chandresh Sanghavi

September 26, 2021 at 7:19 pm

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?

Sql Server Powershell