how to solve Msg 102, Level 15, State 1, Line 2 Incorrect syntax near in SQL.

Question

0.00/5 (No votes)

See more:

Hi Team,

I want to perform this as follows :
alter [FILETYPE] [int] NOT NULL DEFAULT ((0)) .

but it gives me error :
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'FILETYPE'.

Kindly help me .

Thanks
Harshal

Posted 25-Apr-14 2:28am

R Harshal

Updated 23-Dec-20 3:31am

Add a Solution

Comments

Tom Marvolo Riddle 25-Apr-14 8:30am

do you want to alter column or table?

4 solutions

Solution 2

Try with syntax like this. You missed the keyowrd SET

SQL

ALTER TABLE TableName
ALTER COLUMN ColumnName NOT NULL SET DEFAULT 0

And let me tell you about easy way.
When you're struck on issues like this, use Auto generate change scripts[^] option

Posted 25-Apr-14 2:56am

thatraja

Comments

Tom Marvolo Riddle 25-Apr-14 9:08am

I tried this way.But it shows the same error .Even the http://www.w3schools.com/sql/sql_default.asp[^]
shows the same syntax.But it's not working!. Any reasons?

thatraja 25-Apr-14 9:10am

Unfortunately I don't have SQL server to check it. Better option is the link in my answer.

Tom Marvolo Riddle 25-Apr-14 9:12am

Thanks you very much for the response thatraja!

King Fisher 26-Apr-14 1:31am

Syntax Error: :)

Sorry We cant use alter column for default value

Solution 4

SQL

CREATE TABLE [dbo].[sample](
    [Unique_ID] [bigint] NULL,
    [Person_ID] [nvarchar(max)] NULL,
    [Product] [nvarchar](max) NULL,
    [Version] [bigint] NULL,
    [Count] [bigint] NULL
)

To alter the column Datatype

SQL

ALTER TABLE sample
alter column  person_id bigint

To Set Default Value for the existing Column

SQL

alter table sample
add default 0 for person_id

:)

Posted 25-Apr-14 19:29pm

King Fisher

Solution 3

nicely explained here

Posted 25-Apr-14 2:58am

Herman<T>.Instance

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tom Marvolo Riddle · Accepted Answer · 2014-04-25T02:31:00

Solution 1

Your Syntax is wrong

Try this:

SQL

ALTER TABLE K2FILELOG
ADD FILETYPE int NOT NULL DEFAULT(0)

Posted 25-Apr-14 2:31am

Tom Marvolo Riddle

Updated 25-Apr-14 2:55am

v2

Comments

R Harshal 25-Apr-14 8:38am

i try this :
ALTER TABLE [dbo].[K2FILELOG]
alter column [FILETYPE] [int] NOT NULL DEFAULT ((0))

its gives me error :
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'DEFAULT'.

thatraja 25-Apr-14 8:42am

Why 4 brackets for 0?

Try
ALTER TABLE [dbo].[K2FILELOG]
alter column [FILETYPE] [int] NOT NULL DEFAULT (0)

Tom Marvolo Riddle 25-Apr-14 8:45am

Hi,I did the same thing which you wrote above.Still it shows the error.FYI i'm using SQL Server 2008 r2 am i missing something?

thatraja 25-Apr-14 9:08am

It's just a guess & the syntax is wrong. Check my answer. No, I don't have SQL server to check things.

R Harshal 25-Apr-14 8:45am

i have try but it gives me error :
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'DEFAULT'.

Tom Marvolo Riddle 25-Apr-14 8:48am

Hi do onething drop column FILETYPE and try this:

ALTER TABLE K2FILELOG
ADD FILETYPE int NOT NULL DEFAULT(0)

R Harshal 25-Apr-14 9:00am

Thank you Sir.Its working.

Tom Marvolo Riddle 25-Apr-14 9:03am

You're welcome!

thatraja 25-Apr-14 9:07am

Becareful when you have mass data in that table. You shouldn't drop the column in that situation.

thatraja 25-Apr-14 9:06am

I give you only 4 because it's not a good answer when you have millions of records in that table. Would you drop the column in that situation?

Tom Marvolo Riddle 25-Apr-14 9:15am

yes i totally agree with that(Even i'm not satisfied with my answer). I tried to alter the column without dropping the column.But it ain't work for me.I don't know what's wrong with the syntax(or me).Looking for some good answers.

Thank you for sharing your thoughts. :)

thatraja 25-Apr-14 9:18am

Why waiting? Try the "Auto generate change scripts" option to get a working script

Tom Marvolo Riddle 25-Apr-14 9:21am

Oh yes.!Definitely going to try this.Thanks a ton thatraja!