.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan
Post New Web Links

The mdx-query MMSM works - BIDS error

Posted By:      Posted Date: October 03, 2010    Points: 0   Category :Sql Server
 

for me it's very strange, the following mdx-query

SELECT 
 NON EMPTY 
  exists([Date].[Month].[Month].ALLMEMBERS,[Current FY]) ON COLUMNS
 ,NON EMPTY 
  {
    [Company].[Company].[Company].ALLMEMBERS*
    [Country].[Country].[Country].ALLMEMBERS*
    nonempty({[City].[City].[City].ALLMEMBERS},{[Measures].[Budget]})*
    {
     [Measures].[Fee]
     ,[Measures].[Broker]
     ,[Measures].[Income]
     ,[Measures].[FeeS]
     ,[Measures].[Comm]
     ,[Measures].[Budget]
    }
  } ON ROWS
FROM 
(
 SELECT 
  {[Company].[Company].[All]} ON COLUMNS
 FROM 
 (
  SELECT 
   {[City].[City].&[ADR]} ON COLUMNS
  FROM 
  (
   SELECT 
    {[Company].[Company].&[ASZ]} ON COLUMNS
   FROM [Cube]
  )
 )
);

the query works fine in SSMS and returns correct result but in SSRS BIDS returns error

TITLE: Microsoft Visual Studio
------------------------------

Query preparation failed.

------------------------------
ADDITIONAL INFORMATION:

The query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension..
Parameter name: mdx (MDXQueryGenerator)

 




View Complete Post


More Related Resource Links

Parameter error writing to excell 2007 file using SQL 2008 Server Agent JOB, works in BIDS.

  

I am getting the following error message when I try to run a script that writes to an excel file calling my Package with a SQL 2008 JOB.

Executed as user: xxxx\xxxxxxx. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.1600.22 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Option "12.0;HDR=YES;" is not valid.  The command line parameters are invalid.  The step failed.

This same package runs correctly if I execute it in dev mode in BIDS.  I had to set 64bitmode to false in Dev mode to get it to work.  I set executable options in the job step to 'run in 32 bit mode'  The error message indicates to me it is trying to run in 32 bit mode which should be correct but it doesn't like the extended parameters. .   

Here is my connection string from the data sources tab on the job

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\xxxx.xlsx;Extended Properties="EXCEL 12.0;HDR=YES";

This was filled in ny the connection object for me.  I have read the article closely at http://toddmcdermid.blogspot.com/2009/10/quick-reference-ssis-in-32-and-64-bits.html and still can't figure out what I need to change. 

Error Running SSIS Package as SQL Agent Job loading a file from network drive, works fine in bids an

  
Having problems reading an excel file from a networked drive while running a package as a SQL Agent job step as a type "SQL Server Integration Services Package". I have agent running as an AD account, and I can use the execute package utility logged in as this account and it works. When I log into the server as this account, I can see the network drive, read/write/create to folders on the drive but when I try to run the agent job it cant find the file. I tried using the unc path in the config file rather than a mapped drive, still no luck. If I use a local drive (c) in the config file, the job works fine so it has to do with the network drive. Running windows server 2003 standard R2, SQL Server 2005 standard sp3. Any ideas out there?

Syntax error (missing operator) in query expression

  
Good Day Gurus!

I have a Excel user application which has a user form (named 'Registo') that displays criteria and an image that has been entered in it's corresponding spreadsheet. This works the way it should.  There's also the ability to search the spreadsheet via a form (by clicking 'Pesquisar' button) this opens a search form. However, I having a bit of a problem with it. When I try to search for something it basically doesn't do anything at all. It just sits there. So I tried to debug it and I think I'm having a problem with either the JET db engine or somethign with teh query or maybe I don't have the correct reference.

I 'borrowed' this Excel application from another forum because it's exactly what I'm looking for! However, I suck at vb.

So I was hoping somebody could take a look at the code and see if I'm missing something?  I'm kinda' desparate to get this working because I'm been trying to figure it out for days and I'm running out of time.  Cry
 
Option Explicit

'constantes para auxiliar na verificação do código
Private Const Ascendente As Byte = 0

this my query works fine, i need to eliminate when resultset zero

  
hithis is the query working fineSELECT a.Selection,a.SizeRange,a.Thickness,       ISNULL(a.Assotment,0) - ISNULL(b.Issue,0)  AS difference1      FROM ( SELECT Selection,SizeRange,Thickness,                SUM(Pcs) AS Assotment            FROM  dbo.WBCAssortDescp where LotNo='LWC2'          group by Selection,SizeRange,Thickness) AS a LEFT OUTER   JOIN (SELECT Selections,SizeRange,Thickness,                SUM(Pieces) AS Issue            FROM dbo.WBIssue Where LotNo='LWC2'           group by Selections,SizeRange,Thickness) AS b on b.Selections=a.Selection and b.SizeRange=a.SizeRange and a.Thickness=b.ThicknessSELECT a.Se,a.Si.Th,      ISNULL(a.ent,0) - ISNULL(b.Ise,0)  AS difference1   FROM ( SELECT Se,Si,Th,               SUM(Pcs) AS ent           FROM  dbo.Table1where No='C2'         group by Se,Si,Th) AS aLEFT OUTER  JOIN (SELECT Ses,Si,Th,               SUM(Pieces) AS Ise         &nbs

Query Time out error?

  
Iam getting the querey time out error, when processing dimension in SSAS? The error Details is: OLE DB or ODBC error: Query timeout expired; HYT00; Unknown token received from SQL Server; HY000.\ can any one help me on this....how to resolve this?

Javascript Error in Internet Explorer but works fine in Firefox

  
Hi All,i was working on one of the project as it is working fine in Firefox but it is giving the javascript exception in internet explorer.Errror:  Error says  "s" is undefined in the s.currencyCode="USD" line but "s" is defined in the external javascript file  s_code.jsCan anyone suggest the solution for this issue:Please find my code where the error is displayed <script type="text/javascript" language="JavaScript" >var s_account="mnsdev"</script><script type="text/javascript" language="JavaScript" src="http://media.world.com/ads/usen/rpt/Omniture/s_code.js"></script><script type="text/javascript" language="JavaScript" >s.currencyCode="USD"s.charSet="ISO-8859-1"s.SEMct = s.getQueryParam('s_kwcid');s.SEMct = s.getValOnce(s.SEMct,'SEM_ct',0);s.clickPast(s.SEMct,'event9','event10');s.SEMvar=s.getQueryParam('s_kwcid');s.SEMdate=new Date;s.SEMdate.setTime(s.SEMdate.getTime()+60*86400000)if(s.SEMvar){s.c_w('SEM_ck','1',s.SEMdate)}s.SEMchk=s.c_r('SEM_ck');if(s.SEMchk&&s.events)s.SEMok='ok';if(s.SEMvar||s.SEMok) s.t()

BIDS Query Designer very slow in VS 2008

  
I am in the process of upgrading my Report Server projects from 2005 to 2008.  These reports use an Analysis Services 2005 data source and have queries that are anywhere from 20 to 100 lines of MDX.  After upgrading a report, when I open or save a dataset in the Query Designer, it takes up to 20 seconds.  In 2005, this would happen almost instantly. Running SQL Server Profiler, I see that there is an extensive number (600+) of Discover events going on with the Analysis Services server while the Query Designer loads. My version of Visual Studio is 9.0.30729.1 SP and I am running Windows XP SP2. Thanks, Mitch

BIDS Query Designer very slow

  
BIDS Query Designer seems to be very slow while saving the query or opening the query designer when you've got quite a lot of Query Parameters. When I remove all the query parameters, the query designer seems to be running smoothly. Editing a query with about 10 to 15 query parameters takes ages this way! Is there something I can do about this?!

MDX works fine in SSMS but not in query designer

  
Hi all,   I am trying to include this query in my query designer in SSRS. The query below is as follows:WITH MEMBER [Time].[Fiscal Month].[All].[ Subtotal] AS ' AGGREGATE( EXISTING { [Time].[Fiscal Month].[All].CHILDREN }) ', SOLVE_ORDER = 1000  SELECT NON EMPTY { { { { [Time].[Fiscal Month].[All].CHILDREN }, { [Time].[Fiscal Month].[All].[ Subtotal] } } * { { [Measures].[SKU Distinct Count] } } } } ON COLUMNS , NON EMPTY { [Sales Force].[Retailer Code].[All].CHILDREN } ON ROWS  FROM [Sales]CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL The error i am getting isThe query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension..Please let me know where am i going wrong.Thanks and regards,ram

Analysis Services - Query returns a #error result

  
Hi, I have a cube - which i had backed up and just retored it. After the restoration, when i try to reply the cube e.g: select [Time].&[200503] on 0 , {[Process].[All process]} on 1 from [myConsolidations] - i recieve an a result showing #error only. What could be the cause of this?

Query crashes in MSAccess 2007 and 2010 on Windows 7 64bit, but works on 32 bit

  
Hello. I have a prepared a Database that contains 2 tables, and a Query. If you execte the query (Just a very simple join with a few where statments) Windows 7 crashes in 64 bit, but not on a 32 bit platform. Unfortunatly the Database has 300MB and is to big to be uploaded here. So if there is anyone interested at MS to get my Database, please feel free to contact me serge.dornseiffer@sdna.lu

Content Query Web Part Error

  
Hi,I get the following error when i try to add a ContentByQuery.webpart using SharePoint Server 2007:   "Unable to display this Web Part. To troubleshoot the problem, open this Web page in a Windows SharePoint Services-compatible HTML editor such as Microsoft Office SharePoint Designer. If the problem persists, contact your Web server administrator."   I've seen a lot of people having this problem when they aren't on an admin account, but I am, and this error happens before I can do any configuration at all to the webpart.   The webpart didn't show up in the webpart list at first, so I added it from the webparts gallery. The actual name of the part is Microsoft.SharePoint.Publishing.WebControls.ContentByQueryWebPart.Is this the wrong kind of webpart or what seems to be the problem? I don't have much experience in Sharepoint since before, as you might can tell.Help would be VERY appreciated, thanks in advance.

Can anybody Identify whats the error in this update query ??

  
I am getting error as Invalid Column name 'JAVA' when i am havin a value JAVA in txtExamName text box.In my database ExamName field is having a data type nvarchar(25)."UPDATE Exam SET ExamName=' " + txtExamName.Text + " ',SubjectId=" + SubId + ",StartDate=' " + txtDate.Text + " ',StartTime=' " + txtHour.Text + ":" + txtMinutes.Text + " ',EndTime=' " + txtHour0.Text + ":" + txtMinutes0.Text + " ',ExamDescription=' " + txtDescri.Text + " ',TotalNoOfQuestion=" + txtQue.Text + ",MarkofCorrectAns=" + txtPoint.Text + ",MarkofWrongAns=" + txtExamName.Text + " WHERE ExamId=" + Convert.ToInt32(Session["ExamId"])

Error in select query

  
Hi, I am tring to use the following command but getting the following error message --Query Select * from CTOR_DBPF\Amord_wrk\dbo.rate2010 Server:   CTOR_DBPF Database: Amord_wrk Table:    rate2010 --Error message Line 1: Incorrect syntax near '\'.

Error in SQL server 2000 after the migration (internal query processor error)

  
I just moved a couple of databases from one instance of SQL 2000 from different hardware to SQL 2000 instance of other hardware and after the move from the application side the app team gets an error -2147217900 - Internal Query Processor Error: The query processor could not produce a query plan.  Contact your primary support provider for more information.', 'E', '2UA929041X. Does any one have any idea about this. I took a trace while they were testing and I found out this in the trace. exec usp_DlyPrc_LogProcessEvent '-2147217900 - Internal Query Processor Error: The query processor could not produce a query plan.  Contact your primary support provider for more information.', 'E', '2UA929041X An idea or direction on this will be greatly appreciated. Thanks hash

SSRS Query Designer (in BIDS) does not generate ANSI SQL

  
Hello, I have a problem when using SQL Server 2005 BIDS (Business Intelligence Development Studio) when connected to an Oracle data source.  The Query Designer generates the old Oracle SQL syntax instead of ANSI SQL.  This is with the Oracle 10g client installed. So, I would really appreciate it if anybody knows how to get the Query Designer to generate the ANSI SQL.   Thanks, Stuart

Having error in SSRS using oracle query

  

Hi ,

I have to create report based on date filter but when i used below query ,an error has occured

ORA - 01745 : invalid host/bind variable name.

 

query is

 

select top 2* from tablename where prodate>=:date and prodate<:date +1

 

 

Shraddha


Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend