| Excerpt |
|---|
BackgroundA collation sequence is a database setting that controls how string values are ordered. In Synergetic V67 and all prior versions the collation sequence was required to be the SQL collation 'SQL_Latin1_General_CP1_CI_AS'. The SQL server must have been installed with that specific collation sequence and that determined the collation sequence of the master and temp databases of that SQL Server instance. This caused the following limitations at a client organisation:
Alongside these limitations, it seems that SQL_ collation sequences are no longer being updated and will eventually be phased out in favour of Windows collation sequences. Some of the newer Unicode characters are not in the SQL collation but do exist in Windows ones. Changes in V68 |
...
Changes have been made to support different collation sequences to remove these limitations and future proof Synergetic. In V68, the collation sequence of the Synergetic database can be changed to any Case Insensitive (CI) collation sequence and is also permitted to be different to the collation sequence of the SQL Server instance. By default, the Synergetic database in V68 will be changed to the Windows collation sequence Latin1_General_CI_ |
...
AI Benefits of Alternate Collation SequencesThe main noticeable difference of using alternate collation sequences is when performing searching:
|
For most client organisations this change will be invisible. If However, if the organisation has created their own user triggers or user stored procedures that use the tempdb and the Synergetic database uses a different collation sequence to tempdb, they may have to change that user code.
SQL code across databases with different collation sequences
The main issue with collation sequences is when you are comparing table string fields across databases. For example the tempdb database uses the same collation sequence as the model database which uses the server default collation.
The below example assumes the tempdb database is a different collation sequence to the current database.encounter an error such as the below:
| Code Block | ||||
|---|---|---|---|---|
| ||||
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 Cannot resolve the 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 | ||||
|---|---|---|---|---|
| ||||
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 does not need it as we are only displaying it.
I also believe it is a good idea to specify the collation with table variables too as they are backed by the temp database
If the temp table is created by a SELECT INTO we do not appear to have collation issues… Only when we CREATE and then INSERT.
| Code Block | ||||
|---|---|---|---|---|
| ||||
-- In this example we are using system tables therefore we cannot change the collation sequence of the table itself. -- We need to override the collation sequence on the actual field comparison SELECT sd.DatabaseName FROM SynDatabases sd WHERE NOT EXISTS ( SELECT * FROM master.sys.databases md WHERE md.name = sd.DatabaseName COLLATE DATABASE_DEFAULT )between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation. |
If this type of error is encountered the collation sequence will need to be explicitly specified as outlined in the next section.
Further information:
| Child pages (Children Display) |
|---|