hello,
i have to migrate and old schema into a new one.
i have the old table column with collation
Latin1_General_CI_AS
and the new table column with collation
Latin1_General_CP1_CI_AS
both columns are nvarchar
when i transfer the data i get the error
cannot resolve collation conflict for equal to operation
I do not understand this I read tens or hundreds of documentation
documentation says there is no conflict when unsing unicode types like
nvarchar.
but there is a conflict?
why and how can I solve it?
thank you.
michael
Michael Zdarsky
They aren't the same collation hence the message.
You can use the COLLATE option to cast the collation from one to another.
The 'n' in nvarchar just allows you to store double byte characters for
chinese etc... nothing to do with collation as such, its the fact its a
string data type that has a baring on the collation.
Tony.
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)
|||hello tony
thank you for your answer,
yes they are different, but I compared the collation properties
codepage, lcid and comparison style and they are all identical.
so only the name is different, but there is still the problem.
how does the server compare this collations?
thank you
michael
"Tony Rogerson" wrote:
> They aren't the same collation hence the message.
> You can use the COLLATE option to cast the collation from one to another.
> The 'n' in nvarchar just allows you to store double byte characters for
> chinese etc... nothing to do with collation as such, its the fact its a
> string data type that has a baring on the collation.
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://www.sqlserverfaq.com?mbr=21
> (Create your own groups, Forum, FAQ's and a ton more)
>
>
|||Hi Michael,
Seriously, they are different - one is a Windows collation and one SQL.
On the SQL Server set up you get the option of using a Windows collation or
SQL, one for backwards compatibility - can't remember which ones which now,
but this is where collation problems usually start.
Collation is horrible, the set-up doesn't really help you much either.
sp_helpsort can be used to get more information on the collation you are
using.
print cast( databasepropertyex( 'master', 'collation' ) as varchar(128) )
The above statement can be used to determine the database collation.
print cast( databasepropertyex( 'master', 'SQLSortOrder' ) as varchar(128) )
The above can be used to get the server sort id, which is what will differ.
Hope that helps.
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)
Showing posts with label old. Show all posts
Showing posts with label old. Show all posts
Tuesday, March 20, 2012
Collation
I'm rebuilding an application server and am trying to figure out how I can
get the right collation as what my old server has. I haven't installed SQL
Server in quite a while, and this is my first time installing SQL Server 2005
and it's a lot different.
The collation on my old server is SQL_Latin1_General_CP1_Cl_AS how does that
equate to all the SQL Collations I have to choose from? The default is
dictionary order, case-insensitive, for use with 1252 Character Set.
I'm not sure what the CP1_Cl_AS means. Does anyone know what I need to
choose in order to get the right collation? If memory serves me correctly
changing this after the fact isn't recommended (this was SQL 2000).Hi Penny,
This is from BOL:
Codepage
Specifies a one- to four-digit number that identifies the code page used by
the collation. CP1 specifies code page 1252, for all other code pages the
complete code page number is specified. For example, CP1251 specifies code
page 1251 and CP850 specifies code page 850.
CaseSensitivity
CI specifies case-insensitive, CS specifies case-sensitive.
AccentSensitivity
AI specifies accent-insensitive, AS specifies accent-sensitive.
Source:
http://msdn2.microsoft.com/en-us/library/ms180175.aspx
In your case, If you want collation compatibility then you must go with CP
1252 (Because CP1 = Code Page 1252), Case Intensive (CI), Accent Sensetive
(AS).
Note:
Latin1_general use CP 1252
Ekrem Ã?nsoy
"Penny" <Penny@.discussions.microsoft.com> wrote in message
news:DD3B734B-8436-4EED-89E5-CD3E31DC960A@.microsoft.com...
> I'm rebuilding an application server and am trying to figure out how I can
> get the right collation as what my old server has. I haven't installed
> SQL
> Server in quite a while, and this is my first time installing SQL Server
> 2005
> and it's a lot different.
> The collation on my old server is SQL_Latin1_General_CP1_Cl_AS how does
> that
> equate to all the SQL Collations I have to choose from? The default is
> dictionary order, case-insensitive, for use with 1252 Character Set.
> I'm not sure what the CP1_Cl_AS means. Does anyone know what I need to
> choose in order to get the right collation? If memory serves me correctly
> changing this after the fact isn't recommended (this was SQL 2000).
>
get the right collation as what my old server has. I haven't installed SQL
Server in quite a while, and this is my first time installing SQL Server 2005
and it's a lot different.
The collation on my old server is SQL_Latin1_General_CP1_Cl_AS how does that
equate to all the SQL Collations I have to choose from? The default is
dictionary order, case-insensitive, for use with 1252 Character Set.
I'm not sure what the CP1_Cl_AS means. Does anyone know what I need to
choose in order to get the right collation? If memory serves me correctly
changing this after the fact isn't recommended (this was SQL 2000).Hi Penny,
This is from BOL:
Codepage
Specifies a one- to four-digit number that identifies the code page used by
the collation. CP1 specifies code page 1252, for all other code pages the
complete code page number is specified. For example, CP1251 specifies code
page 1251 and CP850 specifies code page 850.
CaseSensitivity
CI specifies case-insensitive, CS specifies case-sensitive.
AccentSensitivity
AI specifies accent-insensitive, AS specifies accent-sensitive.
Source:
http://msdn2.microsoft.com/en-us/library/ms180175.aspx
In your case, If you want collation compatibility then you must go with CP
1252 (Because CP1 = Code Page 1252), Case Intensive (CI), Accent Sensetive
(AS).
Note:
Latin1_general use CP 1252
Ekrem Ã?nsoy
"Penny" <Penny@.discussions.microsoft.com> wrote in message
news:DD3B734B-8436-4EED-89E5-CD3E31DC960A@.microsoft.com...
> I'm rebuilding an application server and am trying to figure out how I can
> get the right collation as what my old server has. I haven't installed
> SQL
> Server in quite a while, and this is my first time installing SQL Server
> 2005
> and it's a lot different.
> The collation on my old server is SQL_Latin1_General_CP1_Cl_AS how does
> that
> equate to all the SQL Collations I have to choose from? The default is
> dictionary order, case-insensitive, for use with 1252 Character Set.
> I'm not sure what the CP1_Cl_AS means. Does anyone know what I need to
> choose in order to get the right collation? If memory serves me correctly
> changing this after the fact isn't recommended (this was SQL 2000).
>
collation
hello,
i have to migrate and old schema into a new one.
i have the old table column with collation
Latin1_General_CI_AS
and the new table column with collation
Latin1_General_CP1_CI_AS
both columns are nvarchar
when i transfer the data i get the error
cannot resolve collation conflict for equal to operation
I do not understand this I read tens or hundreds of documentation
documentation says there is no conflict when unsing unicode types like
nvarchar.
but there is a conflict?
why and how can I solve it?
thank you.
michael
--
Michael ZdarskyThey aren't the same collation hence the message.
You can use the COLLATE option to cast the collation from one to another.
The 'n' in nvarchar just allows you to store double byte characters for
chinese etc... nothing to do with collation as such, its the fact its a
string data type that has a baring on the collation.
Tony.
--
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)|||hello tony
thank you for your answer,
yes they are different, but I compared the collation properties
codepage, lcid and comparison style and they are all identical.
so only the name is different, but there is still the problem.
how does the server compare this collations?
thank you
michael
"Tony Rogerson" wrote:
> They aren't the same collation hence the message.
> You can use the COLLATE option to cast the collation from one to another.
> The 'n' in nvarchar just allows you to store double byte characters for
> chinese etc... nothing to do with collation as such, its the fact its a
> string data type that has a baring on the collation.
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://www.sqlserverfaq.com?mbr=21
> (Create your own groups, Forum, FAQ's and a ton more)
>
>|||Hi Michael,
Seriously, they are different - one is a Windows collation and one SQL.
On the SQL Server set up you get the option of using a Windows collation or
SQL, one for backwards compatibility - can't remember which ones which now,
but this is where collation problems usually start.
Collation is horrible, the set-up doesn't really help you much either.
sp_helpsort can be used to get more information on the collation you are
using.
print cast( databasepropertyex( 'master', 'collation' ) as varchar(128) )
The above statement can be used to determine the database collation.
print cast( databasepropertyex( 'master', 'SQLSortOrder' ) as varchar(128) )
The above can be used to get the server sort id, which is what will differ.
Hope that helps.
--
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)
i have to migrate and old schema into a new one.
i have the old table column with collation
Latin1_General_CI_AS
and the new table column with collation
Latin1_General_CP1_CI_AS
both columns are nvarchar
when i transfer the data i get the error
cannot resolve collation conflict for equal to operation
I do not understand this I read tens or hundreds of documentation
documentation says there is no conflict when unsing unicode types like
nvarchar.
but there is a conflict?
why and how can I solve it?
thank you.
michael
--
Michael ZdarskyThey aren't the same collation hence the message.
You can use the COLLATE option to cast the collation from one to another.
The 'n' in nvarchar just allows you to store double byte characters for
chinese etc... nothing to do with collation as such, its the fact its a
string data type that has a baring on the collation.
Tony.
--
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)|||hello tony
thank you for your answer,
yes they are different, but I compared the collation properties
codepage, lcid and comparison style and they are all identical.
so only the name is different, but there is still the problem.
how does the server compare this collations?
thank you
michael
"Tony Rogerson" wrote:
> They aren't the same collation hence the message.
> You can use the COLLATE option to cast the collation from one to another.
> The 'n' in nvarchar just allows you to store double byte characters for
> chinese etc... nothing to do with collation as such, its the fact its a
> string data type that has a baring on the collation.
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://www.sqlserverfaq.com?mbr=21
> (Create your own groups, Forum, FAQ's and a ton more)
>
>|||Hi Michael,
Seriously, they are different - one is a Windows collation and one SQL.
On the SQL Server set up you get the option of using a Windows collation or
SQL, one for backwards compatibility - can't remember which ones which now,
but this is where collation problems usually start.
Collation is horrible, the set-up doesn't really help you much either.
sp_helpsort can be used to get more information on the collation you are
using.
print cast( databasepropertyex( 'master', 'collation' ) as varchar(128) )
The above statement can be used to determine the database collation.
print cast( databasepropertyex( 'master', 'SQLSortOrder' ) as varchar(128) )
The above can be used to get the server sort id, which is what will differ.
Hope that helps.
--
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)
collation
hello,
i have to migrate and old schema into a new one.
i have the old table column with collation
Latin1_General_CI_AS
and the new table column with collation
Latin1_General_CP1_CI_AS
both columns are nvarchar
when i transfer the data i get the error
cannot resolve collation conflict for equal to operation
I do not understand this I read tens or hundreds of documentation
documentation says there is no conflict when unsing unicode types like
nvarchar.
but there is a conflict?
why and how can I solve it?
thank you.
michael
--
Michael ZdarskyThey aren't the same collation hence the message.
You can use the COLLATE option to cast the collation from one to another.
The 'n' in nvarchar just allows you to store double byte characters for
chinese etc... nothing to do with collation as such, its the fact its a
string data type that has a baring on the collation.
Tony.
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)|||hello tony
thank you for your answer,
yes they are different, but I compared the collation properties
codepage, lcid and comparison style and they are all identical.
so only the name is different, but there is still the problem.
how does the server compare this collations?
thank you
michael
"Tony Rogerson" wrote:
> They aren't the same collation hence the message.
> You can use the COLLATE option to cast the collation from one to another.
> The 'n' in nvarchar just allows you to store double byte characters for
> chinese etc... nothing to do with collation as such, its the fact its a
> string data type that has a baring on the collation.
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://www.sqlserverfaq.com?mbr=21
> (Create your own groups, Forum, FAQ's and a ton more)
>
>|||Hi Michael,
Seriously, they are different - one is a Windows collation and one SQL.
On the SQL Server set up you get the option of using a Windows collation or
SQL, one for backwards compatibility - can't remember which ones which now,
but this is where collation problems usually start.
Collation is horrible, the set-up doesn't really help you much either.
sp_helpsort can be used to get more information on the collation you are
using.
print cast( databasepropertyex( 'master', 'collation' ) as varchar(128) )
The above statement can be used to determine the database collation.
print cast( databasepropertyex( 'master', 'SQLSortOrder' ) as varchar(128) )
The above can be used to get the server sort id, which is what will differ.
Hope that helps.
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)sqlsql
i have to migrate and old schema into a new one.
i have the old table column with collation
Latin1_General_CI_AS
and the new table column with collation
Latin1_General_CP1_CI_AS
both columns are nvarchar
when i transfer the data i get the error
cannot resolve collation conflict for equal to operation
I do not understand this I read tens or hundreds of documentation
documentation says there is no conflict when unsing unicode types like
nvarchar.
but there is a conflict?
why and how can I solve it?
thank you.
michael
--
Michael ZdarskyThey aren't the same collation hence the message.
You can use the COLLATE option to cast the collation from one to another.
The 'n' in nvarchar just allows you to store double byte characters for
chinese etc... nothing to do with collation as such, its the fact its a
string data type that has a baring on the collation.
Tony.
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)|||hello tony
thank you for your answer,
yes they are different, but I compared the collation properties
codepage, lcid and comparison style and they are all identical.
so only the name is different, but there is still the problem.
how does the server compare this collations?
thank you
michael
"Tony Rogerson" wrote:
> They aren't the same collation hence the message.
> You can use the COLLATE option to cast the collation from one to another.
> The 'n' in nvarchar just allows you to store double byte characters for
> chinese etc... nothing to do with collation as such, its the fact its a
> string data type that has a baring on the collation.
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://www.sqlserverfaq.com?mbr=21
> (Create your own groups, Forum, FAQ's and a ton more)
>
>|||Hi Michael,
Seriously, they are different - one is a Windows collation and one SQL.
On the SQL Server set up you get the option of using a Windows collation or
SQL, one for backwards compatibility - can't remember which ones which now,
but this is where collation problems usually start.
Collation is horrible, the set-up doesn't really help you much either.
sp_helpsort can be used to get more information on the collation you are
using.
print cast( databasepropertyex( 'master', 'collation' ) as varchar(128) )
The above statement can be used to determine the database collation.
print cast( databasepropertyex( 'master', 'SQLSortOrder' ) as varchar(128) )
The above can be used to get the server sort id, which is what will differ.
Hope that helps.
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)sqlsql
Subscribe to:
Posts (Atom)