Dynamics Community Forum Thread Details

We have an error in the integration scenario of Management Reporter (2.12.16013.0) with Dynamics Ax 2012 R3 - CU12 (6.3.5000.6267),
the error occurs when finishing the initial integration and then is recurring periodically, the details of the error in Management Reporter console are:

(0x80131904): Divide by zero error Management Reporter

We ran the integration several times from 0 (deleting the datamart database according to the procedure recommended in the msft doc) and in parallel
we let running a sql profiler to try to get more detail of the error, but we still did not get to the data that would be causing the error.
below the detail of the profiler.

We validated whether there are empty dimensions, and that the fiscal calendars were correct.

Error detail in management reporter console:
============================================

exec sp_executesql N'insert [Scheduling].[Message]([Id], [LogId], [Severity], [Time], [Text], [ErrorId], [Key])
values (@0, @1, @2, @3, @4, null, null)
',N'@0 uniqueidentifier,@1 uniqueidentifier,@2 tinyint,@3 datetime,@4 nvarchar(max) ',@0='4C146964-C42D-4311-A3CF-23132FEF74EA',@1='83807DBC-8419-4ABC-9327-A8A0CD725E8C',@2=2,@3='2023-03-28 17:41:03.170',@4=N':System.Data.SqlClient.SqlException (0x80131904): Divide by zero error encountered.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
19 organizations merged
2023-03-28 17:04:57.3566333
19 staged organizations deleted
2023-03-28 17:04:57.3566333
60 staged scenarios updated with organization id
2023-03-28 17:04:57.4576285
60 staged scenarios updated with id
2023-03-28 17:04:57.4746252
0 staged scenarios found requiring recalculation
2023-03-28 17:04:57.5176242
60 scenarios merged
2023-03-28 17:04:57.5476231
0 out-of-date child/parent scenario associations deleted
2023-03-28 17:04:57.5656236
0 scenario child/parent associations inserted
2023-03-28 17:04:57.5876229
Beginning transaction.
Committing transaction.
0 dimensions updated with organization id
2023-03-28 17:04:57.6136211
Beginning transaction.
Full balance calculation NOT required.
Full translation NOT required.
Starting [UpdateBalancesForPeriods]
2023-03-28 17:04:57.6396231
Starting [UpdateBalancesForAccount]
2023-03-28 17:04:57.6396231
Starting [UpdateBalancesForScenario]
2023-03-28 17:04:57.6396231
Committing transaction.
Beginning processing generation 5 of archive 2 at 2023-03-28 17:04:57.64962
Beginning transaction.
Caution: Changing any part of an object name could break scripts and stored procedures.
Caution: Changing any part of an object name could break scripts and stored procedures.
Committing transaction.
Beginning transaction.

insert into [Datamart].Fact with(TABLOCK) (
Id, DimensionsId, PeriodId, FactDate, OriginalAmount,
OriginalUnitOfMeasureId, PrimaryAmount, ScenarioId, IsPosted,
PostingLayerId, FactType, IsDebit, OrganizationId, ShouldBeTombstoned,
AcquisitionDate, SourceKey, [Description], VersionStart, FactTypeQualifierId)
output inserted.DimensionsId into #newlyReferencedCombinations (Id)
select
f.Id, dc.Id, p.PeriodId, f.FactDate, f.OriginalAmount,
f.OriginalUnitOfMeasureId, f.PrimaryAmount, s.Id, f.IsPosted,
f.PostingLayerId, f.FactType, f.IsDebit, o.Id, f.ShouldBeTombstoned,
f.AcquisitionDate, f.SourceKey, f.[Description], ''2023-03-28 17:04:57.6556205'',
ftq.Id
from [Datamart].FactProcessing f
inner hash join [Datamart].Organization o on f.OrganizationKey = o.SourceKey
inner hash join [Datamart].DimensionCombinationUnreferenced dc on f.DimensionsKey = dc.SourceKey and dc.OrganizationId = o.Id
inner hash join [Datamart].Scenario s on f.ScenarioKey = s.SourceKey and s.OrganizationId = o.Id
inner hash join [Datamart].PeriodsByDate p on CAST(f.FactDate AS date) = p.IncludedDate and p.OrganizationId = o.Id
left join [Datamart].FactTypeQualifier ftq on f.FactTypeQualifierKey = ftq.SourceKey and ftq.OrganizationId = o.Id
where (ftq.Id IS NOT NULL or f.FactTypeQualifierKey IS NULL)
and f.Id not in (select Id from #newlyInsertedFactIds)
Warning: The join order has been enforced because a local join hint is used.
6576351 staged facts resolved
2023-03-28 17:14:05.0886906
Rolling back transaction.
Completed processing generation 5 of archive 2 at 2023-03-28 17:41:03.15257
Closing archive 2 at 2023-03-28 17:41:03.15257
Beginning transaction.
Results for archive 2:
0 out of 6577188 facts processed.
0 out of 0 combinations processed.
select top 0 * into Datamart.DimensionCombinationStaging2 from Datamart.DimensionCombinationProcessing
drop table Datamart.DimensionCombinationProcessing
exec sp_rename N''Datamart.FactProcessing'',FactStaging2
Caution: Changing any part of an object name could break scripts and stored procedures.
Committing transaction.
Archive 2 closed at 2023-03-28 17:41:03.16561
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.SqlCommandExtensions.<>c__DisplayClass1.<ExecuteNonQueryWithRetry>b__0()
at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func)
at Microsoft.Dynamics.Performance.Core.SqlCommandWrapper.RunCommand[TResult](Func`1 execute)
at Microsoft.Dynamics.Performance.DDM.FactLoader.ProcessStagedData(String connectionString)
at Microsoft.Dynamics.Integration.Adapters.DimensionalDataMart.DataMartAdapter.RunMaintenance()
at Microsoft.Dynamics.Integration.Service.Tasks.MaintenanceTask.RunImplementation()
ClientConnectionId:6314de6e-63b5-4101-b005-bfc23836c686
Error Number:50000,State:1,Class:16'

Error registered in sql profiler:
=================================

<Column id="10" name="ApplicationName">.Net SqlClient Data Provider</Column>
<Column id="6" name="NTUserName">BCAccount</Column>
<Column id="14" name="StartTime">2023-03-28T14:41:03.173-03:00</Column>
<Column id="9" name="ClientProcessID">7748</Column>
<Column id="11" name="LoginName">Domain\BCAccount</Column>
<Column id="12" name="SPID">112</Column>
<Column id="13" name="Duration">422</Column>
<Column id="15" name="EndTime">2023-03-28T14:41:03.173-03:00</Column>
<Column id="16" name="Reads">9</Column>
<Column id="17" name="Writes">0</Column>
<Column id="18" name="CPU">0</Column>
<Column id="1" name="TextData">exec sp_executesql N'insert [Scheduling].[Log]([Id], [TaskId], [StartTime], [EndTime], [TotalRetryNumber], [IsFailed])
values (@0, @1, @2, @3, @4, @5)
',N'@0 uniqueidentifier,@1 uniqueidentifier,@2 datetime,@3 datetime,@4 tinyint,@5 bit',@0='83807DBC-8419-4ABC-9327-A8A0CD725E8C',@1='2CA218F2-3BC3-4F58-849B-29A39FA142F9',@2='2023-03-28 17:41:03.170',@3='2023-03-28 17:41:03.170',@4=0,@5=1</Column>
</Event>
<Event id="10" name="RPC:Completed">
<Column id="2" name="BinaryData"> <here long binary data> </Column>
<Column id="10" name="ApplicationName">.Net SqlClient Data Provider</Column>
<Column id="6" name="NTUserName">BCAccount</Column>
<Column id="14" name="StartTime">2023-03-28T14:41:03.177-03:00</Column>
<Column id="9" name="ClientProcessID">7748</Column>
<Column id="11" name="LoginName">Domain\BCAccount</Column>
<Column id="12" name="SPID">112</Column>
<Column id="13" name="Duration">1176</Column>
<Column id="15" name="EndTime">2023-03-28T14:41:03.177-03:00</Column>
<Column id="16" name="Reads">68</Column>
<Column id="17" name="Writes">6</Column>
<Column id="18" name="CPU">0</Column>
<Column id="1" name="TextData">exec sp_executesql N'insert [Scheduling].[Message]([Id], [LogId], [Severity], [Time], [Text], [ErrorId], [Key])
values (@0, @1, @2, @3, @4, null, null)
',N'@0 uniqueidentifier,@1 uniqueidentifier,@2 tinyint,@3 datetime,@4 nvarchar(max) ',@0='4C146964-C42D-4311-A3CF-23132FEF74EA',@1='83807DBC-8419-4ABC-9327-A8A0CD725E8C',@2=2,@3='2023-03-28 17:41:03.170',@4=N':System.Data.SqlClient.SqlException (0x80131904): Divide by zero error encountered.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

We also ran these queries to validate consistencies, and in all cases the result was empty (which is correct):

--budgets assigned to invalid company
select * from BUDGETTRANSACTIONLINE l with (nolock)
join BUDGETTRANSACTIONHEADER h with (nolock) on l.BUDGETTRANSACTIONHEADER = h.RECID
where h.PRIMARYLEDGER not in (select recid from ledger)

--bad category assigned to an account that has transactions
--Script start
select MA.MAINACCOUNTID, MA.NAME, L.NAME as CompanyID
from MAINACCOUNT MA with (nolock)
join DIMENSIONATTRIBUTEVALUE DAV with (nolock) on DAV.ENTITYINSTANCE = MA.RECID
join LEDGER L with (nolock) on L.CHARTOFACCOUNTS = MA.LEDGERCHARTOFACCOUNTS
where MA.ACCOUNTCATEGORYREF not in (select ACCOUNTCATEGORYREF from MAINACCOUNTCATEGORY)
and DAV.ISTOTAL = 0
and MA.ACCOUNTCATEGORYREF <> 0
and MA.MAINACCOUNTID in
(
select DISTINCT MA.MAINACCOUNTID from GENERALJOURNALACCOUNTENTRY GJAE with (nolock)
join DIMENSIONATTRIBUTEVALUECOMBINATION DAVC with (nolock) on DAVC.RECID = GJAE.LEDGERDIMENSION
join MAINACCOUNT MA with (nolock) on DAVC.MAINACCOUNT = MA.RECID
)
order by MA.MAINACCOUNTID
--Script end

--transactions with an invalid company
select * from GeneralJournalEntry with (nolock)
where ledger not in (select distinct recid from ledger)

--transaction details with no associated transaction header information
select * from GeneralJournalAccountEntry with (nolock)
where GeneralJournalentry not in (select distinct RECID from GeneralJournalEntry with (nolock))

--invalid dimension combinations
select * from GENERALJOURNALACCOUNTENTRY GJAE with (nolock)
where GJAE.LEDGERDIMENSION not in (select distinct VALUECOMBINATIONRECID from DIMENSIONATTRIBUTELEVELVALUEVIEW with (nolock))

--Custom list financial dimension values
select * from DIMENSIONFINANCIALTAG with (nolock) where VALUE is NULL

--loops through all companies to identify null system dimensions
--Script start
DECLARE @TableName nvarchar(40)
DECLARE @SQL nvarchar(max)
DECLARE tables_cursor CURSOR FAST_FORWARD
FOR
select distinct DA.VIEWNAME
from DIMENSIONATTRIBUTE DA
where DA.TYPE <> 3 and viewname <> 'DimensionFinancialTag'
order by DA.VIEWNAME
OPEN tables_cursor
FETCH NEXT FROM tables_cursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Checking ' + @TableName + ' table...'
SELECT @SQL =
'SELECT VALUE, NAME, ''' + @TableName + ''' as TableName FROM ' + @TableName + ' WHERE NAME='''''
EXEC (@SQL)
FETCH NEXT FROM tables_cursor INTO @TableName
END
CLOSE tables_cursor
DEALLOCATE tables_cursor
--Script end

--------------------------------------------------------------------------------------------------

Any ideas or other queries that might help to detect any data consistency , financial dimension problems, or another cause of div/0 error?

Thanks in advance!

Alejandro G.

Community site session details

(0x80131904): Divide by zero error Management Reporter | Dynamics AX 2012 R3

Helpful resources

News and Announcements

Quick Links

🌸 Community Spring Festival 2025 Challenge 🌸

Jonas ”Jones” Melgaard – Community Spotlight

Kudos to the March Top 10 Community Stars!

Leaderboard

Featured topics

Product updates