Arithmetic overflow error ???
Jesper Friis
a central SQL Server. It has been working quite fine, but suddently it has
stopped working.
The error message is:
arithmetic overflow error converting expression to type int
The error number is:
8115
I have not changed anything in the database design and the tables i am
replicating between has exactly the same layout.
Your suggestions are GREATLY appreciated!
Jesper Friis
Danish Dairy Board
Michael Hotek
pack?
--
Please post all replies to the newsgroup unless otherwise stated.
Mike Hotek - michae...@adoutlet.com
SQL Server MVP
Database and Operations Director
AdOutlet.com - http://www.adoutlet.com
E-SOLUTIONS FOR THE MEDIA MARKETPLACET
SQL Server Resources: http://www.mssqlserver.com
Replication Primer: http://www.mssqlserver.com/replication
SQL Server FAQ: http://www.mssqlserver.com/faq
Jesper Friis
I use the same SP (sp2) on both the publisher and the subscriber.
I have spoken to Microsoft and they say that there is a known BUG thats
causing my pull subscription to fail. Unfortunately there will be no fix
until SQL Server sp3. The problem occurs if the delivery_latency value in
the tables MSlogreader_history or MSdistribution_history in the distribution
database is very high. To solve this problem Microsoft suggest that I set
the delivery_latency value in the MSlogreader_history or
MSdistribution_histroy (depending on the agent where the error occurs) to
zero for the row with latest "time" value in the time column for a given
agent_id.
Example:
Update MSlogreader_history set delivery_latency=0 where agent_id=1 and
time=(select max(time) from MSdistribution_history where agent_id=1)
But it does not solve the problem! Microsoft is looking further into the
problem to see if they can find a solution that is working for me.
If you know of any hacks that can solve the problem I will be a happy man.
Kind regards
Jesper Friis
Danish Dairy Board
Michael Hotek <michae...@adoutlet.com> wrote in message
news:#hPLrT$r$GA....@cppssbbsa02.microsoft.com...
tig...@my-deja.com
Maybe there are some other things that you could do. I do know not if
this will cause more problems than it might solve, but if I was you I
would consider the following ideas.
Of course, they may cause you who knows what kinds of problems, but
when you are desperate then what have you got to loose.
Still, be careful. If you are actually going to do any of these things
then backup before doing them. And meticulously document the before
and after settings (or values) of the things you change.
If you can find out (from MICROSOFT) what field the delivery_latency is
going into then consider changing the data type of that field to be the
same as the delivery_latency field. You may need to make this change
to several other fields. You might only be able to find out what they
all are by waiting for another error to occur and contacting Microsoft,
telling them the error and asking them what field to change next.
or
Update MSlogreader_history set delivery_latency=0 where agent_id=1
or
Update MSlogreader_history set delivery_latency=0
or
Determine what the maximum value that fits into an INT data type is.
Let's say that it is 123456. (It is not, but you can figure out or
guess what number to use.)
Update MSlogreader_history set delivery_latency=123456 where
delivery_latency > 123456
or
Do some research, if you have not already, by (of course you might want
to select * or select more fields than I have listed here):
select agent_id, delivery_latency from MSlogreader_history where
delivery_latency NOT BETWEEN -123456 AND 123456
or
Find a way to turn off the history.
If you do find something that works for a while but then the problem
comes back when more records are added to the MSlogreader_history table
then consider figuring out how to schedule the fix to run right before
the subscriber goes to synchronize or every time a record is added to
the table or every time a record is updated.
I hope that some of these ideas will help. Help maybe they will help
you to have an idea that will work.
-John
In article <#AmY2V0s$GA.213@cppssbbsa03>,
"Jesper Friis" <j...@mejeri.dk> wrote:
> Hi Mikael
>
> I use the same SP (sp2) on both the publisher and the subscriber.
>
> I have spoken to Microsoft and they say that there is a known BUG
> that is
Sent via Deja.com http://www.deja.com/
Before you buy.
Thomas NOKIN
I am having the exact same problem since this morning ("Arithmetic overflow
error converting expression to data type int" on replication log reader).
Also running SP2. Any news since your last mail ?
Also, just a tought: it was working fine previously. So I guess this problem
happens when one recreates a distribution that was deleted. Then maybe we
should remove the "distributor" character on the distributor computer, and
make sure the distributor db is dropped. Then recreate everything. Did MS
told you anything about this ?
Thomas Nokin
"Jesper Friis" <j...@mejeri.dk> wrote in message
news:#AmY2V0s$GA.213@cppssbbsa03...
> Hi Mikael
>
> I use the same SP (sp2) on both the publisher and the subscriber.
>
> I have spoken to Microsoft and they say that there is a known BUG thats
Thomas NOKIN
Your hack did not solved my problem.
However I found another way out: drop all the distributor and publisher
information, and the subscriber database. Then I recreated everything, and
it worked fine. I guess somewhere somehow a database must have been into a
weird state and would trigger this latency problem.
I post this for others having the same problem: just start from scratch !
Thanks a lot for your help, I learned a lot.
Thomas
"Jesper Friis" <j...@mejeri.dk> wrote in message
news:XPRT4.121$e96....@news.get2net.dk...
> Hi Thomas
>
> Since your problem is occuring in the logreader_history then a patch can
be
> done in a system
> stored procedure in the distribution database. Attached is the patched
> version
> of this stored procedure. Better take a backup!
>
> My problem occured in the distribution_history. Since Microsoft could not
> help me at the time I started to look in sp_MSadd_distribution_history in
> the distribution database where I found the following statement:
>
> -- Calculate the latency of the last distributed transaction
> IF @entry_time IS NOT NULL
> SELECT @delivery_latency =DATEDIFF(millisecond, @entry_time,
> @current_time)
> ELSE
> SELECT @delivery_latency = 0
>
> I changed the 'millisecond' with second and then truncated the trancaction
> log. It solved my problem!
>
> Later I spoke to Microsoft and they said that my workaround should do the
> work. Down below follows the new information that I received from
Microsoft
> :
>
> //
> It is very difficult to say what is the best solution, changing
millisecond
> to second reduces the latency
> value by a factor of 1000 so hypothetically the problem would occur if the
> latency was 1000 times as
> bad which seems unlikely.
> I would guess milliseconds was used because in most cases where there is
no
> latency issue the values would
> have been minimal or 0 as seconds.
> An alternative is to cast the datatype in the calculation within the
> procedure:
> SELECT @avg_delivery_latency = (cast(@delivery_latency as decimal(20,0)) +
> @last_delivery_latency)/2
> As you know this would not really be supported either way and they should
be
> aware that installing any future
> SQL Server service pack may recreate the procedures and therefore undo
their
> changes, also if they do ever
> recreate the replication from scratch they will also be lost.
> If this is working then it is probably as good a workaround as any at the
> moment in terms of changing the
> procedure but I would emphasize that they should keep a record of all
> changes they have made and on which
> machines so that if necessary this can be undone in the future.
> It at least proves that this is the problem in the bug and hence we need
to
> look at why the workaround did
> not work rather than for another cause.
>
>
> Kind regards
> Jesper Friis
> Danish Dairy Board
>
>
> Thomas NOKIN <tno...@wanadoo.fr> wrote in message
> news:#JxfZTlv$GA.311@cppssbbsa04...