Cakephp Sqlserver Encoding

Question

It puzzled me. I am trying to set the encoding for my Sqlserver connection, and everything I tried failed. I only get

Error: The connection to the database using "Sqlserver" was absent or was not to connect.
The database server returned this error: SQLSTATE [IMSSP]: An incorrect encoding was specified for SQLSRV_ATTR_ENCODING.

The original error that I tried to solve using encoding:

Error: SQLSTATE [IMSSP]: An error occurred translating the query string for UTF-16: Unicode character mapping in the target multibyte code page.

SQL Version - 2008 R2
CakePHP Version: 2.4.2
PHP Version: 5.3.27

Answer 1

After a lot of trial and error, this works:

public $default = array(
    'datasource'    => 'Database/Sqlserver',
    'persistent'    => false,
    'host'      => 'localhost',
    'login'     => 'sa',
    'password'  => 'password',
    'database'  => 'SchedulingDatabase',
    'encoding'  => PDO::SQLSRV_ENCODING_UTF8
);

Answer 2

Tried this with Cakephp 3.0, it seems to work well.

'Datasources' => [
    'default' => [
        'className' => 'Cake\Database\Connection',
        'driver' => 'Cake\Database\Driver\Sqlserver',
        'persistent' => false,
        'host' => 'localhost',
        'port' => '1433', //using this port
        'username' => 'sa',
        'password' => 'password',
        'database' => 'cake_bookmarks',
        'encoding' => PDO::SQLSRV_ENCODING_UTF8,
        'timezone' => 'UTC',
        'cacheMetadata' => true,
        'quoteIdentifiers' => false,
    ]

Answer 3

SQL 2008 does not support only UTF-16 SQL 2012

http://msdn.microsoft.com/en-us/library/ms143726(v=sql.110).aspx

UTF-8 is not generally supported by MS SQL.