SQL "specified data type is not valid" error

Question

Why does SQL server express 2008 give me this error?

CREATE TABLE model (
name varchar(3),
desc varchar(25)
)


0x80040E14, Minor Error 26302
> CREATE TABLE model (
name varchar(3),
desc varchar(25)
)
The specified data type is not valid. [ Data type (if known) = varchar ]

Answer 1

Because DESC is a SQL keyword. You can either use a different column name (say, description) or put brackets around desc:

[desc] VARCHAR(25)

Answer 2

DESC is a reserved SQL keyword. In SQL Server, you can escape reserved name by surround the name with [], like [desc].

Or, don't use abbreviation and name your column Description.

Answer 3

Both repsonses I've seen are correct.

DESC is short for DESCENDING (as in the ORDER BY clause) and so is reserved. Using 'desc' or [desc] will avoid that problem and allow field names with spaces, etc, as well.

(Note: I don't recommend using keywords as field names or aliases. Nor do I recommend putting spaces in them. but with '' or [] you Can...)

Answer 4

SqlCe is a Unicode-only database so you need nvarchar instead of varchar.