Subscriber to Publisher to Merge Replication

Question

Subscriber to Publisher to Merge Replication

Jeff-131469

Old Hand

Points: 325
More actions
May 18, 2004 at 10:12 am

#87160

Hi -
I'm trying to set up merge replication between a Publisher SQL2K database on the desktop and a Subscribing SQLCE database on a Windows Mobile device. A problem arises because the Subscribing database already (before replication is implemented) has data that I don't want to lose. If I just set up merge replication between the two, I lose the Subscribing database data.
What's the best/right way to get the Subscribing database data into the Publisher SQL2K database and end up with merge replication between the two?
Thanks for your help.
- Jeff

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Melissa Naugler Valued Member Points: 67 More actions · Answer 1

Jeff,

You should not be getting this behavior from SQL Server merge replication. I have a sneaking feeling that the snapshot on your merge publication is set to delete all data in the subscriber table before initializing the snapshot.

Here is what you should try:

Create the merge replication publication on the SQL2K publisher database with the articles (tables) you want to replicate. Once you have setup the publication, check the following parameters before setting up the subscription:

Right click on the publication and choose Properties, Select the Articles tab. Click on the three elispses next to any article, Select the tab labled Snapshot. In the first section under name conflicts verify that the selction "Keep existing table unchanged" is selected. Do this for all of the articles in the publication.

This tells the merge publication that it is not to delete any data in the subscriber table upon initialization. I have many merge publications setup that do just this because we have a need to keep the data in the subscriber table that is entered by the application.

Also, for a little performance hint. Use a pull subscription from the subscriber to retrieve the data, you will get much better performance with merge replication.

Melissa

Jeff-131469 Old Hand Points: 325 More actions · Answer 2

Thanks, Melissa, but no success -

I tried your suggested approach, even tried deleting/recreating both databases and the publication. I made sure that 'Keep existing table unchanged' was selected for the article. There's only one table in my test database, and no foreign keys. I'm getting the following sequence of errors:

Error Code: 80004005

Message : Run

Minor Err.: 28557

Source : Microsoft SQL Server 2000 Windows CE Edition

Err. Par. : data source=\My Documents\ReplHH01.sdf;provider=Microsoft.SQLServer.OLEDB.CE.2.0;

Error Code: 80040E14

Message : The OLE DB Execute method failed. The SQL statement is not

valid. [,,,SQL statement,,]

Minor Err.: 28560

Source : Microsoft SQL Server 2000 Windows CE Edition

Error Code: 80040E14

Message : There was an error parsing the query. [Token line number, Token

line offset,,Token in error,,]

Minor Err.: 25501

Source : Microsoft SQL Server 2000 Windows CE Edition

Num. Par. : 1

Num. Par. : 21

Err. Par. : People

Any additional suggestions??

- Jeff

Melissa Naugler Valued Member Points: 67 More actions · Answer 3

Jeff,

The error codes in your message are pointing to a bug in replication to SQLCE. There is a workaround, I found it in Knowledge Base article 300597. Try this workaround and see if you can replicate.

Jeff-131469 Old Hand Points: 325 More actions · Answer 4

Thanks, Melissa, but I've done that.

Although the KB article seems to refer to the same errors, the cause that it describes is not what I'm experiencing.

The only constraint in my table is the primary key, and it's not NOT FOR REPLICATION. And there's only one table, so there are no Relationships or foreign keys involved.

- Jeff

Mike Rochleau-Rice SSC-Addicted Points: 422 More actions · Answer 5

Jeff,

Did you find a resolution to this problem? I'm experiencing the same sequence of errors.

- Mike

Jeff-131469 Old Hand Points: 325 More actions · Answer 6

Hi, Mike -

The short answer to your question is 'Well, sort of ....'

By design (Thanks, MS) it doesn't appear to be possible to do what I want to do directly.

The workaround that I've settled on is (get ready, it's ugly) to create an xml manifestation of the entire SQLCE database, then copy it from the PDA to the desktop, then fire up my desktop app to create a new database, read the xml file into it, and then publish it. Then, go back to my PDA app to subscribe. There are problems with this approach, but I haven't been able to find a better one (yet).

I haven't yet implemented all of that, but I'm working in that direction. Ultimately, I'll try to hide as much of the complexity as possible from the user.

So far, I've found that the xml pieces are relatively easy and execute surprisingly (to me) fast. I'm currently working on automating the desktop publishing process.

[IMHO, all of this is far more complex than it should be.]

Best of luck on your project.

- Jeff

Mike Rochleau-Rice SSC-Addicted Points: 422 More actions · Answer 7

Hi Jeff

Thanks for the reply. I was able to fix the problem I was having.

I was experiencing the same errors you outlined:

80004005 - 28557

80040E14 - 28560

80040E14 - 25501

but my parameters on the 25501 error were different:

par1: 1 (Line no.)

par2: 160 (Line offset)

par3: if (token in error)

I found in one of the .dri files in the snapshot folder the following lines of code:

if (@@microsoftversion > 0x07000000 )

EXEC ('CREATE STATISTICS [Statistic_RVU] ON [dbo].[tablename] ([RVU])

')

GO

I believe this represents statistics being tracked on a non-indexed column - something apparently not supported in SQLce. I dropped the statistics and reran the snapshot agent and the problem was gone.

One thing that threw me off a little was that the "if" token in this statement was in position 1 of the line. I found, however, that by counting characters from the previous GO in the .dri script, the "if" token is exactly 160 characters in.

- Mike

Jeff-131469 Old Hand Points: 325 More actions · Answer 8

Hi, Mike -

I'm glad you solved your problem.

Are you saying that you now can take an existing SQLCE database (with data) and subscribe it to a new SQL2K database publication without losing the SQLCE data?

If so, I'll probably revisit the issue with my project (and may run across additional MS 'puzzles' to solve).

- Jeff

Mike Rochleau-Rice SSC-Addicted Points: 422 More actions · Answer 9

Jeff

Sorry - but no. My SQLCE subscription database doesn't have existing data. I was drawn to your post because of the sequence of errors you were experiencing.

Could you, instead of copying the xml data up to your desktop, just load it into your SQLCE database on the PDA after you subscribe?

I'm thinking of doing something similar to implement some kind of recovery routine if the subscription becomes invalid or out of date.

- Mike