Bug in SQL Server 2000 sp3?

Hi
I have a table defined as
create table Project
(
Project nvarchar(20) collate database_default not null constraint
DF_Project_Project default '',
/* some other irrelevant fields */
CONSTRAINT [Project_PrimaryKey] PRIMARY KEY CLUSTERED ([Project])
)
If I insert a long numeric value into Project (3123456789), I get some
troubles:
insert Project (Project) values ('3123456789')
If i execute the statement
select Project from Project
in Query Analyzer, I get the expected result (the record shown in the result
grid)
If i execute the statement
select * from Project
I get the following error (and no record shown in the result grid)
Server: Msg 248, Level 16, State 1, Line 1
The conversion of the nvarchar value '3123456789' overflowed an int column.
Maximum integer value exceeded.
Why''
My SQL Server version is (according to Select @.@.Version)
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
IMO, using Select * should be functionally equal to a Select statement
listing all fields in the table, but it is not.
Best regards,
Benny TordrupBenny Tordrup wrote:
> Hi
> I have a table defined as
> create table Project
> (
> Project nvarchar(20) collate database_default not null constraint
> DF_Project_Project default '',
> /* some other irrelevant fields */
> CONSTRAINT [Project_PrimaryKey] PRIMARY KEY CLUSTERED ([Project])
> )
> If I insert a long numeric value into Project (3123456789), I get some
> troubles:
> insert Project (Project) values ('3123456789')
>
> If i execute the statement
> select Project from Project
> in Query Analyzer, I get the expected result (the record shown in the
> result grid)
> If i execute the statement
> select * from Project
> I get the following error (and no record shown in the result grid)
> Server: Msg 248, Level 16, State 1, Line 1
> The conversion of the nvarchar value '3123456789' overflowed an int
> column. Maximum integer value exceeded.
>
> Why''
> My SQL Server version is (according to Select @.@.Version)
>
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> IMO, using Select * should be functionally equal to a Select statement
> listing all fields in the table, but it is not.
> Best regards,
> Benny Tordrup
I went ahead and recreated your table here and don't get any error. Not
sure what the problem is.
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 1)
--
David G.|||You can get the error you mention if you have a computed column in the table
that uses the project column in its expression.
--
Jacco Schalkwijk
SQL Server MVP
"Benny Tordrup" <nospam dot bt at fk-data dot dk> wrote in message
news:%23WshbbwlEHA.3520@.TK2MSFTNGP11.phx.gbl...
> Hi
> I have a table defined as
> create table Project
> (
> Project nvarchar(20) collate database_default not null constraint
> DF_Project_Project default '',
> /* some other irrelevant fields */
> CONSTRAINT [Project_PrimaryKey] PRIMARY KEY CLUSTERED ([Project])
> )
> If I insert a long numeric value into Project (3123456789), I get some
> troubles:
> insert Project (Project) values ('3123456789')
>
> If i execute the statement
> select Project from Project
> in Query Analyzer, I get the expected result (the record shown in the
> result
> grid)
> If i execute the statement
> select * from Project
> I get the following error (and no record shown in the result grid)
> Server: Msg 248, Level 16, State 1, Line 1
> The conversion of the nvarchar value '3123456789' overflowed an int
> column.
> Maximum integer value exceeded.
>
> Why''
> My SQL Server version is (according to Select @.@.Version)
>
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> IMO, using Select * should be functionally equal to a Select statement
> listing all fields in the table, but it is not.
> Best regards,
> Benny Tordrup
>|||Jacco,
It was exactly the problem.
I had a computed column defined as
Cast(Case when IsNumeric(Project)=0 then 0 else Project end as
decimal(20,0))
Changing the definition to
Cast(Case when IsNumeric(Project)=0 then '0' else Project end as
decimal(20,0))
solved my problem
Thanks
Benny
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> skrev
i en meddelelse news:eGgnRixlEHA.596@.tk2msftngp13.phx.gbl...
> You can get the error you mention if you have a computed column in the
table
> that uses the project column in its expression.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Benny Tordrup" <nospam dot bt at fk-data dot dk> wrote in message
> news:%23WshbbwlEHA.3520@.TK2MSFTNGP11.phx.gbl...
> > Hi
> >
> > I have a table defined as
> >
> > create table Project
> > (
> > Project nvarchar(20) collate database_default not null constraint
> > DF_Project_Project default '',
> > /* some other irrelevant fields */
> >
> > CONSTRAINT [Project_PrimaryKey] PRIMARY KEY CLUSTERED ([Project])
> > )
> >
> > If I insert a long numeric value into Project (3123456789), I get some
> > troubles:
> >
> > insert Project (Project) values ('3123456789')
> >
> >
> > If i execute the statement
> >
> > select Project from Project
> >
> > in Query Analyzer, I get the expected result (the record shown in the
> > result
> > grid)
> >
> > If i execute the statement
> >
> > select * from Project
> >
> > I get the following error (and no record shown in the result grid)
> >
> > Server: Msg 248, Level 16, State 1, Line 1
> > The conversion of the nvarchar value '3123456789' overflowed an int
> > column.
> > Maximum integer value exceeded.
> >
> >
> > Why''
> >
> > My SQL Server version is (according to Select @.@.Version)
> >
> >
> > Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> > Dec 17 2002 14:22:05
> > Copyright (c) 1988-2003 Microsoft Corporation
> > Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> >
> > IMO, using Select * should be functionally equal to a Select statement
> > listing all fields in the table, but it is not.
> >
> > Best regards,
> >
> > Benny Tordrup
> >
> >
>