issues deploying snapshot for merge replication.

I'm having issues setting up merge replication and the errors I'm getting are inconsistent but lead me to believe there is a bigger underlying issue than what they actually indicate.

Issue: unable to initialise subscriber with snapshot for merge replication. Fails for various reasons regardless of environment and setup. Previously this has deployed fine but now it is not.

Publisher is SQL 2005 (9.0.2047), Subscribers are SQLExpress or SQL Dev

The various errors I get are as follows:

These come mixed together generally... Unable to INSERT ... cannot insert NULL into column rowguid. [This error comes up on different tables for different snapshots, including ones with no records] bcp (Bulk Copy) error 20253 batch send failed Unspecified error Failed to send batch after max errors end of file reached, terminator missing or field data incomplete

Actions I have taken so far, all to no effect:

Adjusted Agent Profile to increase timeout values and decrease packet sizes Took new snapshots (tables where errors occured changed but still ame errors) Created a local subscription database on the server to remove any network related issues (using merge sync over VPN) Deleted and recreated Publication Created second publication with different articles on same database to find same errors in different locations again

I've tried reinitializing subscriptions (before subscribers were dropped and I tried recreating) but the DROP command fails due to FK constraints so if I reinitialize any subscriptions they will fail to be applied. Have tried editing the properties to just delete data and not drop tables but that still failed.

I've tried setting up subscriber without snapshot - created publication and snapshot, backed up db, copied to subscriber and restored there. See this article: https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=689428&SiteID=1 This bloke suffered same results and the response from MS was to change the help files, not functionality. Doesn't help us dealing with subscribers via satellite AND unable to deploy snapshots...

I've checked the service pack releases to see if any issues like this are addressed and found nothing relevent.

My gut feeling is that the snapshot is not being generated correctly for some reason - system stored procedure errors perhaps.

So far my experience with replication has been good, with the odd issue or frustrating limitation in older versions, but this is really killing me...

Barry

(Sorry that author is "Anonymous" - must have messed that up)

Hey Barry it sounds like you have some problems there.

I would check a few things, maybe you have already done it but perhaps not.

Check that the snapshot is not getting corrupted by something when it is getting transferred accross the network / internet. You could try copying it via FTP and applying manually to the subscriber and see if that helps. Could also look into compressed snapshots for that too. Do a CRC check on the snapshot file and make sure it matches on both ends. You can get a util for that here: http://www.kmrconsulting.com/free_download.html

For that table with the null problems check that the default value for rowguid is set to newid() or newsequential() even though there is no data in there i suspect replication is doing some testing when it sets up the subscription to make sure things are running.

It sounds like your failure is with the bcp so perhaps after the tables are extracted try adding a few triggers on the troublesome tables that will record what replication is trying to insert or update. That will at least give you more info on what it is trying to do on the table.

Also take a look at the procedures that get run when the subscriber is applying the snapshot. Try running them one by one manually and issolate the problem more.

Good Luck!

Martin

issues deploying snapshot for merge replication.

I'm having issues setting up merge replication and the errors I'm getting are inconsistent but lead me to believe there is a bigger underlying issue than what they actually indicate.

Issue: unable to initialise subscriber with snapshot for merge replication. Fails for various reasons regardless of environment and setup. Previously this has deployed fine but now it is not.

Publisher is SQL 2005 (9.0.2047), Subscribers are SQLExpress or SQL Dev

The various errors I get are as follows:

These come mixed together generally... Unable to INSERT ... cannot insert NULL into column rowguid. [This error comes up on different tables for different snapshots, including ones with no records] bcp (Bulk Copy) error 20253 batch send failed Unspecified error Failed to send batch after max errors end of file reached, terminator missing or field data incomplete

Actions I have taken so far, all to no effect:

Adjusted Agent Profile to increase timeout values and decrease packet sizes Took new snapshots (tables where errors occured changed but still ame errors) Created a local subscription database on the server to remove any network related issues (using merge sync over VPN) Deleted and recreated Publication Created second publication with different articles on same database to find same errors in different locations again

I've tried reinitializing subscriptions (before subscribers were dropped and I tried recreating) but the DROP command fails due to FK constraints so if I reinitialize any subscriptions they will fail to be applied. Have tried editing the properties to just delete data and not drop tables but that still failed.

