merge replication error

After running a merge replication consistantly for a couple of months, I
needed to remove a table from replication so that I could modify the datatype
on one of the fields (This table was really unnecessary on the subscribed
db). I removed the table from replication by
sp_MSunmarkreplinfo 'TableName'
After doing this, I started getting errors:
Error Details:
The merge process could not retrieve article information for publication
'db2'.
(Source: Merge Replication Provider (Agent); Error number: -2147201017)
------
Cannot insert the value NULL into column 'tablename', table '@.tmp_table';
column does not allow nulls. INSERT fails.
(Source: db1(Data source); Error number: 515)
------
Failed to allocate new identity range.
(Source: db1 (Data source); Error number: 21197)
------
Could i get some help on this please?
Here are details of how to achieve this:
http://www.replicationanswers.com/AddColumn.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Basically you will need to hack your way through this one. You should have
used sp_dropmergearticle. I think your best bet is to drop the
subscriptions, drop the article and then reinitialize.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Don Deskins" <Don Deskins@.discussions.microsoft.com> wrote in message
news:13D5377F-8635-4F04-9191-D4626618A2E3@.microsoft.com...
> After running a merge replication consistantly for a couple of months, I
> needed to remove a table from replication so that I could modify the
> datatype
> on one of the fields (This table was really unnecessary on the subscribed
> db). I removed the table from replication by
> sp_MSunmarkreplinfo 'TableName'
> After doing this, I started getting errors:
> Error Details:
> The merge process could not retrieve article information for publication
> 'db2'.
> (Source: Merge Replication Provider (Agent); Error number: -2147201017)
> ------
> Cannot insert the value NULL into column 'tablename', table '@.tmp_table';
> column does not allow nulls. INSERT fails.
> (Source: db1(Data source); Error number: 515)
> ------
> Failed to allocate new identity range.
> (Source: db1 (Data source); Error number: 21197)
> ------
> Could i get some help on this please?
|||Hillary and Paul,
Thank you for your answers.

>I think your best bet is to drop the
>subscriptions, drop the article and then reinitialize.
If I drop the subscription, then drop the article and reinitialize, will the
data in db1 and db2 be preserved? Data is being added to each db on a daily
basis.
thanks again
|||No - after dropping the subscription, you'll need to synchronize the data in
the 2 tables and then do a nosync initialization.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||OK, thanks Paul.
To synchronize the data, do I have to run a script on each table and compare
rows or is there a store procedure that will do this for me? It is not just
2 tables. There is one table in particular in which the data has been
altered on both of the hosts (although it is entirely different fields). But
there are also tables in which the data has only been altered on one (or the
other) of the hosts.
Thanks again,
Don
"Paul Ibison" wrote:

> No - after dropping the subscription, you'll need to synchronize the data in
> the 2 tables and then do a nosync initialization.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||There is TABLEDIFF in SQL Server 2005, but the easiest way is using
DataCompare from Redgate.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Paul (and Hillary),
Thank you for your help.
Paul, I took your advice and downloaded DataCompare (will get the full
version soon). I have my dbs now replicating properly.
"Paul Ibison" wrote:

> There is TABLEDIFF in SQL Server 2005, but the easiest way is using
> DataCompare from Redgate.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
|||Hi I am creating a three level merge replication. I mean I have a main
publisher with its distributor, a subscriber/publisher and also with
it's own distributor and lastly a subscriber.
I start by creating the relation between subscriber/Publisher and the
subscriber and than i move up to the main Publisher and
Subscriber\Publisher relation. After this second merge I get the
following errors and the articles in my Subscriber\Publisher are
lost...
The merge process could not retrieve article information for
publication 'I-Test-Publisher'.
(Source: Merge Replication Provider (Agent); Error number: -2147201017)
------
Cannot insert the value NULL into column 'tablename', table
'@.tmp_table'; column does not allow nulls. INSERT fails.
(Source: DEVSERVER3\MSDE (Data source); Error number: 515)
------
Failed to allocate new identity range.
(Source: DEVSERVER3\MSDE (Data source); Error number: 21197)
------