0

The older data types text, Ntext and image are deprecated and while converting some columns to NVARCHAR(MAX) or VARCHAR(max), the data is getting truncated. There's no alternate provided anywhere and I needed some help.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mr. K
  • 380
  • 3
  • 15
  • 1
    Are the data being truncated to a particular size/length? – Andrew Morton May 23 '18 at 13:03
  • 3
    You need to convert `NTEXT` -> `NVARCHAR(MAX)`, `TEXT` -> `VARCHAR(MAX)`, and `IMAGE` -> `VARBINARY(MAX)`. You're not mentioning `varbinary(max)` in your question - so what are you converting your `image` columns to?? You cannot convert those to a string-based datatype like `(n)varchar(max)`, of course! – marc_s May 23 '18 at 13:06
  • 1
    Here the question is about the size and data truncation. of course image should be converted into VARBINARY(max). And I am trying to convert TEXT -> VARCHAR(max). – Mr. K May 23 '18 at 13:09
  • 3
    Please share your code for the convert. – HoneyBadger May 23 '18 at 13:10
  • ALTER TABLE ALTER COLUMN VARCHAR(max) – Mr. K May 23 '18 at 13:11
  • Hope this helps. https://stackoverflow.com/questions/564755/sql-server-text-type-vs-varchar-data-type/564823 – Jyothish Bhaskaran May 23 '18 at 13:13
  • 1
    Check `DATALENGTH` before and after to check if the data is actually getting truncated, or it's an artifact of getting/displaying it. You may also wish to check the result of a `CONVERT(VARBINARY(MAX), ...)` to see if there are icky things like NUL characters that might gum up the works (I think those should just get copied without change, but there are scenarios where SQL Server really doesn't like them in strings). – Jeroen Mostert May 23 '18 at 13:16
  • You may also wish to double check that the column's current collation is what you expect it to be, and that it matches the database default. If not, specify a `COLLATE` clause explicitly in the `ALTER`. – Jeroen Mostert May 23 '18 at 13:23
  • @JeroenMostert I found it as an problem in displaying the data while before and after conversion DATALENGTH remains the same. Thank You – Mr. K May 24 '18 at 05:14

0 Answers0