Query fails.

I have a two development environments running both RS and Database.
Everything works fine. I then set up a test environment with RS on a seperate
server and using a the database server for both reportserver database as well
as datasource for the reports. (medium deployment scenario). I use OLAP cubes
as data source for may reports. When requesting the database from the Test
webserver(with RS) I get OK reports a couple of times. BUT...when I access
the report a third, fourth or fifth time ... I get timeouts and errors in the
ReportServer log stating that the MDX failed. Same MDX works fine in two
different dev environments as well as when using the Test database from one
of the dev servers as datasource... I get confused by this behaviour... The
Test RS server is a w2k server with all necessary SPs... Could this have
something to do with buffersizes used by aspnet_wp ?
The only difference on dev and test is that on dev I have both MDAC 2.7 SP1
and SP1 refresh while on test server I only have MDAC 2.7 SP1 Refresh.When the query fails the aspnet_wp locks and no new requests are processed
until this worker process is restarted...
"GunnarL" wrote:
> I have a two development environments running both RS and Database.
> Everything works fine. I then set up a test environment with RS on a seperate
> server and using a the database server for both reportserver database as well
> as datasource for the reports. (medium deployment scenario). I use OLAP cubes
> as data source for may reports. When requesting the database from the Test
> webserver(with RS) I get OK reports a couple of times. BUT...when I access
> the report a third, fourth or fifth time ... I get timeouts and errors in the
> ReportServer log stating that the MDX failed. Same MDX works fine in two
> different dev environments as well as when using the Test database from one
> of the dev servers as datasource... I get confused by this behaviour... The
> Test RS server is a w2k server with all necessary SPs... Could this have
> something to do with buffersizes used by aspnet_wp ?
> The only difference on dev and test is that on dev I have both MDAC 2.7 SP1
> and SP1 refresh while on test server I only have MDAC 2.7 SP1 Refresh.

Query Error

i cant run the following query on the reporting(2005) surface . but it is perfectly running on the 2005 sql environment. can some one correct it?

select {[Date—VEPLock].[VEP Lock].members as status} on rows,

{[Measures].[Amount Usd]} on columns
from Profitability

where [Date—Fiscal].[Year].[2005].[Quarter 4].lastchild:[Date—Fiscal].[Year].[2006].[Quarter 4].lastchild

Error: measure hierarchi already appear in the axis 0.

Hello. You are using the MDX report designer in SSRS2005? If you enter your own MDX in the generic query designer you cannot use the graphical MDX report designer.

Another issue is that you cannot control where the dimensions will be placed on each axis with the graphical designer.

Third. If you use the generic query designer in SSRS2005 I still think you will have to make the MDX an expression

Code Snippet

="select {[Date—VEPLock].[VEP Lock].members as status} on rows,

{[Measures].[Amount Usd]} on columns
from Profitability

where [Date—Fiscal].[Year].[2005].[Quarter 4].lastchild:[Date—Fiscal].[Year].[2006].[Quarter 4].lastchild"

HTH

Thomas Ivarsson

|||I am using the MDX report designer in SSRS2005.

Query Designer Toolbar

I have added the Query Designer toolbar to my Management Studio environment. After open a new query window this toolbar stays greyed out. How can I use this toolbar with my queries?

Thanks,

Hello CRdesigner,

The only thing that should be greyed out from the toolbar should be "Change Type" button. Other buttons should still be available for you to use. The "Change Type" button has been greyed out intentionally. We are hoping to bring it back in future versions. You can use "Change Type" if you bring up a Query Designer from Visual Studio.

Thanks!

|||

Hi Young Joo-MSFT,

Thanks for responding.

When I click on the New Query button it opens a new window. All the buttons on the Query Designer toolbar are greyed out. Why is that?

|||

If you click on the "New Query" button, it brings up a SQL Editor, not Query Designer. They are two different tools. You can bring up a Query Designer by selecting a query from SQL Editor and selecting "Design Query in Editor" option from right-click menu.

|||

Was a solution ever found? I am also not able to use the Query Designer toolbar. The buttons are all greyed out...

Query Designer Toolbar

I have added the Query Designer toolbar to my Management Studio environment. After open a new query window this toolbar stays greyed out. How can I use this toolbar with my queries?

Thanks,

Hello CRdesigner,

The only thing that should be greyed out from the toolbar should be "Change Type" button. Other buttons should still be available for you to use. The "Change Type" button has been greyed out intentionally. We are hoping to bring it back in future versions. You can use "Change Type" if you bring up a Query Designer from Visual Studio.

Thanks!

|||

Hi Young Joo-MSFT,

Thanks for responding.

When I click on the New Query button it opens a new window. All the buttons on the Query Designer toolbar are greyed out. Why is that?

|||

If you click on the "New Query" button, it brings up a SQL Editor, not Query Designer. They are two different tools. You can bring up a Query Designer by selecting a query from SQL Editor and selecting "Design Query in Editor" option from right-click menu.

|||

Was a solution ever found? I am also not able to use the Query Designer toolbar. The buttons are all greyed out...

query debugger?

I'm setting up a learning environment at home so that I can learn to program
stored procedures. I need a debugger so that I can set breakpoints, examine
variable values, etc.
Is there any way to get the SQL Server 2000 query debugger to work with
MSDE?
If not, would it be worthwhile to get the SQL Server 2000 developer edition
for $50?
T.I.A. for you suggestions.
hi Scott,
Scottww wrote:
> I'm setting up a learning environment at home so that I can learn to
> program stored procedures. I need a debugger so that I can set
> breakpoints, examine variable values, etc.
> Is there any way to get the SQL Server 2000 query debugger to work
> with MSDE?
> If not, would it be worthwhile to get the SQL Server 2000 developer
> edition for $50?
> T.I.A. for you suggestions.
long time ago a free debugger was available by Quest Software, SQL
Navigator, free but time bombed... now I'm no more able to find it on theyr
site... my best advice is SQL Server Dev edition ($50) which provides you
all the tools you ever need for SQL Server development, including Profiler
and Index Tuning...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply