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:
- 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.
- 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.
- 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:
- Hacer a los usuarios dbo_owners y no permitir que inserten más de una fila a la vez (no recomendada)
- Cambiar el manejo de identities a modo manual, recomendada pero mucho trabajo.
- 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.
- 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.
