Arithmetic overflow error - in a changed table!

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
"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.
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.|||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,
"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...
>
>|||Check for triggers on the table.
Hope this helps.
Dan Guzman
SQL Server MVP
"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?