September 13, 2010 at 4:38 am
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')
September 13, 2010 at 4:44 am
auto_increment is access or mysql syntax i think it's not mapped to the SQL equivalent of IDENTITY when you run it in SSMS; simply replace that with IDENTITY(1,1) and your script will work fine.
Lowell
September 13, 2010 at 7:16 am
Hi, thanks. Should it look like this:
seqno INT not NULL identity(1,1) PRIMARY KEY,
September 13, 2010 at 7:18 am
The script won't work fine, because there are other syntax errors in it. Like the BLOB data type.
This looks like an MS-Access script. Is it? If so, you can't just run it on SQL Server without modifications.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 13, 2010 at 7:19 am
Hi, got his when updated to Insert (1,1)
Server: Msg 2715, Level 16, State 7, Line 13
Column or parameter #7: Cannot find data type blob.
September 13, 2010 at 7:36 am
Hi, this is a script to upsize an access db to sql. I'm not really sure about the details. I run across these error during the process. Thanks.
September 13, 2010 at 7:43 am
The columns that are defined as BLOB data type, what are they supposed to be storing?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply