Solved

SQL Replication failing cos of bigints

Posted on 2004-09-05
3
408 Views
Last Modified: 2007-12-19
We have a SQL server database replicating via merge replication with three MSDE database servers. Everything was working fine untill we started getting the error ... The merge process could not retrieve article information for publication 'XXX' ... on all three subscriptions, within seconds of the merge agent starting. The actual error that caused the general error is:

Error converting data type bigint to int.
(Source: SR-INTRANET-01\SQL_INTRANET (Data source); Error number: 8114)

Nothing seems to budge it, even re-initialising the subscriptions etc. Several other publications continued to replicate fine.
We had no option but to delete the subscriptions and publication and recreate them all ... which worked fine. Except we've lost a few hours data from the subscribers.

Until ... today (two weeks later), started happening again.

A bit of background. Several of the tables have bigint identities (although the actual data was still within the int range). Apart from one table which had just gone into bigint range.

I suspect its this table that was causing the merge to fail.

Has anyone successfully solved this problem? Where is the merge replication data stored and why does it think a bigint is an int? Are there any tables/scripts that we could look at to solve the problem without losing data?

0
Comment
Question by:Dansimmo
3 Comments
 
LVL 15

Accepted Solution

by:
jdlambert1 earned 500 total points
ID: 11986420
I've encountered more bugs in SQL Server's replication than all the rest of it put together. I strongly suspect it's examining an initial subset of data which doesn't include any bigint values, and categorizing it as an int. You can occasionally encounter this behavior with DTS. At least with DTS it's easy to fix by manually designating the destination datatype (if you're creating a new destination table).

Unless someone here at EE has had this exact problem and solved it before, I don't think we're going to be able to give you much help. Of course, you can wait to see who else posts comments on this, but I think if you really want to get this resolved, you need to open a technical support incident with Microsoft.

If you want to pursue a technical support incident, call 800-426-9400. If you don't have a pre-paid incident you can use, it'll cost $295. The bright side, in case you aren't familiar with their policies, is that the case stays open until it's resolved, no matter how much work they put into it.

I opened a support incident on a replication problem under SQL Server 7, and at one point their engineers built a special debug version of several of the core files, which we used long enough to get a dump of SQL Server's inner workings during the failure, then sent them the dump. When that still didn't give them what they needed to figure it out, we shipped them copies of our databases (after they signed a non-disclosure agreeement), and they finally discovered the bug, and a fix was put into Service Pack 2.

Oh, when our replication problem started, it stopped a production system, so the incident was categorized by Microsoft as "Production Server Down". Within hours of opening the case, we had a work-around that got us back in production, though it was not a good long-term solution. That Friday afternoon, the senior rep on the case called me and meekly asked if they could have my permission to downgrade the status to one notch below "Production Server Down." He apologized for asking, but he said that 1. he was only asking because he knew we had a work-around working, 2. they couldn't do it without my permission, and 3. if they left it at "Production Server Down", his boss and his boss's boss would have their pagers going off all weekend. Yep, that's exactly what he said. So maybe you can understand why I don't mind recommending them...

HTH, jd
0
 
LVL 9

Expert Comment

by:solution46
ID: 11991102
< off topic >
jd,

I've had plenty of grief with assorted 'features' of MS products and can fully understand people who flame them all over the place. However, it is good to hear somebody giving them credit for the things they do right, even if they do charge an arm and a leg for it!

s46.

< /off topic>
0
 

Author Comment

by:Dansimmo
ID: 12060445
Oh well the problem is still there but on talking to a consulant the best option is to 'avoid' it untill its fixed by MS.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now