DbMessageSource.php Database Exception Mssql

[itzen]

Database Exception when setting language to eg. en-US or pl-PL.

Database server: MSSQL 2012 (with mysql everything works fine)
Driver:sqlsrv (php_pdo_sqlsrv_55_ts.dll)
Apache/2.4.10 (Win32) OpenSSL/1.0.1i PHP/5.5.15, Windows 7
Yii Framework/2.0.2-dev

To reproduce this error:
echo Yii::$app->i18n->translate('frontend', 'Home', array(), 'en-US');
throws error,

echo Yii::$app->i18n->translate('frontend', 'Home', array(), 'en');
works fine.

Probably problem is in DbMessageSource.php in protected function loadMessagesFromDb($category, $language).

Database Exception – yii\db\Exception SQLSTATE[07002]: [Microsoft][ODBC Driver 11 for SQL Server]COUNT field incorrect or syntax error The SQL being executed was: (SELECT [t1].[message] AS [message], [t2].[translation] AS [translation] FROM [i18n_source_message] [t1], [i18n_message] [t2] WHERE t1.id = t2.id AND t1.category = 'frontend' AND t2.language = 'en-US') UNION ALL ( SELECT [t1].[message] AS [message], [t2].[translation] AS [translation] FROM [i18n_source_message] [t1], [i18n_message] [t2] WHERE (t1.id = t2.id AND t1.category = 'frontend' AND t2.language = 'en') AND (t2.id NOT IN (SELECT id FROM [i18n_message] WHERE language = 'en-US')) )

Error Info: Array ( [0] => 07002 [1] => 0 [2] => [Microsoft][ODBC Driver 11 for SQL Server]COUNT field incorrect or syntax error )

Caused by: PDOException SQLSTATE[07002]: [Microsoft][ODBC Driver 11 for SQL Server]COUNT field incorrect or syntax error

in \vendor\yiisoft\yii2\db\Command.php at line 821

