Geeks With Blogs

News Meta
Gruff Code Code and tech musings from Jesse Taber. If I call it a blog I'll feel bad when I don't update it every week.

This blog post has moved:

http://gruffcode.com/2012/06/21/using-table-valued-parameters-with-sql-server-reporting-services/

Posted on Thursday, June 21, 2012 10:08 PM | Back to top


Comments on this post: Using Table-Valued Parameters With SQL Server Reporting Services

# re: Using Table-Valued Parameters With SQL Server Reporting Services
Requesting Gravatar...
I was investigating precisely this problem and you answered it completely, thanks! Also agree that it's a bit cumbersome. I am going to try setting up a function, a view based on the function, and then use a where clause on the view from reporting services, I think it will be cleaner. Here we go... :-)
Left by Michael Berhns-Miller on Nov 07, 2012 9:26 AM

# re: Using Table-Valued Parameters With SQL Server Reporting Services
Requesting Gravatar...
Turns out the function + view + where clause worked great - no looping through parameter array values in Reporting Services, and the view is very convenient for additional adhoc querying. What do you think Jesse?
Left by Michael Berhns-Miller on Nov 07, 2012 1:55 PM

# re: Using Table-Valued Parameters With SQL Server Reporting Services
Requesting Gravatar...
By the way, I misspelled my own name earlier. DOH.
Left by Michael Behrns-Miller on Nov 07, 2012 2:49 PM

# re: Using Table-Valued Parameters With SQL Server Reporting Services
Requesting Gravatar...
The post is very helpful. I had a requirement to use table-value parameter within SSRS. Searched in google and became mad. Atlast found your solution. I have followed your method and able to see the output in Development studio. When i uploaded this to Report server then its throwing error: "Query executin failed for dataset. Column, parameter, or variable #1: Cannot find datatype integer_TableType". Please suggest solution for this.
Left by Aditya on Dec 06, 2012 5:51 AM

# re: Using Table-Valued Parameters With SQL Server Reporting Services
Requesting Gravatar...
@Aditya: I'm glad the post was helpful.
The error you're getting is because you haven't yet setup the user defined type representing a table variable with a single integer column.

If you check out the post just before this one you'll see instructions on how to define the Integer_TableType in your database schema.
Here's a link to that other post:
http://wblo.gs/d0K
Left by Jesse on Dec 06, 2012 8:33 AM

# re: Using Table-Valued Parameters With SQL Server Reporting Services
Requesting Gravatar...
Thank you Jesse for your quick response. I already created table type in the DB before working on the report. And good news is that its working now. There were similar Datasources in Report server, i pointed to wrong one. When i changed, it started working.
Left by Aditya on Dec 06, 2012 12:59 PM

# re: Using Table-Valued Parameters With SQL Server Reporting Services
Requesting Gravatar...
Jesse... I implemented this method in a project a month back after going through your blog. Its working fine but sometimes its failing. Below is the error:
"An error has occurred during report processing.
Query execution failed for dataset 'DSOutput'.
String or binary data would be truncated. The statement has been terminated. "

I don't understand why its failing. Please suggest on this.
Left by Aditya on Mar 05, 2013 3:11 AM

# re: Using Table-Valued Parameters With SQL Server Reporting Services
Requesting Gravatar...
@Aditya: I'd guess that your issue has to do with the number of items that you're selecting and passing into the report. I'd suggest posting your question on stackoverflow.com along with all of the relevant details of the report and the parameters. If you let me where you've posted the question I'd be happy to take a look and try to answer it for you there, or someone else might offer an answer.
Left by Jesse on Mar 05, 2013 3:51 PM

# re: Using Table-Valued Parameters With SQL Server Reporting Services
Requesting Gravatar...
This was really helpful, thanks. Because our parameter list used string identifiers (which needed quotes) instead of integers, I duplicated your function with the slightly amended line:
insertStatements.AppendLine(String.Format("INSERT {0} VALUES ('{1}') ", variableName, paramValue))
Also, I had a problem with Reporting Services not having EXECUTE permission on the table type. This was sorted after looking at:
<http://connect.microsoft.com/SQLServer/feedback/details/355949/execute-permission-missing-on-user-defined-table-type>
Left by Tavis Reddick on May 07, 2013 10:23 AM

# re: Using Table-Valued Parameters With SQL Server Reporting Services
Requesting Gravatar...
Thank you..Jesse.I have sucessfully done it for one TVP parameter, what could i do if there are more than one TVP's.
Left by Prashant on Aug 10, 2013 8:13 AM

# re: Using Table-Valued Parameters With SQL Server Reporting Services
Requesting Gravatar...
I have a similiar requirement where I need to pass a Table-valued paramter to the Stored Proc through SSRS. I followed all your steps as mentioned and I get an error "Parameter X is missing a value. Whaam I doing wrong? When I make the Parameter( which is set to Internal) visible, it works fine. Because it prompts me to select the value from the drop-down box
Left by Gayatri on Apr 09, 2015 9:10 AM

Your comment:
 (will show your gravatar)


Copyright © Jesse Taber | Powered by: GeeksWithBlogs.net | Join free