We set up 2 way replication, the local publication description says:
Transactional publication with updatable subscriptions of database 'XXXX' from Publisher 'SQLDB1'.
on our umbraco database, we got the below error so we rolled back the database to before the erros began, we disabled the replication and removed the failover partner from the sql connection string... in effect setting the system to use 1 database.
The next day we get the same error again.
Is anyone able to help? or suggest what is going on? (see the error below)
Cheers.
Murray.
The insert failed. It conflicted with an identity range check constraint in database 'XXXX', replicated table 'dbo.cmsPropertyData', column 'id'. If the identity column is automatically managed by replication, update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent. The statement has been terminated. System.Data.SqlClient.SqlException: The insert failed. It conflicted with an identity range check constraint in database 'XXXX', replicated table 'dbo.cmsPropertyData', column 'id'. If the identity column is automatically managed by replication, update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent. The statement has been terminated. System.Web.HttpUnhandledException (0x80004005): Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> umbraco.DataLayer.SqlHelperException: Umbraco Exception (DataLayer): SQL helper exception in ExecuteNonQuery ---> System.Data.SqlClient.SqlException: The insert failed. It conflicted with an identity range check constraint in database 'XXXX', replicated table 'dbo.cmsPropertyData', column 'id'. If the identity column is automatically managed by replication, update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent. The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) 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) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters) at umbraco.DataLayer.SqlHelper`1.ExecuteNonQuery(String commandText, IParameter[] parameters) --- End of inner exception stack trace --- at umbraco.DataLayer.SqlHelper`1.ExecuteNonQuery(String commandText, IParameter[] parameters) at umbraco.cms.businesslogic.property.Property.MakeNew(PropertyType pt, Content c, Guid versionId) at umbraco.cms.businesslogic.Content.createNewVersion() at umbraco.cms.businesslogic.web.Document.PublishWithResult(User u) at umbraco.cms.presentation.editContent.Publish(Object sender, EventArgs e) at System.EventHandler.Invoke(Object sender, EventArgs e) at System.Web.UI.WebControls.ImageButton.OnClick(ImageClickEventArgs e) at System.Web.UI.WebControls.ImageButton.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.HandleError(Exception e) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest() at System.Web.UI.Page.ProcessRequest(HttpContext context) at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) Logged on 13 May 2011 at 01:28:28
does this have anything to do with it? ... when setting up the replication we get the below message:
Do all umbraco inert statements contain column lists?
--- message when setting up replicatio ---
All articles in a publication allowing updatable subscriptions contain a uniqueidentifier column named 'MSrepl_tran_version' used for tracking changes to the replicated data. SQL Server adds such a column to published tables that do not have one.
Adding a new column will: » Cause INSERT statements without column lists to fail. » Increase the size of the table.
SQL Server will add a uniqueidentifier column to each of the following tables when the publication is created.
SQL error in replicated table 'dbo.cmsPropertyData'
Hi All
We set up 2 way replication, the local publication description says:
Transactional publication with updatable subscriptions of database 'XXXX' from Publisher 'SQLDB1'.
on our umbraco database, we got the below error so we rolled back the database to before the erros began, we disabled the replication and removed the failover partner from the sql connection string... in effect setting the system to use 1 database.
The next day we get the same error again.
Is anyone able to help? or suggest what is going on? (see the error below)
Cheers.
Murray.
The insert failed. It conflicted with an identity range check constraint in database 'XXXX', replicated table 'dbo.cmsPropertyData', column 'id'. If the identity column is automatically managed by replication, update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent. The statement has been terminated.
System.Data.SqlClient.SqlException: The insert failed. It conflicted with an identity range check constraint in database 'XXXX', replicated table 'dbo.cmsPropertyData', column 'id'. If the identity column is automatically managed by replication, update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent. The statement has been terminated.
System.Web.HttpUnhandledException (0x80004005): Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> umbraco.DataLayer.SqlHelperException: Umbraco Exception (DataLayer): SQL helper exception in ExecuteNonQuery ---> System.Data.SqlClient.SqlException: The insert failed. It conflicted with an identity range check constraint in database 'XXXX', replicated table 'dbo.cmsPropertyData', column 'id'. If the identity column is automatically managed by replication, update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
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)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters)
at umbraco.DataLayer.SqlHelper`1.ExecuteNonQuery(String commandText, IParameter[] parameters)
--- End of inner exception stack trace ---
at umbraco.DataLayer.SqlHelper`1.ExecuteNonQuery(String commandText, IParameter[] parameters)
at umbraco.cms.businesslogic.property.Property.MakeNew(PropertyType pt, Content c, Guid versionId)
at umbraco.cms.businesslogic.Content.createNewVersion()
at umbraco.cms.businesslogic.web.Document.PublishWithResult(User u)
at umbraco.cms.presentation.editContent.Publish(Object sender, EventArgs e)
at System.EventHandler.Invoke(Object sender, EventArgs e)
at System.Web.UI.WebControls.ImageButton.OnClick(ImageClickEventArgs e)
at System.Web.UI.WebControls.ImageButton.RaisePostBackEvent(String eventArgument)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.HandleError(Exception e)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.ProcessRequest()
at System.Web.UI.Page.ProcessRequest(HttpContext context)
at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
Logged on 13 May 2011 at 01:28:28
does this have anything to do with it? ... when setting up the replication we get the below message:
Do all umbraco inert statements contain column lists?
--- message when setting up replicatio ---
All articles in a publication allowing updatable subscriptions contain a uniqueidentifier column named 'MSrepl_tran_version' used for tracking changes to the replicated data. SQL Server adds such a column to published tables that do not have one.
Adding a new column will:
» Cause INSERT statements without column lists to fail.
» Increase the size of the table.
SQL Server will add a uniqueidentifier column to each of the following tables when the publication is created.
[dbo].[cmsContent]
[dbo].[cmsContentType]
[dbo].[cmsContentTypeAllowedContentType]
[dbo].[cmsContentVersion]
[dbo].[cmsContentXml]
[dbo].[cmsDataType]
[dbo].[cmsDataTypePreValues]
[dbo].[cmsDictionary]
[dbo].[cmsDocument]
[dbo].[cmsDocumentType]
[dbo].[cmsLanguageText]
[dbo].[cmsMacro]
[dbo].[cmsMacroProperty]
[dbo].[cmsMacroPropertyType]
[dbo].[cmsMember]
[dbo].[cmsMember2MemberGroup]
[dbo].[cmsMemberType]
[dbo].[cmsPreviewXml]
[dbo].[cmsPropertyData]
[dbo].[cmsPropertyType]
[dbo].[cmsStylesheet]
[dbo].[cmsStylesheetProperty]
[dbo].[cmsTab]
[dbo].[cmsTagRelationship]
[dbo].[cmsTags]
[dbo].[cmsTask]
[dbo].[cmsTaskType]
[dbo].[cmsTemplate]
[dbo].[infocaster301]
[dbo].[UFDataSourceMappings]
[dbo].[UFDataSources]
[dbo].[UFFields]
[dbo].[UFFieldsets]
[dbo].[UFForms]
[dbo].[UFFormStates]
[dbo].[UFPages]
[dbo].[UFPrevalues]
[dbo].[UFPrevalueSources]
[dbo].[UFPrevalueSourceSettings]
[dbo].[UFRecordFields]
[dbo].[UFRecords]
[dbo].[UFRecordsXml]
[dbo].[UFUserFormSecurity]
[dbo].[UFUserSecurity]
[dbo].[UFWorkflowExecutionStates]
[dbo].[UFWorkflows]
[dbo].[UFWorkflowsToForms]
[dbo].[umbracoApp]
[dbo].[umbracoAppTree]
[dbo].[umbracoDomains]
[dbo].[umbracoLanguage]
[dbo].[umbracoLog]
[dbo].[umbracoNode]
[dbo].[umbracoRelation]
[dbo].[umbracoRelationType]
[dbo].[umbracoUser]
[dbo].[umbracoUser2app]
[dbo].[umbracoUser2NodeNotify]
[dbo].[umbracoUser2NodePermission]
[dbo].[umbracoUserType]
Ok the problem seems to have been a partially removed replication setup.
The Question now is.... with umbraco, can we use SQL 2008 Transactional Replication with peer databases? or do we need to use SQL Mirroring?
If we can use Transactional Replication we may give it another go, as our setup may have been faulty.
Note: the objective is for fault tollerance with automatic failover, rather than performance.
Did you ever find any solution to this? I'm looking to do replication as well.
We used SQL mirroring in the end. I wasn't the one making the decisions so I can't tell you much more than that.
is working on a reply...