locked
An error has occurred during report processing RRS feed

  • Question

  • hi

    On server the report run successfully but when a user run this report this message which i see
    An error has occurred during report processing.
    Query execution failed for data set 'DSSum5HUF'.
    For more information about this error navigate to the report server on the local server machine, or enable remote errors

    the query is this:
    DECLARE @sql AS nVarchar(max)
    SET @sql = ' SELECT SUM(QuoteDet1.new_listardevizban * QuoteDet1.quantity) AS SumArbevetDev
    FROM FilteredQuote AS Quote1
    INNER JOIN FilteredQuoteDetail AS QuoteDet1 ON Quote1.quoteid = QuoteDet1.quoteid
    INNER JOIN FilteredProduct AS Product1 ON QuoteDet1.productid = Product1.productid
    INNER JOIN (' + @CRM_FilteredAccount + ') AS Account1 ON Quote1.accountid = Account1.AccountId
    INNER JOIN StringMap AS StringMap_1 ON QuoteDet1.new_rdevizja = StringMap_1.AttributeValue
    INNER JOIN (' + @CRM_FilteredOpportunity + ') AS Opportunity ON Account1.AccountId = Opportunity.AccountId AND Quote1.OpportunityId = Opportunity.OpportunityId
    WHERE Product1.producttypecode = 5 AND StringMap_1.AttributeName = N''New_rdevizja'' AND StringMap_1.Value =N''HUF''
    AND quote1.RevisionNumber = (select max( temp1.RevisionNumber) from ( SELECT Product1.name, quote1.RevisionNumber FROM FilteredQuote AS Quote1 INNER JOIN FilteredQuoteDetail AS QuoteDet1 ON Quote1.quoteid = QuoteDet1.quoteid INNER JOIN FilteredProduct AS Product1 ON QuoteDet1.productid = Product1.productid INNER JOIN (' + @CRM_FilteredAccount + ') AS Account1 ON Quote1.accountid = Account1.AccountId INNER JOIN (' + @CRM_FilteredOpportunity + ') AS Opportunity ON Account1.AccountId = Opportunity.AccountId AND Quote1.OpportunityId = Opportunity.OpportunityId WHERE Product1.producttypecode = 5 ) as temp1 where temp1.name = product1.name) '
    EXEC (@sql)

    what could be the problem is?
    thx all!

    Tuesday, June 29, 2010 2:27 PM

Answers

  • If you can restructure your query to not depend on the 'stringmap' table, you're better off- (Since you're using the filtered views, you potentially can avoid it.)

    Remove the stringmap dependency and try it that way. -

    -Or- (not recommended) Grant 'select' rights on the StringMap table to the CRM ReportingGroup in SQL.  (and be sure to include the With (nolock) hints in your query.


    Scott Sewell, CustomerEffective | http:\\blog.CustomerEffective.com | Twitter:@ScottSewell
    Tuesday, June 29, 2010 3:33 PM
    Moderator

All replies

  • If you log in to SQL as a non-admin user are you able to run the SQL in SQL Query Manager?  (You'll need to supply the values assigned to the variables in order to run it in Query Manager.)

    I'm curious if the non-admin user has rights to all the tables/views included in the query. - (e.g. specifically the "StringMap")

    My guess is that the query will run fine if you run as an SQL administrator, but not as a 'normal' CRM user.


    Scott Sewell, CustomerEffective | http:\\blog.CustomerEffective.com | Twitter:@ScottSewell
    Tuesday, June 29, 2010 3:28 PM
    Moderator
  • If you can restructure your query to not depend on the 'stringmap' table, you're better off- (Since you're using the filtered views, you potentially can avoid it.)

    Remove the stringmap dependency and try it that way. -

    -Or- (not recommended) Grant 'select' rights on the StringMap table to the CRM ReportingGroup in SQL.  (and be sure to include the With (nolock) hints in your query.


    Scott Sewell, CustomerEffective | http:\\blog.CustomerEffective.com | Twitter:@ScottSewell
    Tuesday, June 29, 2010 3:33 PM
    Moderator
  • Report query malfunction, in my experience, tends to fail for queries when the default filters have been created by the Report Wizard.  Based on your query, I can't say that's the case.  Follow Scott's initial recommendation if your query works rock solid from inside the SQL Server Management Studio or Query Analyzer.  Failing that, start looking at the logs for SSRS.  It should be recording information about the failure in greater detail there.
    Dave Berry
    Tuesday, June 29, 2010 4:49 PM
    Moderator
  • yes the proble with stringmap table
    i write it to filteredstringmap and everything is ok

    Big thx!

     

    Wednesday, June 30, 2010 4:48 PM