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?

Alex K.
  • 171,639
  • 30
  • 264
  • 288
RubenHerman
  • 1,674
  • 6
  • 23
  • 42
  • How/within what are you executing this statement? – Alex K. Jul 26 '18 at 11:28
  • I just created a new query in my SQL server management studio (v17.7) – RubenHerman Jul 26 '18 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 Jul 26 '18 at 11:32
  • This is the only command. There's not even a comment before it – RubenHerman Jul 26 '18 at 11:33
  • 1
    Did you try to execute it, not to "hover over"? – sepupic Jul 26 '18 at 11:36
  • I do have an update available for SQL server management studio... Could it be that it's a bug? – RubenHerman Jul 26 '18 at 11:36
  • I tried executing it, but it's giving an "incorrect syntax" error there as well – RubenHerman Jul 26 '18 at 11:37
  • 1
    Open new query window and paste the code there. Try to execute it. The statement is CORRECT – sepupic Jul 26 '18 at 11:38
  • Possible duplicate of [Add a column with a default value to an existing table in SQL Server](https://stackoverflow.com/questions/92082/add-a-column-with-a-default-value-to-an-existing-table-in-sql-server) – Chetan Sanghani Jul 26 '18 at 11:43
  • Tried with a new window, but unfortunately... same problem – RubenHerman Jul 26 '18 at 11:43
  • Try to execute your statement in sqlcmd. – sepupic Jul 26 '18 at 11:49
  • Escape `[Whatevertable]` and `[WhateverColumn]` (using the demonstrated brackets) if either contain spaces, special characters or correspond with a T-SQL keyword. Or just generally always, as it's good practice. Also, when in doubt, retype the whole command from scratch instead of copy-pasting anything, as you may have invisible characters somewhere. – Jeroen Mostert Jul 26 '18 at 11:58

4 Answers4

5

Try this:

ALTER TABLE Whatever
ADD WhateverColumn tinyint NOT NULL DEFAULT 0
Fahmi
  • 37,315
  • 5
  • 22
  • 31
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?

SammuelMiranda
  • 420
  • 4
  • 29
  • I executed this exact command in the past, but on a different table. I guess the table name shouldn't be the issue? – RubenHerman Jul 26 '18 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 Jul 26 '18 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

Singh Amarjeet
  • 45
  • 10
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.

RubenHerman
  • 1,674
  • 6
  • 23
  • 42