MSSQL Query issue in PHP and querying text data

Question

I'm trying a query in PHP to connect and extract data from a MSSQL EXPRESS (2008 R2) database. But i'm getting an error when i'm pulling ntext based data from the DB.

Error is;

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

and my script is

    $myServer = ".\SQLEXPRESS";
    $myUser = "sa";
    $myPass = "blablabla";
    $myDB = "test"; 

    //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 dbo.table WHERE query='2'";
    //$query .= "FROM dbo.table  ";
    //$query .= "WHERE query='2'"; 

    //execute the SQL query and return records
    $result = mssql_query($query);

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

    //display the results 
    while($row = mssql_fetch_array($result))
    {
      echo "<li>" . $row["query"]. "</li>";
    }
    //close the connection
    mssql_close($dbhandle);

any help on this is appreciated ....

Thanks ....

Phil · Accepted Answer · 2011-03-24 04:59:23Z

61

Couple of options from the comments on the mssql_query() manual page

SELECT CAST(field1 AS TEXT) AS field1 FROM table
Chang the version in /etc/freetds.conf from 4.2 to 8.0 (if the PHP server is *nix)
Avoid SELECT * queries

Plenty more if you search ntext on that page.

answered Mar 24, 2011 at 4:59

Phil

166k25 gold badges265 silver badges269 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

megazoid Over a year ago

CAST(field1 AS TEXT) AS field1

LucVH Over a year ago

The second method work just right for me, but it's too magic for me to undertand. "Chang the version in /etc/freetds.conf from 4.2 to 8.0 (if the PHP server is *nix)"

user2662680 Over a year ago

The 8.0 trick is weird...worked for me too. Thanks! Note my freetds conf file was actually in /etc/freetds/freetds.conf

Matt Over a year ago

According to the FreeTds docs v 8 is an alias to 7.1 which is for SQL Server 2000, very old. Probably you should use 7.4 and then fix your queries as needed. freetds.org/userguide/choosingtdsprotocol.htm

Kristaps J. Over a year ago

For anyone using Laravel. I fixed it by adding "version" => "8.0" to sqlsrv driver in database.php config file.

Yura Rodchyn · Accepted Answer · 2012-12-25 16:55:45Z

54

Here are some things you might need to know:

Install mssql support for Debian (Lenny/Squeeze):

apt-get install php5-sybase
When you got this error 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."

In /etc/freetds/freetds.conf add these two lines (last two):
```
[global]
;tds version = 4.2
tds version = 8.0
client charset = UTF-8
```
You can edit "charset" in php.ini too (but you don't need if you did it previously in freetds.conf): ; Specify client character set.. ; If empty or not set the client charset from freetds.comf is used ; This is only used when compiled with FreeTDS
```
mssql.charset = "UTF-8"
```
Use nchar/nvarchar/ntext column types if you need unicode support.

answered Dec 25, 2012 at 16:55

Yura Rodchyn

7165 silver badges3 bronze badges

2 Comments

Edenshaw Over a year ago

You saved me because I didn't have access to the PHP application.

Robert Hafner Over a year ago

I'm using python but encountered the same issue- this solution worked great for that as well. Thanks!

Oscar Intecfra · Accepted Answer · 2017-10-04 16:44:43Z

8

In my case, I needed to install:

sudo apt-get install php-sybase

And modify the /etc/freetds.conf file:

...
[global]
    # TDS protocol version
;   tds version = 4.2
tds version = 8.0
client charset = UTF-8
...

answered Oct 4, 2017 at 16:44

Oscar Intecfra

1011 silver badge3 bronze badges

Collectives™ on Stack Overflow

MSSQL Query issue in PHP and querying text data

3 Answers 3

5 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related