Receive "Must declare the variable" When Upgrading to Reporting Services 2005

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

sql