I'm trying to store the German ß in SQL Server, but it results in ß. For example "Gleimstraße" is stored as "Gleimstraße"

But this also happens for other characters:

  • "König-Karl-Stra­ße" stored as "König-Karl-Straße"
  • "Quai André-Citroën" stored as "Quai André Citroën"
  • "Carrer dels Adreçadors stored as "Carrer dels Adreçadors"

I looked at the database collation and it shows SQL_Latin1_General_CP1_CI_AS. I then Googled and found this.

But I have no idea if my existing collation is causing the issue, or what I need to do to be able to store the special characters like ö, ß, é, ë, ç, ñ, í.


Indeed, I'm storing the strings in an nvarchar column. I send the data to the database via my .NET application. This is what I have in my web.config as the connection string:
<add name="conn" connectionString="data source=(local)\sql;Initial Catalog=mydb;User Id=myuser;Password=mypassword;" providerName="System.Data.SqlClient"/>


I read values from a JSON file which I've passed into a Newtonsoft.Json.Linq.JArray and then pass it to a function, like below:

    InsertStreetId(item.SelectToken("location.street").Value(Of String))

  Public Shared Function InsertStreetId(ByVal street As String) As Integer
        Dim streetId As Integer

        Dim myConnection As SqlConnection = GetConnection()
        Dim cmd As New SqlCommand("INSERT INTO geo_streets(streetname) VALUES (@streetname)" +
                             ";SELECT CAST(scope_identity() as int);", myConnection)
        cmd.Parameters.Add(New SqlParameter("@streetname", street))

            streetId = CInt(cmd.ExecuteScalar)
        Catch ex As Exception
        End Try
        Return streetId
    End Function


Ok, so here's what I did:

Dim myConnection As SqlClient.SqlConnection = GetConnection()

Dim cmd As New SqlClient.SqlCommand("UPDATE cities SET name=@name,updatedate=getdate() WHERE geonameid=@geonameid", myConnection)
cmd.Parameters.Add(New SqlClient.SqlParameter("@name", "Kąty Wrocławskie"))
cmd.Parameters.Add(New SqlClient.SqlParameter("@geonameid", 6474))


This correctly inserts the string "Kąty Wrocławskie" into my database. So I guess the database collation is working properly to support special characters?

If so, I now may have another issue, but please let me know if it's best to create a new issue on that: As stated, newly inserted records work fine. However, in my database I still have values like: "SÄpĂłlno KrajeĹskie" (it should be "Sępólno Krajeńskie").
Should I now do a find/replace on all columns? And if so, where can I find a mapping table to know what to look for and what te replace with, because I have no idea where to start.

  • In some php sql drivers it is required to use correct TDS version and never convert any charset/encoding in SQL data exchange.
    – Arvo
    Jun 3, 2022 at 8:47
  • @Adam I've updated my answer based on Update 2 in the question. Please review. Jun 3, 2022 at 16:10
  • @SolomonRutzky Sorry, was out for a bit. Thanks, I'll check on this and report back asap
    – Adam
    Jun 18, 2022 at 12:36

1 Answer 1


Somehow the data is being sent over as UTF-8 bytes. Because your database collation is SQL_Latin1_General_CP1_CI_AS, that uses code page 1252 (Latin1) for VARCHAR data. The ß character is encoded as bytes C3 9F. In code page 1252, those two bytes equate to the following characters: Ã and Ÿ.

You can see this for yourself by executing the following in SSMS or Visual Studio Code, etc:


One option to solve this would be to use a UTF-8 collation (i.e. one ending in _UTF8) as the database default collation, which you can do since you are using SQL Server 2019.

