Solved

SQL Replication failing cos of bigints

Posted on 2004-09-05
3
410 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

895 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

13 Experts available now in Live!

Get 1:1 Help Now