Query that works in Management Studio, fails in Reporting Services

I can run the following query in Management Studio, but get the error listed below when I run it from the data tab in Reporting Services:

declare @.starttime as datetime
declare @.endtime as datetime
declare @.timezone as integer
declare @.date as datetime

set @.timezone = 1
set @.date = '5/1/2007'

set @.starttime = dateadd(hh, @.timezone, @.date)
set @.endtime = dateadd(d, 1, @.starttime)

select @.Starttime, @.endtime from site

Error Message:

TITLE: Microsoft Report Designer

An error occurred while executing the query.
The variable name '@.starttime' has already been declared. Variable names must be unique within a query batch or stored procedure.

ADDITIONAL INFORMATION:

The variable name '@.starttime' has already been declared. Variable names must be unique within a query batch or stored procedure. (Microsoft SQL Server, Error: 134)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.2039&EvtSrc=MSSQLServer&EvtID=134&LinkId=20476

BUTTONS:

OK

What I am trying to accomplish is the ability for users to select which time zone they want the data in the report to display in. To do this, I created a timezone parameter that has the offset from Central Time (which is how all data is stored in our database).

Any help would be greatly appreciated!

Add the word "my" as a prefix to your variables:

DECLARE @.myStartTime smalldate time

etc...

Adamus

|||

Hi,

Please note that expressions in SSRS are case sensitive. In the code above, the starttime is declared as @.starttime and while referring to it in the select clause, you have typed it as @.Starttime. This is causing the conflict. SSRS created a report parameter, Starttime. Since SQL is not case sensitive, while execution it got two declarations for @.starttime and hence the error that you are facing.

After changing the Select clause, delete the parameter from Reports --> Parameters menu option and also from the dataset --> parameters Tab and your query should work.

Also, there is no need for the from clause.

HTH.

Regards,

Ashish

|||They are in fact case-sensitive and I've corrected it and the query now runs is RS. Thank you so much!!!