Microsoft KB Archive/914534

= Error message when you run the sp_databases stored procedure in SQL Server 2000: &quot;Arithmetic overflow error converting expression to data type int&quot; =

Article ID: 914534

Article Last Modified on 11/2/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Personal Edition
 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 2000 Workgroup Edition
 * Microsoft SQL Server 2000 Developer Edition
 * Microsoft SQL Server 2000 Enterprise Edition
 * Microsoft SQL Server 2000 Developer Edition
 * Microsoft SQL Server 2000 Enterprise Edition 64-bit

-



Bug #: 474872 (SQL Server 8.0)



SYMPTOMS
When you run the sp_databases stored procedure in Microsoft SQL Server 2000, you may receive the following error message:

Msg 8115, Level 16, State 2, Procedure sp_databases, Line 31

Arithmetic overflow error converting expression to data type int



CAUSE
The problem occurs because the size of a database in SQL Server 2000 exceeds 2 terabytes.



WORKAROUND
To work around this problem, run the following query instead of the sp_databases stored procedure: set nocount on declare @name sysname declare @SQL nvarchar(600)

create table #databases (   DATABASE_ID int NOT NULL,    size bigint NOT NULL)

declare c1 cursor for select name from master.dbo.sysdatabases where has_dbaccess(name) = 1

open c1 fetch c1 into @name

while @@fetch_status >= 0 begin select @SQL = 'insert into #databases select '+ convert(sysname, db_id(@name)) + ', sum(convert(bigint,size)) from ' + QuoteName(@name) + '.dbo.sysfiles' execute (@SQL) fetch c1 into @name end

deallocate c1

select DATABASE_NAME = db_name(DATABASE_ID), DATABASE_SIZE = convert(bigint,size)*8, REMARKS = convert(varchar(254),null) from #databases order by 1

drop table #databases



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; section.



MORE INFORMATION
For more information about the sp_databases stored procedure, visit the following Microsoft Developer Network (MSDN) Web site:

http://msdn2.microsoft.com/en-us/library/aa259636(SQL.80).aspx

Additional query words: TB

Keywords: kbtshoot kbprb kbexpertiseadvanced KB914534

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.