DELETE sysmergeschemachange exec dbo.sp_MSreinit_hub , 1 to cancel Re initialization

• Thursday, November 29, 2012 9:02 PM

   

   
  

  0

  Please, we need an urgent advice if you can.

  We have a merge replication schema in SQL Server 2008 R2.

  All articles were created like:

  CREATE TABLE Encuesta 
  	( 
  	 ID_Encuesta BIGINT  NOT NULL , 
  	 Por_Producto BIT DEFAULT 0 NOT NULL , 
  	 Nombre NVARCHAR (250)  NOT NULL , 
  	 Descripcion NVARCHAR (500)  NOT NULL , 
  	 Fecha DATETIME  NOT NULL,
  	Usr_Creacion BIGINT NOT NULL DEFAULT 1,
  	Facha_Creacion DATETIME  NOT NULL DEFAULT GETDATE(), 
  	Usr_Modificacion BIGINT,
  	Fecha_Modificacion DATETIME,
  	Row_Version ROWVERSION
  	) 
  ;

  
  

  exec sp_addmergearticle @publication = @publication_name, @article = N'Encuesta', @source_owner = N'dbo', @source_object = N'Encuesta', @type = N'table', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000010C034FD1, @identityrangemanagementoption = N'manual', @destination_owner = N'dbo', @force_reinit_subscription = 1, @column_tracking = N'false', @subset_filterclause = N'', @vertical_partition = N'false', @verify_resolver_signature = 1, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @subscriber_upload_options = 1, @delete_tracking = N'true', @compensate_for_errors = N'false', @stream_blob_columns = N'true', @partition_options = 0

  Later we ran:

  Alter table Encuesta	  add Fecha_Creacion DATETIME  NOT NULL DEFAULT GETDATE()

  After all subscribers had the new column, we dorped the unneeded column
  

  Alter table Encuesta	drop column Facha_Creacion --Wrong  spelling

  By some very strange cause the ROW_VERSION was removed from replication.
  

  The script generated by SSMS for that article now is:

  exec sp_addmergearticle @publication = N'IPoint_Pub', @article = N'Encuesta', @source_owner = N'dbo', @source_object = N'Encuesta', @type = N'table', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000010C034FD1, @identityrangemanagementoption = N'none', @destination_owner = N'dbo', @force_reinit_subscription = 1, @column_tracking = N'false', @subset_filterclause = N'', @vertical_partition = N'true', @verify_resolver_signature = 1, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @subscriber_upload_options = 1, @delete_tracking = N'true', @compensate_for_errors = N'false', @stream_blob_columns = N'true', @partition_options = 0
  exec sp_mergearticlecolumn @publication = N'IPoint_Pub', @article = N'Encuesta', @column = N'ID_Encuesta', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
  exec sp_mergearticlecolumn @publication = N'IPoint_Pub', @article = N'Encuesta', @column = N'Por_Producto', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
  exec sp_mergearticlecolumn @publication = N'IPoint_Pub', @article = N'Encuesta', @column = N'Nombre', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
  exec sp_mergearticlecolumn @publication = N'IPoint_Pub', @article = N'Encuesta', @column = N'Descripcion', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
  exec sp_mergearticlecolumn @publication = N'IPoint_Pub', @article = N'Encuesta', @column = N'Fecha', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
  exec sp_mergearticlecolumn @publication = N'IPoint_Pub', @article = N'Encuesta', @column = N'Usr_Creacion', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
  exec sp_mergearticlecolumn @publication = N'IPoint_Pub', @article = N'Encuesta', @column = N'Usr_Modificacion', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
  exec sp_mergearticlecolumn @publication = N'IPoint_Pub', @article = N'Encuesta', @column = N'Fecha_Modificacion', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
  exec sp_mergearticlecolumn @publication = N'IPoint_Pub', @article = N'Encuesta', @column = N'rowguid', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
  exec sp_mergearticlecolumn @publication = N'IPoint_Pub', @article = N'Encuesta', @column = N'Fecha_Creacion', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
  GO

  
  

  As you can see, ROW_VERSION column is not present in the new script.

  1- Do you know if this is a bug? Obviously in the future we will not remove a column any more.

  2- Now, the urgent issue is that the software that runs on top the database is not working any more for the new subscriptions.

  Trying to fix it we ran:

  exec sp_mergearticlecolumn @publication = N'XXXX_Pub', @article = N'ENCUESTA',
   @column = N'ROW_VERSION', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1

  We expected that the although we marked 

  @force_reinit_subscription = 1

  it will only apply the snapshot to the "Encuesta" table, no having in mind that it will try to drop ALL TABLES!

  Dropping and recreating all subscriptions is not an option for us because we have 70 subscribers running 24/7.

  Although, the re initialization is marked, we only synced 1 subscription. The others didn't ran the sync process since we marked for reinitialization.

  Can we delete  the following row?

  select * from sysmergeschemachange where schematext= 'exec dbo.sp_MSreinit_hub [NEO], [IPoint], [IPoint_Pub], 1'

  Any help will be really appreciated.

  Thanks in advance.

  Daniel.

  ---

  Best Regards, Daniel.

  
  

  • Edited by dKaco Thursday, November 29, 2012 9:04 PM bad title
  •  
  • Reply
  • Quote