?
Solved

SQL Replication failing cos of bigints

Posted on 2004-09-05
3
Medium Priority
?
415 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 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

741 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