2

I'm trying to change my SQL server database by adding another column to a table with 0 as a default value.

I thought this script worked in the past (for another table), but now I have an error when I try to execute the script

The script

ALTER TABLE Whatever
ADD WhateverColumn tinyint NOT NULL 
DEFAULT(0)

The errors

  • On hovering mouse over "NOT": Incorrect syntax near 'NOT'. Expecting 'TO'
  • On hovering mouse over "0": Incorrect syntax near 0. Expecting '(', or SELECT

Anyone knows what's wrong with this?

Improve this question
12
  • How/within what are you executing this statement?
    – Alex K.
    Commented Jul 26, 2018 at 11:28
  • I just created a new query in my SQL server management studio (v17.7)
    – RubenHerman
    Commented Jul 26, 2018 at 11:29
  • 1
    Is this the only command in the batch (or the whole input edit box)? I suspect you have something else in there before the ALTER and as you apparently don't use terminating semicolons that might confuse the parser.
    – sticky bit
    Commented Jul 26, 2018 at 11:32
  • 1
    Did you try to execute it, not to "hover over"?
    – sepupic
    Commented Jul 26, 2018 at 11:36
  • 1
    Open new query window and paste the code there. Try to execute it. The statement is CORRECT
    – sepupic
    Commented Jul 26, 2018 at 11:38

4 Answers 4

Reset to default
5

Try this:

ALTER TABLE Whatever
ADD WhateverColumn tinyint NOT NULL DEFAULT 0
Improve this answer
4
  • This solves the incorrect syntax error on "0" but does not solve the error on the "NOT"
    – RubenHerman
    Commented Jul 26, 2018 at 11:36
  • Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '‌'. Msg 102, Level 15, State 1, Line 2 Incorrect syntax near 'WhateverColumn'.
    – RubenHerman
    Commented Jul 26, 2018 at 11:39
  • ALTER TABLE taxi_trip_test_Data add test tinyint NOT NULL DEFAULT 0
    – Fahmi
    Commented Jul 26, 2018 at 11:39
  • 1
    I've tried with this and it didn't show me any error
    – Fahmi
    Commented Jul 26, 2018 at 11:40
1

Maybe the "Whatever" you are using as the table name has unclosed quotation marks, or even the "WhateverColumn" (both that you place here as a token, i get it) my have this problem, or even the "WhateverColumn" actual name is a reserved word?

Improve this answer
2
  • I executed this exact command in the past, but on a different table. I guess the table name shouldn't be the issue?
    – RubenHerman
    Commented Jul 26, 2018 at 11:40
  • 1
    my point, as complemented by @Singh-amarjeet, is that you can have problems if the column (problably not the table, since it's been created already) has a name like "system" ou "column", or any other name that can be confused with a key word. Try, just to see, with an mame like "tb_whatever", or if you're confident about it, then it should indeed work, and i can't say more over pseudo-code, need database and table declarations to see
    – SammuelMiranda
    Commented Jul 26, 2018 at 12:29
0


@SammuelMiranda has just asked the same just now. It matters if you are using reserved keyword as table or column name also.
you can check this link Add a column with a default value to an existing table in SQL Server

Improve this answer
0

As I expected, updating my SQL Server Management Studio to version 17.8.1 solved my issue.
After updating, I was able to run this command without any problem.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.