`exception 'PDOException' with message 'SQLSTATE[07002]: [Microsoft][ODBC Driver 11 for SQL Server]COUNT field incorrect or syntax error' in \vendor\yiisoft\yii2\db\Command.php:821 Stack trace: #0 \vendor\yiisoft\yii2\db\Command.php(821): PDOStatement->execute() #1 \vendor\yiisoft\yii2\db\Command.php(350): yii\db\Command->queryInternal('fetchAll', NULL) #2 \vendor\yiisoft\yii2\i18n\DbMessageSource.php(161): yii\db\Command->queryAll() #3 \vendor\yiisoft\yii2\i18n\DbMessageSource.php(124): yii\i18n\DbMessageSource->loadMessagesFromDb('frontend', 'en-US') #4 \vendor\yiisoft\yii2\i18n\MessageSource.php(107): yii\i18n\DbMessageSource->loadMessages('frontend', 'en-US') #5 \vendor\yiisoft\yii2\i18n\MessageSource.php(87): yii\i18n\MessageSource->translateMessage('frontend', 'Home', 'en-US') #6 \vendor\yiisoft\yii2\i18n\I18N.php(89): yii\i18n\MessageSource->translate('frontend', 'Home', 'en-US') #7 \frontend\controllers\SiteController.php(34): yii\i18n\I18N->translate('frontend', 'Home', Array, 'en-US') #8 [internal function]: frontend\controllers\SiteController->actionIndex() #9 \vendor\yiisoft\yii2\base\InlineAction.php(55): call_user_func_array(Array, Array) #10 \vendor\yiisoft\yii2\base\Controller.php(151): yii\base\InlineAction->runWithParams(Array) #11 \vendor\yiisoft\yii2\base\Module.php(455): yii\base\Controller->runAction('index', Array) #12 \vendor\yiisoft\yii2\web\Application.php(83): yii\base\Module->runAction('', Array) #13 \vendor\yiisoft\yii2\base\Application.php(375): yii\web\Application->handleRequest(Object(yii\web\Request)) #14 \frontend\web\index.php(37): yii\base\Application->run() #15 {main} Next exception 'yii\db\Exception' with message 'SQLSTATE[07002]: [Microsoft][ODBC Driver 11 for SQL Server]COUNT field incorrect or syntax error The SQL being executed was: (SELECT [t1].[message] AS [message], [t2].[translation] AS [translation] FROM [i18n_source_message] [t1], [i18n_message] [t2] WHERE t1.id = t2.id AND t1.category = 'frontend' AND t2.language = 'en-US') UNION ALL ( SELECT [t1].[message] AS [message], [t2].[translation] AS [translation] FROM [i18n_source_message] [t1], [i18n_message] [t2] WHERE (t1.id = t2.id AND t1.category = 'frontend' AND t2.language = 'en') AND (t2.id NOT IN (SELECT id FROM [i18n_message] WHERE language = 'en-US')) )' in \vendor\yiisoft\yii2\db\Schema.php:532 Stack trace: #0 \vendor\yiisoft\yii2\db\Command.php(836): yii\db\Schema->convertException(Object(PDOException), '(SELECT [t1].[m...') #1 \vendor\yiisoft\yii2\db\Command.php(350): yii\db\Command->queryInternal('fetchAll', NULL) #2 \vendor\yiisoft\yii2\i18n\DbMessageSource.php(161): yii\db\Command->queryAll() #3 \vendor\yiisoft\yii2\i18n\DbMessageSource.php(124): yii\i18n\DbMessageSource->loadMessagesFromDb('frontend', 'en-US') #4 \vendor\yiisoft\yii2\i18n\MessageSource.php(107): yii\i18n\DbMessageSource->loadMessages('frontend', 'en-US') #5 \vendor\yiisoft\yii2\i18n\MessageSource.php(87): yii\i18n\MessageSource->translateMessage('frontend', 'Home', 'en-US') #6 \vendor\yiisoft\yii2\i18n\I18N.php(89): yii\i18n\MessageSource->translate('frontend', 'Home', 'en-US') #7 \frontend\controllers\SiteController.php(34): yii\i18n\I18N->translate('frontend', 'Home', Array, 'en-US') #8 [internal function]: frontend\controllers\SiteController->actionIndex() #9 \vendor\yiisoft\yii2\base\InlineAction.php(55): call_user_func_array(Array, Array) #10 \vendor\yiisoft\yii2\base\Controller.php(151): yii\base\InlineAction->runWithParams(Array) #11 \vendor\yiisoft\yii2\base\Module.php(455): yii\base\Controller->runAction('index', Array) #12 \vendor\yiisoft\yii2\web\Application.php(83): yii\base\Module->runAction('', Array) #13 \vendor\yiisoft\yii2\base\Application.php(375): yii\web\Application->handleRequest(Object(yii\web\Request)) #14 \frontend\web\index.php(37): yii\base\Application->run() #15 {main} Additional Information: Array ( [0] => 07002 [1] => 0 [2] => [Microsoft][ODBC Driver 11 for SQL Server]COUNT field incorrect or syntax error )

[MDjet]

Hi a have problem with MSSQL 2012
When i write
I get this error

Database Exception – yii\db\Exception
SQLSTATE[07002]: [Microsoft][ODBC Driver 11 for SQL Server]COUNT field incorrect or syntax error
The SQL being executed was: (SELECT [t1].[message] AS [message], [t2].[translation] AS [translation] FROM [source_message] [t1], [message] [t2] WHERE t1.id = t2.id AND t1.category = 'frontend/pages' AND t2.language = 'ru-RU') UNION ALL ( SELECT [t1].[message] AS [message], [t2].[translation] AS [translation] FROM [source_message] [t1], [message] [t2] WHERE (t1.id = t2.id AND t1.category = 'frontend/pages' AND t2.language = 'ru') AND (t2.id NOT IN (SELECT id FROM [message] WHERE language = 'ru-RU')) )

Error Info: Array
(
    [0] => 07002
    [1] => 0
    [2] => [Microsoft][ODBC Driver 11 for SQL Server]COUNT field incorrect or syntax error
)

[MDjet]

When i run this query no errors

(SELECT [t1].[message] AS [message], [t2].[translation] AS [translation] FROM [source_message] [t1], [message] [t2] WHERE t1.id = t2.id AND t1.category = 'frontend/pages' AND t2.language = 'ru-RU') UNION ALL ( SELECT [t1].[message] AS [message], [t2].[translation] AS [translation] FROM [source_message] [t1], [message] [t2] WHERE (t1.id = t2.id AND t1.category = 'frontend/pages' AND t2.language = 'ru') AND (t2.id NOT IN (SELECT id FROM [message] WHERE language = 'ru-RU')) )

[MDjet]

I found an error occurs when you use WHERE in UNION ALL. If we use UNION ALL without WHERE all work

[alexkart]

Not relevant for the MSSQL 12.0.2000.8 and 'ODBC Driver 17 for SQL Server', can be closed I think.