Using SQLCMD Unable to create database when db name starts with a number

Question

I am using SQLserver 2008 R2 edition. I am trying to create a database using SQL CMD.

When I try to run the following command

Sqlcmd -S 10.2.202.213 -U sa -P 123@123 -Q "create database 125Build5"

I get the following error:

Msg 102, Level 15, State 1, Server WS-INBLR567, Line 1 Incorrect syntax near '125'.

If I use the database name that does not start with a number, something like 'Build5125', then it works. However I can create the database from SSMS without any problem.

Jason Goemaat · Accepted Answer · 2012-05-15 17:06:22Z

5

Try:

create database [125Build5]

The rules for identifiers state than they must begin with a letter among other things. You can get around that by enclosing them in brackets or double quotes.

edited May 15, 2012 at 17:06

answered May 15, 2012 at 6:00

Jason Goemaat

29.3k15 gold badges89 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SqlRyan · Accepted Answer · 2012-05-15 06:04:45Z

2

I'd really encourage you to re-think creating a database that starts with a number, as it's going to make cross-database selects miserable by always requiring a qualifier.

However, that said, just wrap the name of the database in brackets, like [123Whatever]. The brackets tell SQL to take the name as literal, so you can break whatever standard normal naming rules you want, including spaces or other special characters. The reason SSMS lets you start with a number is because the SQL statement it executes behind the scenes uses these brackets.

answered May 15, 2012 at 6:04

SqlRyan

34k37 gold badges120 silver badges207 bronze badges

2 Comments

Dunxton Over a year ago

Thanks but im not sure I understand how creating a DB starting with a number causes a problem. Can u pls elaborate ?

SqlRyan Over a year ago

@Dunxton: There's no requirement that prevents this, but it means you'll have to bracket-qualify the database name every time you do a select that crosses database boundaries. Also, poorly coded applications (including purchased vendor apps) don't always use brackets internally, and I've seen cases where the app throws an error when trying to access data in a database that's not named according to standards (showing an error similar to your own). You're welcome to name your database whatever you want, just as you can put spaces in your table names, but it requires a bit of extra care later.

Collectives™ on Stack Overflow

Using SQLCMD Unable to create database when db name starts with a number

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related