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 schema. Show all posts
Showing posts with label schema. Show all posts
Tuesday, March 20, 2012
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
Wednesday, March 7, 2012
Code behaviour/performance on 2 machines
Hi,
I have UAT and production servers with same database schema. I am running a
stored procedure on both machines, Its taking much more time on UAT (31 hrs)
where as it is taking less time i.e. 8 hrs (which is expected because of
nature of query) Can someone please explain why is it taking more time on UA
T?
UAT SQL SERVER 2000 has SP4
Production SQL SERVER 2000 has SP3
Please help ASAP.
I can also be reached at sahil.arora@.cit.com
Thanks
SahilJust becuase the servers share the same database model, there is the issue
of differences in hardware configuration and even differences in the
physical implementation of the database such as: volume of data, placement
of files, index fragmentation, statistics, sever/database configurations,
etc. To start with, take a look at the Performance tab of Task Manager on
both servers and compare to what extent they are maxing out on memory and
CPU.
Compare the execution plan between the 2 servers:
http://msdn.microsoft.com/library/d... />
1_1pfd.asp
Also, the following article provides some good performance oriented check
lists and explains how to audit performance.
http://www.sql-server-performance.c...mance_audit.asp
"Sahil Arora" <Sahil Arora@.discussions.microsoft.com> wrote in message
news:40E34A48-D573-46EF-98BE-BA4A84A9A113@.microsoft.com...
> Hi,
> I have UAT and production servers with same database schema. I am running
> a
> stored procedure on both machines, Its taking much more time on UAT (31
> hrs)
> where as it is taking less time i.e. 8 hrs (which is expected because of
> nature of query) Can someone please explain why is it taking more time on
> UAT?
> UAT SQL SERVER 2000 has SP4
> Production SQL SERVER 2000 has SP3
> Please help ASAP.
> I can also be reached at sahil.arora@.cit.com
> Thanks
> Sahil|||I just checked everything on both servers, its same and the server on which
is taking more time is much more powerful than server with less time. any
comments?
"JT" wrote:
> Just becuase the servers share the same database model, there is the issue
> of differences in hardware configuration and even differences in the
> physical implementation of the database such as: volume of data, placement
> of files, index fragmentation, statistics, sever/database configurations,
> etc. To start with, take a look at the Performance tab of Task Manager on
> both servers and compare to what extent they are maxing out on memory and
> CPU.
> Compare the execution plan between the 2 servers:
> http://msdn.microsoft.com/library/d...>
n_1_1pfd.asp
> Also, the following article provides some good performance oriented check
> lists and explains how to audit performance.
> http://www.sql-server-performance.c...mance_audit.asp
>
> "Sahil Arora" <Sahil Arora@.discussions.microsoft.com> wrote in message
> news:40E34A48-D573-46EF-98BE-BA4A84A9A113@.microsoft.com...
>
>|||Do both servers have identical volumes of data, is the execution plan of the
query identical, is the OS level file fragmentation and database level index
fragmentation optimized or similar on both servers, etc.. It could be one or
all of a hundred things. Perhaps running a performance audit log on both
servers and comparing the results will reveal something.
INF: Job to Monitor SQL Server 2000 Performance and Activity
http://support.microsoft.com/defaul...kb;en-us;283696
"Sahil Arora" <SahilArora@.discussions.microsoft.com> wrote in message
news:2FE8B79F-9EC2-445A-9648-65F1C7CAABAD@.microsoft.com...
>I just checked everything on both servers, its same and the server on which
> is taking more time is much more powerful than server with less time. any
> comments?
> "JT" wrote:
>
I have UAT and production servers with same database schema. I am running a
stored procedure on both machines, Its taking much more time on UAT (31 hrs)
where as it is taking less time i.e. 8 hrs (which is expected because of
nature of query) Can someone please explain why is it taking more time on UA
T?
UAT SQL SERVER 2000 has SP4
Production SQL SERVER 2000 has SP3
Please help ASAP.
I can also be reached at sahil.arora@.cit.com
Thanks
SahilJust becuase the servers share the same database model, there is the issue
of differences in hardware configuration and even differences in the
physical implementation of the database such as: volume of data, placement
of files, index fragmentation, statistics, sever/database configurations,
etc. To start with, take a look at the Performance tab of Task Manager on
both servers and compare to what extent they are maxing out on memory and
CPU.
Compare the execution plan between the 2 servers:
http://msdn.microsoft.com/library/d... />
1_1pfd.asp
Also, the following article provides some good performance oriented check
lists and explains how to audit performance.
http://www.sql-server-performance.c...mance_audit.asp
"Sahil Arora" <Sahil Arora@.discussions.microsoft.com> wrote in message
news:40E34A48-D573-46EF-98BE-BA4A84A9A113@.microsoft.com...
> Hi,
> I have UAT and production servers with same database schema. I am running
> a
> stored procedure on both machines, Its taking much more time on UAT (31
> hrs)
> where as it is taking less time i.e. 8 hrs (which is expected because of
> nature of query) Can someone please explain why is it taking more time on
> UAT?
> UAT SQL SERVER 2000 has SP4
> Production SQL SERVER 2000 has SP3
> Please help ASAP.
> I can also be reached at sahil.arora@.cit.com
> Thanks
> Sahil|||I just checked everything on both servers, its same and the server on which
is taking more time is much more powerful than server with less time. any
comments?
"JT" wrote:
> Just becuase the servers share the same database model, there is the issue
> of differences in hardware configuration and even differences in the
> physical implementation of the database such as: volume of data, placement
> of files, index fragmentation, statistics, sever/database configurations,
> etc. To start with, take a look at the Performance tab of Task Manager on
> both servers and compare to what extent they are maxing out on memory and
> CPU.
> Compare the execution plan between the 2 servers:
> http://msdn.microsoft.com/library/d...>
n_1_1pfd.asp
> Also, the following article provides some good performance oriented check
> lists and explains how to audit performance.
> http://www.sql-server-performance.c...mance_audit.asp
>
> "Sahil Arora" <Sahil Arora@.discussions.microsoft.com> wrote in message
> news:40E34A48-D573-46EF-98BE-BA4A84A9A113@.microsoft.com...
>
>|||Do both servers have identical volumes of data, is the execution plan of the
query identical, is the OS level file fragmentation and database level index
fragmentation optimized or similar on both servers, etc.. It could be one or
all of a hundred things. Perhaps running a performance audit log on both
servers and comparing the results will reveal something.
INF: Job to Monitor SQL Server 2000 Performance and Activity
http://support.microsoft.com/defaul...kb;en-us;283696
"Sahil Arora" <SahilArora@.discussions.microsoft.com> wrote in message
news:2FE8B79F-9EC2-445A-9648-65F1C7CAABAD@.microsoft.com...
>I just checked everything on both servers, its same and the server on which
> is taking more time is much more powerful than server with less time. any
> comments?
> "JT" wrote:
>
Subscribe to:
Posts (Atom)