各位高手,我遇到这样一个问题:
我在sql里面创建了一个存储过程,用来创建动态的建表语句,然后在delphi中用它创建access表,创建了很多表都没有问题,惟独一个tUser表没法创建,报的错误是“字段定义语法错误”,但是我换成其他表没有问题,而且我把程序中动态产生的create语句复制到access中也没有问题,不知道这是什么原因,请高手指点!!存储过程如下:CREATE PROCEDURE [dbo].[spCreateTableSQL]
@TableName varchar(100),
@CreateTableSQL varchar(4000) output
AS
Declare @strTemp varchar(4000)
Declare @strT varchar(4000)
Declare @FName varchar(4000)
Declare @FType varchar(2000)
Declare @FLength varchar(8000)
set @strTemp=''
Declare CreateTableSQL_cursor CURSOR FOR select name, xtype, length from syscolumns where id=object_id(''+@TableName+'')
Open CreateTableSQL_cursor
Fetch Next From CreateTableSQL_cursor Into @FName, @FType, @FLength
While @@Fetch_status=0
begin
if @FType in (175, 167, 35, 36, 99 ,231)
if @FLength<255
set @strT=N','+@FName+' text('+@FLength+')'
else
set @strT=N','+@FName+' memo'
else if @FType='61'
set @strT=N','+@FName+' date'
else if @FType='56'
set @strT=N','+@FName+' int'
set @strTemp=@strTemp+@strT
Fetch Next From CreateTableSQL_cursor Into @FName, @FType, @FLength
end
Close CreateTableSQL_cursor
Deallocate CreateTableSQL_cursor
set @strTemp=right(@strTemp,len(@strTemp)-1)
set @CreateTableSQL=N'Create Table '+@TableName+'('+@strTemp+')'
GO
tUser表的sql脚本如下:CREATE TABLE [dbo].[tUser] (
[ID] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[UserName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[CUserName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Password] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[CharatorCode] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[UserStatus] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[RoleID] [varchar] (8000) COLLATE Chinese_PRC_CI_AS NULL ,
[DepartmentID] [varchar] (4000) COLLATE Chinese_PRC_CI_AS NULL ,
[Description] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[DisplayOrder] [varchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[DutyLevel] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[DefaultRoleID] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[DefaultDepartmentID] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
我在sql里面创建了一个存储过程,用来创建动态的建表语句,然后在delphi中用它创建access表,创建了很多表都没有问题,惟独一个tUser表没法创建,报的错误是“字段定义语法错误”,但是我换成其他表没有问题,而且我把程序中动态产生的create语句复制到access中也没有问题,不知道这是什么原因,请高手指点!!存储过程如下:CREATE PROCEDURE [dbo].[spCreateTableSQL]
@TableName varchar(100),
@CreateTableSQL varchar(4000) output
AS
Declare @strTemp varchar(4000)
Declare @strT varchar(4000)
Declare @FName varchar(4000)
Declare @FType varchar(2000)
Declare @FLength varchar(8000)
set @strTemp=''
Declare CreateTableSQL_cursor CURSOR FOR select name, xtype, length from syscolumns where id=object_id(''+@TableName+'')
Open CreateTableSQL_cursor
Fetch Next From CreateTableSQL_cursor Into @FName, @FType, @FLength
While @@Fetch_status=0
begin
if @FType in (175, 167, 35, 36, 99 ,231)
if @FLength<255
set @strT=N','+@FName+' text('+@FLength+')'
else
set @strT=N','+@FName+' memo'
else if @FType='61'
set @strT=N','+@FName+' date'
else if @FType='56'
set @strT=N','+@FName+' int'
set @strTemp=@strTemp+@strT
Fetch Next From CreateTableSQL_cursor Into @FName, @FType, @FLength
end
Close CreateTableSQL_cursor
Deallocate CreateTableSQL_cursor
set @strTemp=right(@strTemp,len(@strTemp)-1)
set @CreateTableSQL=N'Create Table '+@TableName+'('+@strTemp+')'
GO
tUser表的sql脚本如下:CREATE TABLE [dbo].[tUser] (
[ID] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[UserName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[CUserName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Password] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[CharatorCode] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[UserStatus] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[RoleID] [varchar] (8000) COLLATE Chinese_PRC_CI_AS NULL ,
[DepartmentID] [varchar] (4000) COLLATE Chinese_PRC_CI_AS NULL ,
[Description] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[DisplayOrder] [varchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[DutyLevel] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[DefaultRoleID] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[DefaultDepartmentID] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
换吧
access就这样
换吧
access就这样
[RoleID] [varchar] (8000)
[DepartmentID] [varchar] (4000)这两个地长度有关喔,但我用了memo类型,而且我把长度改成几十也不行,太奇怪了,哪位高手知道,急
Create Table tblUser(ID text(10),UserName text(20),CUserName text(50),Password text(20),CharatorCode text(50),UserStatus text(1),RoleID memo,DepartmentID memo,Description text(100),DisplayOrder text(5),DutyLevel text(50),DefaultRoleID text(10),DefaultDepartmentID text(10))