0

I'm pulling out data from an MSSQL server via PDO on a Unix system. Having tried the Easysoft ODBC Driver, which works as intended (but expensive!) I'm just trying out FreeTDS before we commit to buying a license.

Trying to pull out data from the SQL server that is set to nvarchar(MAX) - it comes out as some very strange characters, rather than what is expected. I am unable to edit the column types in the database.

For instance, a value in the nvarchar(max) field is set to "admin" but comes out as " ›÷Ý" - all other column types seem to work OK.

To fix this with the Easysoft driver, I had to set "VarMaxAsVarchar" to on (http://www.easysoft.com/products/data_access/odbc-sql-server-driver/manual/configuration.html)

Is there a similar setting from within FreeTDS?

For reference, I am connecting to a Microsoft SQL Server 2012 database, with TDS Version set to 7.4.

Here is the config files:

/etc/freetds.conf:

[global]
# TDS protocol version
tds version = 7.4

# If you get out-of-memory errors, it may mean that your client
# is trying to allocate a huge buffer for a TEXT field.  
# Try setting 'text size' to a more reasonable limit 
text size = 64512

[mssql_freetds]
    host = xxxxx
    port = xxxx
    tds version = 7.4

/etc/odbc.ini

[mssql_freetds]
Driver=FreeTDS
Description=MSSQL FreeTDS
Server=xxxxx
Port=xxxx
TDS_Version = 7.4

Any thoughts?

spirie
  • 21
  • 5
  • Check missed `N` literal before all string resources... – Devart Apr 18 '16 at 14:07
  • What version of FreeTDS are you using? v1.0 is close to released and is the only version that supports version `TDS_Version` 7.4: https://github.com/FreeTDS/freetds/blob/master/NEWS Otherwise, it'll fallback to version 7.1. You can find our what version you're using with `tsql -C`. – FlipperPA Apr 20 '16 at 14:00
  • Hi @FlipperPA using v0.95.91 - didn't realise v1 was in production. Shall I wait, as this is on a production server? – spirie Apr 20 '16 at 14:07
  • FYI, current getting around it by using `CAST(Column AS varchar(4000)) as Column` in SQL queries. This works, but a pain to use throughout – spirie Apr 20 '16 at 14:10
  • 1
    I'd definitely hold off, I just noticed 1.0 dropped myself, and have been using FreeTDS for years. Let's try three things. First, in `freetds.conf` and `odbc.ini` change `TDS_Version = 7.3`. Then add the line `client charset = UTF-8` to `freetds.conf`. Finally, in `odbc.ini` add `Charset = UTF8`. Since you're on 0.95, these settings should be supported. Give that a try and let me know if it works? – FlipperPA Apr 20 '16 at 14:56

1 Answers1

0

Casting the column seems the best option so far, has been working well for my application.

CAST(Column AS varchar(4000)) as Column
spirie
  • 21
  • 5