Share
ReportHi,
I am running GP 2010 (11.00.2272.00). We are still using FRx for reporting but we have setup Management Reporter 2012 (2.1.12000.26) which is not in production yet. We pretty much just have the Data Mart Running so that after EOY, the software can be evaluated by Accounting. I have been getting these 2 errors over the last 10 days. I have changed the the time out setting referenced in the 2 links below as a well as removed the recoreds from the Period Table in the ManagementReporterDM DB. Nothing has worked.
http://www.crestwood.com/blog/view/mr-2012-upgrade-timeout-error-fix/
http://www.summitgroupsoftware.com/blog/management-reporter-data-mart
Here is the 1st Error:
Description:
Cannot insert duplicate key row in object 'dbo.Period' with unique index 'PeriodIndex_DateRange'. The duplicate key value is (1057, 2016-01-01 00:00:00.0000000, 2016-01-31 23:59:59.0000000).
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
5 organizations merged
2017-01-17 21:32:39.0062931
5 staged organizations deleted
2017-01-17 21:32:39.0062931
0 dimensions updated with organization id
2017-01-17 21:32:39.0492974
0 accounts updated with account category id
2017-01-17 21:32:39.0502975
0 accounts updated with exchange rate id
2017-01-17 21:32:39.0873012
INSERT INTO DimensionCombinationResolving
WITH (TABLOCK) (Id, SourceKey, OrganizationId, Description, Dimension1Id, Dimension2Id, Dimension3Id, Dimension4Id, Dimension5Id)
SELECT dcs.Id, dcs.SourceKey, o.Id, dcs.Description, dv1.Id, dv2.Id, dv3.Id, dv4.Id, dv5.Id
FROM DimensionCombinationStaging dcs
JOIN Organization o on dcs.OrganizationKey = o.SourceKey
LEFT JOIN DimensionValuesWithDefinitions dv1 on
dv1.SourceKey = dcs.Dimension1Key and
dv1.OrganizationId = o.Id and
dv1.ColumnIndex = 1
LEFT JOIN DimensionValuesWithDefinitions dv2 on
dv2.SourceKey = dcs.Dimension2Key and
dv2.OrganizationId = o.Id and
dv2.ColumnIndex = 2
LEFT JOIN DimensionValuesWithDefinitions dv3 on
dv3.SourceKey = dcs.Dimension3Key and
dv3.OrganizationId = o.Id and
dv3.ColumnIndex = 3
LEFT JOIN DimensionValuesWithDefinitions dv4 on
dv4.SourceKey = dcs.Dimension4Key and
dv4.OrganizationId = o.Id and
dv4.ColumnIndex = 4
LEFT JOIN DimensionValuesWithDefinitions dv5 on
dv5.SourceKey = dcs.Dimension5Key and
dv5.OrganizationId = o.Id and
dv5.ColumnIndex = 5
where
(dv1.Id IS NOT NULL OR dcs.Dimension1Key IS NULL) AND
(dv2.Id IS NOT NULL OR dcs.Dimension2Key IS NULL) AND
(dv3.Id IS NOT NULL OR dcs.Dimension3Key IS NULL) AND
(dv4.Id IS NOT NULL OR dcs.Dimension4Key IS NULL) AND
(dv5.Id IS NOT NULL OR dcs.Dimension5Key IS NULL)
86 combinations updated
2017-01-17 21:32:39.4523377
MERGE INTO DimensionCombinationUnreferenced with (TABLOCK) AS tar
USING (select * from DimensionCombinationResolving) as src
on tar.SourceKey = src.SourceKey and tar.OrganizationId = src.OrganizationId
WHEN MATCHED THEN
UPDATE SET
tar.Description = src.Description, tar.Dimension1Id = src.Dimension1Id, tar.Dimension2Id = src.Dimension2Id, tar.Dimension3Id = src.Dimension3Id, tar.Dimension4Id = src.Dimension4Id, tar.Dimension5Id = src.Dimension5Id
WHEN NOT MATCHED THEN
INSERT (Id, SourceKey, OrganizationId, Description, Dimension1Id, Dimension2Id, Dimension3Id, Dimension4Id, Dimension5Id)
VALUES (src.Id, src.SourceKey, src.OrganizationId, src.Description, src.Dimension1Id, src.Dimension2Id, src.Dimension3Id, src.Dimension4Id, src.Dimension5Id);
86 staged combinations merged
2017-01-17 21:32:39.6433568
0 staged periods updated with organization id
2017-01-17 21:32:39.9483873
0 staged periods updated with fiscal year id
2017-01-17 21:32:39.9493874
5904 staged periods updated to hold key of existing period
2017-01-17 21:32:40.0303955
12 overlapping periods found.
2017-01-17 21:32:40.2864211
0 overlapping periods deleted from staging table
2017-01-17 21:32:40.4594384
Starting [UpdateDimensionValueReferences]
2017-01-17 21:32:46.8690793
11090 dimension values moved
2017-01-17 21:32:49.3353259
4 staged scenarios updated with organization id
2017-01-17 21:32:49.3863310
0 staged scenarios updated with parent id
2017-01-17 21:32:49.3863310
4 staged scenarios updated with id
2017-01-17 21:32:49.3873311
0 staged scenarios found requiring recalculation
2017-01-17 21:32:49.4013325
4 scenarios merged
2017-01-17 21:32:49.4213345
4 staged scenarios not requiring recalculation deleted
2017-01-17 21:32:49.4273351
4 scenarios moved
2017-01-17 21:32:49.4273351
0 staged scenarios updated with parent id
2017-01-17 21:32:49.4273351
0 staged scenarios updated with id
2017-01-17 21:32:49.4273351
0 staged scenarios found requiring recalculation
2017-01-17 21:32:49.4273351
0 scenarios merged
2017-01-17 21:32:49.4273351
0 staged scenarios not requiring recalculation deleted
2017-01-17 21:32:49.4273351
0 scenarios moved
2017-01-17 21:32:49.4273351
The statement has been terminated.
0 periods merged
2017-01-17 21:32:49.6633587
0 facts updated to reflect period change
2017-01-17 21:32:49.9043828
456 periods deleted from PeriodStaging
2017-01-17 21:32:49.9623886
insert into Fact with(TABLOCK) (
Id, DimensionsId, PeriodId, FactDate, OriginalAmount,
OriginalUnitOfMeasureId, PrimaryAmount, ScenarioId, IsPosted,
PostingLayerId, FactType, IsDebit, OrganizationId, ShouldBeTombstoned,
AcquisitionDate, SourceKey, [Description], VersionStart, FactTypeQualifierId)
output inserted.Id into #newlyInsertedFactIds
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], '2017-01-17 21:32:38.9392864',
ftq.Id
from FactStaging f
inner join Organization o on f.OrganizationKey = o.SourceKey
inner join DimensionCombination dc on f.DimensionsKey = dc.SourceKey and dc.OrganizationId = o.Id
inner join Scenario s on f.ScenarioKey = s.SourceKey and s.OrganizationId = o.Id
inner join PeriodsByDate p on CAST(f.FactDate AS date) = p.IncludedDate and p.OrganizationId = o.Id
left join FactTypeQualifier ftq on f.FactTypeQualifierKey = ftq.SourceKey and ftq.OrganizationId = o.Id
where (ftq.Id IS NOT NULL or f.FactTypeQualifierKey IS NULL)
insert into 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], '2017-01-17 21:32:38.9392864',
ftq.Id
from FactStaging f
inner join Organization o on f.OrganizationKey = o.SourceKey
inner join DimensionCombinationUnreferenced dc on f.DimensionsKey = dc.SourceKey and dc.OrganizationId = o.Id
inner join Scenario s on f.ScenarioKey = s.SourceKey and s.OrganizationId = o.Id
inner join PeriodsByDate p on CAST(f.FactDate AS date) = p.IncludedDate and p.OrganizationId = o.Id
left join 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)
--- Exception Dump ---
Caught Exception: [System.Data.SqlClient.SqlException] Cannot insert duplicate key row in object 'dbo.Period' with unique index 'PeriodIndex_DateRange'. The duplicate key value is (1057, 2016-01-01 00:00:00.0000000, 2016-01-31 23:59:59.0000000).
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
5 organizations merged
2017-01-17 21:32:39.0062931
5 staged organizations deleted
2017-01-17 21:32:39.0062931
0 dimensions updated with organization id
2017-01-17 21:32:39.0492974
0 accounts updated with account category id
2017-01-17 21:32:39.0502975
0 accounts updated with exchange rate id
2017-01-17 21:32:39.0873012
INSERT INTO DimensionCombinationResolving
WITH (TABLOCK) (Id, SourceKey, OrganizationId, Description, Dimension1Id, Dimension2Id, Dimension3Id, Dimension4Id, Dimension5Id)
SELECT dcs.Id, dcs.SourceKey, o.Id, dcs.Description, dv1.Id, dv2.Id, dv3.Id, dv4.Id, dv5.Id
FROM DimensionCombinationStaging dcs
JOIN Organization o on dcs.OrganizationKey = o.SourceKey
LEFT JOIN DimensionValuesWithDefinitions dv1 on
dv1.SourceKey = dcs.Dimension1Key and
dv1.OrganizationId = o.Id and
dv1.ColumnIndex = 1
LEFT JOIN DimensionValuesWithDefinitions dv2 on
dv2.SourceKey = dcs.Dimension2Key and
dv2.OrganizationId = o.Id and
dv2.ColumnIndex = 2
LEFT JOIN DimensionValuesWithDefinitions dv3 on
dv3.SourceKey = dcs.Dimension3Key and
dv3.OrganizationId = o.Id and
dv3.ColumnIndex = 3
LEFT JOIN DimensionValuesWithDefinitions dv4 on
dv4.SourceKey = dcs.Dimension4Key and
dv4.OrganizationId = o.Id and
dv4.ColumnIndex = 4
LEFT JOIN DimensionValuesWithDefinitions dv5 on
dv5.SourceKey = dcs.Dimension5Key and
dv5.OrganizationId = o.Id and
dv5.ColumnIndex = 5
where
(dv1.Id IS NOT NULL OR dcs.Dimension1Key IS NULL) AND
(dv2.Id IS NOT NULL OR dcs.Dimension2Key IS NULL) AND
(dv3.Id IS NOT NULL OR dcs.Dimension3Key IS NULL) AND
(dv4.Id IS NOT NULL OR dcs.Dimension4Key IS NULL) AND
(dv5.Id IS NOT NULL OR dcs.Dimension5Key IS NULL)
86 combinations updated
2017-01-17 21:32:39.4523377
MERGE INTO DimensionCombinationUnreferenced with (TABLOCK) AS tar
USING (select * from DimensionCombinationResolving) as src
on tar.SourceKey = src.SourceKey and tar.OrganizationId = src.OrganizationId
WHEN MATCHED THEN
UPDATE SET
tar.Description = src.Description, tar.Dimension1Id = src.Dimension1Id, tar.Dimension2Id = src.Dimension2Id, tar.Dimension3Id = src.Dimension3Id, tar.Dimension4Id = src.Dimension4Id, tar.Dimension5Id = src.Dimension5Id
WHEN NOT MATCHED THEN
INSERT (Id, SourceKey, OrganizationId, Description, Dimension1Id, Dimension2Id, Dimension3Id, Dimension4Id, Dimension5Id)
VALUES (src.Id, src.SourceKey, src.OrganizationId, src.Description, src.Dimension1Id, src.Dimension2Id, src.Dimension3Id, src.Dimension4Id, src.Dimension5Id);
86 staged combinations merged
2017-01-17 21:32:39.6433568
0 staged periods updated with organization id
2017-01-17 21:32:39.9483873
0 staged periods updated with fiscal year id
2017-01-17 21:32:39.9493874
5904 staged periods updated to hold key of existing period
2017-01-17 21:32:40.0303955
12 overlapping periods found.
2017-01-17 21:32:40.2864211
0 overlapping periods deleted from staging table
2017-01-17 21:32:40.4594384
Starting [UpdateDimensionValueReferences]
2017-01-17 21:32:46.8690793
11090 dimension values moved
2017-01-17 21:32:49.3353259
4 staged scenarios updated with organization id
2017-01-17 21:32:49.3863310
0 staged scenarios updated with parent id
2017-01-17 21:32:49.3863310
4 staged scenarios updated with id
2017-01-17 21:32:49.3873311
0 staged scenarios found requiring recalculation
2017-01-17 21:32:49.4013325
4 scenarios merged
2017-01-17 21:32:49.4213345
4 staged scenarios not requiring recalculation deleted
2017-01-17 21:32:49.4273351
4 scenarios moved
2017-01-17 21:32:49.4273351
0 staged scenarios updated with parent id
2017-01-17 21:32:49.4273351
0 staged scenarios updated with id
2017-01-17 21:32:49.4273351
0 staged scenarios found requiring recalculation
2017-01-17 21:32:49.4273351
0 scenarios merged
2017-01-17 21:32:49.4273351
0 staged scenarios not requiring recalculation deleted
2017-01-17 21:32:49.4273351
0 scenarios moved
2017-01-17 21:32:49.4273351
The statement has been terminated.
0 periods merged
2017-01-17 21:32:49.6633587
0 facts updated to reflect period change
2017-01-17 21:32:49.9043828
456 periods deleted from PeriodStaging
2017-01-17 21:32:49.9623886
insert into Fact with(TABLOCK) (
Id, DimensionsId, PeriodId, FactDate, OriginalAmount,
OriginalUnitOfMeasureId, PrimaryAmount, ScenarioId, IsPosted,
PostingLayerId, FactType, IsDebit, OrganizationId, ShouldBeTombstoned,
AcquisitionDate, SourceKey, [Description], VersionStart, FactTypeQualifierId)
output inserted.Id into #newlyInsertedFactIds
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], '2017-01-17 21:32:38.9392864',
ftq.Id
from FactStaging f
inner join Organization o on f.OrganizationKey = o.SourceKey
inner join DimensionCombination dc on f.DimensionsKey = dc.SourceKey and dc.OrganizationId = o.Id
inner join Scenario s on f.ScenarioKey = s.SourceKey and s.OrganizationId = o.Id
inner join PeriodsByDate p on CAST(f.FactDate AS date) = p.IncludedDate and p.OrganizationId = o.Id
left join FactTypeQualifier ftq on f.FactTypeQualifierKey = ftq.SourceKey and ftq.OrganizationId = o.Id
where (ftq.Id IS NOT NULL or f.FactTypeQualifierKey IS NULL)
insert into 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], '2017-01-17 21:32:38.9392864',
ftq.Id
from FactStaging f
inner join Organization o on f.OrganizationKey = o.SourceKey
inner join DimensionCombinationUnreferenced dc on f.DimensionsKey = dc.SourceKey and dc.OrganizationId = o.Id
inner join Scenario s on f.ScenarioKey = s.SourceKey and s.OrganizationId = o.Id
inner join PeriodsByDate p on CAST(f.FactDate AS date) = p.IncludedDate and p.OrganizationId = o.Id
left join 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)
Stack trace:
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)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.SqlCommandExtensions.<>c__DisplayClass1.b__0()
at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func)
at Microsoft.Dynamics.Performance.Core.SqlCommandWrapper.RunCommand[TResult](SqlCommand command, 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()
User: NT AUTHORITY\NETWORK SERVICE
Machine: MFG-NYC-GPRPT1 
Here is the 2nd Error:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
--- Exception Dump ---
Caught Exception: [System.Data.SqlClient.SqlException] Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Stack trace:
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()
at System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()
at System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer()
at System.Data.SqlClient.TdsParserStateObject.TryReadByte(Byte& value)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteScalar()
at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.SqlCommandExtensions.<>c__DisplayClassa.<ExecuteScalarWithRetry>b__9()
at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func)
at Microsoft.Dynamics.Performance.Core.SqlCommandWrapper.RunCommand[TResult](SqlCommand command, Func`1 execute)
at Microsoft.Dynamics.Integration.Adapters.DimensionalDataMart.DataMartAdapter.RunMaintenance()
at Microsoft.Dynamics.Integration.Service.Tasks.MaintenanceTask.RunImplementation()
Inner Exception: [System.ComponentModel.Win32Exception] The wait operation timed out
Stack trace:
User: NT AUTHORITY\NETWORK SERVICE
*This post is locked for comments
All responses (
Answers (
292,494
