March 22, 2017 at 7:42 pm
When we tried to access a table from .net code we get this error:
An unhandled exception of type'System.Data.SqlClient.SqlException' occurred in EntityFramework.dll
Index 'PK_dbo.Mytable' row length exceeds the maximumpermissible length of '8060' bytes.
Could not create constraint or index
I know probably this is because the maximum size of row exceeds the 8kb. But this error happened when we try to access the table.
How can we avoid this error?
The table has dataTypes like varchar250, varchar(max), datetime, decimal(4,1), char(1), 52 columns.
How can I avoid the error, and is there a query to find out each row byte size of this table, and found out which record cause the error?
Thanks
March 22, 2017 at 11:16 pm
Gosh... sounds odd. The only way I can see this happening is if there are enough fixed length columns to push you over the 8060 limit. Varchars will go "out of row" if they need to just like VARCHAR(MAX) will... unless someone got cute and tried to force everything to be "inrow".
It's also possible that the table may have had many columns added and deleted over time and no one ever did a table cleanup. For example, someone may have made a mistake and added a CHAR(8000) column and then dropped it. Dropping a column doesn't necessary and usually won't actually drop a fixed length column until the Clustered Index is rebuilt.
Can you post the CREATE TABLE statement along with any indexes there may be against the table?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 23, 2017 at 9:49 am
Attached is the table. Please note this is not a normalized table. the reason we do like this way is for a sharepoint list that can only work on a table. That is another topic. for this one , we just want to figure out what caused the error. and how to avoid it.
Thanks for taking a look
March 23, 2017 at 10:13 am
I built a simple query to insertmax length varchars for all the fields.
I am able to insert and select via TSQL.
could the issue be coming from your application? is it losely binding the data sizes? ie assuming everything is nvarchar(4000) or varchar(8000), isntead of strongly typed data columns?
INSERT INTO [dbo].[ParentReferral]([STUDENT_NUMBER],[LAST_NAME],[FIRST_NAME],[GRADE_LEVEL],[SCHOOLID],[SCHOOL],[ACCOMM_NEEDED_IEP],[ACCOMM_NEEDED_504],[ACCOMM_NEEDED_OTHER],[PERMISSION_FRL],[PERMISSION_MCKINNEY_VENTO],[SIGNATURE_YN],[DECISION_FROM_COMM_1],[DECISION_FROM_COMM_2],[DECISION_FROM_COMM_3],[DECISION_FROM_COMM_4],[ELIGIBILITY_DECISION],[DECISION_DATE],[APPEAL_DECISION],[APPEAL_DECISION_DATE],[LETTER_SENT_PRINTCODE],[IN_PS_YN],[PRIOR_ELIG],[COGSCREENER],[COGSCREENER_SCORE],[VERB],[QUANT],[NVERB],[VQ],[VN],[QN],[VQN],[SCHOOL_YEAR],[SPR_MAP_RDNG_PRC],[SPR_MAP_MATH_PRC],[FALL_MAP_RDNG_PRC],[FALL_MAP_MATH_PRC],[WNT_MAP_RDNG_PRC],[WNT_MAP_MATH_PRC],[SPR_ST_RDNG_PRC],[SPR_ST_MATH_PRC],[SPR_STAS_SCR_R],[SPR_STAS_SCR_M],[ALT_ACH_NAME],[ALT_RDNG_PRC],[ALT_MATH_PRC],[ALT_COG_NAME],[FSIQ],[GAI],[NOTES],[HOME_ADDRESS],[CITY],[STATE],[ZIP],[GUARDIAN_CONTACT_ID],[GUARDIAN_LAST_NAME],[GUARDIAN_FIRST_NAME],[GUARDIAN_CONTACT_EMAIL],[GUARDIAN_HOME_PHONE],[GUARDIAN_CELL_PHONE],[GUARDIAN_EMAIL],[HOME_ROOM],[HOME_ROOM_TEACHER],[GENDER],[FRL_YN],[BIRTHDATE],[RACE],[STATUS504],[SPECIAL_ED_YN],[CEDARS_DISABILITY],[BILINGUAL_YN],[STUDENT_HOME_LANG],[PRIMARY_LANG],[IS_CONFIDENTIAL_YN],[FIRST_TEST_DATE],[SECOND_TEST_DATE],[THIRD_TEST_DATE],[FOURTH_TEST_DATE],[FIFTH_TEST_DATE],[SIXTH_TEST_DATE],[TEST_LOCATION_1],[TEST_LOCATION_2],[TEST_LOCATION_3],[TEST_LOCATION_4],[TEST_LOCATION_5],[TEST_LOCATION_6],[GUARDIAN_SUBMIT_DATE],[GUARDIAN_COMMENTS],[GUARDIAN_PERMISSION_GRANTED],[GUARDIAN_INFO_ACCURATE],[GUARDIAN_SIGNATURE],[STAFF_ENTERED],[TEACHER_REFERRAL],[StateAssessSpringReadingPercent],[StateAssessSpringMathPercent],[HighReadAchievementPercent],[HighMathAchievementPercent],[AltCogName],[HCEligibleCogAT],[ALEligibleCogAT])
SELECT
1 AS [STUDENT_NUMBER],
REPLICATE('x',255) AS [LAST_NAME],
REPLICATE('x',255) AS [FIRST_NAME],
1 AS [GRADE_LEVEL],
1 AS [SCHOOLID],
1 AS [SCHOOL],
'X' AS [ACCOMM_NEEDED_IEP],
'X' [ACCOMM_NEEDED_504],
'X' [ACCOMM_NEEDED_OTHER],
'X' [PERMISSION_FRL],
'X' [PERMISSION_MCKINNEY_VENTO],
'X' [SIGNATURE_YN],
REPLICATE('x',3) AS [DECISION_FROM_COMM_1],
REPLICATE('x',3) AS [DECISION_FROM_COMM_2],
REPLICATE('x',3) AS [DECISION_FROM_COMM_3],
REPLICATE('x',3) AS [DECISION_FROM_COMM_4],
REPLICATE('x',25) AS [ELIGIBILITY_DECISION],
getdate() AS [DECISION_DATE],
REPLICATE('x',255) AS [APPEAL_DECISION],
getdate() AS [APPEAL_DECISION_DATE],
REPLICATE('x',9000) AS [LETTER_SENT_PRINTCODE],
'X' AS [IN_PS_YN],
'xX' AS [PRIOR_ELIG],
REPLICATE('x',255) AS [COGSCREENER],
1 AS [COGSCREENER_SCORE],
10.1 AS [VERB],
10.1 AS [QUANT],
10.1 AS [NVERB],
10.1 AS [VQ],
10.1 AS [VN],
10.1 AS [QN],
10.1 AS [VQN],
2017 AS [SCHOOL_YEAR],
1 AS [SPR_MAP_RDNG_PRC],
1 AS [SPR_MAP_MATH_PRC],
1 AS [FALL_MAP_RDNG_PRC],
1 AS [FALL_MAP_MATH_PRC],
1 AS [WNT_MAP_RDNG_PRC],
1 AS [WNT_MAP_MATH_PRC],
1 AS [SPR_ST_RDNG_PRC],
1 AS [SPR_ST_MATH_PRC],
1 AS [SPR_STAS_SCR_R],
1 AS [SPR_STAS_SCR_M],
REPLICATE('x',15) AS [ALT_ACH_NAME],
10.1 AS [ALT_RDNG_PRC],
10.1 AS [ALT_MATH_PRC],
10.1 AS [ALT_COG_NAME],
10.1 AS [FSIQ],
10.1 AS [GAI],
REPLICATE('x',9000) AS [NOTES],
REPLICATE('x',60) AS [HOME_ADDRESS],
REPLICATE('x',50) AS [CITY],
'XX' AS [STATE],
REPLICATE('x',10) AS [ZIP],
1 AS [GUARDIAN_CONTACT_ID],
REPLICATE('x',255) AS [GUARDIAN_LAST_NAME],
REPLICATE('x',255) AS [GUARDIAN_FIRST_NAME],
REPLICATE('x',255) AS [GUARDIAN_CONTACT_EMAIL],
REPLICATE('x',30) AS [GUARDIAN_HOME_PHONE],
REPLICATE('x',30) AS [GUARDIAN_CELL_PHONE],
REPLICATE('x',255) AS [GUARDIAN_EMAIL],
REPLICATE('x',255) AS [HOME_ROOM],
REPLICATE('x',255) AS [HOME_ROOM_TEACHER],
'X' AS [GENDER],
'X' AS [FRL_YN],
getdate() AS [BIRTHDATE],
REPLICATE('x',50) AS [RACE],
'X' AS [STATUS504],
'X' AS [SPECIAL_ED_YN],
'X' AS [CEDARS_DISABILITY],
'X' AS [BILINGUAL_YN],
REPLICATE('x',50) AS [STUDENT_HOME_LANG],
REPLICATE('x',50) AS [PRIMARY_LANG],
'X' AS [IS_CONFIDENTIAL_YN],
getdate() AS [FIRST_TEST_DATE],
getdate() AS [SECOND_TEST_DATE],
getdate() AS [THIRD_TEST_DATE],
getdate() AS [FOURTH_TEST_DATE],
getdate() AS [FIFTH_TEST_DATE],
getdate() AS [SIXTH_TEST_DATE],
REPLICATE('x',20) AS [TEST_LOCATION_1],
REPLICATE('x',20) AS [TEST_LOCATION_2],
REPLICATE('x',20) AS [TEST_LOCATION_3],
REPLICATE('x',20) AS [TEST_LOCATION_4],
REPLICATE('x',20) AS [TEST_LOCATION_5],
REPLICATE('x',20) AS [TEST_LOCATION_6],
getdate() AS [GUARDIAN_SUBMIT_DATE],
REPLICATE('x',9000) AS [GUARDIAN_COMMENTS],
'X' AS [GUARDIAN_PERMISSION_GRANTED],
'X' AS [GUARDIAN_INFO_ACCURATE],
REPLICATE('x',255) AS [GUARDIAN_SIGNATURE],
'X' AS [STAFF_ENTERED],
'X' AS [TEACHER_REFERRAL],
10.1 AS [StateAssessSpringReadingPercent],
10.1 AS [StateAssessSpringMathPercent],
10.1 AS [HighReadAchievementPercent],
10.1 AS [HighMathAchievementPercent],
REPLICATE('x',255) AS [AltCogName],
'X' AS [HCEligibleCogAT],
'X' AS [ALEligibleCogAT]
SELECT * FROM [dbo].[ParentReferral]
Lowell
March 23, 2017 at 11:15 am
Is there a way to find out that, this table is created by using entity framework originally, code first instead of database first.
March 23, 2017 at 12:19 pm
Also we do added some columns recently and dropped some. Is there a way to clean tables not data?
March 23, 2017 at 1:17 pm
sqlfriends - Thursday, March 23, 2017 12:19 PMAlso we do added some columns recently and dropped some. Is there a way to clean tables not data?
Yes. Run DBCC CLEANTABLE (https://technet.microsoft.com/en-us/library/ms174418(v=sql.110).aspx ). See the "Best Practices" section in that article for some important information.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 23, 2017 at 1:38 pm
Thanks, if I run a rebuild of index, does that help too?
March 23, 2017 at 7:52 pm
According to the documentation, you don't need to do both and either will work. If you're going to rebuild indexes (the Clustered Index would be most important here) anyway, then that would be all that you need to do.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 24, 2017 at 2:54 am
sqlfriends - Wednesday, March 22, 2017 7:42 PMWhen we tried to access a table from .net code we get this error:An unhandled exception of type'System.Data.SqlClient.SqlException' occurred in EntityFramework.dll
Index 'PK_dbo.Mytable' row length exceeds the maximumpermissible length of '8060' bytes.
Could not create constraint or index
I know probably this is because the maximum size of row exceeds the 8kb. But this error happened when we try to access the table.
How can we avoid this error?The table has dataTypes like varchar250, varchar(max), datetime, decimal(4,1), char(1), 52 columns.
How can I avoid the error, and is there a query to find out each row byte size of this table, and found out which record cause the error?
Thanks
The error message gives pretty certain direction.
The error "occurred in EntityFramework.dll", not in SQL Server itself.
Check the version of the dll, it might be too old and not aware of SQL Server capability to accept records longer than 8060 bytes.
I had an issue of the same kind when I tried to call a procedure with a VARCHAR(MAX) parameter from VB6 code.
_____________
Code for TallyGenerator
March 24, 2017 at 6:48 am
If you're using Entity Framework, you might also check your code. Make sure your mapping classes (if you are using them) are specifying column sizes for string columns, or that you have appropriate attributes on your entity classes that specify column lengths for those columns (and they should match the database definition). Entity Framework, by default (unless it's changed recently) maps unattributed strings to NVARCHAR(MAX),
March 24, 2017 at 7:13 am
Sergiy - Friday, March 24, 2017 2:54 AMsqlfriends - Wednesday, March 22, 2017 7:42 PMWhen we tried to access a table from .net code we get this error:An unhandled exception of type'System.Data.SqlClient.SqlException' occurred in EntityFramework.dll
Index 'PK_dbo.Mytable' row length exceeds the maximumpermissible length of '8060' bytes.
Could not create constraint or index
I know probably this is because the maximum size of row exceeds the 8kb. But this error happened when we try to access the table.
How can we avoid this error?The table has dataTypes like varchar250, varchar(max), datetime, decimal(4,1), char(1), 52 columns.
How can I avoid the error, and is there a query to find out each row byte size of this table, and found out which record cause the error?
Thanks
The error message gives pretty certain direction.
The error "occurred in EntityFramework.dll", not in SQL Server itself.
Check the version of the dll, it might be too old and not aware of SQL Server capability to accept records longer than 8060 bytes.
I had an issue of the same kind when I tried to call a procedure with a VARCHAR(MAX) parameter from VB6 code.
Yowch... I totally missed that.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply