0

I have an odd scenario that I'm looking for some clarification on.

I currently have an VB.net application that is sending the parameterized query below to an SQL Server 2019 database using ADO.net. To make the database support UTF8, we are using one of the new _UTF8 collations. For this test, we are using a database configured to use Latin1_General_100_CI_AI_SC_UTF8.

Query:

exec sp_executesql 
   N'UPDATE Roles SET Name = @pName WHERE Role_ID = 6',
   N'@pName varchar(1000)',
   @pName='æææDeveloper'

Now, I know that when I run the query above in MSSQL Management Studio, I don't lose my 'æ' characters but if I run this through code, my characters are replaced by '?'. My question is why would I lose them through code via ADO.net and not through MSSQL Management Studio.

I also know that sp_executesql does not support varchar as parameters which was the main problem and we were able to solve but again. Why would it work in MSSQL but not in ADO.net?

dj.canuk
  • 13
  • 4
  • Curious why you would downgrade to UTF-8 when Sql Server NVarChar values use UTF-16 by default, which can encode more characters, and ADO.Net is still sending UTF-16 or UTF-32 data to the server. – Joel Coehoorn Nov 06 '20 at 14:28
  • 1
    Also, it would be nice to see the VB.Net side of things in the question, so we can know how this code is invoked. And shouldn't `@pName='æææDeveloper'` be `@pName=N'æææDeveloper'`? – Joel Coehoorn Nov 06 '20 at 14:30
  • Not if the database is UTF8, @JoelCoehoorn . – Thom A Nov 06 '20 at 14:30
  • @Larnu That's my point. Sql Server databases aren't UTF-8 by default, and changing them to be that way should be seen as a downgrade. – Joel Coehoorn Nov 06 '20 at 14:31
  • This is a 20 year old system and all datatypes are using varchar, char and text instead of their N superior types. They don't want to change them due to increased database size and potential performance implications. – dj.canuk Nov 06 '20 at 14:32
  • Indeed, I wonder if the OP is using a database in UTF-8, or a column. If the former, I would expect the above to work, but I suspect it's only the column, meaning that the data would be lost as the variable would *not* be UTF-8. – Thom A Nov 06 '20 at 14:32
  • My question is about why I lose the characters when running through .net and not when running the same query through MSSQL Management Studio. I know the variable is supposed to be @pName=N'æææDeveloper' as I already implemented that fix which is why I didn't include the VB.net code. – dj.canuk Nov 06 '20 at 14:37
  • SQL doesn't change the way it runs is the same SQL is used in 2 different applications, @dj.canuk, which means that either the SQL isn't the same, or the VB.net is using different connection settings to what you are in SSMS. Without the VB.net, we don't know. Are you also saying you're executing that statement, with `sp_executesql`, and not doing something like simply `SET Name = 'æææDeveloper'`? – Thom A Nov 06 '20 at 14:42
  • I run profiler to see what query is being run by the application. Copy that into SSMS and get different results. I'm using a basic query string of Server, Database and windows authentication. – dj.canuk Nov 06 '20 at 14:49
  • _This is a 20 year old system and..._ on the VB.net side don't you need a modern .NET Framework/.NET Core using [Microsoft.Data.SqlClient](https://learn.microsoft.com/en-us/sql/connect/ado-net/introduction-microsoft-data-sqlclient-namespace) to support UTF-8 on SQL Server 2019? – AlwaysLearning Nov 06 '20 at 15:13
  • Yes, the code in question is using ADO.net – dj.canuk Nov 06 '20 at 15:42
  • 1
    @JoelCoehoorn: UTF8 has the same range of characters as UTF16, it's just optimized for western characters that usually use up only one 1 byte. But e.g. for Chinese characters it uses 3 bytes each, where UTF-16 is better suited with 2 bytes... – Christoph Nov 06 '20 at 19:23
  • ADO.net uses System.Data.SqlClient, not Microsoft.Data.SqlClient. In System.Data.SqlClient some third party providers implement custom UTF-8 support, such as SAP HANA with `CHAR_AS_UTF8=True` in the Connection String. If someone could show that the SQL Server provider for System.Data.Sql has similar support I'd be interested to see it. – AlwaysLearning Nov 07 '20 at 00:20

0 Answers0