PHP & MSSQL : "select *" returns Unicode error

Byoung

I am using php to access a mssql server, and any query I attempt to run with a * yeilds a Unicode error.

"Warning: mssql_query() [function.mssql-query]: message: 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. (severity 16) in "

I am a LAMP developer trying to inherit a large asp site with a mssql database attached on an IIS server (ugh). I'm trying to take baby steps in getting a simple sql query though but this unicode error is baffling. Also, due to the vagueness of the problem, I havent been able to find anything on google. I also do not have the required proprietary programs needed to open up the database (I'm a notepad only kind of guy). I'm trying to get a SELECT * FROM query to work so I can see all the fields it returns. I have very limited access to the server, so any help would be appreciated, keeping that in mind.

Is there some fundamentally elementary step that I'm messing up (Like that I don't completely understand what Unicode is, or why it would be used in a D😎? or is there some sort of compatability issue with my server? I'm starting to think that theres a field in the db, in a format that it cannot return to my php script's mssql_query(), and hence the error but mssql documentation and help forums seem to be few and far between.

I know I'm walking into a gun fight without even a knife here, but if anyone has any clues or suggestions, I'd be glad to learn.

A query that did work was "SELECT story_id,story_headline FROM stories WHERE writer = '$_GET[writer]'". The following errors out right at die("Query has a problem : $query");. The code up top is silly with $var = $var, but the connection works.

$myServer = $server;
$myUser = $username;
$myPass = $password;
$myDB = $db; 

//connection to the database
$dbhandle = mssql_connect($myServer, $myUser, $myPass)
  or die("Couldn't connect to SQL Server on $myServer"); 

//select a database to work with
$selected = mssql_select_db($myDB, $dbhandle)
  or die("Couldn't open database $myDB"); 

//declare the SQL statement that will query the database
$query = "SELECT * FROM stories";
//execute the SQL query and return records
$result = mssql_query($query) 
  or die("Query has a problem : $query");

$numRows = mssql_num_rows($result); 
echo "<h1>" . $numRows . " stories" . ($numRows == 1 ? "" : "s") . " Returned </h1>"; 

//display the results 
while($row = mssql_fetch_array($result))
{
  echo '<li><a href="/story.asp?story_id=' .$row['story_id']. '">' .$row["story_headline"]. "</a></li>";
}
//close the connection    

mssql_close($dbhandle);

TheDefender

This comes from the php manual for [man]mssql_query[/man] (in the user comments section at the bottom). I don't use mssql and have never seen anything like this in mysql, so I'm not sure myself, but it may help you out or start pointing you in the right direction:

huberkev11 at hotmail dot com
12-May-2006 08:47
Solution for the following Error:

Warning: mssql_query() [function.mssql-query]: message: 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.

This is because you are using column types of like ntext instead of text. There are 2 solutions.

Change all ntext column types to text or

Your query must look like: SELECT CAST(field1 AS TEXT) AS field1 FROM table

Sxooter

Would updating the odbc driverrs not also work?

Byoung

I managed to solve my problem through some hairpulling troubleshooting. Defender's post is exactly correct. Thanks for finding that, wish I had found that one 🙂

Here's some tips to help you guys mess with a MSSQL db if you're a MySQL person like myself.

First :

	$query = "SELECT   c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE
	FROM     INFORMATION_SCHEMA.COLUMNS c
	WHERE    c.TABLE_NAME = 'your table here!'
	ORDER BY c.ORDINAL_POSITION";
	$result = mssql_query($query);

echo "<b>Table Layout</b><pre>";
while ( $record = mssql_fetch_array($result) ) {
     print $record[1] . " | <b>" .$record[2]. '</b>.<br />'; 
}
echo "</pre>";

Use this to scan the db for it's datatypes. Any result returned as Ntext, needs to be 'casted'.

SELECT CAST(field1 AS TEXT) AS field1 FROM table

This solution solved my problem. Thanks! Hopefully this post can help some others, as I found googling this problem was real tough.

TheDefender

Byoung wrote:
SELECT CAST(field1 AS TEXT) AS field1 FROM table

This solution solved my problem. Thanks! Hopefully this post can help some others, as I found googling this problem was real tough.

Then I won't tell you that I googled "Warning: mssql_query() [function.mssql-query]: message: Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library" (sans quotes) and it was the only solution that came up... :evilgrin:

Byoung

My problem was I didnt pay enough attention to the comments on the php page >.< thanks though, your help is appreciated.