En SQL 2008 y SQL 2008 R2 (y es muy probable que en SQL 2005), la combinación de replicación de mezcla (Merge), el uso de Identities (automático) y el usar cuentas que no son propietarias de las tablas produce el error siguiente:

The insert failed. It conflicted with an identity range check constraint in database ‘NombreBD’,
replicated table ‘NombreTabla’, column
‘NombreColumna’.
If the identity column is automatically managed by replication,
update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange;

El ejecutar el procedimiento recomendado repara el error, pero tarde o temprano volverá a aparecer. Después de un rato de análisis e investigación encontré que el error se produce debido a que el código de los triggers de replicación, no está muy limpio. Específicamente el código que genera el error se puede encontrar en los triggers de Inserción que agrega la replicación de mezcla que son:

if is_member(‘db_owner’) = 1

begin

— select the range values from the MSmerge_identity_range table

— this can be hardcoded if performance is a problem

declare @range_begin numeric(38,0)

declare @range_end numeric(38,0)

declare @next_range_begin numeric(38,0)

declare @next_range_end numeric(38,0)

 

select @range_begin = range_begin,

@range_end = range_end,

@next_range_begin = next_range_begin,

@next_range_end = next_range_end

from dbo.MSmerge_identity_range where artid=’9B2CC4A9-ED70-431A-9245-4B50D39F3724′ and subid=’48765403-284D-4701-9A76-F350955C1786′ and is_pub_range=0

 

if @range_begin is not null and @range_end is not NULL and @next_range_begin is not null and @next_range_end is not NULL

begin

if IDENT_CURRENT(‘[NombreEsquema].[NombreTabla]’) = @range_end

begin

DBCC CHECKIDENT (‘[NombreEsquema].[NombreTabla]’, RESEED, @next_range_begin) with no_infomsgs

end

else if IDENT_CURRENT(‘[NombreEsquema].[NombreTabla]’) >= @next_range_end

begin

exec sys.sp_MSrefresh_publisher_idrange ‘[NombreEsquema].[NombreTabla]’, ‘48765403-284D-4701-9A76-F350955C1786’, ‘9B2CC4A9-ED70-431A-9245-4B50D39F3724’, 2, 1

if @@error<>0 or @retcode<>0

goto FAILURE

end

end

end

De este código se desprende:

  1. No hay código ELSE. Lo que es lo mismo, si el usuario no es db_owner, ni se incrementa el siguiente valor del identity, ni se ajustan los rangos. Inserciones por usuarios no propietarios terminaran “rompiendo” el sistema.
  2. Si se insertan varios registros a la tabla de un golpe, es posible que el valor del siguiente identity sea superior al final del rango, lo cual producirá un error en el CHECK que pone la replicación, pero el identity queda incrementado. Si usuarios propietarios insertan varias filas en un solo comando, puede romperse el sistema.
  3. Si el valor actual del identity es mayor que el valor final del rango pero menor que el siguiente inicio de rango, tampoco se incrementan los valores ni se ajustan rangos. O sea una vez roto, no se repara.

Hay varias alternativas para hacer que la cosa funcione:

  1. Hacer a los usuarios dbo_owners y no permitir que inserten más de una fila a la vez (no recomendada)
  2. Cambiar el manejo de identities a modo manual, recomendada pero mucho trabajo.
  3. Escribir código que una vez montada la replicación repare el código de los triggers. Esto implica que en el trigger debería usarse un EXECUTE AS, y habría que agregar un TRIGGER INSTEAD OF que maneje el escenario de agregar múltiples registros que se pasen del final del rango. También puede modificarse el procedimiento original que genera los triggers, este código puede encontrarse en: sp_MSaddmergetriggers_internal. Posible, pero me preocupa el mantenimiento de esta solución cuando MS salga con Service Packs o versiones nuevas del motor.
  4. Ajustar los identities antes que se presente el problema, usando un JOB que se ejecute periódicamente.

En mi caso opte por la solución 4, ya que me pareció la más limpia. El código es el siguiente:

— Fixed Value

DECLARE @MaxGap INT=20;
— between CurrentIdentityValue and RangeEnd or NextRangeEnd

 

— Working table

DECLARE @Tables2Fix TABLE(

SchemaName SYSNAME
NOT
NULL


, TableName SYSNAME
NOT
NULL


, Subid UNIQUEIDENTIFIER
NOT
NULL


, Artid UNIQUEIDENTIFIER
NOT
NULL


, CurrentIdentityValue BIGINT
NOT
NULL


, RangeBegin BIGINT
NOT
NULL


, RangeEnd BIGINT
NOT
NULL


, NextRangeBegin BIGINT
NOT
NULL


, NextRangeEnd BIGINT
NOT
NULL


,
PRIMARY
KEY (SchemaName, TableName)

);

