Arithmetic overflow error when doing outer joins

Hey everyone,

I have two tables, one is a large table (v_userviews) containing a list of all the servers and various information about those servers. The other table (l_printers) contains printer information for those servers. I am working on a view to consolidate the printer information in l_printers with the other server information in v_userviews.

I've been trying to get outer joins to work but I am getting this error:
"Server: Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type int.
Warning: Null value is eliminated by an aggregate or other SET operation."

Here is my select statement:

select u.propid, u.address,
SUM((CASE u.Tree WHEN 'tree1' then 1 ELSE 0 END)) AS One,
SUM((CASE u.Tree WHEN 'tree2' then 1 ELSE 0 END)) AS Two,
SUM((CASE u.Tree WHEN 'tree3' then 1 ELSE 0 END)) AS Three,
SUM((CASE u.Tree WHEN 'tree4' then 1 ELSE 0 END)) AS Four,
SUM((CASE u.Tree WHEN 'tree5' then 1 ELSE 0 END)) AS Five,
SUM((CASE u.Tree WHEN 'tree6' then 1 ELSE 0 END)) AS Six,
SUM((CASE u.Tree WHEN 'tree7' then 1 ELSE 0 END)) AS Seven,
SUM((CASE u.Tree WHEN 'tree8' then 1 ELSE 0 END)) AS Eight,
SUM((CASE u.Tree WHEN 'tree9' then 1 ELSE 0 END)) AS Nine,
SUM((CASE u.Tree WHEN 'tree10' then 1 ELSE 0 END)) AS Ten,
SUM((CASE u.Tree WHEN 'tree11' then 1 ELSE 0 END)) AS Eleven,
SUM((CASE u.Tree WHEN 'tree12' then 1 ELSE 0 END)) AS Twelve,
SUM((CASE u.Tree WHEN 'tree13' then 1 ELSE 0 END)) AS Thirteen,
SUM((CASE u.Tree WHEN 'tree14' then 1 ELSE 0 END)) AS Fourteen,

count(u.server) as totalservers,
sum(cast(left(u.totalspace,len(u.totalspace)-2) as int)) as totalspace,
sum(cast(left(u.totalusedspace,len(u.totalusedspac e)-2) as int)) as totalusedspace,
count(p.printer) as numprinters

from serverops.dbo.v_userviews u LEFT OUTER JOIN novell_twr.dbo.l_printers p ON u.propid = p.propid
where u.os='netware'and u.state in ('ny', 'nj', 'fl')
group by u.propid, u.address

the l_printers table is in this format:

Printers Server Propid
nvarchar nvarchar varchar

Thanks for all your help. :beer:Try commenting out all the sums, and see what the count of total servers looks like, I am going to guess it is somewhat larger than you expect. If so, then you will have to look over how you are joining the two tables.|||Try commenting out all the sums, and see what the count of total servers looks like, I am going to guess it is somewhat larger than you expect. If so, then you will have to look over how you are joining the two tables.

You were right, the total servers was definitely larger than expected. Will I have to use another method other than outer joins to consolidate the two tables?|||How many servers do you have?|||How many servers do you have?

Around 4800|||what's defined as tiny int?

bigint

Integer (whole number) data from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807).

int

Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).

smallint

Integer data from 2^15 (-32,768) through 2^15 - 1 (32,767).

tinyint

Integer data from 0 through 255.

post some ddl|||what's defined as tiny int?

post some ddl

Nothing is defined as tinyint

Here are some DDL

L_Printers:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[L_Printers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[L_Printers]
GO

CREATE TABLE [dbo].[L_Printers] (
[Printer] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Server] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PropID] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

R_Servers (v_userviews is actually just a distinct top 100 view of r_servers):
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[R_Servers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[R_Servers]
GO

CREATE TABLE [dbo].[R_Servers] (
[Server] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Type] [varchar] (51) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Classification] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IPX Internal] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Secondary IP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DNS_IP1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DNS_IP2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DNS_RIBIP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PropID] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Branch #] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OS] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OSVersion] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InProduction] [bit] NULL ,
[NOTES] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Tree] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NWContext] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NTDomain] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NTDomainRole] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LASTXEUpdate] [datetime] NULL ,
[MacAddress] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CommonName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Zip] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TFloors] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Bank_Floors] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Country] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TotalEmp] [float] NULL ,
[Address] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Street Address] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Room] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Floor] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IPResponse] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ResponseOK] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TapeDrive] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TapeType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DriveCapacity] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IP Address] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RIBIP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RIBPW] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Remote PW] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Console PW] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DeviceStatus] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RIBInstalled] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SPack] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DeviceOwner] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ASource] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BackupDetails] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ServerID] [numeric](10, 0) NOT NULL ,
[Model] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Serial] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AssetNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Memory] [numeric](18, 0) NULL ,
[ROM] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CPU] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CPUSpeed] [decimal](18, 0) NULL ,
[TotalCPUs] [int] NULL ,
[Vendor] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PODate] [datetime] NULL ,
[ReceivedDate] [smalldatetime] NULL ,
[ActivationDate] [smalldatetime] NULL ,
[RefreshDate] [smalldatetime] NULL ,
[WarrantyEndDate] [smalldatetime] NULL ,
[TowerName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TowerID] [int] NULL ,
[DistrictID] [int] NULL ,
[Priority] [int] NULL ,
[Severity] [int] NULL ,
[SupportComment] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SiteContact] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SupportStaff] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PSRegion] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DistrictManager] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DMName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Area] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SupportQueue] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[District] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Domain] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[inProductionDate] [smalldatetime] NULL ,
[Region] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TotalSpace] [nvarchar] (257) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TotalUsedSpace] [nvarchar] (257) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RackID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RackPosition] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RackRow] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OSType] [int] NULL ,
[Project#] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ClusterID] [numeric](18, 0) NULL ,
[RIBLicenseKey] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Prop_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastUpdate] [datetime] NULL ,
[CheckSum] [int] NULL ,
[ManagmentServer] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastAudit] [datetime] NULL ,
[RecordAuditor] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrimarySupport] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KVM] [bit] NULL ,
[KVMType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[expanse] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LOBOwner] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LocationCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrimaryRecordOwner] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreationDate] [datetime] NULL ,
[Strategic] [bit] NULL ,
[ServiceBillingCC] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HardwareSupport] [bit] NULL ,
[Updated] [datetime] NOT NULL
) ON [PRIMARY]
GO