Search code examples
sql-serverlaravelfreetdsnvarchar

Microsoft SQL Server error with Laravel: Why this solves my mistake?

I am creating an application in Laravel, which accesses information stored in a remote database Microsoft SQL Server.

And I have encountered an error, which I solved; but I do not quite know why...

The Problem

I created a "users" table within the database in the Microsoft SQL Server, in order to use the application through a login system: Works well.

My job is to display information from the tables found in the existing database in Microsoft SQL Server ... so I have created the model of one of this tables —called, for example, Sales— BUT when I try to query information with:

$sale = Sales::find("1");

I get the next error:

SQLSTATE[HY000]: General error: 4004 General SQL Server error: Check messages from the SQL Server [4004] (severity 16) [(null)]

The same error with other Eloquent ways and Fluent.


The problematic table

These are some of the columns which has the queried table:

nvarchar


How I solved this:

I have solved making two changes in two configuration files:

1) Inside /etc/freetds/freetds.conf

I changed the tds version from:

tds version = 4.2

to

tds version = 8.0

And set client charset as follow:

client charset = UTF-8

2) Inside /etc/php5/apache2/php.ini

mssql.charset = "UTF-8"

default_charset = "UTF-8"

My question: Why this works?

From what I've read this is due to:

Laravel uses db-library (if it's available) to connect to Sql Server which cannot receive unicode data from MSSQL. (1,2) — From Here

So I guess it may be due to the type fields nvarchar... But when I changed my model to remove all field with nvarchar, I still get the same problem...


Fragment of my Laravel database config: config/database.php

 [...]

'default' => 'sqlsrv',

    [...]
    'sqlsrv' => array(
        'driver' => 'sqlsrv',
        'host' => 'foo.bar',
        'database' => 'MyDataBase',
        'username' => 'UserName',
        'password' => 'MySecret',
        'prefix' => ''
    )

[...]

I do not know if there is another configuration for sqlsrv to specify the version of FreeTDS.


Result of tsql -C command:

enter image description here

Solution

  • tds version = 4.2 is the global default for FreeTDS, but very out of date, doesn't support UTF-8, and has been problematic even when connecting to modern version of SQL Server. Please see the documentation here:

    http://www.freetds.org/userguide/choosingtdsprotocol.htm

    Your nvarchar columns will require UTF-8 throughout your stack.

    Please also note that tds version = 8.0 is deprecated, and not valid, likely to be removed in a future version of FreeTDS. You probably want tds version = 7.2, or tds version = 7.3, depending on what version of the FreeTDS driver you have installed. You can find the FreeTDS version installed by using tsql -C from the command line.

    UPDATE: the key was changing the tds version = 7.2.