change collation of table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Express Edition and Compact Edition (2005)

change collation of table

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-01-07 : 08:59:08

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
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-07 : 10:44:40

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"

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-01-07 : 10:49:16

i did design the tables but did not purposely use that collation
is there a way to change the collation with sql?

Kristen
Test

22859 Posts

Posted - 2007-01-07 : 11:07:09

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

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-01-07 : 11:19:52

can i change the collation or will i need to drop and recreate the table??

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-07 : 15:00:24

You can use SSMS to change collation in the table.

Peter Larsson
Helsingborg, Sweden

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-01-08 : 02:02:13

how?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-08 : 02:29:12

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"

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-01-08 : 02:39:40

can i do that for the whole table or I do column by column?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-08 : 02:51:10

I believe you will have to do it for each char/nchar/varchar/nvarchar column.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Kristen
Test

22859 Posts

Posted - 2007-01-08 : 02:53:57

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

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-01-08 : 03:57:02

ok that worked - thanks for your help :)

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-01-23 : 22:05:56

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?

lfcost
Starting Member

1 Post

Posted - 2009-02-02 : 12:41:51

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

mishrsud
Starting Member

1 Post

Posted - 2009-09-08 : 03:33:13

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"

sherrymgm
Starting Member

1 Post

Posted - 2011-01-05 : 12:05:14

To change the collation, Click on the table, go to design at Collation change it to the desired one.

Righteousman
Starting Member

1 Post

Posted - 2013-01-21 : 14:19:36

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]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources