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

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

Realtime record count for table...

Here's a little sql 2005 script I wrote:

1. Start by running this script....

declare @.x int

select @.x = 1

while ( @.x < 75000)
begin
insert into myTesttable values (@.x)
Select @.x = @.x + 1
end

2. While the script is still running, I want to know how many records are in the table. From the same query window as the script, I have run both of the following statements.

select count(*) from mytesttable
witn (nolock)

select count(*) from mytesttable
witn (tablock)

Instead of getting the answer immediately, they run only after the original script has completed. They seem to be "blocked". How can I get a near realtime count of the number of records in this table while the script populates the table?

Thanks,

Barkingdog

Barkingdog, just open another query window and run your "select count(*) from mytesttable". You'll get the row count while the other script is running.|||

If you use GridView it only displayed after the batch execution completed.

You can use different window or change the GridView to TextView to get the result immd.

|||

Yes, opening a new window did enable to the "select count(*) ...." to run but why is this the case? (After all, I couldn't run the "Select count(*) .." from the window that invoked the original script. Seems like the orignal script "blocks" the conneciton so I need to open a new conneciton (window). But why?

TIA,

barkingdog

|||

barkingdog wrote:

Yes, opening a new window did enable to the "select count(*) ...." to run but why is this the case? (After all, I couldn't run the "Select count(*) .." from the window that invoked the original script. Seems like the orignal script "blocks" the conneciton so I need to open a new conneciton (window). But why?

TIA,

barkingdog

The query window runs the statements sequentially and does not run the next statement until the prior one has finished, thus your select count(*)... will not run until the statements in the while loop complete. It's not "blocking" the connection, it just has not completed the prior statements...

If one of the replies solved your problem, please mark them as answered...

READTEXT error

I can't figure out I continually get a msg 7124 error using READTEXT.
The script:
DECLARE @.TextfieldPtr varbinary(16)
DECLARE @.CommentsBytes int
SELECT @.TextfieldPtr = TEXTPTR(TextComments)
FROM Comments
WHERE CommentId = 25
SELECT @.CommentsBytes = DATALENGTH(TextComments)
FROM Comments
WHERE CommentId = 25
/*This returns a value of 17,830*/
SET TEXTSIZE @.CommentsBytes
READTEXT Comments.TextComments @.TextfieldPtr 0 @.CommentsBytes
The result:
**********
17830
Server: Msg 7124, Level 16, State 1, Line 19
The offset and length specified in the READTEXT statement is greater
than the actual data length of 8915.
***********
Why 8915, which is half of the actual size returned by the DATALENGTH()
function?If it is NVARCHAR, use DATALENGTH(column)/2
On 3/12/05 4:31 PM, in article
1110663061.251893.218680@.z14g2000cwz.googlegroups.com, "Rlane"
<rmathuln@.pacbell.net> wrote:

> I can't figure out I continually get a msg 7124 error using READTEXT.
> The script:
> DECLARE @.TextfieldPtr varbinary(16)
> DECLARE @.CommentsBytes int
> SELECT @.TextfieldPtr = TEXTPTR(TextComments)
> FROM Comments
> WHERE CommentId = 25
> SELECT @.CommentsBytes = DATALENGTH(TextComments)
> FROM Comments
> WHERE CommentId = 25
> /*This returns a value of 17,830*/
> SET TEXTSIZE @.CommentsBytes
> READTEXT Comments.TextComments @.TextfieldPtr 0 @.CommentsBytes
> The result:
> **********
> 17830
> Server: Msg 7124, Level 16, State 1, Line 19
> The offset and length specified in the READTEXT statement is greater
> than the actual data length of 8915.
> ***********
> Why 8915, which is half of the actual size returned by the DATALENGTH()
> function?
>