Solved

Pass table valued parameter to Stored Proc from Powershell

Posted on 2013-01-18
4
1,659 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
4 Comments
 
LVL 28

Expert Comment

by:becraig
Comment Utility
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 42

Expert Comment

by:EugeneZ
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
Thanks!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Synchronize a new Active Directory domain with an existing Office 365 tenant
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now