I've tried setting up subscriber without snapshot - created publication and snapshot, backed up db, copied to subscriber and restored there. See this article: https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=689428&SiteID=1 This bloke suffered same results and the response from MS was to change the help files, not functionality. Doesn't help us dealing with subscribers via satellite AND unable to deploy snapshots...

I've checked the service pack releases to see if any issues like this are addressed and found nothing relevent.

My gut feeling is that the snapshot is not being generated correctly for some reason - system stored procedure errors perhaps.

So far my experience with replication has been good, with the odd issue or frustrating limitation in older versions, but this is really killing me...

Barry

(Sorry that author is "Anonymous" - must have messed that up)

Hey Barry it sounds like you have some problems there.

I would check a few things, maybe you have already done it but perhaps not.

Check that the snapshot is not getting corrupted by something when it is getting transferred accross the network / internet. You could try copying it via FTP and applying manually to the subscriber and see if that helps. Could also look into compressed snapshots for that too. Do a CRC check on the snapshot file and make sure it matches on both ends. You can get a util for that here: http://www.kmrconsulting.com/free_download.html

For that table with the null problems check that the default value for rowguid is set to newid() or newsequential() even though there is no data in there i suspect replication is doing some testing when it sets up the subscription to make sure things are running.

It sounds like your failure is with the bcp so perhaps after the tables are extracted try adding a few triggers on the troublesome tables that will record what replication is trying to insert or update. That will at least give you more info on what it is trying to do on the table.

Also take a look at the procedures that get run when the subscriber is applying the snapshot. Try running them one by one manually and issolate the problem more.

Good Luck!

Martin

Issue with Identity Columns after replication Changes

Dear all,

We have an application that use merge replication between MSDE and Devices with SQL CE.

Due to a major application changes, we have to change our replication on all our workstation. Our process is the following:

- drop current replication

- recreate our replication

After first check our replication seems to work but after some test we have identify that all identity ranges have been reset on the workstation. As side effect, device start to reuse existing range and also existing value in the range.

- Have you ever encounter this type of issue?

- What can create it?

- Is our approach fully inappropriate?

Please let me know if you need more information,

Best regards,

I have a client (SQL2000<->SqlCE) that is experiencing similar problems. We have been unable to determine a cause as yet.

Symptoms:

-"duplicate key on insert" errors during replication for one table (two subscribers appearing to be sharing the same number range)

-"duplicate key on insert" error with a back-end process inserting data into a publisher table (diff to the one above)

The tables are using automatic identity range management and have been synchronising happily for a long time prior to this.

Does anyone know of any actions that might cause the automatic identity range management to go screwy like this?

|||You can try re-adjusting your identity ranges, look at sp_adjustpublisheridentityrange. Otherwise i'm not sure what could have caused the issue, maybe someone manually reset the identity seed on the dbs?

Issue with Identity Columns after replication Changes

Dear all,

We have an application that use merge replication between MSDE and Devices with SQL CE.

Due to a major application changes, we have to change our replication on all our workstation. Our process is the following:

- drop current replication

- recreate our replication

After first check our replication seems to work but after some test we have identify that all identity ranges have been reset on the workstation. As side effect, device start to reuse existing range and also existing value in the range.

- Have you ever encounter this type of issue?

- What can create it?

- Is our approach fully inappropriate?

Please let me know if you need more information,

Best regards,

I have a client (SQL2000<->SqlCE) that is experiencing similar problems. We have been unable to determine a cause as yet.

Symptoms:

-"duplicate key on insert" errors during replication for one table (two subscribers appearing to be sharing the same number range)

-"duplicate key on insert" error with a back-end process inserting data into a publisher table (diff to the one above)

The tables are using automatic identity range management and have been synchronising happily for a long time prior to this.

Does anyone know of any actions that might cause the automatic identity range management to go screwy like this?

|||You can try re-adjusting your identity ranges, look at sp_adjustpublisheridentityrange. Otherwise i'm not sure what could have caused the issue, maybe someone manually reset the identity seed on the dbs?

Issue when my Merge Publisher DB is Subscriber for Trans Replicati

Hi,
I have Database which is configured as a Merge Publisher , This database
has 3 Tables Employee,Dept and Country. All these 3 tables are repliated to
another database for high availability purpose using Merger Replication.
The Merge Publisher is also an Subscriber for another Transactional
Publication, where in only Country Table is being pulled.
My issue is When i add a new record in for a new country in the
Transactional Pulisher the data gets populated to my Merge Publisher but it
dosent get pushed to the Merge Subscriber automatically, i have to explisitly
run sp_addtabletocontents for the country table to get the new record pushed
to Merge Subscriber.
This has become an additional overhead , due to this am never sure that both
Merge Publisher and Subscriber have exact data.
Can some one provide the solution for this.
Regards
Pradeep
Please verify if the @.published_in_tran_pub bit is set for the articles
you can take a look at the sysmergearticles table for it
If you can enable it by specifying @.published_in_tran_pub='true' using
sp-addmergeartice or sp_changemergearticle
“This posting is provided "AS IS" with no warranties, and confers no rights.”
"PRADEEP M.M." wrote:

> Hi,
> I have Database which is configured as a Merge Publisher , This database
> has 3 Tables Employee,Dept and Country. All these 3 tables are repliated to
> another database for high availability purpose using Merger Replication.
> The Merge Publisher is also an Subscriber for another Transactional
> Publication, where in only Country Table is being pulled.
> My issue is When i add a new record in for a new country in the
> Transactional Pulisher the data gets populated to my Merge Publisher but it
> dosent get pushed to the Merge Subscriber automatically, i have to explisitly
> run sp_addtabletocontents for the country table to get the new record pushed
> to Merge Subscriber.
> This has become an additional overhead , due to this am never sure that both
> Merge Publisher and Subscriber have exact data.
> Can some one provide the solution for this.
> Regards
> Pradeep

Issue in Merge Replication

Hi Kevin,
I am setting up a SQL Server Database Merge Replication between Publisher
(P) & Subscriber (S) using EXCHANGE_TYPE '1' (Data only needs to be merged
from Subscriber to Publisher - Upload). Publisher priority is maximaum by
default.
However, I am facing this issue:
Under normal scenario, if a row of Data is changed at Subscriber, it is
updated at the Publisher since there is no change at Publisher.
But when same Row of Data in Publication (Table x) is updated at the
Publisher and also at the Subscriber (conflict situation), the row do not
particicipate in Replication when the agent runs and then it never
participates in Replication. Means the Data at Subscriber and Publisher
reamins different for that row. SQL Server do show any conflict that I can
resolve. Kindly help me to find out root cause and how to fix this!
Database : SQL Server 2000 SP3a
OS: Win2003 (No Service Pack)
Hi All,
I found the solution myself . Basically by changing the conflict resolver to
"Subscriber will always win the conflict" resolves the problem.
Cheers!
Vikas
"Vikas Kohli" wrote:

> Hi Kevin,
> I am setting up a SQL Server Database Merge Replication between Publisher
> (P) & Subscriber (S) using EXCHANGE_TYPE '1' (Data only needs to be merged
> from Subscriber to Publisher - Upload). Publisher priority is maximaum by
> default.
> However, I am facing this issue:
> Under normal scenario, if a row of Data is changed at Subscriber, it is
> updated at the Publisher since there is no change at Publisher.
> But when same Row of Data in Publication (Table x) is updated at the
> Publisher and also at the Subscriber (conflict situation), the row do not
> particicipate in Replication when the agent runs and then it never
> participates in Replication. Means the Data at Subscriber and Publisher
> reamins different for that row. SQL Server do show any conflict that I can
> resolve. Kindly help me to find out root cause and how to fix this!
> Database : SQL Server 2000 SP3a
> OS: Win2003 (No Service Pack)

IsSorted Property problem

Dear friends,

I have a ETL to import some data. When I try to open de merge join transformation, the BIDS give me an error. The error is the IsSorted must me set to true in both datasources, but I dont want to insert the sort transformation, because I know that both datasources are sorted... How can I update the IsSorted property for the both datasources? I didn't find the place to do it... it's not possible?

Thanks!

in the SOurce Components; you can right click, advanced properties; Input and output properties and set the IsSorted property of the 'OLE DB Source Output' equal to true. Additionally; you have to expand the 'output columns' node and set SortKeyPosition property for the proper columns. 0 means it is not sorted; 1 for the first column in the sort; 2 for the second one; 3 for the 3rd one....

Notice that by doing this you are just changing the metadata; the data will no be sorted. If data is not properly sorted; merge join will yield unexpected results. Also, make sure there are not tranformations in the dataflow, prior to the merge join; that alter the original sort of the data.

|||

Dear Rafael Salas

Thanks for your answer. I have some problem about "IsSorted Property". And I solve it by your guide. It is useful.

Again, Thanks for your support!

Tam Nguyen!