Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2006
    Posts
    13

    Unanswered: 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:
    Code:
    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.totalusedspace)-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:
    Code:
    Printers      Server      Propid
    nvarchar      nvarchar   varchar

    Thanks for all your help.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    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.

  3. #3
    Join Date
    Jul 2006
    Posts
    13
    Quote Originally Posted by MCrowley
    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?

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How many servers do you have?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jul 2006
    Posts
    13
    Quote Originally Posted by Brett Kaiser
    How many servers do you have?
    Around 4800

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jul 2006
    Posts
    13
    Quote Originally Posted by Brett Kaiser
    what's defined as tiny int?



    post some ddl
    Nothing is defined as tinyint

    Here are some DDL

    L_Printers:
    Code:
    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):
    Code:
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •