Getting error while creating table in SQL Server database using SVN, SVN execute the statement through SQLCMD and by default QUOTED_IDENTIFIER is OFF.
The error was Incorrect syntax near 'EMP_ID'
13> CREATE TABLE EMPLOYEE(
14>
EMP_ID NUMERIC(18,0) NOT NULL,
15> EMP_TYPE VARCHAR(10) NOT NULL,
16> FIRST_NAME VARCHAR(40) NULL,
17> LAST_NAME VARCHAR(40) NULL,
18> EMAIL_ID VARCHAR(50) NOT NULL,
19> MOBILE VARCHAR(40) NOT NULL,
20> CONSTRAINT PK_EMPLOYEE PRIMARY KEY ("EMP_ID", "EMP_TYPE")
21> );
22> GO
Msg 102, Level 15, State 1, Server DKBLAPT\MSSQLSERVER, Line 20
1>
15> EMP_TYPE VARCHAR(10) NOT NULL,
16> FIRST_NAME VARCHAR(40) NULL,
17> LAST_NAME VARCHAR(40) NULL,
18> EMAIL_ID VARCHAR(50) NOT NULL,
19> MOBILE VARCHAR(40) NOT NULL,
20> CONSTRAINT PK_EMPLOYEE PRIMARY KEY ("EMP_ID", "EMP_TYPE")
21> );
22> GO
Msg 102, Level 15, State 1, Server DKBLAPT\MSSQLSERVER, Line 20
1>
Incorrect syntax near 'EMP_ID'.
I tried to run through SQL Server Management Studio, same statement is executed successfully because of by default QUOTED_IDENTIFIER is ON.
SSMS
--> Tools
--> Option
--> Query Execution
--> SQL Sever
--> ANSI
--> SET QUOTED_IDENTIFIER [check box selected]
2> SET QUOTED_IDENTIFIER ON;
3> CREATE TABLE EMPLOYEE(
4> EMP_ID NUMERIC(18,0) NOT NULL,
5> EMP_TYPE VARCHAR(10) NOT NULL,
6> FIRST_NAME VARCHAR(40) NULL,
7> LAST_NAME VARCHAR(40) NULL,
8> EMAIL_ID VARCHAR(50) NOT NULL,
9> MOBILE VARCHAR(40) NOT NULL,
10> CONSTRAINT PK_EMPLOYEE PRIMARY KEY ("EMP_ID", "EMP_TYPE")
11> );
12> GO
(1 rows affected)
1>
Generally we create table without use of double quotes, we should use like below statement so that we could avoid error.
CREATE TABLE EMPLOYEE(
EMP_ID NUMERIC(18,0) NOT NULL,
EMP_TYPE VARCHAR(10) NOT NULL,
FIRST_NAME VARCHAR(40) NULL,
LAST_NAME VARCHAR(40) NULL,
EMAIL_ID VARCHAR(50) NOT NULL,
MOBILE VARCHAR(40) NOT NULL,
CONSTRAINT PK_EMPLOYEE PRIMARY KEY (EMP_ID, EMP_TYPE)
);
GO