Cannot store chinese characters in MSSQL

[eluser]Loupax[/eluser]
Hello,

I'm working on a CI 2 application, and I'm having trouble storing multibyte characters to my MSSQL database.

The reason I believe this has to do with CodeIgniter and not SQL is because if I try to store a chinese string directly using SQL Server Management Studio, the string is stored just fine, but when I enter the same data through a form, all I get in the table are question marks where the characters should be.

These are my database settings:

Code:

$db['local']['dbdriver'] = 'sqlsrv';
$db['local']['dbprefix'] = '';
$db['local']['pconnect'] = FALSE;
$db['local']['db_debug'] = TRUE;
$db['local']['cache_on'] = FALSE;
$db['local']['cachedir'] = '';
$db['local']['char_set'] = 'utf8';
$db['local']['dbcollat'] = 'utf8_general_ci';
$db['local']['swap_pre'] = '';
$db['local']['autoinit'] = TRUE;
$db['local']['stricton'] = FALSE;

And this is the structure of the table I'm currently testing on:

Code:

CREATE TABLE [dbo].[languages](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [language] [nvarchar](1024) NULL,
    [language_local] [nvarchar](1024) NULL,
    [lang_code] [nvarchar](100) NULL,
    [core] [bit] NULL,
CONSTRAINT [PK_languages] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,         ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

And this is my charset encoding setting in my config.php

Code:

$config['charset'] = 'utf-8';

Finally, all the strings are being validated by the form validation library with required|trim rules.

There is also a question made in Stack Overflow, so if you want the reputation you can answer there

Thanks for your time!

[eluser]Aken[/eluser]
Are you using XSS filters on any inputs? It might affect Eastern characters.

[eluser]Loupax[/eluser]
I do, but even when I disabled the XSS filtering I got the same result.

[eluser]InsiteFX[/eluser]
Try changing the collation to:

Code:

$db['local']['dbcollat'] = 'utf8_unicode_ci';

You will also need to make sure your database is using the same collation.

[eluser]Loupax[/eluser]
That didn't work either... My guess is that the problem lies in the fact that MSSQL does NOT have a "default collation" setting in the same way mySQL does... I didn't understand it quite well, but it looks like the nvarchar data types find the proper collation automatically...

And they said that if you pay for it, it will work more smooth :/