String or binary data would be truncated.- sql 2000
Hi,
I have field that has data in it. When I attempt to update either using sql languageor by the enterprise manager I get the error "String or binary data would be truncated.".
I have tried to delete all the data in the field without success. I cannot modify this field in any way. I have also increased the column size without success. I can do nothing with this.
Any ideas?
I have field that has data in it. When I attempt to update either using sql languageor by the enterprise manager I get the error "String or binary data would be truncated.".
I have tried to delete all the data in the field without success. I cannot modify this field in any way. I have also increased the column size without success. I can do nothing with this.
Any ideas?
String or binary data would be truncated.
Nathan Riley
It's because the data would be shortened by the update b/c the field is limited to the amount of characters. What type of field is it?
What is the column definition and what is the update statement?
ASKER
The column is a char... originally 1000 that I increased to 2000. That didn't work.
The sql statement is
UPDATE tbl_form_requestv2
SET additionalcomments=
'American FF# E6599C0
National Car Rental Emerald Number:_3333333
request Mövenpick Hotel Nürnberg Airport for the night of the 3 Apr. '
WHERE (travelrequestid = 15277)
__________________________
No matter what I do to the set additionalcoments = statement, it fails.
The sql statement is
UPDATE tbl_form_requestv2
SET additionalcomments=
'American FF# E6599C0
National Car Rental Emerald Number:_3333333
request Mövenpick Hotel Nürnberg Airport for the night of the 3 Apr. '
WHERE (travelrequestid = 15277)
__________________________
No matter what I do to the set additionalcoments = statement, it fails.
Can I see your table structure
ASKER
I show your update statement with the column "additionalcomments" but I don't see that field in the table, I do see "additionalremarks", is that a typo?
I think you are exceeding the limit in bytes per row (around 8000) not the limit in bytes per column. Suggest converting most of those large char columns to VARCHARS to conserve space. If that is not enough, then consider vertical partition to another table.
ASKER
Can I change a char to a nvar while the server is live? As you can see, this isn't exceeding 8000 char.
My code
UPDATE tbl_form_travelrequestv2
SET additionalremarks = 'American FF# E6599C0
National Car Rental Emerald Number:_663999652
request Mövenpick Hotel Nürnberg Airport for the night of the 31st Mar. '
WHERE (travelrequestid = 15277)
My code
UPDATE tbl_form_travelrequestv2
SET additionalremarks = 'American FF# E6599C0
National Car Rental Emerald Number:_663999652
request Mövenpick Hotel Nürnberg Airport for the night of the 31st Mar. '
WHERE (travelrequestid = 15277)
ASKER
I tried to change the fields to nvarchar and am getting this error message:
'tbl_form_travelrequestv2'
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot create a row of size 10677 which is greater than the allowable maximum of 8060.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.
'tbl_form_travelrequestv2'
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot create a row of size 10677 which is greater than the allowable maximum of 8060.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.
ASKER
How did the row size get to be 10677 when none of those char fields are that long!?!
nVarchar takes 2 bytes per character. try Varchar.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Thank you for the insight. Yes, I know the table structure is not great. Will attempt to restructure.