Solved

SQL Replication failing cos of bigints

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

695 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