If you can't, or simply do not want to, change the database default collation to a _UTF8 collation, then you will need to find a way to change the encoding / culture / locale / collation of the connection to match your database code page (i.e. Windows 1252). Fortunately code page 1252 does store those characters (i.e. ö, ß, é, ë, ç, ñ, í ), but if you need to store a greater range of characters, you might need to either switch to a UTF-8 collation (if sticking with VARCHAR data), OR switch to using NVARCHAR columns / data (in which case you need to prefix your string literals, in the app, with an upper-case N, and/or specify the NVARCHAR instead of VARCHAR datatype for parameters, depending on how the data is being sent). Both of those options are Unicode, which can handle all characters. If switching to NVARCHAR data and columns (which could be a much larger change), the database connectivity software should assume the encoding to be UTF-16 Little Endian without you needing to specify that.

Update 2 of the Question indicates two possible suspects:

  1. I read values from a JSON file which I've passed into a Newtonsoft.Json.Linq.JArray


  2. then pass it to a function:

    cmd.Parameters.Add(New SqlParameter("@streetname", street))

Let's start with the second item since it's an issue even if it's not the source of this particular problem.

You are using the following constructor for SqlParameter: SqlParameter(String, Object) (which I'm guessing is the same as the infamous SqlParameterCollection.AddWithValue(String, Object) method). The problem here is that the datatype is inferred from the value, and sometimes mistakes can be made when trying to guess the datatype. It is always better to specify the datatype explicitly. And you know the datatype. So, use the following instead (be sure to adjust the size of the parameter to match the actual column if not 500):

Dim param as New SqlParameter("@streetname", SqlDbType.NVarChar, 500)
param.Value = street

You should make that change first to see if it fixes the problem (and then fix all code that creates parameters without explicitly setting the datatype). While it will be an improvement in either case, I suspect that it won't actually solve the problem. I think that it would have to infer the type being XML in order to convert to UTF-8 encoding, and I don't believe that would happen with just a name and no actual XML in the value.

The other item of interest is reading the data from a file via Json.NET. I wouldn't be surprised if you need to explicitly set the output encoding else it defaults to UTF-8. It appears that the file is being read correctly since ß does indeed encode into UTF-8 as the bytes C3 9F.

So, if fixing how the SqlParameter is created doesn't fix the issue, then you need to look into setting the encoding of the JSON.

I highly recommend that you save the bytes of the value coming into your InsertStreetId method to a file for debugging, using something like:

BitConverter.ToString( BitConverter.GetBytes( street ) )

I suspect you will see a value such as:

4700 6C00 6500 6900 6D00 7300 7400 7200 6100 C300 7801 6500

which is the UTF-16 LE representation of N'Gleimstraße'. If this is the case, then the issue is happening prior to the InsertStreetId method being called, which means this has nothing to do with SQL Server.

  • 2
    Collation could be also set at column level, but very likely OP would benefit of a database level collation.
    – bradbury9
    Jun 3, 2022 at 6:21
  • Thanks @solomon and @bradbury9. I updated the post to reflect what I missed. Seeing that, would it be best AND safe to change my full database collation to an _UTF8 collation? If so, which one would be best?
    – Adam
    Jun 3, 2022 at 9:50
  • @Adam Based on the updated info and findings (from June 3rd), it appears that the problem might be in the app code, so most likely you shouldn't change anything at the DB-level. Please confirm by posting the results of my recommended test to the question. Thanks. Jun 18, 2022 at 23:30
  • 1
    @Adam You don't need any UTF-8 collation. As you said, you aren't using one now and things are getting inserted correctly. That's because you're using all NVARCHAR (also Unicode, but UTF-16). You just need to pick the culture that you will use the most for sorting and filtering. Latin1_General is Western European, so SQL_Latin1_General_CP1_CI_AS is ok. The only problem with that collation is that it's old and missing definitions added in the Latin1_General_100_* series. Ideally you would use Latin1_General_100_CI_AS_SC (_SC = functions handle supplementary chars correctly). Aug 18, 2022 at 16:01
  • 1
    @Adam There are some rather unlikely risks. Please see my post, "Changing the Collation of the SQL Server Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?", for details. Also, regarding fixing the existing incorrectly imported characters: just do UPDATE statements while forcing a binary collation by adding COLLATE Latin1_General_100_BIN2 to the WHERE clause. Does that make sense? Aug 18, 2022 at 19:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.