Delete a subscription but constrains created for auto-identity remains

Hello,
I've a merge replication in SQL Server 2000 with one publication, where all
the articles are tables. Some of these tables have IDENTITYs and I've
choised SQL Server auto identity range.
When I delete a subscription all replication tables and information related
to the replication is deleted in the Subscriber, but the constraints created
for the identity's management (something like
'repl_identity_range_sub_9DD926BD_FEC3_443C_850B_B 02538F2D06A') still exists
(in the Subscriber).
I'm not sure if I'm doing something wrong or if I've to create a script to
drop all these constraints. Does someone know about it?
Thank you in advance,
Josep.
I have reported this problem to Microsoft. You have to write a script to
remove it.
"Josep" <jmartinez@.autec.es> wrote in message
news:eeO1YXdgHHA.1216@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I've a merge replication in SQL Server 2000 with one publication, where
> all the articles are tables. Some of these tables have IDENTITYs and I've
> choised SQL Server auto identity range.
> When I delete a subscription all replication tables and information
> related to the replication is deleted in the Subscriber, but the
> constraints created for the identity's management (something like
> 'repl_identity_range_sub_9DD926BD_FEC3_443C_850B_B 02538F2D06A') still
> exists (in the Subscriber).
> I'm not sure if I'm doing something wrong or if I've to create a script to
> drop all these constraints. Does someone know about it?
>
> Thank you in advance,
> Josep.
>
|||Thank you Hilary for your help !!
I have wrote the script. I post it here in case someone is interested in it:
/*
This script drops all CONSTRAINTs created by Merge Replication to manage
automaticaly the range of IDENTITYs,
due to SQL Server don't drop them when you delete the subscription in a
Subscriber.
*/
-- It should be executed on the replicated database
DECLARE @.table NVARCHAR(2000), @.constraint NVARCHAR(2000), @.sql
NVARCHAR(2000)
-- We get all the CONSTRAINTS used by automatic IDENTITY range
SELECT
table_name, constraint_name
INTO #IdentityConstraints
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_NAME LIKE 'repl_identity_range_sub_%'
-- We DROP these CONSTRAINTs
WHILE (SELECT COUNT(*) FROM #IdentityConstraints)>0
BEGIN
SELECT TOP 1 @.table=table_name, @.constraint=constraint_name FROM
#IdentityConstraints
SET @.sql = N'ALTER TABLE ' + @.table + ' DROP CONSTRAINT ' + @.constraint
EXEC sp_executesql @.sql
PRINT 'Dropped CONSTRAINT "' + @.constraint + '" in table "' + @.table + '"'
-- Once dropped the constraint selected, we delete it in the temporary
table
DELETE FROM #IdentityConstraints WHERE constraint_name=@.constraint
END
"Hilary Cotter" <hilary.cotter@.gmail.com> escribi en el mensaje
news:u16RYadgHHA.4952@.TK2MSFTNGP02.phx.gbl...
>I have reported this problem to Microsoft. You have to write a script to
>remove it.
> "Josep" <jmartinez@.autec.es> wrote in message
> news:eeO1YXdgHHA.1216@.TK2MSFTNGP03.phx.gbl...
>