Is it possible to pass in a table valued parameter to a stored procedure from Powershell using the Invoke-SqlCmd?
This is what I've tried so far from my googling but I'm not having any luck.
$query = "EXEC TestProc @DbStats = `$(DBStats)"
Invoke-Sqlcmd -ServerInstance LOCALHOST\DEFAULT -Database MyDb -Query $query -Variable "DBSTATS='$DBStats'"
The error I'm getting is "Invoke-Sqlcmd : Operand type clash: varchar is incompatible with DBStatsTable"
DBStatsTable is my user defined table type. Whenever I do a get-member on $DBStats it's coming back as an array object so I'm not sure why it's being passed in as a varchar.
Edit: I could also pass in an XML data type but I'm not having any luck that way either.
Invoke-Sqlcmd -ServerInstance LOCALHOST\DEFAULT -Database MyDB -Query $query -Variable "DBStats='$TestXML'"
This doesn't cause any errors but all I'm getting from the proc is "System.Xml.XmlDocument"