kmcclung


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!




Re: Query that works in Management Studio, fails in Reporting Services

Adamus Turner


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

DECLARE @myStartTime smalldate time

etc...

Adamus







Re: Query that works in Management Studio, fails in Reporting Services

Ashish P. Ruparel

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






Re: Query that works in Management Studio, fails in Reporting Services

kmcclung

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