We are in the process of migrating our databases to SQL Server 2005 and our Reporting Services Reports to 2005. We have been doing this in a phased approach with excellent success.
However, I have a set of Reporting Services 2000 reports that are reading from a SQL Server 7.0 database. If possible, I would like to migrate the reports before we migrate the database (we're not ready to migrate the database yet).
When I converted the reports to Reporting Services 2005, I first received an error message regarding my data source. Basically the message says anything developed in Visual Studio 2005 using the Microsoft SQL Server connection type cannot connect to a database prior to Microsoft SQL Server 2000. So I switched the connection string to be a OLE DB type.
Well ... the reports contain parameters (i.e. @.plant, @.employee, etc). So when I attempt to run the query, I get a message saying "Must declare the variable '@.plant'". I have searched for a work around until we migrate the database but I am coming up empty.
Is there a way for me to run a report with parameters from Reporting Services 2005 to a SQL Server database that is prior to SQL Server 2000?
Thanks in advance.
OLE DB Parameters are not named. Instead of @.foo for parameters in the SELECT statement, you use ? I thought the managed provider should work, though. What is the exact error?|||Thank you for replying. The exact error that is displayed is as follows:
An error occurred during the local report processing
An error has occurred during report processing
Query execution failed for data set 'Journal'
Must declare the variable '@.plant'.
So if OLE DB does not support named parameters, can I use multiple parameters in this report? The report contains seven different parameters.
|||According to documentation...
The OLE DB provider for SQL Server does not support named variables. Use the question mark (?) character to specify a variable. Parameters passed to the OLE DB provider must be passed in the order they occur in the WHERE clause. For example, PM.Name LIKE ('%' + ? + '%').
http://msdn2.microsoft.com/en-us/library/aa337223.aspx
Other providers may support.
However, you should be able to use query expression & a Reporting Services parameter.
eg. ="Select value from table where value = " + Parameters!MyParam.Value
cheers,
Andrew
|||Hi,
Did you get the solution to this error ?
I'm getting the same error when I try to pass a multi-list of values from SRS2005 to a storeprocedure.
Please let me know if your report in the dataset has a query or SP.
Thanks
Pepe