— Variables from Cursors

DECLARE @SchemaName SYSNAME;

DECLARE @TableName SYSNAME;

DECLARE @NextRangeBegin BIGINT;

DECLARE @SQLString NVARCHAR(2000)

DECLARE @SubId UNIQUEIDENTIFIER;

DECLARE @ArtId UNIQUEIDENTIFIER;

 

 

SET
NOCOUNT
ON;

— Populate working table

INSERT @Tables2Fix(SchemaName, TableName, SubId, Artid, CurrentIdentityValue, RangeBegin, RangeEnd


, NextRangeBegin, NextRangeEnd)

SELECT destination_owner, destination_object, subid, artid, CurrentIdentityValue, range_begin, range_end


, next_range_begin, next_range_end

FROM (SELECT sysmergearticles.destination_owner


, sysmergearticles.destination_object


, sysmergesubscriptions.subid


, sysmergearticles.artid


,
IDENT_CURRENT(sysmergearticles.destination_owner


+
‘.’
+ sysmergearticles.destination_object)
AS CurrentIdentityValue


, range_begin, range_end, next_range_begin, next_range_end


FROM dbo.MSmerge_identity_range


JOIN sysmergesubscriptions


ON MSmerge_identity_range.subid


=sysmergesubscriptions.subid


AND subscriber_server=@@SERVERNAME


AND is_pub_range=0


JOIN sysmergearticles


ON MSmerge_identity_range.artid=sysmergearticles.artid)
AS A


WHERE (NOT A.CurrentIdentityValue BETWEEN range_begin AND range_end@MaxGap )


AND
(NOT A.CurrentIdentityValue BETWEEN next_range_begin AND next_range_end@MaxGap )

 

— First Range is over or almost over, move to next_range_begin


IF
EXISTS(SELECT *
FROM @Tables2Fix


WHERE CurrentIdentityValue<NextRangeBegin)


BEGIN


DECLARE Ajuste CURSOR


FOR
SELECT SchemaName, TableName, NextRangeBegin


FROM @Tables2Fix


WHERE CurrentIdentityValue<NextRangeBegin;


OPEN Ajuste


FETCH
NEXT
FROM Ajuste


INTO @SchemaName, @TableName, @NextRangeBegin ;


WHILE
@@FETCH_STATUS=0


BEGIN


SET @SQLString=‘DBCC CHECKIDENT (”’+@SchemaName+ ‘.’+@TableName+”’, RESEED, ‘+CAST(@NextRangeBegin AS
VARCHAR(10))+‘)’


— PRINT @SQLString


EXECUTE
sp_executesql
@SQLString


FETCH
NEXT
FROM Ajuste


INTO @SchemaName, @TableName, @NextRangeBegin ;


— Consider adding a sys.sp_MSrefresh_publisher_idrange


— @qualified_object_name, @subid, @artid, @ranges_needed=1, @refresh_check_constraint=1


END


CLOSE Ajuste


DEALLOCATE Ajuste;


DELETE @Tables2Fix


WHERE CurrentIdentityValue<NextRangeBegin;


END


— if thera are object OVER the NextRangeBegin and out of range or almost out of range


IF
EXISTS(SELECT *
FROM @Tables2Fix)


BEGIN


DECLARE Ajuste2 CURSOR


FOR
SELECT SchemaName, TableName, SubId, ArtId


FROM @Tables2Fix


OPEN Ajuste2


FETCH
NEXT
FROM Ajuste2


INTO @SchemaName, @TableName, @SubId, @ArtId ;


WHILE
@@FETCH_STATUS=0


BEGIN


SET @SQLString =‘EXEC sys.sp_MSrefresh_publisher_idrange ”[‘


+ @SchemaName+
‘].[‘+ @TableName + ‘]”, ”’


+
CAST(@SubId AS
CHAR(36))
+
”’, ”’


+
CAST(@ArtId AS
CHAR(36))+
”’, 2, 1′
— 2 Ranges, Adjust Checks.


EXECUTE
sp_executesql
@SQLString


— PRINT @SQLString


FETCH
NEXT
FROM Ajuste2


INTO @SchemaName, @TableName, @SubId, @ArtId ;


END


CLOSE Ajuste2


DEALLOCATE Ajuste2;


END

 

Por supuesto este código no tiene garantías, y aunque lo probé y parece funcionar solo tiene 1 día en producción, a pesar de esto me pareció que a alguien podría servirle.

 

0 Shares:
Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

You May Also Like
Leer más

NOEXPAND y las vistas indizadas

Optimizar vistas indexes NOEXPAND. No siempre el optimizador de consultas de SQL tiene toda la información necesaria para generar el mejor plan de optimización y a veces hay que ayudarle, en este caso los desarrolladores de Navision han utilizado la siguiente opción para salvaguardarse.