locked
SQL Server 2008 Reporting Services - Query Parameter Issue RRS feed

  • Question

  • We recently upgraded SQL 2005 to SQL 2008 R2. In development environment we have standalone servers for DB, SSRS and SSAS. All the three have been upgraded to SQL 2008 R2.  While testing the reports from Report Manager we found an issue with some of the reports.

    The issues generated was "An error has occurred during report processing. (rsProcessingAborted)
    Query execution failed for dataset 'Main_Dataset'. (rsErrorExecutingCommand)
    For more information about this error navigate to the report server on the local server machine, or enable remote errors".

    To debug the report issue we downloaded the rdl and opened it with the BIDS from SSAS. The report uses some MDX query to generate data. The MDX query contains some Query Parameters named as Application, Server and Date. These parameters are used with STRTOSET command as STRTOSET(@Application), STRTOSET(@Server) and STRTOSET(@Date).When the query was executed from Query Builder it gave error as mentioned below

    "Query Preparation Failed

    Additional Information

    Query(4,422) Parser: The syntax for 'Application' is incorrect (msmgdsrv)." This error was coming for 'Server' as well as 'Date' also.

    The problem here is that these MDX query works fine in 2005 environment. At first it looks that these must be part of Reserved Words used by SSAS but going through the site mentioned below it was found that in both the versions these three were not used as Reserved Words.

    http://msdn.microsoft.com/en-us/library/ms145629%28v=SQL.90%29.aspx

    We tried some workarounds and found that using @[Application] instead of @Application. the MDX worked fine. What could be the probable reason for this issue/change.

     

     

    Thursday, October 21, 2010 1:37 PM