is this a bug ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

is this a bug ?

Author

Topic

walank
Starting Member

12 Posts

Posted - 2010-10-12 : 23:14:38

I spend many hours trying to figure out WHY I'm getting error (When I do right click "Edit first 200 rows"):
"Error converting data type varchar to numeric". My table has no varchar columns !!!

Then I created very simple table:

CREATE TABLE [dbo].[test_table](
[recordID] [int] IDENTITY(1,1) NOT NULL,
[list] [numeric](18, 2) NOT NULL,
CONSTRAINT [PK_test_table] PRIMARY KEY CLUSTERED
(
[recordID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[test_table] ADD CONSTRAINT [DF_test_table_list] DEFAULT ((0.00)) FOR [list]
GO

Turns out column name "list" is causing problem. If I rename it to listPrice or something else than list - works fine.

I shouldn't be able to create column with name which is reserved keyword...

What do you guys think? Is this a bug in Ms SQL Server 2005 ?
Adam

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-10-12 : 23:29:56

Not sure if I'd call it a bug although the gui interface is no where near as solid as the database engine. It's not a good idea to use key words for object names and you shouldn't use the Management Studio GUI to modify data. Use t-sql for adhoc changes.

Be One with the Optimizer
TG

walank
Starting Member

12 Posts

Posted - 2010-10-13 : 00:18:47

quote:
Originally posted by TG

Not sure if I'd call it a bug although the gui interface is no where near as solid as the database engine. It's not a good idea to use key words for object names and you shouldn't use the Management Studio GUI to modify data. Use t-sql for adhoc changes.

Be One with the Optimizer
TG

Not my idea.... This database was converted from another DBS.
I found this problem by accident (wasn't easy - over 200 columns). Cursoradapter (ADO) was not able to update data in backend... when I started digging I found It's also not possible to update record manually in Management Studio. T-SQL was updating just fine. ODBC also no problem. Only ADO.DB and Management Studio was having troubles.

I just checked Ms website - "LIST" is NOT on reserved keywords listing...

Thanks,
Adam

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-10-13 : 02:57:53

I just tried this on my SQL 2008 R2 Developer and the behaviour is exactly the same...totally weird!! Renaming the column solves the problem exactly as you say.

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Kristen
Test

22859 Posts

Posted - 2010-10-13 : 03:19:09

Anyone captured the SQL that is being generated by SSMS? (using profiler for example). That might shed some light on it.

"you shouldn't use the Management Studio GUI to modify data. Use t-sql for adhoc changes."

T-SQL not easy when you have some random changes to make in order to fix up some data. I use SSMS for such data fix-ups in DEV frequently. 'Bout the only thing I do use it for though!

"I shouldn't be able to create column with name which is reserved keyword..."

No harm in that, surely? (May not be a good idea ... but that's another story ... using [ReservedWord] quoting of a column name which is a reserved word is valid)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-10-13 : 07:17:52

Works for me in SQL 2008R2 Enterprise.
I am using the "normal" SSMS not the Express SSMS edition..

N 56°04'39.26"
E 12°55'05.63"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-10-13 : 07:20:03

quote:
Works for me in SQL 2008R2 Enterprise.

Did you try to add/edit some rows in the table?

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-10-13 : 08:00:11

[code]USE TempDB;

DROP TABLE dbo.TestTable;

CREATE TABLE dbo.TestTable
(
ID INT IDENTITY(1, 1) NOT NULL,
List NUMERIC(18, 2) NOT NULL, -- Error converting data type varchar to numeric.
--List MONEY NOT NULL, -- Cannot convert char value to money. The char value has incorrect syntax.
--List INT NOT NULL, -- Conversion failed when converting the varchar value 'List' to data type int.
--List VARCHAR(2) NOT NULL, -- Data has changed since the Results pane was last retrieved. Do you want to save your data now?
--List BIT NOT NULL, -- Conversion failed when converting the varchar value 'List' to data type bit.
--List DATETIME NOT NULL, -- Conversion failed when converting date and/or time from varchar value.
CONSTRAINT PK_TestTable PRIMARY KEY CLUSTERED
(
ID
)
);

INSERT dbo.TestTable
(
List
)
VALUES (
4
);[/code]

N 56°04'39.26"
E 12°55'05.63"

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-10-13 : 08:32:55

Works for me just fine in SQL2k5 SE, EE, SQL2K8 EE

by the way, List isn't a reserved word.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-10-13 : 08:51:10

In my experience over the years there have been numerous "buggy" behaviors in the GUI of Enterprise Manager and Management Studio. [List] is not a t-sql reserved key word but it is an object name in some programming languages and obviously trips up the MS GUI. I'm not saying don't report it just that I'm not shocked by it. I stand by my original suggestion: use t-sql for adhoq data changes.

Be One with the Optimizer
TG

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-10-13 : 17:47:28

Russell, try the code I posted and "Edit 200" afterwards. Then you get the error message.
You can see why if you turn on SQL Profiler to see what SSMS GUI sends to the database.

N 56°04'39.26"
E 12°55'05.63"

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-10-13 : 20:03:23

I don't doubt you Peter, but I can't reproduce it, using the versions of SQLWB / SSMS below, trying against:

SQL2005 SE RTM
SQL2005 SE SP2
SQL2005 SE SP3
SQL2005 EE SP3

SQL2008 SE RTM
SQL2008 SE SP1
SQL2008 EE SP1

Microsoft SQL Server Management Studio			9.00.3042.00
Microsoft Analysis Services Client Tools		2005.090.3042.00
Microsoft Data Access Components (MDAC)			2000.085.1132.00 (xpsp.080413-0852)
Microsoft MSXML						2.6 3.0 5.0 6.0 
Microsoft Internet Explorer				7.0.5730.13
Microsoft .NET Framework				2.0.50727.3603
Operating System					5.1.2600


Microsoft SQL Server Management Studio			10.0.1600.22 ((SQL_PreRelease).080709-1414 )
Microsoft Analysis Services Client Tools		2007.0100.1600.022 ((SQL_PreRelease).080709-1414 )
Microsoft Data Access Components (MDAC)			2000.085.1132.00 (xpsp.080413-0852)
Microsoft MSXML						2.6 3.0 5.0 6.0 
Microsoft Internet Explorer				7.0.5730.13
Microsoft .NET Framework				2.0.50727.3603
Operating System					5.1.2600


Microsoft SQL Server Management Studio			9.00.3042.00
Microsoft Analysis Services Client Tools		2005.090.3042.00
Microsoft Data Access Components (MDAC)			2000.086.3959.00 (srv03_sp2_rtm.070216-1710)
Microsoft MSXML						2.6 3.0 6.0 
Microsoft Internet Explorer				7.0.5730.11
Microsoft .NET Framework				2.0.50727.3603
Operating System					5.2.3790

maybe .Net Framework issue?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-10-13 : 20:11:02

Profiler just shows

SELECT TOP (200) List FROM TestTable

Kristen
Test

22859 Posts

Posted - 2010-10-14 : 02:25:47

Thanks Russell. Is it odd that it doesn't also select [recordID] ?

I would have expected it to ORDER BY the PK (or clustered index perhaps) too (for consistency / repeatability) but obviously that's not a requirement of course.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-10-14 : 05:15:52

It is definetly something funky about the "List" word...this is what profiler captured of an update I did using Pesos table:

exec sp_executesql N'UPDATE TOP (200) TestTable SET List = @List WHERE (ID = @Param1) AND (''List'' = @Param2)',N'@List decimal(3,2),@Param1 int,@Param2 decimal(3,2)',@List=5.00,@Param1=1,@Param2=4.00

For some reason it adds two single-quotes around the List in the where-statement. If the column name is changed to i.e. ListX this doesn't happen. Removing the single-quotes and running the query manually works just fine. I'd report this if it were me who discovered it.

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Kristen
Test

22859 Posts

Posted - 2010-10-14 : 06:31:14

Good catch

walank
Starting Member

12 Posts

Posted - 2010-10-14 : 09:52:30

quote:
Originally posted by Lumbago

It is definetly something funky about the "List" word...this is what profiler captured of an update I did using Pesos table:
exec sp_executesql N'UPDATE TOP (200) TestTable SET List = @List WHERE (ID = @Param1) AND (''List'' = @Param2)',N'@List decimal(3,2),@Param1 int,@Param2 decimal(3,2)',@List=5.00,@Param1=1,@Param2=4.00
For some reason it adds two single-quotes around the List in the where-statement. If the column name is changed to i.e. ListX this doesn't happen. Removing the single-quotes and running the query manually works just fine. I'd report this if it were me who discovered it.

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Thanks guys for spending time on this. It's not gonna harm if I will report it...

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-10-14 : 11:06:49

If you need a workaround, I was able to edit it using a view where I gave the List column a different name:

create view dbo.V_TestTable
as
select
	ID, 
	List as lista
from
	dbo.TestTable

CODO ERGO SUM

walank
Starting Member

12 Posts

Posted - 2010-10-14 : 11:17:52

quote:
Originally posted by Michael Valentine Jones

If you need a workaround, I was able to edit it using a view where I gave the List column a different name:
create view dbo.V_TestTable
as
select
ID,
List as lista
from
dbo.TestTable

CODO ERGO SUM

Thanks Mike but I already changed that column name in front end so I should be fine...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources