| Author |
Topic |
|
sapator
Constraint Violating Yak Guru
462 Posts |
 Posted - 2010-07-29 : 21:06:14
|
Hi.I tried to join 2 columns on the same table one with not null, the other with null, both nvarchar(50).I got "Cannot resolve collation conflict for column 1 in SELECT statement".I suspect it has something to do with nvarchar and database being in Greek collation.I used collation like this:SELECT (owners.name collate DATABASE_DEFAULT + ' ' + owners.lastname collate DATABASE_DEFAULT) as ownersname Works ok.What i want to know is if it is ok to remove the second collate statements.It works with some tests i did with only one collate statement but i included the second one out of fear mostly.Thanks. |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-07-30 : 03:10:16
|
Please post the create statement of that table.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
sapator
Constraint Violating Yak Guru
462 Posts |
Posted - 2010-07-30 : 19:48:45
|
No problem...CREATE TABLE [dbo].[Owners]( [id] [bigint] IDENTITY(1,1) NOT NULL, [name] [nvarchar](50) NOT NULL, [lastname] [nvarchar](50) NULL, [phone] [varchar](30) NULL, [mail] [nvarchar](50) NULL, [mobilephone] [varchar](30) NULL, [address] [nvarchar](50) NULL, CONSTRAINT [PK_Owners] PRIMARY KEY CLUSTERED ( [id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-07-31 : 07:39:07
|
OK since there are no other (somehow mixed) collations in your table then you can remove the second collate statement.But honestly I wonder why there is a collation conflict...
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-07-31 : 07:40:16
|
Maybe you can also post the name of the collation so I can try to reproduce that problem?
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
sapator
Constraint Violating Yak Guru
462 Posts |
Posted - 2010-08-01 : 00:46:49
|
| Sure.This database collation is Greek_CI_AS, the server collation is SQL_Latin1_General_CP1_CI_AS.Im using SQL Server 2005. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-01 : 01:08:03
|
| owners.name and owners.lastname have different collations. Generate a CREATE TABLE statement that includes collations to check what they are. You probably don't want / intend to have different collations within the application's database columns?(SSMS : Tools : Options : SQL Server object explorer : Scripting : Table and view options : Include collation = True)As the Server Collations is different make sure that you explicitly include COLLATE statements on the definition of all temporary table columns [that are Text/Varchar] and be careful not to use / if you use the SELECT ... INTO #MyTempTable construction. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-08-01 : 07:00:12
|
quote:
Originally posted by Kristen
owners.name and owners.lastname have different collations. Generate a CREATE TABLE statement that includes collations to check what they are. You probably don't want / intend to have different collations within the application's database columns?(SSMS : Tools : Options : SQL Server object explorer : Scripting : Table and view options : Include collation = True)As the Server Collations is different make sure that you explicitly include COLLATE statements on the definition of all temporary table columns [that are Text/Varchar] and be careful not to use / if you use the SELECT ... INTO #MyTempTable construction.
Very good point.That's why I wanted to see the create statement but I missed that (SSMS : Tools : Options : SQL Server object explorer : Scripting : Table and view options : Include collation = True)
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
sapator
Constraint Violating Yak Guru
462 Posts |
Posted - 2010-08-02 : 17:38:51
|
| Hey.Where do you see that they have different collations?And how on earth did i manage to do that, if they indeed that have different collations?I just created the columns, nothing more. |
|
|
|
sapator
Constraint Violating Yak Guru
462 Posts |
Posted - 2010-08-02 : 18:54:56
|
| So you are 100% correct.The collation is different!!How can this have happened?As i've said i just added a new column.How on earth did this happened? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-03 : 04:58:46
|
| Collation has changed at some point in the life of the table? Columns added later (after collation change).Or you accidentally twiddled the wheel on your mouse when you were on the Collation selection field.Probably not help, but we script all DDL changes to the database (we use the GUI tools in SSMS etc. but then use Generate Script rather than "Save") and review the script before running - which tends to bring those sorts of slip-ups to light (or, when not, we do at least have an audit of the scripts that we ran to go back and see what happened, and then tighten up our procedures to prevent it happening again) |
|
|
|
sapator
Constraint Violating Yak Guru
462 Posts |
Posted - 2010-08-03 : 18:07:23
|
| Well i'll be more careful on simple things from now own.Rest assure.Thanks. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-04 : 02:11:16
|
| Its annoying when things like this happen. Perhaps check collation of all other varchar/text columns to make sure no other columns have incorrect collation? |
|
|
|
sapator
Constraint Violating Yak Guru
462 Posts |
Posted - 2010-08-04 : 21:07:48
|
| Yep.thanks again. :) |
|
|
|
|
Collation error when joining 2 columns