Archive

Archive for the ‘Collations’ Category

Compound Operators in SQL Server

July 5, 2011 1 comment

There was a time when we all wished SQL Server supports compound operators just like any other programming languages. Wouldn’t it be nice to have a shorter syntax for assigning the result of an arithmetic operator?  Well, SQL server 2008 introduced Compound operators that allow you to perform several arithmetic operations using an operand along with equal to (=) operand.

 For example:

Declare @var1  int

Set @var1 = 150

Set @var1 += 100

Read more…

 

 

Advertisements

resolving collation issues in sql server

August 4, 2009 Leave a comment

I had to write a query to fetch data from tables located in two different databases using a join. The collation used for the databases were different. This resulted in an error as below

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “SQL_Hungarian_CP1250_CI_AS” in the equal to operation
.

This was a new issue that I faced, May be because i never queried the databases on different collations :).

How ever this is easy to resolve. We can use the “COLLATE DATABASE_DEFAULT” in the join clause for the columns used from the second database. This will change the collation of the column to the default collation used in the database on which the query gets executed. An example is shown below


SELECT *
FROM
ARCOBJECTS ARO
INNER JOIN
TESTDB.DBO.SUBLIST SUB
ON
SUB.SUBNAME COLLATE DATABASE_DEFAULT= ARO.DRIVERTABLE

You can alternatively mention the collation that you want to use with “COLLATE <<collation Name>>

example:

SELECT *
FROM
ARCOBJECTS ARO
INNER JOIN
TESTDB.DBO.SUBLIST SUB
ON
SUB.SUBNAME COLLATE SQL_Latin1_General_CP1_CI_AS = ARO.DRIVERTABLE