此语句在2005上执行正常,但在2000上有报错,提示如下,还请大师们帮助看看应该如何修改,才可以在2000上正常执行呢?多谢多谢!!服务器: 消息 170,级别 15,状态 1,行 6
第 6 行: 'try' 附近有语法错误。
服务器: 消息 156,级别 15,状态 1,行 229
在关键字 'end' 附近有语法错误。
服务器: 消息 195,级别 15,状态 1,行 240
'ERROR_LINE' 不是可以识别的 函数名。
服务器: 消息 195,级别 15,状态 1,行 241
'ERROR_MESSAGE' 不是可以识别的 函数名。--------------------------------------------------------------------------begin tryBEGIN TRANSACTIONdeclare @i integer
declare @rootdeptid varchar(40)
declare @deptid varchar(40)
declare @childdeptid varchar(40)
declare @deptintid varchar(40)
declare @childdeptintid varchar(40)
declare @deptname varchar(40)
declare @doctablename varchar(40)
declare @idxtablename varchar(40)
declare @batchid varchar(10)
declare @batchname varchar(40)
declare @appid varchar(2)
declare @batchlevel integer
declare @tablename varchar(40)
declare @sql varchar(1000)
declare @fd_idx varchar(40)
declare @srcsvr varchar(40)
declare @destsvr varchar(40)
declare @ftpsvr varchar(40)
declare @errmsg varchar(200)
declare @errno integer
set @errno = 0------------------------------------------------
-- 插入虚拟机构select @deptid=ltrim(rtrim(deptid)), @deptname=deptname, @deptintid=ltrim(rtrim(deptintid))
from department where parent_deptid='&'set @childdeptid = @deptid+'00'
set @batchid = @childdeptid
set @batchname = @deptname
set @rootdeptid = @deptidif exists(select * from department where deptid=@childdeptid)
begin
set @errmsg = '机构已经存在:'+@childdeptid
raiserror(@errmsg, 11, 1)
end
else
begin
set @i = 0
while @i<100
begin
set @childdeptintid = @deptintid+right('00'+rtrim(ltrim(str(@i, 3))),2)
IF EXISTS(select deptintid from department where deptintid=@childdeptintid)
set @i = @i+1
else
break
end
print @childdeptintid
set @sql = 'insert into department(deptid, parent_deptid, deptname, deptlevel, deptintid)
values('''+@childdeptid+''', '''+@deptid+''', '''+@deptname+''', 2, '''+@childdeptintid+''')'
print @sql
exec(@sql)
endset @deptid = @childdeptid
set @deptintid = @childdeptintid
set @childdeptid = @deptid+'00'
if exists(select * from department where deptid=@childdeptid)
begin
set @errmsg = '机构已经存在:'+@childdeptid
raiserror(@errmsg, 11, 1)
end
begin
set @i = 0
while @i<100
begin
set @childdeptintid = @deptintid+right('00'+rtrim(ltrim(str(@i, 3))),2)
IF EXISTS(select deptintid from department where deptintid=@childdeptintid)
set @i = @i+1
else
break
end
print @childdeptintid
set @sql = 'insert into department(deptid, parent_deptid, deptname, deptlevel, deptintid)
values('''+@childdeptid+''', '''+@deptid+''', '''+@deptname+''', 3, '''+@childdeptintid+''')'
print @sql
exec(@sql)
end
------------------------------------------------
-- 插入虚拟数据集select @srcsvr=src_imageserver, @destsvr=dest_imageserver, @ftpsvr=ftpserver from batch where appid='N'DECLARE batch_cursor CURSOR FOR
select distinct ltrim(rtrim(appid)) from application where batch_level=2 and not(appid in('A', 'B', 'C', 'Y'))
OPEN batch_cursorFETCH NEXT FROM batch_cursor INTO @appid
WHILE @@FETCH_STATUS = 0
BEGIN print '--------------------'
print 'appid: ' + @appid select @fd_idx=ltrim(rtrim(fd_connect)) from application where appid=@appid
-- 已经存在的数据集
select top 1 @doctablename='doc'+ltrim(rtrim(appid))+ltrim(rtrim(docbatchid)),
@idxtablename='idx'+ltrim(rtrim(appid))+ltrim(rtrim(idxbatchid))
from batch where appid=@appid order by batchid
set @sql = 'insert into batch(appid, batchid, batchname, deptid, ifused, src_imageserver, dest_imageserver,
docbatchid, annbatchid, idxbatchid, adtbatchid, seqbatchid, ftpserver)
values('''+@appid+''', '''+@batchid+''', '''+@batchname+''', '''+@batchid+''', ''1'', '''+@srcsvr+''','''+@destsvr+''', '''+
@batchid+''', '''+@rootdeptid+''', '''+@batchid+''', '''+@rootdeptid+''', '''+@rootdeptid+''', '''+@ftpsvr+''' )'
print @sql
exec(@sql)
set @tablename = 'DOC'+@appid+ltrim(rtrim(@batchid)) if exists (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@tablename) AND type in (N'U'))
begin
set @errmsg = '数据集已经存在:'+@tablename
raiserror(@errmsg, 11, 1)
end
set @sql = 'select * into '+@tablename+' from '+@doctablename+' where 0=1'
print @sql
exec(@sql)
set @sql = 'ALTER TABLE ' + @tablename+' WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
( [DOCDATE],
[DOCID],
[PARTID],
[PAGEID],
[SIDEFLAG])'
print @sql
exec(@sql)
set @sql = 'Create View PRT'+@appid+ltrim(rtrim(@batchid))+
' as Select DISTINCT DOCDATE, DOCID, PARTID, MIN(ORDERID) AS ORDERID '+
' from '+@tablename +' GROUP BY DOCDATE, DOCID, PARTID'
print @sql
exec(@sql) set @tablename = @tablename+'XXX'
if exists (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@tablename) AND type in (N'U'))
begin
set @errmsg = '数据集已经存在:'+@tablename
raiserror(@errmsg, 11, 1)
end set @sql = 'select * into '+@tablename+' from '+@doctablename+'xxx where 0=1'
print @sql
exec(@sql)
set @sql = 'ALTER TABLE ' + @tablename+' WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
( [jobid],
[DOCDATE],
[DOCID],
[PARTID],
[PAGEID],
[SIDEFLAG])'
print @sql
exec(@sql)
set @tablename = 'IDX'+@appid+ltrim(rtrim(@batchid))
if exists (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@tablename) AND type in (N'U'))
begin
set @errmsg = '数据集已经存在:'+@tablename
raiserror(@errmsg, 11, 1)
end set @sql = 'select * into '+@tablename+' from '+@idxtablename+' where 0=1'
print @sql
exec(@sql)
set @sql = 'ALTER TABLE '+@tablename+' WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
[DOCDATE],
[DOCID],
[INDEXID] )'
print @sql
exec(@sql)
set @sql = 'CREATE INDEX IX_'+@tablename+' ON '+@tablename+' (['+@fd_idx+'], [sysid], [status])'
print @sql
exec(@sql)
set @tablename = @tablename+'XXX'
if exists (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@tablename) AND type in (N'U'))
begin
set @errmsg = '数据集已经存在:'+@tablename
raiserror(@errmsg, 11, 1)
end set @sql = 'select * into '+@tablename+' from '+@idxtablename+'xxx where 0=1'
print @sql
exec(@sql) set @sql = 'ALTER TABLE '+@tablename+' WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
[jobid],
[DOCDATE],
[DOCID],
[INDEXID] )'
print @sql
exec(@sql)
FETCH NEXT FROM batch_cursor INTO @appid
ENDCLOSE batch_cursor
DEALLOCATE batch_cursorCOMMIT TRANSACTIONend trybegin catch
ROLLBACK TRANSACTION
if Cursor_Status('variable', '@batch_cursor') >= 0
begin
CLOSE batch_cursor
DEALLOCATE batch_cursor
end print '错误行:'+str(ERROR_LINE())
print '错误:'+ERROR_MESSAGE()
end catchGO
--|--------------------------------------------------------------------------------
第 6 行: 'try' 附近有语法错误。
服务器: 消息 156,级别 15,状态 1,行 229
在关键字 'end' 附近有语法错误。
服务器: 消息 195,级别 15,状态 1,行 240
'ERROR_LINE' 不是可以识别的 函数名。
服务器: 消息 195,级别 15,状态 1,行 241
'ERROR_MESSAGE' 不是可以识别的 函数名。--------------------------------------------------------------------------begin tryBEGIN TRANSACTIONdeclare @i integer
declare @rootdeptid varchar(40)
declare @deptid varchar(40)
declare @childdeptid varchar(40)
declare @deptintid varchar(40)
declare @childdeptintid varchar(40)
declare @deptname varchar(40)
declare @doctablename varchar(40)
declare @idxtablename varchar(40)
declare @batchid varchar(10)
declare @batchname varchar(40)
declare @appid varchar(2)
declare @batchlevel integer
declare @tablename varchar(40)
declare @sql varchar(1000)
declare @fd_idx varchar(40)
declare @srcsvr varchar(40)
declare @destsvr varchar(40)
declare @ftpsvr varchar(40)
declare @errmsg varchar(200)
declare @errno integer
set @errno = 0------------------------------------------------
-- 插入虚拟机构select @deptid=ltrim(rtrim(deptid)), @deptname=deptname, @deptintid=ltrim(rtrim(deptintid))
from department where parent_deptid='&'set @childdeptid = @deptid+'00'
set @batchid = @childdeptid
set @batchname = @deptname
set @rootdeptid = @deptidif exists(select * from department where deptid=@childdeptid)
begin
set @errmsg = '机构已经存在:'+@childdeptid
raiserror(@errmsg, 11, 1)
end
else
begin
set @i = 0
while @i<100
begin
set @childdeptintid = @deptintid+right('00'+rtrim(ltrim(str(@i, 3))),2)
IF EXISTS(select deptintid from department where deptintid=@childdeptintid)
set @i = @i+1
else
break
end
print @childdeptintid
set @sql = 'insert into department(deptid, parent_deptid, deptname, deptlevel, deptintid)
values('''+@childdeptid+''', '''+@deptid+''', '''+@deptname+''', 2, '''+@childdeptintid+''')'
print @sql
exec(@sql)
endset @deptid = @childdeptid
set @deptintid = @childdeptintid
set @childdeptid = @deptid+'00'
if exists(select * from department where deptid=@childdeptid)
begin
set @errmsg = '机构已经存在:'+@childdeptid
raiserror(@errmsg, 11, 1)
end
begin
set @i = 0
while @i<100
begin
set @childdeptintid = @deptintid+right('00'+rtrim(ltrim(str(@i, 3))),2)
IF EXISTS(select deptintid from department where deptintid=@childdeptintid)
set @i = @i+1
else
break
end
print @childdeptintid
set @sql = 'insert into department(deptid, parent_deptid, deptname, deptlevel, deptintid)
values('''+@childdeptid+''', '''+@deptid+''', '''+@deptname+''', 3, '''+@childdeptintid+''')'
print @sql
exec(@sql)
end
------------------------------------------------
-- 插入虚拟数据集select @srcsvr=src_imageserver, @destsvr=dest_imageserver, @ftpsvr=ftpserver from batch where appid='N'DECLARE batch_cursor CURSOR FOR
select distinct ltrim(rtrim(appid)) from application where batch_level=2 and not(appid in('A', 'B', 'C', 'Y'))
OPEN batch_cursorFETCH NEXT FROM batch_cursor INTO @appid
WHILE @@FETCH_STATUS = 0
BEGIN print '--------------------'
print 'appid: ' + @appid select @fd_idx=ltrim(rtrim(fd_connect)) from application where appid=@appid
-- 已经存在的数据集
select top 1 @doctablename='doc'+ltrim(rtrim(appid))+ltrim(rtrim(docbatchid)),
@idxtablename='idx'+ltrim(rtrim(appid))+ltrim(rtrim(idxbatchid))
from batch where appid=@appid order by batchid
set @sql = 'insert into batch(appid, batchid, batchname, deptid, ifused, src_imageserver, dest_imageserver,
docbatchid, annbatchid, idxbatchid, adtbatchid, seqbatchid, ftpserver)
values('''+@appid+''', '''+@batchid+''', '''+@batchname+''', '''+@batchid+''', ''1'', '''+@srcsvr+''','''+@destsvr+''', '''+
@batchid+''', '''+@rootdeptid+''', '''+@batchid+''', '''+@rootdeptid+''', '''+@rootdeptid+''', '''+@ftpsvr+''' )'
print @sql
exec(@sql)
set @tablename = 'DOC'+@appid+ltrim(rtrim(@batchid)) if exists (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@tablename) AND type in (N'U'))
begin
set @errmsg = '数据集已经存在:'+@tablename
raiserror(@errmsg, 11, 1)
end
set @sql = 'select * into '+@tablename+' from '+@doctablename+' where 0=1'
print @sql
exec(@sql)
set @sql = 'ALTER TABLE ' + @tablename+' WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
( [DOCDATE],
[DOCID],
[PARTID],
[PAGEID],
[SIDEFLAG])'
print @sql
exec(@sql)
set @sql = 'Create View PRT'+@appid+ltrim(rtrim(@batchid))+
' as Select DISTINCT DOCDATE, DOCID, PARTID, MIN(ORDERID) AS ORDERID '+
' from '+@tablename +' GROUP BY DOCDATE, DOCID, PARTID'
print @sql
exec(@sql) set @tablename = @tablename+'XXX'
if exists (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@tablename) AND type in (N'U'))
begin
set @errmsg = '数据集已经存在:'+@tablename
raiserror(@errmsg, 11, 1)
end set @sql = 'select * into '+@tablename+' from '+@doctablename+'xxx where 0=1'
print @sql
exec(@sql)
set @sql = 'ALTER TABLE ' + @tablename+' WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
( [jobid],
[DOCDATE],
[DOCID],
[PARTID],
[PAGEID],
[SIDEFLAG])'
print @sql
exec(@sql)
set @tablename = 'IDX'+@appid+ltrim(rtrim(@batchid))
if exists (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@tablename) AND type in (N'U'))
begin
set @errmsg = '数据集已经存在:'+@tablename
raiserror(@errmsg, 11, 1)
end set @sql = 'select * into '+@tablename+' from '+@idxtablename+' where 0=1'
print @sql
exec(@sql)
set @sql = 'ALTER TABLE '+@tablename+' WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
[DOCDATE],
[DOCID],
[INDEXID] )'
print @sql
exec(@sql)
set @sql = 'CREATE INDEX IX_'+@tablename+' ON '+@tablename+' (['+@fd_idx+'], [sysid], [status])'
print @sql
exec(@sql)
set @tablename = @tablename+'XXX'
if exists (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@tablename) AND type in (N'U'))
begin
set @errmsg = '数据集已经存在:'+@tablename
raiserror(@errmsg, 11, 1)
end set @sql = 'select * into '+@tablename+' from '+@idxtablename+'xxx where 0=1'
print @sql
exec(@sql) set @sql = 'ALTER TABLE '+@tablename+' WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
[jobid],
[DOCDATE],
[DOCID],
[INDEXID] )'
print @sql
exec(@sql)
FETCH NEXT FROM batch_cursor INTO @appid
ENDCLOSE batch_cursor
DEALLOCATE batch_cursorCOMMIT TRANSACTIONend trybegin catch
ROLLBACK TRANSACTION
if Cursor_Status('variable', '@batch_cursor') >= 0
begin
CLOSE batch_cursor
DEALLOCATE batch_cursor
end print '错误行:'+str(ERROR_LINE())
print '错误:'+ERROR_MESSAGE()
end catchGO
--|--------------------------------------------------------------------------------
这两个函数应该是05新加的吧
可以使用@@ERROR判断,程序太长,没兴趣看完。建议升级数据库,一劳永逸。
2000没有try catch语法,这是2005的新语法,看提示也没有ERROR_LINE()以及ERROR_MESSAGE()函数,这些都去掉吧,2000一般是用@@error来控制错误的,不过很麻烦,一个简单的方法是在事务开始前开启set xact_abort on选项,可以提示数据库执行遇到错误时全部回滚