Incorrect syntax - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Incorrect syntax

Author

Topic

mwtoty
Starting Member

5 Posts

Posted - 2010-09-13 : 07:09:58

Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'auto_increment'.

CREATE TABLE tauditlog
(
seqno INT not NULL auto_increment PRIMARY KEY,
id INT NULL ,
idtype CHAR(1) NULL ,
rid INT NULL ,
menu CHAR(1) NULL ,
MODE CHAR(1) NULL ,
rdate CHAR(12) NULL
)

CREATE TABLE tcommand (
c_regtime char (14) NULL ,
c_key int NULL ,
c_type char (1) NULL ,
c_gid int NULL ,
c_time char (14) NULL ,
c_retry int NULL ,
c_data blob NULL ,
c_result char (1) NULL
)

CREATE TABLE tconfig (
maxuser int NULL ,
minvid int NULL ,
maxvid int NULL ,
fpnum char (1) NULL ,
autodn char (1) NULL ,
dntime char (4) NULL ,
autoup char (1) NULL ,
groupid char (1) NULL ,
gateid char (1) NULL ,
userid char (1) NULL ,
passwd char (1) NULL ,
attend char (1) NULL ,
tsockport int NULL ,
csockport int NULL ,
polltime int NULL ,
serverip varchar (20) NULL ,
savemode char (1) NULL
)

CREATE TABLE tenter (
e_date char (8) NOT NULL ,
e_time char (6) NOT NULL ,
g_id int NOT NULL ,
e_id int NOT NULL ,
e_name varchar (30) NULL ,
e_idno varchar (30) NULL ,
e_group smallint NULL ,
e_user char (1) NULL ,
e_mode char (1) NULL ,
e_type char (1) NULL ,
e_result char (1) NULL
)

CREATE TABLE tgate (
id int NOT NULL ,
name varchar (30) NULL ,
reg_date char (12) NULL ,
floor int NULL ,
place varchar (30) NULL ,
block char (1) NULL ,
userctrl char (1) NULL ,
passtime char (8) NULL ,
version varchar (4) NULL ,
admin blob NULL ,
lastup char (14) NULL ,
remark varchar (140) NULL,
antipass int NULL,
antipass_level int NULL,
antipass_mode int NULL
)

CREATE TABLE tgatelog (
e_date char (8) NULL ,
e_time char (6) NULL ,
id int NULL ,
bstatus char (1) NULL
)

CREATE TABLE tgroup (
id smallint NOT NULL ,
name varchar (30) NULL ,
reg_date char (12) NULL ,
timelimit char (8) NULL ,
gate_id blob NULL ,
remark varchar (50) NULL
)

CREATE TABLE tuser (
id int NOT NULL ,
name varchar (30) NULL ,
reg_date char (12) NULL ,
datelimit char (17) NULL ,
idno varchar (30) NULL ,
badmin char (1) NULL ,
padmin int NULL ,
company varchar (30) NULL ,
dept varchar (30) NULL ,
phone varchar (50) NULL ,
group_id smallint NULL ,
cantgate blob NULL ,
timegate blob NULL ,
validtype char (1) NULL ,
pwd varchar (8) NULL ,
cancard char (1) NULL ,
cardnum varchar (20) NULL ,
identify char (1) NULL ,
seculevel char (1) NULL ,
fpdata blob NULL ,
fpimage blob NULL ,
fpname blob NULL ,
face blob NULL ,
voice blob NULL ,
remark varchar (50) NULL ,
antipass_state int NULL,
antipass_lasttime char(14) NULL
)

CREATE TABLE tvisited (
id int NULL ,
name varchar (30) NULL ,
reg_date char (12) NULL ,
datelimit char (17) NULL ,
timelimit char (8) NULL ,
out_date char (12) NULL ,
idno varchar (30) NULL ,
contact varchar (50) NULL ,
company varchar (30) NULL ,
dept varchar (30) NULL ,
phone varchar (50) NULL ,
address varchar (50) NULL ,
group_id smallint NULL ,
cantgate blob NULL ,
timegate blob NULL ,
validtype char (1) NULL ,
pwd varchar (8) NULL ,
cancard char (1) NULL ,
cardnum varchar (20) NULL ,
identify char (1) NULL ,
seculevel char (1) NULL ,
fpdata blob NULL ,
fpimage blob NULL ,
fpname blob NULL ,
face blob NULL ,
id_image blob NULL ,
voice blob NULL ,
remark varchar (50) NULL
)

CREATE TABLE tvisitor (
id int NOT NULL ,
name varchar (30) NULL ,
reg_date char (12) NULL ,
datelimit char (17) NULL ,
timelimit char (8) NULL ,
out_date char (12) NULL ,
idno varchar (30) NULL ,
contact varchar (50) NULL ,
company varchar (30) NULL ,
dept varchar (30) NULL ,
phone varchar (50) NULL ,
address varchar (50) NULL ,
group_id smallint NULL ,
cantgate blob NULL ,
timegate blob NULL ,
validtype char (1) NULL ,
pwd varchar (8) NULL ,
cancard char (1) NULL ,
cardnum varchar (20) NULL ,
identify char (1) NULL ,
seculevel char (1) NULL ,
fpdata blob NULL ,
fpimage blob NULL ,
fpname blob NULL ,
face blob NULL ,
id_image blob NULL ,
voice blob NULL ,
remark varchar (50) NULL
)

ALTER TABLE tenter ADD
CONSTRAINT PK_tenter PRIMARY KEY CLUSTERED
(
e_date,
e_time,
g_id,
e_id
)

ALTER TABLE tgate ADD
CONSTRAINT PK_tgate PRIMARY KEY CLUSTERED
(
id
)

ALTER TABLE tgroup ADD
CONSTRAINT PK_tgroup PRIMARY KEY CLUSTERED
(
id
)

ALTER TABLE tuser ADD
CONSTRAINT PK_tuser PRIMARY KEY CLUSTERED
(
id
)

ALTER TABLE tvisitor ADD
CONSTRAINT PK_tvisitor PRIMARY KEY CLUSTERED
(
id
)

Insert into tgroup (id, name, reg_date, timelimit) values (0, 'None', '197008130000', '00002359')
Insert into tgroup (id, name, reg_date, timelimit) values (1, 'All', '197008130000', '00002359')
Insert into tconfig values (9999, 9999, 9999, '3', '0', '0300', '1', '2', '4', '4', '8', '0', 2201, 2202, 5, '127.0.0.1', '0')

Sachin.Nand

2937 Posts

Posted - 2010-09-13 : 07:19:38

Are you using SQL SERVER?
If yes then look below for the part marked in red.


CREATE TABLE tauditlog
(
seqno INT not NULL identity(1,1)PRIMARY KEY,
id INT NULL ,
idtype CHAR(1) NULL ,
rid INT NULL ,
menu CHAR(1) NULL ,
MODE CHAR(1) NULL ,
rdate CHAR(12) NULL
)

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

mwtoty
Starting Member

5 Posts

Posted - 2010-09-13 : 09:11:16

Thanks for the advice. Yes, SQL server. Are these missing parts to the statement?

mwtoty
Starting Member

5 Posts

Posted - 2010-09-13 : 09:13:14

Hi, got the message below.

Server: Msg 2715, Level 16, State 7, Line 13
Column or parameter #7: Cannot find data type blob.

Sachin.Nand

2937 Posts

Posted - 2010-09-13 : 09:15:45

quote:
Originally posted by mwtoty

Hi, got the message below.

Server: Msg 2715, Level 16, State 7, Line 13
Column or parameter #7: Cannot find data type blob.

What kind of data is going to be stored in blob field?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

mwtoty
Starting Member

5 Posts

Posted - 2010-09-13 : 09:34:36

I think these are text data containing biometric records. This script creates new tables on a newly created DB.

Kristen
Test

22859 Posts

Posted - 2010-09-13 : 10:19:28

CREATE TABLE tcommand (
c_regtime char (14) NULL ,
c_key int NULL ,
c_type char (1) NULL ,
c_gid int NULL ,
c_time char (14) NULL ,
c_retry int NULL ,
c_data blob NULL ,
c_result char (1) NULL 
)

BLOB is not a valid datatype. Use VARCHAR(MAX) if it is Text that you are storing, otherwise VARBINARY(MAX) or somesuch.

Maybe you should be using an upsizing tool rather than jsut taking a script from some other flavour of database (Access perhaps?) and trying to run it?#

For example, I doubt that CHAR is desirable as a datatype for some of these fields, and more likely VARCHAR would be more suitable; the dates being stored in CHAR should be in DATE or DATETIME datatypes and so on.

mwtoty
Starting Member

5 Posts

Posted - 2010-09-13 : 10:41:24

Thank you great help. Not really familiar with blob. Have a question, if the data is fingerprint data or image. Does blob best fits these types of data?

Kristen
Test

22859 Posts

Posted - 2010-09-13 : 14:25:28

I have no idea what BLOB is used for in your tables. It could be plain text, or it might need or be binary. The acronym BLOB stands for "Binary Large Object" ... so on that definition alone it would need to be Binary ... but if the data is text storing it as a text object will make data manipulation much easier

Alternatively you might want to store them in external files, and then just store the path + name of the file in the database.

nheidorn
Starting Member

28 Posts

Posted - 2010-09-13 : 17:43:08

Based on some casual Googling, those datatypes look like DB2 or Oracle instead of SQL Server.

Kristen
Test

22859 Posts

Posted - 2010-09-14 : 03:23:32

Oracle would be varchar2 perhaps?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-14 : 05:19:17

Are you moving the tables from mysql to SQL Server?

Madhivanan

Failing to plan is Planning to fail