帮忙看下这个存储过程(在线等)
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [sys].[sp_addextendedproperty]
@name sysname,
@value sql_variant = NULL,
@level0type varchar(128) = NULL,
@level0name sysname = NULL,
@level1type varchar(128) = NULL,
@level1name sysname = NULL,
@level2type varchar(128) = NULL,
@level2name sysname = NULL
as
declare @ret int
if datalength(@value) > 7500
begin
raiserror(15097,-1,-1)
return 1
end
if @name is null
begin
raiserror(15600,-1,-1, 'sp_addextendedproperty ')
return (1)
end
execute @ret = sys.sp_validname @name
if (@ret <> 0)
begin
raiserror(15600,-1,-1, 'sp_addextendedproperty ')
return (1)
end
BEGIN TRANSACTION
SAVE TRANSACTION SP_ADDEXTENDEDPROPERTY
begin
EXEC %%ExtendedPropertySet().AddValue(Name = @name, Value = @value, Level0type = @level0type, Level0name = @level0name, Level1type = @level1type, Level1name = @level1name, Level2type = @level2type, Level2name = @level2name)
IF @@error <> 0
begin
ROLLBACK TRANSACTION SP_ADDEXTENDEDPROPERTY
COMMIT TRANSACTION
return (1)
end
end
COMMIT TRANSACTION
return (0)
上面的存储过程在Sql Server 2005中是正常的,在Sql Server 2000中提示错误“第 39 行: '% ' 附近有语法错误。”
大家帮忙看看啊,谢谢
------解决方案--------------------EXEC %%ExtendedPropertySet().AddValue()
Sql2000是不支持这样的用法的,CLR是Sql2005的新功能
------解决方案--------------------在Sql Server 2005中也报错:
Msg 102, Level 15, State 1, Procedure sp_addextendedproperty, Line 39
Incorrect syntax near '% '.