locked
How to show syntax error in trace RRS feed

  • Question

  • I am writing an MDX generator, and whenever the syntax is incorrect all I can get from AS is the line the error was on, and a brief description.  Is there a setting in profiler that will allow it to show the MDX that was sent, along with the error message?  It's kind of a black box...

     

    Thanks,

    John

    Monday, November 26, 2007 9:26 PM

Answers

  • Bummer, I was afraid you would say that.  Do you know of any way I can make a request for the next service pack / release for this enhancement?  It's kind of a pain, and I'm sure I'm not the first person to have this frustration.

     

    Thanks,

    John

    Thursday, November 29, 2007 1:44 PM

All replies

  • Anyone?

    Wednesday, November 28, 2007 2:02 PM
  • Can you give us an example of the MDX you're generating (is it queries or calculations or both?) and the type of error you'd like to trap?

    Wednesday, November 28, 2007 2:20 PM
  • This is a *very* simple example, but something like:

     

    With Member [Measures].[Calc1] as

    [Measures].[Preack Return Count] * 10

    Select { [Measures].[Preack Return Count], [Measures].[Calc1] } on 0

    From [Compliance]

     

    Now the MDX I am creating can be a hundred lines or so, and if I have a syntax error, say something as simple as:

     

    With Member [Measures].[Calc1] as

    [Measures].[Preack Return Count] * 10

    Select { [Measures].[Preack Return Count], [Measures].[Calc1] } on 0

    From [Compliance]

    Where ()

     

    Notice the Where clause at the end is missing any kind of critieria.  I have separate functions that build the individual clauses (depending on what the user selects on a c# page).  When the generated MDX is sent to AS, running a trace will return "Query (6, 8) Parser: The syntax for ')' is incorrect."  Granted this one is extremely simple to troubleshoot, but once it gets big with lots of different stuff going into the different axes and where clause, it would be nice to see exactly what was passed to the engine.  The only way to see the MDX is to work a little magic in the application to have it display on the screen vs. pass it in (otherwise an error is generated).

     

    Any suggestions are welcomed!

    Thanks,

    John

     

    Wednesday, November 28, 2007 2:35 PM
  • So you want to find out what happens when the AS engine parses a query at a more granular level? I don't think that's going to be possible with the current APIs; you can only get the MDX that was run (using the Query Begin trace event) and any error messages that come out, as far as I know.

     

    Mosha's MDX Studio tool (see http://sqlblog.com/blogs/mosha/archive/2007/09/18/announcing-mdx-studio-ctp1-v0-1-alpha.aspx and http://www.ssas-info.com/forum/MDXStudio ) has a lot of cool functionality for parsing queries and expressions, and although it doesn't work 100% perfectly it's probably the best tool available for the job.

     

    Chris

     

    Wednesday, November 28, 2007 4:38 PM
  • Thank you for your reply - I will check out the tool.  The problem as I see it, is there are no events fired before the error is shown in the profiler tool.  I enabled every column of every event, fired off a syntactically incorrect MDX statement, and only go the error event.

     

    Do you see anything before it?  (Query Begin?)  I'm wondering if I am working with an outdated version of Profiler or something...

     

    Thanks,

    John

    Wednesday, November 28, 2007 8:29 PM
  • Sorry, I didn't look at your exact example - in some circumstances you get a Query Begin (for example if you misspell a dimension name) but when your syntax error is in the structure of the query itself you don't get a Query Begin. I don't see a way around this, apart from building some kind of logging into your application. The old Log File connection string property that worked with AS2K and which I thought might be another option seems to do nothing in AS2005.

     

    Chris

     

    Thursday, November 29, 2007 10:43 AM
  • Bummer, I was afraid you would say that.  Do you know of any way I can make a request for the next service pack / release for this enhancement?  It's kind of a pain, and I'm sure I'm not the first person to have this frustration.

     

    Thanks,

    John

    Thursday, November 29, 2007 1:44 PM
  • The best place to send feedback to the dev team is to go to

    https://connect.microsoft.com

     

    Although it's going to be too late now to request new functionality for AS2008, I think.

     

    Sorry...

     

    Chris

    Thursday, November 29, 2007 2:27 PM