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 Unknown Member - Measures are 'off'

I have a problem with a cube where the some of the measures seems to be ‘off’ when compared against the underlying table. Digging into that I noticed that the measures do not reflect the amounts/numbers tie to the unknown member in of my dimensions (date dimension specifically).

e.g. at the leaf level it looks something like:

01/01/2007 100

02/01/2007 100

Unknown 50

Grand Total 200

If I browsed it at a higher level, let’s say month, then it looks like:

Jan 2007 200

Unknown (yes, empty)

Grand Total 200

The odd thing is that my date dimension is playing 3 roles, and this only happens in 2 of the roles.

I am expecting lot of rows to be tie to the unknown member as the source has referential integrity issues.

Any advice is appreciated

Have you checked your attribute relationships? Incorrectly set attribute relationships can cause this kind of problem.

Chris

|||

Christopher Webb wrote:

Have you checked your attribute relationships? Incorrectly set attribute relationships can cause this kind of problem.

Chris

Thanks Chris for your reply. I will be double cheking that; but I still don't understand why this happens only with 2 of the 3 roles that dimension is playing. Are not the attribute relationships defined in the underlying dimension only? Should not all the roles behave the same?|||

Yes, all the role-playing dimensions should behave the same - but sometimes the behaviour is quite hard to spot, so it could be that all three are producing incorrect values but it's only obvious on two of them.

Chris