0

When I use MSSQL in php to read a field that has veru long content, I get an error

Warning: mssql_query() [function.mssql-query]: Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier

I searched online and find out to solve this problem, and find out that I need to cast the result to get readable text.

SELECT CAST(Content AS TEXT) AS Content FROM myTable

However, another problem I am getting is that the text data is truncated after 4096 characters. Is there anyway to further reading the text, or another option to cast the returned data?

Thanks

Ian Hsieh
  • 85
  • 3
  • 8
  • 1
    What type is `Content` in the database? Are you sure it stores more than 4096 characters? – Mike Christensen Jul 24 '12 at 16:04
  • Contents is ntext. And I am sure it stores more than 4096 characters. When I read the database through myLittleAdmin, it show more thext after when it get cut off in my PHP code. – Ian Hsieh Jul 24 '12 at 16:14
  • What version of SQL Server? Why are you using NTEXT instead of, say, NVARCHAR(MAX)? How are you validating that there are 4096 characters, by counting what gets output in Management Studio? This is truncated by the application and has absolutely no reflection on what's in your database. – Aaron Bertrand Jul 24 '12 at 16:30

1 Answers1

1

If you are counting characters in Management Studio, try:

SELECT LEN(Content), DATALENGTH(Content) FROM dbo.myTable;

Does that still show 4096? I bet not.

I don't think you want to cast as TEXT. I assume you are using NTEXT because you may have Unicode characters. Converting to TEXT will eliminate any double-byte characters and actually change your data. Perhaps you could try this from your application:

SELECT CONVERT(NVARCHAR(MAX), Content) FROM dbo.myTable;

And don't rely on Management Studio's output to be a testament to what's actually in the database. It is truncated by default, and limited to a fixed maximum even if you change the defaults (different character counts depending on results to text or results to grid). If you want to know how much data is in a column, ask SQL Server (like I did above), not Management Studio.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • I didn't use Management Studio, I use php code and query the database, then I use "echo strlen($content)" to get the output/character count. – Ian Hsieh Jul 24 '12 at 16:40
  • Also when I use LEN(Content), I get the exact same error as I query the field directly(without CAST) – Ian Hsieh Jul 24 '12 at 16:40
  • @IanHsieh Can you try it in Management Studio? This will prove the problem is with PHP or the version of the driver you're using. And can you edit the question to include answers to the comments I left on the question? – Aaron Bertrand Jul 24 '12 at 16:42
  • I solved it, in the PHP setting, it restrict the text value length from mssql to 4096. So I just need to change the value to a big enough number that works for me. – Ian Hsieh Jul 24 '12 at 17:46