We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Solved
Pass table valued parameter to Stored Proc from Powershell
Posted on 2013-01-18
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"
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
4 Comments
Active 4 days ago
It is telling you the value you are passing does not match with the datatype expected on the db side:
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.
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.
Active 3 days ago
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/
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes. We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In this post we will be converting StringData saved within a text file into a hash table. This can be further used in a PowerShell script for replacing settings that are dynamic in nature from environment to environment.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses
Course of the Month10 days, 8 hours left to enroll
618 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.