Maybe a stupid question but I'm new to the db admin work so please bear with me.
I've imported an Access db into SQL, in the Access db the field type was 'memo' to accomodate the large amount of text (on avg ruffly 4100 chars. with spaces). Now in SQL the field in the table I have set up as an ntext field, which I understood to be equivalent to a memo field in Access.
My problem is when saving data to the field the first time it saves all the data correctly with the exception of the field in question. The data in the field is '<LongText>', now when I try to update the data in the table I get a 'Data Truncated' error message and no update takes place throughout the table.
After testing this and trying different things, I've found that if I shorten this one field and try to save to the db I still get the 'Data Truncated' error message. If I shorten the data in the field AND delete the record from the SQL table then it will save just fine from there on out (which won't work for the reports).
I'm not sure what I'm missing here to get this to work the way it did in Access.
In Sql server, there is another data type which is equivalent to a memo data type in Access. That is VARCHAR. If you can sure that the character number for the field is less than 8000, it is better to use VARCHAR as your data type instead of TEXT, NTEXT.
good point gyuan - this is also one of the most common mistakes i see in databases - using a text datatype when varchar would suffice. But Jonathan, you need to validate the requirements for that field. text datatypes present performance and maintainence issues as well.
I'm not sure what you mean by validate the requirements. I think your talking about making sure that I'm trying to use the right field for the data that I'm trying to put in it. If so, then the only data that is going in the field is alphanumeric characters. Is that what your talking about?
I tried to change the field type to VarChar and change the length to 4000 (the max) and I get a 'string right truncated' error message which is telling me the same thing. To much data for the field to hold (I thought it could hold 8000?, I'm missing something there).
The way the data is being inserted into the db is through the ODBC, the program they use is coded to use specific entries in the ODBC which then passes the data to the appropriate db in SQL.
Also check the setting for Maximum field width. The default in QA is 255. In addition, check the @@textsize global variable (select @@textsize) and if it's insufficient, increase it by SET TEXTSIZE <number>.
Originally posted by devwilcat
I was having a similar problem today and came across this post. I tried using VARCHAR as suggested entering a field length of 8000, but the data got truncated after 255 characters.
gyuan, did you mean nvarchar or varchar?
It is varchar which can hold 8000 characters. Since TEXT field can hold more than 8000 characters, you will get the following warning:
Warning: Data may be lost converting column 'MyField' from 'text'.
This means if there is a string with more than 8000 characters in this column, data may be lost when you convert TEXT to VARCHAR.
When you do a SELECT query on Query Analyzer, you only can get the first 256 characters if data have more than 256 characters for VARCHAR, TEXT or NTEXT. That does not mean data got truncated after 256 characters.
In the safe case, it is better to backup the table before doing that.