EXEC是否存在問題，請教大俠
我使用如下的語句得一錯誤
Server: Msg 510, Level 16, State 2, Line 1
Cannot create a worktable row larger than allowable maximum. Resubmit your query with the ROBUST PLAN hint.
語句如下
DECLARE @EXECSQL VARCHAR(500)
declare @sourceservername char(20)
declare @destinationservername char(20)
declare @dbname char(20)
set @sourceservername='FILESRV'
set @destinationservername='FILESRV'
set @dbname='WY2000_80'
SET @EXECSQL='INSERT INTO '+
LTRIM(RTRIM(@DESTINATIONSERVERNAME))+
'.WY2000.DBO.TRIGGERS (DBNAME,TBNAME,NAME,CONTENT,ID) '+
' SELECT '''+LTRIM(RTRIM(@DBNAME))+''',d.NAME, A.NAME,C.TEXT,A.ID '+
' FROM '+LTRIM(RTRIM(@SOURCESERVERNAME))+'.'+RTRIM(LTRIM(@DBNAME))+'.DBO.SYSOBJECTS A, '+
LTRIM(RTRIM(@SOURCESERVERNAME))+'.'+RTRIM(LTRIM(@DBNAME))+'.DBO.SYSCOMMENTS C, '+
LTRIM(RTRIM(@SOURCESERVERNAME))+'.'+RTRIM(LTRIM(@DBNAME))+'.DBO.SYSOBJECTS D '+
' WHERE D.XTYPE=''U'' AND A.PARENT_OBJ=D.ID '+
' AND A.XTYPE=''TR'' AND C.ID=A.ID '+
' AND RTRIM(A.NAME) NOT IN '+
' (SELECT RTRIM(TR.NAME) FROM '+
LTRIM(RTRIM(@DESTINATIONSERVERNAME))+
'.WY2000.DBO.TRIGGERS TR WHERE RTRIM(UPPER(TR.DBNAME))='''+
RTRIM(LTRIM(upper(@DBNAME)))+''' )'
EXEC(@execsql)
如果用下面的語句就不會出錯:
SELECT 'WY2000_80',d.NAME, A.NAME,C.TEXT,A.ID
FROM SYSOBJECTS A,SYSCOMMENTS C, .SYSOBJECTS D
WHERE D.XTYPE='U' AND A.PARENT_OBJ=D.ID
AND A.XTYPE='TR' AND C.ID=A.ID
AND RTRIM(A.NAME) NOT IN
(SELECT RTRIM(TR.NAME) FROM WY2000.DBO.TRIGGERS TR WHERE RTRIM(UPPER(TR.DBNAME))='WY2000_80')
請各位大俠指教。謝謝。
问题点数：0、回复次数：5Top
1 楼weixiao51（4fen）回复于 2003-06-03 17:01:19 得分 0
可能是你的字段总长度超过8000了吧
试试将较大的字段改成text型Top
2 楼liuyun2003（流云(老老实实做人,认认真真还贷)）回复于 2003-06-03 17:04:13 得分 0
WY2000.DBO.TRIGGERS 这个可能也是问题。表名可是使用保留字吗？如果使用的话，应该是要加[]的吧？？Top
3 楼joygxd（不经风雨怎见彩虹）回复于 2003-06-03 17:23:00 得分 0
应该是超长了Top
4 楼pengdali（）回复于 2003-06-03 17:34:08 得分 0
DECLARE @EXECSQL VARCHAR(5000)
declare @sourceservername varchar(20)
declare @destinationservername varchar(20)
declare @dbname varchar(20)
set @sourceservername='FILESRV'
set @destinationservername='FILESRV'
set @dbname='WY2000_80'
SET @EXECSQL='INSERT INTO '+
LTRIM(RTRIM(@DESTINATIONSERVERNAME))+
'.WY2000.DBO.TRIGGERS (DBNAME,TBNAME,NAME,CONTENT,ID) '+
' SELECT '''+LTRIM(RTRIM(@DBNAME))+''',d.NAME, A.NAME,C.TEXT,A.ID '+
' FROM '+LTRIM(RTRIM(@SOURCESERVERNAME))+'.'+RTRIM(LTRIM(@DBNAME))+'.DBO.SYSOBJECTS A, '+
LTRIM(RTRIM(@SOURCESERVERNAME))+'.'+RTRIM(LTRIM(@DBNAME))+'.DBO.SYSCOMMENTS C, '+
LTRIM(RTRIM(@SOURCESERVERNAME))+'.'+RTRIM(LTRIM(@DBNAME))+'.DBO.SYSOBJECTS D '+
' WHERE D.XTYPE=''U'' AND A.PARENT_OBJ=D.ID '+
' AND A.XTYPE=''TR'' AND C.ID=A.ID '+
' AND RTRIM(A.NAME) NOT IN '+
' (SELECT RTRIM(TR.NAME) FROM '+
LTRIM(RTRIM(@DESTINATIONSERVERNAME))+
'.WY2000.DBO.TRIGGERS TR WHERE RTRIM(UPPER(TR.DBNAME))='''+
RTRIM(LTRIM(upper(@DBNAME)))+''' )'
EXEC(@execsql)
????Top
5 楼tangmins（tom）回复于 2003-06-03 18:53:06 得分 0
問題應是在content上面，TRIGGERS中的content是text型，而SYSCOMMENTS則是varbinary型，
有的時間執行不出錯，有的時候則出錯。Top
