SSRS error with RequestId

James, for some reason, when importing the RDL to a new report project in Visual Studio, the dataset for ReportParameterValues inadvertently gets reset to a Text query type, instead of Stored Procedure (which it should be).  Check this out in the Dataset properties window.  You may need to update the dataset manually to call the stored procedure.

I think this is somewhat to do with the RDL schema differences in 2005, 2008, 2008 R2 and how VS reads it into the project.

If you set the dataset query type to “Stored Procedure”, you may have to retype the stored procedure (or just copy/paste before you change).  If you hit “Refresh Fields” it will automagically update the parameters for you.  Click on the Parameters window and make sure the parameters are filled in correctly.  It will either be all filled in for you already, or you’ll have to manually set them again.  The ONLY parameter that is really required is Request_id.  The others can either be deleted or set to :

=System.DBNull.Value

Hope that gets you on track.

+Ryan Creps

+Tessitura Network

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of James Fairfield
Sent: Monday, October 17, 2011 4:47 PM
To: Ryan Creps
Subject: [Tessitura Technical Forum] SSRS error with RequestId

Hi:

I have a new report that I am using the Tessitura report template on windows 2008 with SQL 2008 R2.  If I use the RequestId I receive this error when I open the report in in Tessitura:

An error has occurred during report processing. (rsProcessingAborted) Query execution failed for dataset 'ReportParameterValues'. (rsErrorExecutingCommand) Incorrect syntax near the keyword 'From'.

If I remove the RequestId, the report runs OK.  Any ideas as to what is going on?  Also, why do I need the request Id?

James

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

Yep, I mentioned that might happen in the reply.  You can simply delete the additional parameters. 

Or alternatively, you can set them to this expression: =System.DBNull.Value

+Ryan Creps

+Tessitura Network

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of James Fairfield
Sent: Tuesday, October 18, 2011 11:12 AM
To: Ryan Creps
Subject: RE: [Tessitura Technical Forum] SSRS error with RequestId

Hi Ryan,

I did what you said and now the report has some new issues.  In the parameters section a few new parameters in addition to the normal ones I usually see i.e.  UserGroup, UserId, Company, RequestId.  But these have just showed up: request_id, ug_id, user_id, report_id, request_type.  The report will not run now. ugh!

James