?
Solved

Pass table valued parameter to Stored Proc from Powershell

Posted on 2013-01-18
4
Medium Priority
?
1,920 Views
Last Modified: 2013-01-22
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'"

Open in new window


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'"

Open in new window


This doesn't cause any errors but all I'm getting from the proc is "System.Xml.XmlDocument"
0
Comment
Question by:tim_cs
[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
 
LVL 29

Expert Comment

by:becraig
ID: 38796424
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.
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 38796755
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
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 38796773
It looks like you need to define the TVP as [System.Data.SqlDbType]::Structured.  Here are a couple of examples using Powershell with TVPs:
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/
0
 
LVL 15

Author Closing Comment

by:tim_cs
ID: 38805884
Thanks!
0

Featured Post

Industry Leaders: We Want Your Opinion!

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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A recent project that involved parsing Tableau Desktop and Server log files to extract reusable user queries for use in other systems. I chose to use PowerShell to gather the data, and SharePoint to present it...
In previous parts of this Nano Server deployment series, we learned how to create, deploy and configure Nano Server as a Hyper-V host. In this part, we will look for a clustering option. We will create a Hyper-V cluster of 3 Nano Server host nodes w…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question