BUG: sp_articleview May Generate an Arithmetic Overflow

ID: Q234028

The information in this article applies to:

Microsoft SQL Server version 7.0

BUG #: 55703 (SQLBUG_70)

SYMPTOMS

sp_articleview creates the synchronization object for an article when a table is filtered vertically or horizontally. If the number of articles added to a published database exceed 32767, sp_articleview generates the following errors:

Server: Msg 220, Level 16, State 1, Procedure sp_articleview, Line 56
Arithmetic overflow error for data type smallint, value = 32768.
Server: Msg 20026, Level 11, State 1, Procedure sp_articleview, Line 59
The publication '<publication name>' does not exist.

This system stored procedure is called when adding an article from Enterprise Manager, causing the add article process to fail with the same message.

CAUSE

This happens when the pubid in the syspublications table grows larger than 32767. Look for the syspublications table in the published database. The parameter @pubid, used in sp_articleview to obtain the syspublications pubid field, is defined as a smallint. However, the pubid column in syspublications is defined as an int. If the value in the syspublications table is greater than the largest possible value for a smallint (32767), an arithmetic overflow occurs when the system tries to place the data into the parameter.

WORKAROUND

The identity value for syspublications.pubid can become large when publications are created and dropped multiple times. Over time the current identity value may need to be corrected to avoid this error.

Check the largest publication pubid number in the database by issuing the following query:

 
select max(pubid) from <dbname>.dbo.syspublications

If the value returned is less than 32767, run the following query in the database to correct the problem:


declare @t1 int
select @t1 = (select max(pubid) from <dbname>.dbo.syspublications) + 1
DBCC CHECKIDENT('<dbname>.dbo.syspublications', RESEED, @t1)

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 7.0.

Additional query words:

Keywords : kbSQLServ700bug
Version : winnt:7.0
Platform : winnt
Issue type : kbbug