minermadison
asked on
'String or binary data would be truncated' in Manegement Studio Express
I am getting this error:
--------------------------
Microsoft SQL Server Management Studio Express
--------------------------
No row was updated.
The data in row 158 was not committed.
Error Source: .Net SqlClient Data Provider.
Error Message: String or binary data would be truncated.
The statement has been terminated.
Correct the errors and retry or press ESC to cancel the change(s).
--------------------------
OK Help
--------------------------
When I try and edit any of the fields in a record. Even when I try and make a field shorter. It seems like this error is either wrong, or the database already has a value that is too long. As is stands now I can not edit any fields at all in the records that have this error. I have looked over every field and it looks like every field is large enough to hold all the data. I made all the fields that were close much larger and still no luck. This is really a problem. Any clues at all would be very much appreicated. Thanks in advance.
I'm using SQL server express 2005 and SQL Server Management Studio Express. Sorry I can't give more points, 195 is all I have.
--------------------------
Microsoft SQL Server Management Studio Express
--------------------------
No row was updated.
The data in row 158 was not committed.
Error Source: .Net SqlClient Data Provider.
Error Message: String or binary data would be truncated.
The statement has been terminated.
Correct the errors and retry or press ESC to cancel the change(s).
--------------------------
OK Help
--------------------------
When I try and edit any of the fields in a record. Even when I try and make a field shorter. It seems like this error is either wrong, or the database already has a value that is too long. As is stands now I can not edit any fields at all in the records that have this error. I have looked over every field and it looks like every field is large enough to hold all the data. I made all the fields that were close much larger and still no luck. This is really a problem. Any clues at all would be very much appreicated. Thanks in advance.
I'm using SQL server express 2005 and SQL Server Management Studio Express. Sorry I can't give more points, 195 is all I have.
ASKER
I tried the workaround and it did not work for me. Most of the field typed are nvarchar(xx) where xx is PLENTY large. Should I change the datatype? What do you recomend?
ASKER
OK, there was one field that was TEXT, when I upgraded to SQL Server from access, I read that TEXT was the field type to use for fields that were 'Memo' in access. But I just swtiched the datatype to nVarChar(Max) and now it works... Thanks anyway.
minermadison,
>Should I change the datatype? What do you recomend?
i normally use the varchar datatype unless i need the unicode character set then i use nvarchar so i dont think you need to change
>Most of the field typed are nvarchar(xx) where xx is PLENTY large
strange i know imstateing the obvious but
are you changing multiple fields or one at a time?
do you get this when you delete all the contents of one field ?
>Should I change the datatype? What do you recomend?
i normally use the varchar datatype unless i need the unicode character set then i use nvarchar so i dont think you need to change
>Most of the field typed are nvarchar(xx) where xx is PLENTY large
strange i know imstateing the obvious but
are you changing multiple fields or one at a time?
do you get this when you delete all the contents of one field ?
ASKER
I thought it would be better to allow unicode.
I was changing one field at a time. And when I tried delete the whole row I got the same error. The problem must have been the TEXT datatype, I mean I was not trying to update that field, but when I changed it to nVarChar(max) the error went away. I'm not sure exactly why this worked, but it worked so I'm happy for now. I'm trying to read more about SQL datatypes now.... Thanks for your help anyway....
I was changing one field at a time. And when I tried delete the whole row I got the same error. The problem must have been the TEXT datatype, I mean I was not trying to update that field, but when I changed it to nVarChar(max) the error went away. I'm not sure exactly why this worked, but it worked so I'm happy for now. I'm trying to read more about SQL datatypes now.... Thanks for your help anyway....
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
i have had this when attempting to edit fields that have a char data type in EM and management studio
the issue is that the char datatype pads the field with spaces so whenever you try modify it you go over the field size
a workround is when in the grid push F2 to edit the fiels then push the END key to go yo yhe end of the field then back space to remove all the paded spaces you should now be able to edit your field