| Author |
 Topic  |
|
|
esthera
Flowing Fount of Yak Knowledge
1340 Posts |
 Posted - 01/07/2007 : 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
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 01/07/2007 : 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
Flowing Fount of Yak Knowledge
1340 Posts |
Posted - 01/07/2007 : 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
United Kingdom
22191 Posts |
Posted - 01/07/2007 : 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
Flowing Fount of Yak Knowledge
1340 Posts |
Posted - 01/07/2007 : 11:19:52
|
| can i change the collation or will i need to drop and recreate the table?? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 01/07/2007 : 15:00:24
|
You can use SSMS to change collation in the table.
Peter Larsson
Helsingborg, Sweden |
|
|
|
esthera
Flowing Fount of Yak Knowledge
1340 Posts |
Posted - 01/08/2007 : 02:02:13
|
how?
|
|
|
|
harsh_athalye
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 01/08/2007 : 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
Flowing Fount of Yak Knowledge
1340 Posts |
Posted - 01/08/2007 : 02:39:40
|
| can i do that for the whole table or I do column by column? |
|
|
|
harsh_athalye
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 01/08/2007 : 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
United Kingdom
22191 Posts |
Posted - 01/08/2007 : 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
Flowing Fount of Yak Knowledge
1340 Posts |
Posted - 01/08/2007 : 03:57:02
|
| ok that worked - thanks for your help :) |
|
|
|
rnbguy
Constraint Violating Yak Guru
293 Posts |
Posted - 01/23/2007 : 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
Portugal
1 Posts |
Posted - 02/02/2009 : 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 Posts |
Posted - 09/08/2009 : 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
USA
1 Posts |
Posted - 01/05/2011 : 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
Canada
1 Posts |
Posted - 01/21/2013 : 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] |
|
|
| |
Topic |
|
change collation of table
