collation conflict

why do i get collation conflict when i used temp table ??

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

i solved it by using COLLATE Latin1_General_CI_AS (the column name)

will i have collation conflicts again when i put my web app on a web hosting company??

Hi Kakusei,

why do i get collation conflict when i used temp table ??

I assume your database use a different collation from the default one( the default collation is the one which is used by your sql instance). If you create a user database and specify a different default collation thanmodel, the user database has a different default collation thantempdb. All temporary stored procedures or temporary tables are created and stored intempdb. This means that all implicit columns in temporary tables and all coercible-default constants, variables, and parameters in temporary stored procedures have collations that are different from comparable objects created in permanent tables and stored procedures.

This could lead to problems with a mismatch in collations between user-defined databases and system database objects. As to fix it, just specify the collation name when you create temp tables, for example:

CREATE TABLE #TestTempTab (PrimaryKey int PRIMARY KEY, Col1 nchar COLLATE database_default -- use database_default option to explictly specify the temp table use the same collation as the user database )

You can find more detailed information at:http://msdn2.microsoft.com/en-us/library/ms190920.aspx

Hope my suggestion helps

|||

Hi Bo, thanks for your reply and solution.

I also want to know what is the default collation ? and how can i change it back to the default collation now?

|||

Hi Kakusei,

The default collation is the one which your selected when you install sql server. It diverse from the sql verstion, local region, etc. You can check it through Management Studio (After you have loggin to sql server , right click the instance name and select "properties", there is one entry called "server collation" which is the default collation i mean here). All the talbes, procedure, functions use default collation in tempdb. If you do not specify a collation when creating database/talbes in your user database, then it use default collation.

I suggest you reading that madn article again, it's highly explanatory. I'm sure you will learn pretty a lot after finish reading that. thanks.

Hope my suggestion helps