• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4659
  • Last Modified:

Using PHP to get MSSQL data - Error: "Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library"

I am trying to use a PHP webpage to get data from MSSQL and get the error message shown in the Question Title.

This is my connection code:

$hostname = "MAIN";
$username = "sa";
$password = "";
$dbName = "NewSQL";
MSSQL_CONNECT($hostname,$username,$password) or DIE("DATABASE FAILED TO RESPOND.");
mssql_select_db($dbName) or DIE("Table unavailable");

$query = "SELECT * FROM Customer";
$result = mssql_query($query);      
$numrows = mssql_num_rows($result);

I have found out the problem is down to 2 NTEXT fields in the MSSQL table I am trying to select from. I have found this solution:

mssql_query("SELECT CAST(field1 AS TEXT) AS field1 FROM table;");

Which would solve it - but I want to do a 'SELECT *'.

Can anyone provide me with a better solution or a way of rewriting my currently located solution to 'SELECT *' and simultaneously select the 2 NTEXT fields 'AS TEXT'?

1 Solution
wbstechAuthor Commented:
Sorry that was an idiotic question. I should have paid more attention to the similarities between MySQL and MSSQL.

The below line fixed it:

$query = "SELECT CusID, OperaRef, CoName, Name, Add1, Add2, Add3, City, PostCode, Tel, Fax, Mobile, Email, Custype, CAST(Notes AS Text) As Notes, SendMail, Status, CAST(Hyperlink AS Text) AS Hyperlink FROM Customer";
Anthony PerkinsCommented:
A better approach would be to change the column data type from ntext to text.  Unless, of course you are using double-byte characters such as chinese, in which case your select with CAST statement would fail, too.

>>The below line fixed it:<<
Please close the question.  See here:
I answered my question myself. What do I do?
Closed, 500 points refunded.
The Experts Exchange
Community Support Moderator of all Ages

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now