-1

I'm trying to run a query like this in SQL Server:

SELECT 
    a.itemID, a.ItemName 
FROM 
    [SRVR1].[DB1].[dbo].[ItemsOnStock]
WHERE 
    a.itemID NOT IN (SELECT b.ID FROM [SRVR2].[DB10].[dbo].[InTransItems] b)

The column types are as follows:

a.itemID, nvarchar(80)
a.ItemName, nvarchar(100)
b.ID, nvarchar(100)

When the query run, I get this error:

Cannot resolve the collation conflict between "SQL_Latin1_General_CP850_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

How do I rewrite the query?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Jaime Dolor jr.
  • 897
  • 1
  • 11
  • 28
  • 1
    Ideally, you'd have databases of the same collation set when you are doing joins, or other kinds of cross-querying. However, sometimes it's not as simple as being able to dictate the collation of a (particularly live/production) database, if there are constraints or requirements coming from elsewhere. This previous question gives you some tips on how to specify the collation for joins / column matching https://stackoverflow.com/questions/39300955/how-to-use-the-collate-in-a-join-in-sql-server – Craig Feb 23 '23 at 04:53
  • This question has been answered many times before. – Dale K Feb 23 '23 at 05:15

1 Answers1

1

What you are facing is not a different type but different encoding for the nvarchar. I strongly advise you to change the encoding to UTF-8 for all databases and tables unless there is a strong reason to do otherwise. Otherwise, you need to encode the first string to the same encoding as the second or vice versa.

Tarik
  • 10,810
  • 2
  • 26
  • 40
  • 3
    You seem to be conflating collation and encoding here. For `nchar` and `nvarchar` data types the strings are considered to be UCS-2 encoding, unless using a Supplementary Characters collation with the `_SC` suffix in which case they are considered to be UTF-16 encoding. UTF-8 encoding is something that can be applied to `char` and `varchar` data types. – AlwaysLearning Feb 23 '23 at 04:54
  • 1
    Ignoring the encoding differences above, collation is used to determine the equivalence of character sequences and how they sort, for example in [Danish_Norwegian_CS_AS](https://collation-charts.org/mssql/mssql.0406.1252.Danish_Norwegian_CS_AS.html) the characters `å` and `aa` are equivalent, and the characters `Å`, `Aa` and `AA` are equivalent. Their other main function is to determine character mappings for octets in the range of 128-255, as in which code page is used for non-ASCII characters. – AlwaysLearning Feb 23 '23 at 04:54
  • @AlwaysLearning Regardless of the encoding used, at least stick to one to avoid this kind of problem while I am certain people have other fish to fry than solving this kind of problem. Furthermore, UTF-8 stores stuff like emojis and whatever character is needed, which can come handy in many situations. – Tarik Feb 23 '23 at 05:02