SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Express Edition and Compact Edition (2005)
 change collation of table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

esthera
Flowing Fount of Yak Knowledge

1393 Posts

Posted - 01/07/2007 :  08:59:08  Show Profile  Reply with Quote
i'm getting an error

[Microsoft][SQL Native Client][SQL Server]Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Hebrew_CI_AS" in the equal to operation.

How can I change these tables to have the saem collation? I did not purposely make it different.

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 01/07/2007 :  10:44:40  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
If you haven't designed the tables, try to find out the reasons for having different collation for two tables. Other option is to to explicitly make collations equal in the queries using COLLATE clause.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

esthera
Flowing Fount of Yak Knowledge

1393 Posts

Posted - 01/07/2007 :  10:49:16  Show Profile  Reply with Quote
i did design the tables but did not purposely use that collation
is there a way to change the collation with sql?
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 01/07/2007 :  11:07:09  Show Profile  Reply with Quote
The usual reasons for Collation errors are:

1) Using #TEmpTable or @AppVar tables and NOT specifying the collation when they are created, or creating a #TempTable on the fly with:

SELECT *
INTO #MyTempTable
FROM ...

OR
2) comparing columns from TWO DIFFERENT databases that have different collations.

3) Different tables WITHIN a single database have been defined with different collations.
Possible solutions:

1a) ALWAYS pre-create the Temp Tables (this is Good Practice anyway as it reduces locking time on TEMDB and lets the optimiser have a better shot at the query).
1b) ALWAYS include a COLLATE phrase for all CHAR/VARCHAR/TEXT columns in the CREATE TABLE statement

2) Include a COLLATE phrase to FORCE the collation of the comparison.

3) See (1b)

Kristen
Go to Top of Page

esthera
Flowing Fount of Yak Knowledge

1393 Posts

Posted - 01/07/2007 :  11:19:52  Show Profile  Reply with Quote
can i change the collation or will i need to drop and recreate the table??
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 01/07/2007 :  15:00:24  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You can use SSMS to change collation in the table.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

esthera
Flowing Fount of Yak Knowledge

1393 Posts

Posted - 01/08/2007 :  02:02:13  Show Profile  Reply with Quote
how?
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 01/08/2007 :  02:29:12  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
Try ALTER TABLE statement:

ALTER TABLE table-name
ALTER COLUMN col1 varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS


Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

esthera
Flowing Fount of Yak Knowledge

1393 Posts

Posted - 01/08/2007 :  02:39:40  Show Profile  Reply with Quote
can i do that for the whole table or I do column by column?
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 01/08/2007 :  02:51:10  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
I believe you will have to do it for each char/nchar/varchar/nvarchar column.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 01/08/2007 :  02:53:57  Show Profile  Reply with Quote
There is a default collation for the server.

There is a default collation for the database (which defaults to the collation for the server)

The default collation for the database will be applied to any column you add to a table UNLESS you explicitly specify a collation at the column level.

(There is no such thing as a collation for a table)

If the collation for a column is different to the collation of another column you will get the error above. So if your Server and Column have different databases you need to specify the collation in any temporary table you make.

You can only change collation column-by-column, but you can do several columns in one ALTER TABLE statement.

You need to check what changes have occurred to your existing data, if any, after you have changed the collation

Kristen
Go to Top of Page

esthera
Flowing Fount of Yak Knowledge

1393 Posts

Posted - 01/08/2007 :  03:57:02  Show Profile  Reply with Quote
ok that worked - thanks for your help :)
Go to Top of Page

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 01/23/2007 :  22:05:56  Show Profile  Reply with Quote
hi just bringing this forum back from the graves, im having this problem

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

and i dont know where its comming from ive tried the above, changing all temp tables i created in script to have collation SQL_Latin1_General_CP1_CI_AS

but no luck... what could it be?
Go to Top of Page

lfcost
Starting Member

Portugal
1 Posts

Posted - 02/02/2009 :  12:41:51  Show Profile  Reply with Quote
I think you can create a diagram, add all tables you need to change, and then field by field check the properties (columns tab) and change the collation.

It worked fine with me.

quote:
Originally posted by rnbguy

hi just bringing this forum back from the graves, im having this problem

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

and i dont know where its comming from ive tried the above, changing all temp tables i created in script to have collation SQL_Latin1_General_CP1_CI_AS

but no luck... what could it be?



Luís Costa
Go to Top of Page

mishrsud
Starting Member

1 Posts

Posted - 09/08/2009 :  03:33:13  Show Profile  Reply with Quote
Hi,

I faced an issue similar to the one in this thread. I have listed what worked for me. If the table properties state the collation as <database-default> what we can do is:
1. In SQL server management studio, right click on the database in question and choose properties.
2. In the properties dialogue box, choose "Options" in the left pane
3. In the right pane, the current collation will be listed against Collation label.
4. Change to desired and apply.

PS: Ensure all components accessing the database are stopped while doing the above. Failing this will cause an error to pop-up.

--Regards
Sudhanshu
"Impossible is only a state of the mind"
Go to Top of Page

sherrymgm
Starting Member

USA
1 Posts

Posted - 01/05/2011 :  12:05:14  Show Profile  Reply with Quote
To change the collation, Click on the table, go to design at Collation change it to the desired one.
Go to Top of Page

Righteousman
Starting Member

Canada
1 Posts

Posted - 01/21/2013 :  14:19:36  Show Profile  Reply with Quote
ok, let's cut to the chase. No need to read any of the other comments posted in this thread.

Assuming the collation you want to use is Hebrew_CI_AS:

1. ALTER DATABASE <DATABASE> COLLATE Hebrew_CI_AS

2. Run the SQL generated by the following script in order to change all existing text fields to the new collation:

-- Get a list of every column that's char, varchar, nvarchar, nchar
SELECT s.[name], o.[name], c.[name], t.[name],
'ALTER TABLE [' + s.[name] + '].[' + o.[name] + '] ALTER COLUMN [' + c.[name] + '] ' + t.[name] + '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CONVERT(VARCHAR(50), c.max_length) END + ') COLLATE Hebrew_CI_AS' AS SQL
FROM sys.columns c
INNER JOIN sys.objects o ON (o.object_id = c.object_id)
INNER JOIN sys.schemas s ON (s.schema_id = o.schema_id)
INNER JOIN sys.types t ON (t.user_type_id = c.user_type_id)
WHERE s.[name] = 'dbo' AND t.[name] IN ('varchar', 'char', 'nvarchar', 'nchar')
ORDER BY s.[name], o.[name], c.[name]
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000