viernes, 30 de marzo de 2012

Cannot resolve collation conflict for equal to operation

Collation in SQL Server can be set at column level.

If you have differente collation in two columns, and you try to make some kind of query with one of these things:

1) Join predicates

2) Where clauses

3) Use more than one database or temporal tables  (for example: TempDB with different collation than the other database).

4) Functions

The error message is:

Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "Modern_Spanish_CI_AS" in the equal to operation.

(Latin1_General_CI_AI and Modern_Spanish_CI_AS are collation examples).

See the example to resolve collation column conflict:

Test1.Col1 ->Latin1_General_CI_AI (DATABASE_DEFAULT)

Test2.Col2 ->Modern_Spanish_CI_AS

You can set the COLLATE to Query:

 

SELECT ID FROM Test1


INNER JOIN Test2


WHERE Test1.Col1 = Test2.Col1 COLLATE DATABASE_DEFAULT (or COLLATE Latin1_General_CI_AI)



Another options are set the same Collation to any level column, database, or servers.

To see the collation database, go to properties database, or use this:

SELECT DATABASEPROPERTYEX('DATABASE', 'Collation') as DB;


miércoles, 28 de marzo de 2012

SSMA Bug Migration Sybase to SQL Server

I have been involved with a Sybase migration from Sybase to SQL Server
The migration wizard SSMA(version 5.2.1260) makes the next wrong conversion.

The scenario is:

  • Table named NCO

  • Table named TestConversion with colum name NCO

  • Any statement (Insert, Update, Select...)


Update TestConversion
SET NCO = 0
where NCO > 1


SSMA converted statement

Update TestConversion
SET NCO = 0
where dbo.NCO > 1


SSMA identifies NCO like a table (because the field name matches with Table name) and adds the prefix dbo. (or the name schema especified in the application).
If we want to execute this sentence, it returns an error.

The solution is to change the column name for another name that doesn't match with any table name, or correct all sentences, deleting dbo. in applied cases.