I have a server with de windows latin_1... collation. But we restored some
older databases with SQL_Latin_1... collation.
Is this a problem or are these collation compatible?
RegardsHi
You have not specified the full collation names, but you can easily test the
issue such as
USE TEMPDB
GO
CREATE TABLE MixedCollations ( id int not null identity CONSTRAINT PK_MiX
PRIMARY KEY,
col_SQL char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
col_WIN char(1) COLLATE Latin1_General_CI_AS NOT NULL )
GO
INSERT INTO MixedCollations ( col_SQL, col_WIN )
SELECT 'A', 'A'
UNION ALL SELECT 'B', 'B'
GO
SELECT A.*, B.*
FROM MixedCollations A
JOIN MixedCollations B ON A.col_WIN = B.col_SQL
GO
/*
Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.
*/
SELECT A.*, B.*
FROM MixedCollations A
JOIN MixedCollations B ON A.col_WIN = B.col_SQL COLLATE Latin1_General_CI_AS
GO
/*
id col_SQL col_WIN id col_SQL col_WIN
-- -- -- -- -- --
1 A A 1 A A
2 B B 2 B B
(2 row(s) affected)
*/
DROP TABLE MixedCollations
GO
Therefore you may have issue when creating temporary tables where you don't
specify a collation and then join them to tables in the original database.
John
"Zekske" wrote:
> I have a server with de windows latin_1... collation. But we restored some
> older databases with SQL_Latin_1... collation.
> Is this a problem or are these collation compatible?
> Regards|||OK, but normaly those databases on the server are databases for dedicated
applications. Normally there won't be a compare or sort with data coming from
two different databases.
Maybe I have to rephrase my question to:
What are the consequences of putting SQL_LATIN1_CI_AS databases on a
LATIN1_CI_AS SQL server?
"John Bell" wrote:
> Hi
> You have not specified the full collation names, but you can easily test the
> issue such as
> USE TEMPDB
> GO
> CREATE TABLE MixedCollations ( id int not null identity CONSTRAINT PK_MiX
> PRIMARY KEY,
> col_SQL char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> col_WIN char(1) COLLATE Latin1_General_CI_AS NOT NULL )
> GO
> INSERT INTO MixedCollations ( col_SQL, col_WIN )
> SELECT 'A', 'A'
> UNION ALL SELECT 'B', 'B'
> GO
> SELECT A.*, B.*
> FROM MixedCollations A
> JOIN MixedCollations B ON A.col_WIN = B.col_SQL
> GO
> /*
> Server: Msg 446, Level 16, State 9, Line 1
> Cannot resolve collation conflict for equal to operation.
> */
> SELECT A.*, B.*
> FROM MixedCollations A
> JOIN MixedCollations B ON A.col_WIN = B.col_SQL COLLATE Latin1_General_CI_AS
> GO
> /*
> id col_SQL col_WIN id col_SQL col_WIN
> -- -- -- -- -- --
> 1 A A 1 A A
> 2 B B 2 B B
> (2 row(s) affected)
> */
> DROP TABLE MixedCollations
> GO
> Therefore you may have issue when creating temporary tables where you don't
> specify a collation and then join them to tables in the original database.
> John
> "Zekske" wrote:
> > I have a server with de windows latin_1... collation. But we restored some
> > older databases with SQL_Latin_1... collation.
> > Is this a problem or are these collation compatible?
> >
> > Regards|||Hi
I did answer this, if you use temporary tables and don't create them with
the specified collation it WILL cause you problems. If you never do this and
only access tables within the database then you may get away with it.
John
"Zekske" wrote:
> OK, but normaly those databases on the server are databases for dedicated
> applications. Normally there won't be a compare or sort with data coming from
> two different databases.
> Maybe I have to rephrase my question to:
> What are the consequences of putting SQL_LATIN1_CI_AS databases on a
> LATIN1_CI_AS SQL server?
> "John Bell" wrote:
> > Hi
> >
> > You have not specified the full collation names, but you can easily test the
> > issue such as
> >
> > USE TEMPDB
> > GO
> >
> > CREATE TABLE MixedCollations ( id int not null identity CONSTRAINT PK_MiX
> > PRIMARY KEY,
> > col_SQL char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > col_WIN char(1) COLLATE Latin1_General_CI_AS NOT NULL )
> > GO
> >
> > INSERT INTO MixedCollations ( col_SQL, col_WIN )
> > SELECT 'A', 'A'
> > UNION ALL SELECT 'B', 'B'
> > GO
> >
> > SELECT A.*, B.*
> > FROM MixedCollations A
> > JOIN MixedCollations B ON A.col_WIN = B.col_SQL
> > GO
> >
> > /*
> > Server: Msg 446, Level 16, State 9, Line 1
> > Cannot resolve collation conflict for equal to operation.
> > */
> >
> > SELECT A.*, B.*
> > FROM MixedCollations A
> > JOIN MixedCollations B ON A.col_WIN = B.col_SQL COLLATE Latin1_General_CI_AS
> > GO
> > /*
> > id col_SQL col_WIN id col_SQL col_WIN
> > -- -- -- -- -- --
> > 1 A A 1 A A
> > 2 B B 2 B B
> >
> > (2 row(s) affected)
> > */
> >
> > DROP TABLE MixedCollations
> > GO
> >
> > Therefore you may have issue when creating temporary tables where you don't
> > specify a collation and then join them to tables in the original database.
> >
> > John
> >
> > "Zekske" wrote:
> >
> > > I have a server with de windows latin_1... collation. But we restored some
> > > older databases with SQL_Latin_1... collation.
> > > Is this a problem or are these collation compatible?
> > >
> > > Regards|||None unless there are cross database queries (including joins between temp
tables and physical ones).
However sql_latin_1 is effectively the sql based equivalant of the windows
based collation latin1. Windows based collations should be preferred over
the old style sql ones.
"Zekske" <Zekske@.discussions.microsoft.com> wrote in message
news:7693A675-BD40-4D0C-8DE0-35D11FF29622@.microsoft.com...
> OK, but normaly those databases on the server are databases for dedicated
> applications. Normally there won't be a compare or sort with data coming
> from
> two different databases.
> Maybe I have to rephrase my question to:
> What are the consequences of putting SQL_LATIN1_CI_AS databases on a
> LATIN1_CI_AS SQL server?
> "John Bell" wrote:
>> Hi
>> You have not specified the full collation names, but you can easily test
>> the
>> issue such as
>> USE TEMPDB
>> GO
>> CREATE TABLE MixedCollations ( id int not null identity CONSTRAINT PK_MiX
>> PRIMARY KEY,
>> col_SQL char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> col_WIN char(1) COLLATE Latin1_General_CI_AS NOT NULL )
>> GO
>> INSERT INTO MixedCollations ( col_SQL, col_WIN )
>> SELECT 'A', 'A'
>> UNION ALL SELECT 'B', 'B'
>> GO
>> SELECT A.*, B.*
>> FROM MixedCollations A
>> JOIN MixedCollations B ON A.col_WIN = B.col_SQL
>> GO
>> /*
>> Server: Msg 446, Level 16, State 9, Line 1
>> Cannot resolve collation conflict for equal to operation.
>> */
>> SELECT A.*, B.*
>> FROM MixedCollations A
>> JOIN MixedCollations B ON A.col_WIN = B.col_SQL COLLATE
>> Latin1_General_CI_AS
>> GO
>> /*
>> id col_SQL col_WIN id col_SQL col_WIN
>> -- -- -- -- -- --
>> 1 A A 1 A A
>> 2 B B 2 B B
>> (2 row(s) affected)
>> */
>> DROP TABLE MixedCollations
>> GO
>> Therefore you may have issue when creating temporary tables where you
>> don't
>> specify a collation and then join them to tables in the original
>> database.
>> John
>> "Zekske" wrote:
>> > I have a server with de windows latin_1... collation. But we restored
>> > some
>> > older databases with SQL_Latin_1... collation.
>> > Is this a problem or are these collation compatible?
>> >
>> > Regards|||... and SQL Collations are for backward support.
--
Ekrem Önsoy
"Mark B" <nospam@.nospam.com> wrote in message
news:Od8xWi0NIHA.4740@.TK2MSFTNGP02.phx.gbl...
> None unless there are cross database queries (including joins between temp
> tables and physical ones).
> However sql_latin_1 is effectively the sql based equivalant of the windows
> based collation latin1. Windows based collations should be preferred over
> the old style sql ones.
> "Zekske" <Zekske@.discussions.microsoft.com> wrote in message
> news:7693A675-BD40-4D0C-8DE0-35D11FF29622@.microsoft.com...
>> OK, but normaly those databases on the server are databases for dedicated
>> applications. Normally there won't be a compare or sort with data coming
>> from
>> two different databases.
>> Maybe I have to rephrase my question to:
>> What are the consequences of putting SQL_LATIN1_CI_AS databases on a
>> LATIN1_CI_AS SQL server?
>> "John Bell" wrote:
>> Hi
>> You have not specified the full collation names, but you can easily test
>> the
>> issue such as
>> USE TEMPDB
>> GO
>> CREATE TABLE MixedCollations ( id int not null identity CONSTRAINT
>> PK_MiX
>> PRIMARY KEY,
>> col_SQL char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> col_WIN char(1) COLLATE Latin1_General_CI_AS NOT NULL )
>> GO
>> INSERT INTO MixedCollations ( col_SQL, col_WIN )
>> SELECT 'A', 'A'
>> UNION ALL SELECT 'B', 'B'
>> GO
>> SELECT A.*, B.*
>> FROM MixedCollations A
>> JOIN MixedCollations B ON A.col_WIN = B.col_SQL
>> GO
>> /*
>> Server: Msg 446, Level 16, State 9, Line 1
>> Cannot resolve collation conflict for equal to operation.
>> */
>> SELECT A.*, B.*
>> FROM MixedCollations A
>> JOIN MixedCollations B ON A.col_WIN = B.col_SQL COLLATE
>> Latin1_General_CI_AS
>> GO
>> /*
>> id col_SQL col_WIN id col_SQL col_WIN
>> -- -- -- -- -- --
>> 1 A A 1 A A
>> 2 B B 2 B B
>> (2 row(s) affected)
>> */
>> DROP TABLE MixedCollations
>> GO
>> Therefore you may have issue when creating temporary tables where you
>> don't
>> specify a collation and then join them to tables in the original
>> database.
>> John
>> "Zekske" wrote:
>> > I have a server with de windows latin_1... collation. But we restored
>> > some
>> > older databases with SQL_Latin_1... collation.
>> > Is this a problem or are these collation compatible?
>> >
>> > Regards
>
