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

Collation conflict

I have 2 servers, both of them have the same collation.
Then I create temp table on 1st server:
create table #tmpNovi (country char(3) COLLATE database_default ,datum
datetime)
Then fill the table.
Then I join this table to second server:
select s.* FROM
[SERVER2].[DW_Temp].[dbo].[t_stanje_cube] s INNER JOIN #tmpNovi n
ON s.RCO=n.country
and I get an error message:
Cannot resolve collation conflict for equal to operation.
Why? Both servers have the same collation, also temp table country field has
defined COLLATE database_default and still an error?
Thank you,
SimonRun below statement on both servers and post back the results:
SELECT DATABASEPROPERTYEX('pubs', 'Collation')
Substitute pubs with DW_Temp when you execute it on SERVER2 and with the dat
abase name from where
you create the temp table on the other server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"simon" <simon.zupan@.stud-moderna.si> wrote in message
news:%23P5qSQeNFHA.2520@.tk2msftngp13.phx.gbl...
>I have 2 servers, both of them have the same collation.
> Then I create temp table on 1st server:
> create table #tmpNovi (country char(3) COLLATE database_default ,datum dat
etime)
> Then fill the table.
> Then I join this table to second server:
> select s.* FROM
> [SERVER2].[DW_Temp].[dbo].[t_stanje_cube] s INNER JOIN #tmpNovi n
> ON s.RCO=n.country
> and I get an error message:
> Cannot resolve collation conflict for equal to operation.
> Why? Both servers have the same collation, also temp table country field h
as defined COLLATE
> database_default and still an error?
> Thank you,
> Simon
>|||Dear all,
As far as I know it's very simply: just for that is needed that both level o
f
COLLATION coinciding, i.e, level table, level database.
It's strange.
See you,
"simon" wrote:

> I have 2 servers, both of them have the same collation.
> Then I create temp table on 1st server:
> create table #tmpNovi (country char(3) COLLATE database_default ,datum
> datetime)
> Then fill the table.
> Then I join this table to second server:
> select s.* FROM
> [SERVER2].[DW_Temp].[dbo].[t_stanje_cube] s INNER JOIN #tmpNovi n
> ON s.RCO=n.country
> and I get an error message:
> Cannot resolve collation conflict for equal to operation.
> Why? Both servers have the same collation, also temp table country field h
as
> defined COLLATE database_default and still an error?
> Thank you,
> Simon
>
>|||Hi Tibor,
Great with DATABASEPROPERTYEX.I was wondering if it is possible to have
available a similar sentence but at table level?
thanx
"Tibor Karaszi" wrote:

> Run below statement on both servers and post back the results:
> SELECT DATABASEPROPERTYEX('pubs', 'Collation')
> Substitute pubs with DW_Temp when you execute it on SERVER2 and with the d
atabase name from where
> you create the temp table on the other server.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "simon" <simon.zupan@.stud-moderna.si> wrote in message
> news:%23P5qSQeNFHA.2520@.tk2msftngp13.phx.gbl...
>
>|||Try sp_help.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:3B49DD33-1160-4069-A161-7A5313738DBC@.microsoft.com...
> Hi Tibor,
> Great with DATABASEPROPERTYEX.I was wondering if it is possible to have
> available a similar sentence but at table level?
> thanx
> "Tibor Karaszi" wrote:
>