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"

Posted on 2006-04-27
Last Modified: 2012-05-05
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'?

Question by:wbstech
    LVL 4

    Author Comment

    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";
    LVL 75

    Expert Comment

    by:Anthony Perkins
    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?

    Accepted Solution

    Closed, 500 points refunded.
    The Experts Exchange
    Community Support Moderator of all Ages

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now