Strange error from QA
-
Can anyone help with this error message. I have tried Microsoft KB and BOL but has noidea why it is happeneing.
Cannot create a worktable row larger than allowable maximum. Resubmit your query with the ROBUST PLAN hint.
-
Is it not because you total data length exceeds 8000 bytes?
That is the most SQL can handle per page. (I think this is correct)
Do you have two varchar 5000 for example?
Cheers,
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
I have the following:
DECLARE @Name varchar(200)
DECLARE @Text nVarchar(4000)
Can this be the problem?
-
It could be. I tried that and it did not complain.
A nVarchar consumes double the specified amount. eg: A varchar(1000) would be 1000 where as a nVarchar(1000) would be 2000 (See data types in BOL)
So having a NVarchar(4000) AND varchar(200) would exceed 8000.
Is there a reason you need a NVarchar? Would normal varchar not surfice?
I may be right off the mark here but.....
Try change it.
Cheers,
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
Hi eletuw,
it might be helpful if you can post the whole statement you're trying to run
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I made the chnages but still got the error.
I am psoting the entire query in cae it might help.
==========================================
DECLARE @ID INT
DECLARE @Name varchar(200)
DECLARE @Text nVarchar(2000)
DECLARE @cDate datetime
DECLARE @ColID int
DECLARE @Type varchar(10)
-- Declare the cursor
DECLARE CURSOR_OBJ CURSOR LOCAL FAST_FORWARD
FOR SELECT TOP 100 PERCENT dbo.sysobjects.id, dbo.sysobjects.name,
dbo.syscomments.text, dbo.sysobjects.crdate, dbo.syscomments.colid,
dbo.sysobjects.xtype FROM dbo.sysobjects LEFT OUTER JOIN dbo.syscomments
ON dbo.sysobjects.id = dbo.syscomments.id WHERE (dbo.sysobjects.xtype
IN ('P', 'V', 'FN', 'U')) AND (dbo.sysobjects.category <> 2)
ORDER BY dbo.sysobjects.xtype, dbo.sysobjects.name, dbo.syscomments.colid
FOR READ ONLY
-- Open the Cursor
OPEN CURSOR_OBJ
-- Fetch Objects
READLOOPx:
fetch next from CURSOR_OBJ into @ID, @Name, @Text, @cDate, @ColID, @Type
if @@FETCH_STATUS = -1 goto EOF_READLOOPx /* END OF RECORD SET */
if @@FETCH_STATUS = -2 goto READLOOPx /* DELETED ROW, READ AGAIN */
-- Write the object text
IF @Type = 'P'
BEGIN
PRINT 'Object: ' + @Name + ' (STORED PROCEDURE)'
PRINT @Text
END
ELSE IF @Type = 'U'
BEGIN
PRINT 'Object: ' + @Name + ' (TABLE)'
PRINT @Text
END
ELSE IF @Type = 'V'
BEGIN
PRINT 'Object: ' + @Name + ' (USER VIEW)'
PRINT @Text
END
ELSE IF @Type = 'TR'
BEGIN
PRINT 'Object: ' + @Name + ' (USER TRIGGER)'
PRINT @Text
END ELSE IF @Type = 'FN'
BEGIN
PRINT 'Object: ' + @Name + ' (USER DEFINED FUNCTION)'
PRINT @Text
END
PRINT ''
--If Table then Print Columns IF @Type = 'U'
-- Set declarations
DECLARE @ColName varchar(100)
DECLARE @DataType varchar(100)
DECLARE @Len int
DECLARE @AllowNull bit
DECLARE @IsNullable bit
-- Declare the cursor
DECLARE CURSOR_TBLDEF CURSOR LOCAL FAST_FORWARD
FOR SELECT TOP 100 PERCENT dbo.syscolumns.name AS ColName,
dbo.systypes.name AS DType, dbo.syscolumns.length, dbo.systypes.allownulls,
dbo.syscolumns.isnullable FROM dbo.sysobjects INNER JOIN dbo.syscolumns
ON dbo.sysobjects.id = dbo.syscolumns.id INNER JOIN dbo.systypes
ON dbo.syscolumns.xtype = dbo.systypes.xtype WHERE (dbo.sysobjects.type = 'U') AND (dbo.sysobjects.id = @ID) ORDER BY dbo.syscolumns.colid FOR READ ONLY
-- Open the Cursor
OPEN CURSOR_TBLDEF PRINT 'Column Definition - Column Name, DataType, Length,
Allow Null, Is Nullable'
READLOOPxxx:
fetch next from CURSOR_TBLDEF into @ColName, @DataType, @Len, @AllowNull, @IsNullable
if @@FETCH_STATUS = -1 goto EOF_READLOOPxxx /* END OF RECORD SET */
if @@FETCH_STATUS = -2 goto READLOOPxxx /* DELETED ROW, READ AGAIN */
PRINT '[' + @ColName + '], ' + @DataType + ', ' + CAST(@Len AS VARCHAR(10))
+ ', ' + CAST(@AllowNull AS VARCHAR(10)) + ', '
+ CAST(@IsNullable AS VARCHAR(10))
goto READLOOPxxx
--Get next COLUMN record
EOF_READLOOPxxx:
close CURSOR_TBLDEF deallocate CURSOR_TBLDEF
--Print Security
PRINT '' PRINT 'Assigned security users/Groups for ''' + @Name + ''''
/* Setup sub loop to get security permisions for the object */
DECLARE @uName varchar(200)
DECLARE CURSOR_SEC CURSOR LOCAL FAST_FORWARD FOR SELECT dbo.sysusers.name
FROM dbo.sysobjects INNER JOIN dbo.syspermissions
ON dbo.sysobjects.id = dbo.syspermissions.id INNER JOIN dbo.sysusers
ON dbo.syspermissions.grantee = dbo.sysusers.uid
WHERE (dbo.sysobjects.id = @ID) FOR READ ONLY
-- Open the Cursor
OPEN CURSOR_SEC
READLOOPxx:
fetch next from CURSOR_SEC into @uName
if @@FETCH_STATUS = -1 goto EOF_READLOOPxx /* END OF RECORD SET */
if @@FETCH_STATUS = -2 goto READLOOPxx /* DELETED ROW, READ AGAIN */
-- Process record
PRINT '' + @uName goto READLOOPxx
-- Get next SECURITY record
EOF_READLOOPxx:
close CURSOR_SEC deallocate CURSOR_SEC
PRINT '---------------------------------------------------------------------'
goto READLOOPx
-- Get next OBJECT record
EOF_READLOOPx:
close CURSOR_OBJ
deallocate CURSOR_OBJ
============================================
This code was not written by me but was obtained from public forum.
-
Hi eletuw,
I get this running when I remove the LOCAL FAST FORWARD option.
Hm, now I only need to figure out, what this is doing?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
quote:
============================================This code was not written by me but was obtained from public forum.
it sems to be pretty interesting.
Where did you get this from?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Do you get the error right away or during the CURSOR_TBLDEF loop? If is is during CURSOR_TBLDEF loop perhaps SQL is grabing a big chunck of the syscomments entry and allocating too much for the record, are there any large table/procedure definitions in your DB?
-
quote:
Do you get the error right away or during the CURSOR_TBLDEF loop? If is is during CURSOR_TBLDEF loop perhaps SQL is grabing a big chunck of the syscomments entry and allocating too much for the record, are there any large table/procedure definitions in your DB?I was a little bit unprecise!
I only need to remove the first occurence of FAST_FORWARD to get it running.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
quote:
I have the following:DECLARE @Name varchar(200)
DECLARE @Text nVarchar(4000)
Can this be the problem?
Most definitely that is the problem. If the sum of your data in the row exeeds the maximum length of the page size your data will be truncated and you will get this message. An nvarchar(4000) is 8000 bytes so if you add that to the 200 from the varchar(200) you will be over the 8092? byte maximum.
Gary Johnson
DBA
Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply