Versions Compared

Version Old Version 19 New Version 20
Changes made by

Julie Main

Julie Main

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Code Block
languagesql
titleCollation example
CREATE TABLE #test
(
  ID        INTEGER,
  Surname   VARCHAR(50),
  Given1    VARCHAR(30)
)
SELECT * FROM #test WHERE Surname = 'rose' -- this will work as we are not comparing with data from a table in the current database
 
-- however if we try
SELECT * FROM #test WHERE Surname IN (SELECT Surname FROM community)
-- this causes a collation conflict as we are comparing string values across databases with different collation sequences

 

The resolution is to specify the collation on each column that will be compared with a field in the source database.

Code Block
languagesql
titleCorrect collation order handling
CREATE TABLE #test
(
  ID        INTEGER,
  Surname   VARCHAR(50) COLLATE DATABASE_DEFAULT,
  Given1    VARCHAR(30)
)

SELECT * FROM #test WHERE Surname IN (SELECT Surname FROM community) -- now it works

...

Note that you only need to specify collation sequence on fields you are doing cross database comparisons on.  The Given1 field in the example above does not need it as we are only displaying it.
...