Versions Compared

Version Old Version 6 New Version 7
Changes made by

Julie Main (Deactivated)

Julie Main (Deactivated)

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 just fine 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
 

...