Managing Active Directory does not always have to be complicated. If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why
Pass table valued parameter to Stored Proc from Powershell
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.
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.
This doesn't cause any errors but all I'm getting from the proc is "System.Xml.XmlDocument"
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"
Asked:
1 Solution
as per many posts regarding this error
you need to check what datatype your are passing vs. what it is expected to be
http://forums.asp.net/t/1167056.aspx
you need to make sure the data types are correct
<to pass in a table valued parameter to a stored procedure from >
try to test in the sql server without PS and with later- adjust as needed
you need to check what datatype your are passing vs. what it is expected to be
http://forums.asp.net/t/1167056.aspx
you need to make sure the data types are correct
<to pass in a table valued parameter to a stored procedure from >
try to test in the sql server without PS and with later- adjust as needed
It looks like you need to define the TVP as [System.Data.SqlDbType]::S
http://technet.microsoft.com/en-us/library/bb510489.aspx (see the example at the end)
http://sev17.com/2012/04/13/table-valued-parameter-example/
http://technet.microsoft.com/en-us/library/bb510489.aspx (see the example at the end)
http://sev17.com/2012/04/13/table-valued-parameter-example/
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Featured Post
Tackle projects and never again get stuck behind a technical roadblock.
Join Now
you need to look at the ddl for your table and verify the datatype you are passing into dbstats then simply use powershell to convert the string to that datatype before passing into your invoke-sql
Not much else I can offer without knowing the design of the table, but that seems to be a likely culprit.