| Author |
Topic |
|
raky
Aged Yak Warrior
767 Posts |
 Posted - 2009-12-11 : 01:14:20
|
| hi,In my stored procedure iam using a table variable DECLARE @SAItemsTable TABLE(Item_id VARCHAR(50),Item_number VARCHAR(50),Description VARCHAR(1500) ,Firms VARCHAR(8000),User_Roles VARCHAR(8000))and having one statement for insert like thisINSERT INTO @SAItemsTable VALUES(@itemId,@Item_number,@Description,SUBSTRING(@Companies,0,LEN(@Companies)),SUBSTRING(@Roles,0,LEN(@Roles)))Iam getting one warning message as belowThe table '@SAItemsTable' has been created but its maximum row size (17631) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.Please let me know what is the reason for it.. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
raky
Aged Yak Warrior
767 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
raky
Aged Yak Warrior
767 Posts |
Posted - 2009-12-11 : 01:34:42
|
quote:
Originally posted by tkizer
Your columns are too big. All of these below need to add up to less than 8060, or more realistically less than or equal to 8000:Item_id VARCHAR(50)Item_number VARCHAR(50)Description VARCHAR(1500)Firms VARCHAR(8000)User_Roles VARCHAR(8000)Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong."
Does you mean i need to reduce the length of columns in my table variable @SAItemsTable to less than 8000 ?I tried by doing as above but it is giving another message as "String or binary data would be truncated" |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-12-11 : 01:45:33
|
quote:
I tried by doing as above but it is giving another message as "String or binary data would be truncated"
Well you can't have it both ways. You either have to ignore the first warning or have your data be trimmed. You should consider upgrading to SQL Server 2005 where you can use varchar(max) to avoid issues like this.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-12-11 : 04:35:38
|
| You could move the big columns into another table (or tables) with a surrogate primary key and then reference that key in your 'main' table. That would keep the row size down.However,It sounds like there must be much better ways to approach whatever problem you are trying to solve using other methods.Also, table variables don't perform well with lots of data.....Do you actually *need* to keep those values stored in a temporary space?Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
|
Warning message with table variable