Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187

    Unanswered: The conversion of the varchar value '' overflowed an int column. ERROR

    Hi guys,
    Can someone please save some of my hair .
    I am at a loss. OK, I have a query in a sproc that works perfect in SQL 2000. We are converting all to SQL 2005 and the same query gives me this error:
    The conversion of the varchar value '20080012416486' overflowed an int column. Maximum integer value exceeded.

    The data comes from a view. Here is all my code and table DDL. Can anyone tell me what is wrong here? If I take the join out and just select from the view, no error. As soon as I join to the tblChartNumber, I get the error. Really wild.

  2. #2
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    Query:
    SELECT g.ChartNumber,
    c.ChartNumberDescription,
    g.TxnID,
    g.ReferenceNumber,
    g.Department,
    g.AccountingDate,
    g.SysID,
    g.SubID,
    g.Remarks,
    g.TotalAmount,
    g.DebitCreditIndicator,
    FROM AcctDB..GLDetailed g
    INNER JOIN tblChartNumber c ON g.ChartNumber = c.ChartNumber
    WHERE (g.AccountingDate BETWEEN '01/01/2008' AND '01/01/2008')
    AND g.ChartNumber NOT IN ('1060', '2010', '3800', '3900', '6020', '7020')

  3. #3
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    VIEW:
    CREATE VIEW dbo.GLDetailed
    AS
    SELECT g.GLID AS TxnID,
    d.SequenceNumber,
    g.GalaxyID,
    g.SysID,
    g.SubID,
    g.AccountingDate,
    'Remarks' = '',
    g.GLReference AS ReferenceNumber,
    g.PostedDate,
    g.WLReference AS JournalNumber,
    d.ChartNumber,
    d.Department,
    d.DCIndicator AS DebitCreditIndicator,
    d.Amount AS TotalAmount,
    'ClientCode' = ''
    FROM AcctDB.dbo.tblGL g (NOLOCK) INNER JOIN
    AcctDB.dbo.tblGLDetail d (NOLOCK) ON g.GLID = d.GLID

    3 Tables:
    CREATE TABLE [tblGL] (
    [GLID] [int] IDENTITY (1, 1) NOT NULL ,
    [GalaxyID] [char] (14) NOT NULL ,
    [SysID] [char] (2) NOT NULL ,
    [SubID] [char] (3) NOT NULL ,
    [AccountingDate] [smalldatetime] NOT NULL ,
    [Remarks] [char] (40) NOT NULL ,
    [GLReference] [int] NOT NULL ,
    [PostedDate] [datetime] NOT NULL ,
    [WLReference] [char] (14) NOT NULL ,
    [DayEndID] [int] NOT NULL,
    CONSTRAINT [XPKtblGL] PRIMARY KEY CLUSTERED
    (
    [GLID]
    ) WITH FILLFACTOR = 90 ON [PRIMARY]
    ) ON [PRIMARY]

    CREATE TABLE [tblGLDetail] (
    [GLID] [int] NOT NULL ,
    [SequenceNumber] [smallint] NOT NULL ,
    [ChartNumber] [char] (4) NOT NULL ,
    [Department] [char] (5) NOT NULL ,
    [DCIndicator] [char] (1) NOT NULL ,
    [Amount] [money] NOT NULL ,
    CONSTRAINT [XPKtblGLDetail] PRIMARY KEY CLUSTERED
    (
    [GLID],
    [SequenceNumber]
    ) WITH FILLFACTOR = 90 ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    CREATE TABLE [tblChartNumber] (
    [ChartNumber] [char] (4) NOT NULL ,
    [ActiveDate] [datetime] NOT NULL ,
    [InactiveDate] [datetime] NOT NULL ,
    [ChartNumberDescription] [char] (50) NOT NULL ,
    [ChartType] [tinyint] NOT NULL ,
    [ReportGroupID] [tinyint] NOT NULL ,
    [UseFor1099] [bit] NOT NULL ,
    [InhibitManualEntry] [bit] NOT NULL ,
    [ChangedDate] [datetime] NOT NULL ,
    [ChangedUserID] [char] (10) NOT NULL ,
    CONSTRAINT [XPKtblChartNumber] PRIMARY KEY CLUSTERED
    (
    [ChartNumber],
    [ActiveDate],
    [InactiveDate]
    ) WITH FILLFACTOR = 90 ON [PRIMARY]
    ) ON [PRIMARY]
    GO

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    It would be great if you mentioned the version (build #) of the server, including OS, and zipped up some sample data for us to test it out.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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