Solved

SQL Replication failing cos of bigints

Posted on 2004-09-05
3
412 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

726 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