MOM Summary Reporting Pack Reports Fail

  • Article

The issue you might experience when using MOM SRP on a Windows Server with regional settings different then US (I had the problem on polish locale). The problem is in the reports and the way they handle dates.

The error is:

An error has occurred during report processing. (rsProcessingAborted) Get Online Help

Cannot read the next data row for the data set DailyMOMPerformance. (rsErrorReadingNextDataRow) Get Online Help

Arithmetic overflow error converting expression to data type smalldatetime.

The error happens for default date format. If you input the date in format 17/5/2006, instead of 2006-05-17 you will notice the error does not appear.

To fix this change the following 3 things (either in each RDL or the XML reports) and reimport the reports:

1.

    <DataSet Name="CurrentDate">

      <Fields>

        <Field Name="BeginDate">

          <DataField>BeginDate</DataField>

          <rd:TypeName>System.String</rd:TypeName>

        </Field>

        <Field Name="EndDate">

          <DataField>EndDate</DataField>

          <rd:TypeName>System.String</rd:TypeName>

        </Field>

      </Fields>

      <Query>

        <DataSourceName>SCDW</DataSourceName>

        <CommandType>StoredProcedure</CommandType>

        <CommandText>p_GetDateRange</CommandText>

        <QueryParameters>

          <QueryParameter Name="@NumberOfDays">

            <Value>7</Value>

          </QueryParameter>

        </QueryParameters>

        <Timeout>3000000</Timeout>

      </Query>

    </DataSet>

CHANGE TO:

    <DataSet Name="CurrentDate">

      <Fields>

        <Field Name="BeginDate">

          <DataField>BeginDate</DataField>

          <rd:TypeName>System.DateTime</rd:TypeName>

        </Field>

        <Field Name="EndDate">

          <DataField>EndDate</DataField>

          <rd:TypeName>System.DateTime</rd:TypeName>

        </Field>

      </Fields>

      <Query>

        <DataSourceName>SCDW</DataSourceName>

        <CommandText>SELECT GETDATE() - 7 AS BeginDate, GETDATE() AS EndDate</CommandText>

        <Timeout>30</Timeout>

      </Query>

    </DataSet>

2.

    <ReportParameter Name="BeginDate">

      <DataType>String</DataType>

CHANGE TO:

    <ReportParameter Name="BeginDate">

      <DataType>DateTime</DataType>

3.

    <ReportParameter Name="EndDate">

      <DataType>String</DataType>

CHANGE TO:

    <ReportParameter Name="EndDate">

      <DataType>DateTime</DataType>

I am negatively surprised that this was released w/o testing on locale other then US. This is an official MS download at:

https://www.microsoft.com/downloads/details.aspx?familyid=65651A27-4A20-4BF4-AF11-1691EF184BF6&displaylang=en

There is no reference on the Web as to this problem, except https://support.microsoft.com/?id=904750 which mentions a very similar problem for the SMS reports.

I see no good reason why STRING was used instead of DATETIME for these reports, when most other MOM reports are using DATETIME. This causes problems on any machine with date format set to different then US.

Another solution is to change the SQL LOGIN user/group name locale we are using to log on to reporting services from our own regional to English. This resolves the error (I noticed the error also happens with Exchange Mailboxes and Database Size reports).