更新text类型的难道远程更新不了吗? 它提示的错误是: 务器: 消息 117,级别 15,状态 1,行 3 The object name 'srv_lnk.newjoyo.dbo.tt002.' contains more than the maximum number of prefixes. The maximum is 3. 服务器: 消息 117,级别 15,状态 1,行 3 The number name 'srv_lnk.newjoyo.dbo.tt002.tt002' contains more than the maximum number of prefixes. The maximum is 3.超过三列有什么办法能更新呀!,谢谢
update srvlnk.db.dbo.tt002 set tt002_02='' where ...改为: update a set tt002_02='' from srvlnk.db.dbo.tt002 a where ...
--在我的sql 2000+sp3的电脑上测试,如果没有表中没有主键/唯一键--则更新的错误提示是不能定位--加上主键/唯一键后,下面的两种写法都支持update srvlnk.db.dbo.tt002 set tt002_02='' where ...update a set tt002_02='' from srvlnk.db.dbo.tt002 a where ...
declare @ptrval binary(16) --用来写text值的指针 --初始化变量 set @v_textone ='NULL' set @v_texttwo ='NULL' set @v_textthree ='NULL' set @v_textfour ='NULL' set @v_textfive ='NULL' set @v_textsix ='NULL' set @v_textseven ='NULL' set @v_texteight ='NULL' set @v_textnine ='NULL' set @v_textten ='NULL' set XACT_ABORT on set ansi_nulls on set ansi_warnings on --首先需要从表信息中提取属于text类型的字段 declare cur_fieldname cursor for select fieldname from tableinfo where tablename=@v_tablename and fieldtype='text' --触发器取text字段名 open cur_fieldname fetch next from cur_fieldname into @v_fieldname set @v_integer=1 while(@@fetch_status=0) begin set @v_insertSql=@v_insertsql+','+@v_fieldname --声明触发器取得text字段的内容 exec('declare cur_t1 cursor for select coalesce('+@v_fieldname+','''') from '+@v_tablename+' where 1=1 and '+@v_wheresql) open cur_t1 fetch next from cur_t1 into @v_outValue while(@@fetch_status=0) begin if @v_integer=1 set @v_textone =replace(@v_outValue,'''','''''') if @v_integer=2 set @v_texttwo =replace(@v_outValue,'''','''''') if @v_integer=3 set @v_textthree =replace(@v_outValue,'''','''''') if @v_integer=4 set @v_textfour =replace(@v_outValue,'''','''''') if @v_integer=5 set @v_textfive =replace(@v_outValue,'''','''''') if @v_integer=6 set @v_textsix =replace(@v_outValue,'''','''''') if @v_integer=7 set @v_textseven=replace(@v_outValue,'''','''''') if @v_integer=8 set @v_texteight =replace(@v_outValue,'''','''''') if @v_integer=9 set @v_textnine =replace(@v_outValue,'''','''''') if @v_integer=10 set @v_textten =replace(@v_outValue,'''','''''') fetch next from cur_t1 into @v_outValue end deallocate cur_t1 set @v_integer=@v_integer+1 fetch next from cur_fieldname into @v_fieldname end deallocate cur_fieldname
insert into [srv_lnk].[newjoyo].[dbo].TT002(TT002_02,TT002_03,TT002_04,TT002_05) values(@v_insertSql+')'+@v_insertvalueSql,0,@v_memo,''+CONVERT(char(24),getdate(),21)+'') select @v_ttid=tt002_01 from tt002 --写text值 select @ptrval = TEXTPTR(tt002_02) from srv_lnk.newjoyo.dbo.tt002 where tt002_01=@v_ttid if @v_textone<>'NULL' begin updatetext [srv_lnk].[newjoyo.dbo].tt002.tt002_02 @ptrval null 0 ',''' updatetext tt002.tt002_02 @ptrval null 0 @v_textone updatetext tt002.tt002_02 @ptrval null 0 '''' end if @v_texttwo<>'NULL' begin updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 ',''' updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 @v_texttwo updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 '''' end if @v_textthree<>'NULL' begin updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 ',''' updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 @v_textthree updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 '''' end if @v_textfour<>'NULL' begin updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 ',''' updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 @v_textfour updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 '''' end if @v_textfive<>'NULL' begin updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 ',''' updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 @v_textfive updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 '''' end if @v_textsix<>'NULL' begin updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 ',''' updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 @v_textsix updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 '''' end if @v_textseven<>'NULL' begin updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 ',''' updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 @v_textseven updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 '''' end if @v_texteight<>'NULL' begin updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 ',''' updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 @v_texteight updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 '''' end if @v_textnine<>'NULL' begin updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 ',''' updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 @v_textnine updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 '''' end if @v_textten<>'NULL' begin updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 ',''' updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 @v_textten updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 '''' end updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 ')'GO
主要的问题就在于: select @ptrval = TEXTPTR(tt002_02) from srv_lnk.newjoyo.dbo.tt002 where tt002_01=@v_ttid if @v_textone<>'NULL' begin updatetext [srv_lnk].[newjoyo.dbo].tt002.tt002_02 @ptrval null 0 ',''' updatetext tt002.tt002_02 @ptrval null 0 @v_textone updatetext tt002.tt002_02 @ptrval null 0 '''' end这部分.
该库中是否有 tt002 这个表,它的所有者是否是dbo
select * from srv_lnk.newjoyo.dbo.tt002数据也存在,
查执行这句:
declare @ptrval binary(16)
select @ptrval = TEXTPTR(tt002_02) from srv_lnk.newjoyo.dbo.tt002.tt002
updatetext srv_lnk.newjoyo.dbo.tt002.tt002.tt002_02 @ptrval null 0 'asdfasdfdas'
就不行.
它提示的错误是:
务器: 消息 117,级别 15,状态 1,行 3
The object name 'srv_lnk.newjoyo.dbo.tt002.' contains more than the maximum number of prefixes. The maximum is 3.
服务器: 消息 117,级别 15,状态 1,行 3
The number name 'srv_lnk.newjoyo.dbo.tt002.tt002' contains more than the maximum number of prefixes. The maximum is 3.超过三列有什么办法能更新呀!,谢谢
update a set tt002_02=''
from srvlnk.db.dbo.tt002 a
where ...
则要求你的表中有主键/唯一键,否则更新也会出错.
from srvlnk.db.dbo.tt002 a
where ...
updatetext 函数,不允许这样写,我把我的存储过程粘上来,您帮我看一下吧,谢谢--功能:传输程序取得text类型的存储过程
--参数:v_tablename,v_wheresql,v_insertsql,v_insertvaluesql,v_memo
--
--
CREATE procedure dbo.sp_insertValue
@v_tablename varchar(20), --表名称
@v_wheresql varchar(200), --查询条件
@v_insertSql varchar(8000), --新增的sql语句
@v_insertvalueSql varchar(8000), --新增的值sql语句
@v_memo varchar(40) --备注值
as
declare @v_outValue varchar(8000) --保存text值参数
declare @v_fieldname varchar(20) --text类型的字段名称
declare @v_sql varchar(1000)
declare @v_textone varchar(8000)
declare @v_texttwo varchar(8000)
declare @v_textthree varchar(8000)
declare @v_textfour varchar(8000)
declare @v_textfive varchar(8000)
declare @v_textsix varchar(8000)
declare @v_textseven varchar(8000)
declare @v_texteight varchar(8000)
declare @v_textnine varchar(8000)
declare @v_textten varchar(8000)
declare @v_integer int --根据@v_integer来分textvalue值
declare @v_ttid int
declare @ptrval binary(16) --用来写text值的指针 --初始化变量
set @v_textone ='NULL'
set @v_texttwo ='NULL'
set @v_textthree ='NULL'
set @v_textfour ='NULL'
set @v_textfive ='NULL'
set @v_textsix ='NULL'
set @v_textseven ='NULL'
set @v_texteight ='NULL'
set @v_textnine ='NULL'
set @v_textten ='NULL' set XACT_ABORT on
set ansi_nulls on
set ansi_warnings on
--首先需要从表信息中提取属于text类型的字段
declare cur_fieldname cursor for
select fieldname from tableinfo where tablename=@v_tablename and fieldtype='text'
--触发器取text字段名
open cur_fieldname
fetch next from cur_fieldname into @v_fieldname
set @v_integer=1
while(@@fetch_status=0)
begin
set @v_insertSql=@v_insertsql+','+@v_fieldname
--声明触发器取得text字段的内容
exec('declare cur_t1 cursor for select coalesce('+@v_fieldname+','''') from '+@v_tablename+' where 1=1 and '+@v_wheresql)
open cur_t1
fetch next from cur_t1 into @v_outValue
while(@@fetch_status=0)
begin
if @v_integer=1 set @v_textone =replace(@v_outValue,'''','''''')
if @v_integer=2 set @v_texttwo =replace(@v_outValue,'''','''''')
if @v_integer=3 set @v_textthree =replace(@v_outValue,'''','''''')
if @v_integer=4 set @v_textfour =replace(@v_outValue,'''','''''')
if @v_integer=5 set @v_textfive =replace(@v_outValue,'''','''''')
if @v_integer=6 set @v_textsix =replace(@v_outValue,'''','''''')
if @v_integer=7 set @v_textseven=replace(@v_outValue,'''','''''')
if @v_integer=8 set @v_texteight =replace(@v_outValue,'''','''''')
if @v_integer=9 set @v_textnine =replace(@v_outValue,'''','''''')
if @v_integer=10 set @v_textten =replace(@v_outValue,'''','''''') fetch next from cur_t1 into @v_outValue
end
deallocate cur_t1
set @v_integer=@v_integer+1
fetch next from cur_fieldname into @v_fieldname
end
deallocate cur_fieldname
insert into [srv_lnk].[newjoyo].[dbo].TT002(TT002_02,TT002_03,TT002_04,TT002_05) values(@v_insertSql+')'+@v_insertvalueSql,0,@v_memo,''+CONVERT(char(24),getdate(),21)+'')
select @v_ttid=tt002_01 from tt002
--写text值
select @ptrval = TEXTPTR(tt002_02) from srv_lnk.newjoyo.dbo.tt002 where tt002_01=@v_ttid
if @v_textone<>'NULL'
begin
updatetext [srv_lnk].[newjoyo.dbo].tt002.tt002_02 @ptrval null 0 ','''
updatetext tt002.tt002_02 @ptrval null 0 @v_textone
updatetext tt002.tt002_02 @ptrval null 0 ''''
end
if @v_texttwo<>'NULL'
begin
updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 ','''
updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 @v_texttwo
updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 ''''
end
if @v_textthree<>'NULL'
begin
updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 ','''
updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 @v_textthree
updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 ''''
end
if @v_textfour<>'NULL'
begin
updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 ','''
updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 @v_textfour
updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 ''''
end
if @v_textfive<>'NULL'
begin
updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 ','''
updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 @v_textfive
updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 ''''
end
if @v_textsix<>'NULL'
begin
updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 ','''
updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 @v_textsix
updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 ''''
end
if @v_textseven<>'NULL'
begin
updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 ','''
updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 @v_textseven
updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 ''''
end
if @v_texteight<>'NULL'
begin
updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 ','''
updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 @v_texteight
updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 ''''
end
if @v_textnine<>'NULL'
begin
updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 ','''
updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 @v_textnine
updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 ''''
end
if @v_textten<>'NULL'
begin
updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 ','''
updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 @v_textten
updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 ''''
end
updatetext [srv_lnk.newjoyo.dbo.tt002].tt002_02 @ptrval null 0 ')'GO
select @ptrval = TEXTPTR(tt002_02) from srv_lnk.newjoyo.dbo.tt002 where tt002_01=@v_ttid
if @v_textone<>'NULL'
begin
updatetext [srv_lnk].[newjoyo.dbo].tt002.tt002_02 @ptrval null 0 ','''
updatetext tt002.tt002_02 @ptrval null 0 @v_textone
updatetext tt002.tt002_02 @ptrval null 0 ''''
end这部分.