HELP: Dropped table column causes sp_addmergearticle to fail with replication

From: Pagus (pagus_at_writeme.com)
Date: 11/05/04 

Date: Fri, 05 Nov 2004 10:58:10 +0100

PROBLEM:
I'm setting up merge replication on sql server 2000 sp3.
While creating publication the following message was received:
------------------
Server: Msg 50000, Level 16, State 1, Procedure sp_MSreseed, Line 40
there is no identity column in this table
Server: Msg 20009, Level 16, State 1, Procedure sp_addmergearticle,
Line 1231
The article 'tkDPLRS' could not be added to the publication 'TEST1'.
------------------------

I have a number of tables with same problem, and it is not simple to
drop-recreate the tables with all constraints, ... etc.

We could fix the sp_MSreseed procedure with :

***
SET NOCOUNT ON
        select @prefix = 'repl_identity_range_'
-- select @colid=1
        select @colid= (select MIN(colid) FROM syscolumns WHERE id =
@objid)
***

starting with first existing COLID instead of 1, but I'm afraid to
touch system procedures, and who knows where else could be the same
catch.
Same database with replication on SQL2000 SP2 works fine :(((

Any help is greatly appreciated

PAGUS

There was an article found with same problem:
=========
From:Justin von Perger (justin21@v-biz.net)
Subject:dropped table column causes sp_addmergearticle to fail with
replication

  
View this article only
News grupe:microsoft.public.sqlserver.programming
Datum:2003-11-19 22:38:14 PST

Hi
I'm setting up merge replication on sql server sp3.
On certain tables I get this error when creating the publication:

Server: Msg 50000, Level 16, State 1, Procedure sp_MSreseed, Line 40
there is no identity column in this table
Server: Msg 20009, Level 16, State 1, Procedure sp_addmergearticle,
Line 1231
The article 'Z__Ob' could not be added to the publication 'test'.
Warning: only Subscribers running SQL Server 2000 can synchronize with
publication 'test' because automatic identity ranges are being used.

The script is:

exec sp_addmergearticle @publication = N'test', @article = N'Z__Ob',
@source_owner = N'dbo', @source_object = N'Z__Ob', @type = N'table',
@description = null, @column_tracking = N'true', @pre_creation_cmd =
N'drop',
@creation_script = null, @schema_option = 0x000000000000CFF1,
@article_resolver = null,
@subset_filterclause = null, @vertical_partition = N'false',
@destination_owner = N'dbo',
@auto_identity_range = N'true', @pub_identity_range = 1000,
@identity_range = 1000,
@threshold = 80, @verify_resolver_signature = 0,
@allow_interactive_resolver = N'false',
@fast_multicol_updateproc = N'true', @check_permissions = 0

I'm using auto identity ranges.

I've tracked it down to sp_MSreseed, where it scans for the identity
column:

(excerpt from sp_MSReseed):

SET NOCOUNT ON
select @prefix = 'repl_identity_range_'
select @colid=1
select @dbname=db_name()
select @colname=name from syscolumns where colid=@colid and id =
@objid
while COLUMNPROPERTY(@objid, @colname, 'IsIdentity')=0 and @colname
is not NULL
begin
  select @colid = @colid + 1
  select @colname=name from syscolumns where colid=@colid and id =
@objid
end
if @colname is NULL
begin
  raiserror('there is no identity column in this table', 16, -1)
  return (1)
end

It assumes that column IDs are sequential from 1, which is not the
case for this table as I've deleted a column from it, leaving gaps in
the objid sequence.

I've changed the way I create the table by re-creating it after
deleting the column and solved the problem that way, but this is an
issue with sp_MSreseed that probably needs addressing...
==============

Relevant Pages

  • Re: Replication Monitor didnt work because different collations
    ... Looking for a SQL Server replication book? ... SQL 2005 filtered merge replication. ... We changed collations to match the server collation and Replication ... After this change we started all publication snapshots. ...
    (microsoft.public.sqlserver.replication)
  • RE: Error creating new push subscription after deleting the existi
    ... To drop a push subscription to all articles for a transactional ... At the Publisher, open SQL Server Enterprise Manager, expand a server ... group, expand the Replication folder, expand the Publications folder, click ... the publication that has the subscription you want to delete, ...
    (microsoft.public.sqlserver.replication)
  • When do we need to delete/recreate a subscription?
    ... I'm using SQL Server for Merge replication and I looking for a list of ... Deleting a sub. ... when modifying a publication table's structure (like ...
    (microsoft.public.sqlserver.replication)
  • Re: Replication over a firewall
    ... for a publication named "MyDB" should be "c:\MyDB"? ... Since it works inside the firewall, i think items 1,2 and 3 are ok. ... make sure the snapshot folder name is the default name that SQL Server ... merge replication just has a lot of points of failure ...
    (microsoft.public.sqlserver.ce)
  • Re: IDENTITY = Yes (NOT FOR REPLICATION) - update error at Subscri
    ... Inserts into an identity column are allowed if it has the ... however updates of an identity column will ... always fail whether used in replication of not. ... Paul Ibison SQL Server MVP, ...
    (microsoft.public.sqlserver.replication)
Loading