Connection to database with valid collation returns "The Collation specified by SQL Server is not supported." (Kazakh_90_CI_AS)

[dineubr]

Description

When we connect to databases that have the collation Kazakh_90_CI_AS and run queries with explicit text on field names (such as "select 'test'") we get the following error:

Exception message: The Collation specified by SQL Server is not supported
Stack trace:
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bul
kCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCo
pyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.DrainData(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.ThrowUnsupportedCollationEncountered(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParserStateObject.TryReadStringWithEncoding(Int32 length, Encoding encoding, Boolean isPlp, String& value)
   at System.Data.SqlClient.TdsParser.TryReadSqlStringValue(SqlBuffer value, Byte type, Int32 length, Encoding encoding, Boolean isPlp, TdsParserS
tateObject stateObj)
   at System.Data.SqlClient.TdsParser.TryReadSqlValue(SqlBuffer value, SqlMetaDataPriv md, Int32 length, TdsParserStateObject stateObj, SqlCommand
ColumnEncryptionSetting columnEncryptionOverride, String columnName)
   at System.Data.SqlClient.SqlDataReader.TryReadColumnInternal(Int32 i, Boolean readHeaderOnly)
   at System.Data.SqlClient.SqlDataReader.TryReadColumn(Int32 i, Boolean setTimeout, Boolean allowPartiallyReadColumn)
   at System.Data.SqlClient.SqlDataReader.GetValues(Object[] values)
   at System.Data.ProviderBase.SchemaMapping.LoadDataRow()
   at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
   at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 s
tartRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
   at System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable,
 IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBeha
vior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
   at CallSite.Target(Closure , CallSite , Object , Object )

To reproduce

1. Create a database using the collation Kazakh_90_CI_AS
2. Connect to the database using the database name as the Initial Catalog (as the example below):

Data Source=localhost;Initial Catalog=CollationKazakh;Integrated Security=SSPI;MultiSubnetFailover=False;Connect Timeout=15;Pooling=False

3. Run the query SELECT 'test'

Here is a powershell script that connects and executes the query:

try
{
$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "Data Source=localhost;Initial Catalog=CollationKazakh;Integrated Security=SSPI;MultiSubnetFailover=False;Connect Timeout=15;Pooling=False"
$conn.open()

# SELECT 'test'
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.connection = $conn
$cmd.CommandText = "SELECT 'test'"
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $cmd
$DataSet = New-Object System.Data.DataSet
$res = $SqlAdapter.Fill($DataSet)


}
catch
{
	$_.Exception.Message
	$_.Exception.StackTrace
    if ($_.Exception.InnerException -ne $null)
	{
		$_.Exception.InnerException.Message
		$_.Exception.InnerException.StackTrace
    }
}

Expected behavior

It was expected to have one row with 'test' as a result.

Further technical details

dotnet --info:

.NET Core SDK (reflecting any global.json):
 Version:   3.1.201
 Commit:    b1768b4ae7

Runtime Environment:
 OS Name:     Windows
 OS Version:  10.0.14393
 OS Platform: Windows
 RID:         win10-x64
 Base Path:   C:\Program Files\dotnet\sdk\3.1.201\

Host (useful for support):
  Version: 3.1.3
  Commit:  4a9f85e9f8

.NET Core SDKs installed:
  3.1.201 [C:\Program Files\dotnet\sdk]

.NET Core runtimes installed:
  Microsoft.AspNetCore.App 3.1.3 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
  Microsoft.NETCore.App 3.1.3 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.WindowsDesktop.App 3.1.3 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App]

.NET Framework Version (Registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\NET Framework Setup\NDP\v4\Full): 4.8.03761

System.Data.dll:

PS C:\Windows\Microsoft.NET\assembly\GAC_64\System.Data> Get-ChildItem -Filter *.dll -Recurse | Select-Object -ExpandProperty VersionInfo

ProductVersion   FileVersion      FileName                                                                                                       
--------------   -----------      --------                                                                                                       
4.8.4121.0       4.8.4121.0 bu... C:\Windows\Microsoft.NET\assembly\GAC_64\System.Data\v4.0_4.0.0.0__b77a5c561934e089\System.Data.dll 

[karinazhou]

Hi @dineubr ,
Thank you for reporting this. I am working on reproducing it locally.

---

One question, what's the data type of your column where you inserted the 'test' value?

[dineubr]

Hi Karina! Thanks for your help with this.
In fact, there is no need to have a test table: the select that fails does not contain any table specified in the "FROM", I mean, the full command line is:
select 'test'

But in order to do other tests using inserts and updates for example, I created a simple table through the following code:
CREATE TABLE TestTable ( id INT null, testField nvarchar(255) null )

Let me know if I can help with anything else :)

[karinazhou]

@dineubr Thanks for the information. I am able to reproduce it with both powershell and SSMS.
Though System.Data.SqlClient nad Microsoft.Data.SqlClient have the same code base, the latter has more fixes. I will try with the latest master branch for Microsoft.Data.SqlClient to see whether the same issue is still there.

BTW, I tried with sqlcmd which uses ODBC driver and didn't see the exception. And I also checked with other collations such as Uzbek_Latin_90_CI_AS / Thai_100_CI_AS / Persian_100_CI_AS / Korean_90_CI_AS / Kazakh_100_CI_AS and found that only Kazakh has this collation errors. I will get back to you if I find anything behind the scene.

[karinazhou]

@dineubr I look more into the driver's code. We get CollationUnsupportedException because the code page we get from CultureInfo.GetCultureInfo(cultureId).TextInfo.ANSICodePage equals zero when cultureId is 1087 (Kazakh_90_CI_AS).

This piece of code can be found in
src/Microsoft.Data.SqlClient/netfx/src/Microsoft/Data/SqlClient/TdsParser.cs : GetCodePage().

When we reach codePage = 0, we won't be able to get its corresponding encoding. The following query you are using
SELECT 'test'

will follow the path of how we deal with narrow characters in the driver. This requires valid encoding for the database collation. Otherwise, it will throw CollationUnsupportedException.

One workaround for your case is to use wide characters in the query:
SELECT N'test'

In this way, the driver will pass the test value as a Unicode string to the server which doesn't require encoding conversion.

[karinazhou]

@dineubr After more investigation and discussion about this, the 0 code page returned from CultureInfo.GetCultureInfo(cultureId).TextInfo.ANSICodePage is misused by the .NET driver. Compared to other drivers such as ODBC and JDBC, they all follow the corresponding code pages which are used by the SQL Server. In this Kazakh_90_CI_AS case, the correct code page should be 1251.

A temporary fix for this will be created soon. In order to avoid this kind of mismatching code page and collation scenarios in the future, we will need a new design in the driver to fetch the correct code page according to the SQL Server standard.

[dineubr]

Hi @karinazhou. Thanks for the update and the quick reply on the investigation :)
I will keep waiting for the fix.

[cheenamalhotra]

Hi @dineubr

Since #584 is now merged, we will close the issue.
It will be released with v2.0.0 soon. Feel free to re-open if you face any issues.