|
|
You're in the
sql server new users
group:Arithmetic overflow error - in a changed table!
sql server new users:
I had a table with identity column set to datatype [smallint]. I've reached near 32800 records. That's the limit for [smallint]. So, I've changed the identity datatype to [int], that SHOULD allow me to have more records. But the error continues. "Arithmetic overflow error converting expression to data type smallint". AND there's no smallint anymore. It's weird. See by yourself: ------------------- CREATE TABLE [forum] ( [forumid] [int] IDENTITY (1, 1) NOT NULL , [forumdata] [smalldatetime] NOT NULL CONSTRAINT [DF_forum_forumdata] DEFAULT (getdate()), [forumnome] [varchar] (100) NULL , [forumemail] [varchar] (100) NULL , [forumidade] [varchar] (50) NULL , [forumcidade] [varchar] (100) NULL , [forummensagem] [varchar] (1000) NULL , [forumstatus] [bit] NULL CONSTRAINT [DF_forum_forumstatus] DEFAULT (1), [forumip] [char] (15) NULL , CONSTRAINT [PK_forum] PRIMARY KEY CLUSTERED ( [forumid] ) ON [PRIMARY] ) ON [PRIMARY] ------------------ Am I missing something? Should I need to execute some kind of
Hi Rodrigo,
It's very strange without a doubt. One solution should be simply do the following steps: 1)select * into forum20050323 from forum 2)truncate table forum 3)insert into forum select (do just sp_help forum in order to get all the filed but leaving off the identity field) from forum20050323 truncate command is bearing on mind the original values on a identity field. Let me know your doubts or concerns with this. See you later, [quoted text, click to view] "Denis" wrote:
> I might be a variable that you are using in one of you sp where you do the > insert > > "Rodrigo Volponi" <volps@hotmail.com> wrote in message > news:5e7e88ed.0503230723.7dace12e@posting.google.com... > > Hello. > > > > I had a table with identity column set to datatype [smallint]. I've > > reached near 32800 records. That's the limit for [smallint]. > > > > So, I've changed the identity datatype to [int], that SHOULD allow me > > to have more records. But the error continues. > > > > "Arithmetic overflow error converting expression to data type > > smallint". > > > > AND there's no smallint anymore. It's weird. See by yourself: > > > > ------------------- > > CREATE TABLE [forum] ( > > [forumid] [int] IDENTITY (1, 1) NOT NULL , > > [forumdata] [smalldatetime] NOT NULL CONSTRAINT [DF_forum_forumdata] > > DEFAULT (getdate()), > > [forumnome] [varchar] (100) NULL , > > [forumemail] [varchar] (100) NULL , > > [forumidade] [varchar] (50) NULL , > > [forumcidade] [varchar] (100) NULL , > > [forummensagem] [varchar] (1000) NULL , > > [forumstatus] [bit] NULL CONSTRAINT [DF_forum_forumstatus] DEFAULT > > (1), > > [forumip] [char] (15) NULL , > > CONSTRAINT [PK_forum] PRIMARY KEY CLUSTERED > > ( > > [forumid] > > ) ON [PRIMARY] > > ) ON [PRIMARY] > > > > ------------------ > > > > Am I missing something? Should I need to execute some kind of > > "table-reseter" to make SQLServer2000 forget it's [smallint] past? > >
I might be a variable that you are using in one of you sp where you do the
insert [quoted text, click to view] "Rodrigo Volponi" <volps@hotmail.com> wrote in message news:5e7e88ed.0503230723.7dace12e@posting.google.com... > Hello. > > I had a table with identity column set to datatype [smallint]. I've > reached near 32800 records. That's the limit for [smallint]. > > So, I've changed the identity datatype to [int], that SHOULD allow me > to have more records. But the error continues. > > "Arithmetic overflow error converting expression to data type > smallint". > > AND there's no smallint anymore. It's weird. See by yourself: > > ------------------- > CREATE TABLE [forum] ( > [forumid] [int] IDENTITY (1, 1) NOT NULL , > [forumdata] [smalldatetime] NOT NULL CONSTRAINT [DF_forum_forumdata] > DEFAULT (getdate()), > [forumnome] [varchar] (100) NULL , > [forumemail] [varchar] (100) NULL , > [forumidade] [varchar] (50) NULL , > [forumcidade] [varchar] (100) NULL , > [forummensagem] [varchar] (1000) NULL , > [forumstatus] [bit] NULL CONSTRAINT [DF_forum_forumstatus] DEFAULT > (1), > [forumip] [char] (15) NULL , > CONSTRAINT [PK_forum] PRIMARY KEY CLUSTERED > ( > [forumid] > ) ON [PRIMARY] > ) ON [PRIMARY] > > ------------------ > > Am I missing something? Should I need to execute some kind of > "table-reseter" to make SQLServer2000 forget it's [smallint] past?
Show us how to get that error.
[quoted text, click to view] Rodrigo Volponi wrote: > Hello. > > I had a table with identity column set to datatype [smallint]. I've > reached near 32800 records. That's the limit for [smallint]. > > So, I've changed the identity datatype to [int], that SHOULD allow me > to have more records. But the error continues. > > "Arithmetic overflow error converting expression to data type > smallint". > > AND there's no smallint anymore. It's weird. See by yourself: > > ------------------- > CREATE TABLE [forum] ( > [forumid] [int] IDENTITY (1, 1) NOT NULL , > [forumdata] [smalldatetime] NOT NULL CONSTRAINT [DF_forum_forumdata] > DEFAULT (getdate()), > [forumnome] [varchar] (100) NULL , > [forumemail] [varchar] (100) NULL , > [forumidade] [varchar] (50) NULL , > [forumcidade] [varchar] (100) NULL , > [forummensagem] [varchar] (1000) NULL , > [forumstatus] [bit] NULL CONSTRAINT [DF_forum_forumstatus] DEFAULT > (1), > [forumip] [char] (15) NULL , > CONSTRAINT [PK_forum] PRIMARY KEY CLUSTERED > ( > [forumid] > ) ON [PRIMARY] > ) ON [PRIMARY] > > ------------------ > > Am I missing something? Should I need to execute some kind of > "table-reseter" to make SQLServer2000 forget it's [smallint] past? -- Microsoft MVP -- ASP/ASP.NET Please reply to the newsgroup. The email account listed in my From header is my spam trap, so I don't check it very often. You will get a quicker response by posting to the newsgroup.
Check for triggers on the table.
-- Hope this helps. Dan Guzman SQL Server MVP [quoted text, click to view] "Rodrigo Volponi" <volps@hotmail.com> wrote in message news:5e7e88ed.0503230723.7dace12e@posting.google.com... > Hello. > > I had a table with identity column set to datatype [smallint]. I've > reached near 32800 records. That's the limit for [smallint]. > > So, I've changed the identity datatype to [int], that SHOULD allow me > to have more records. But the error continues. > > "Arithmetic overflow error converting expression to data type > smallint". > > AND there's no smallint anymore. It's weird. See by yourself: > > ------------------- > CREATE TABLE [forum] ( > [forumid] [int] IDENTITY (1, 1) NOT NULL , > [forumdata] [smalldatetime] NOT NULL CONSTRAINT [DF_forum_forumdata] > DEFAULT (getdate()), > [forumnome] [varchar] (100) NULL , > [forumemail] [varchar] (100) NULL , > [forumidade] [varchar] (50) NULL , > [forumcidade] [varchar] (100) NULL , > [forummensagem] [varchar] (1000) NULL , > [forumstatus] [bit] NULL CONSTRAINT [DF_forum_forumstatus] DEFAULT > (1), > [forumip] [char] (15) NULL , > CONSTRAINT [PK_forum] PRIMARY KEY CLUSTERED > ( > [forumid] > ) ON [PRIMARY] > ) ON [PRIMARY] > > ------------------ > > Am I missing something? Should I need to execute some kind of > "table-reseter" to make SQLServer2000 forget it's [smallint] past?
Don't see what you're looking for? Try a search.
|
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |