"Pagus" wrote:

>
> 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...
> ==============
>
>
>