Collation problem

Hi all.
I have a problem with collation in SQLServer database. My table contains few
rows in field of type char(20),
field and server Collation is set to Croatian_CI_AS (Windows collation).
When I execute query SELECT field FROM table ORDER BY field, return set is
ordered as follows
---
field
---
+
-
7
7-
7+
7+1
71
7-1
72
Why does value 7-1 is placed between values 71 and 72 and not after 7+1.
Is there any way to tell SQLServer to set order of returned resultset in the
same way that windows
would set order of files in explorer. Collation Croatian_CI_AS is important
for me because of
correct order of special Croatian characters.

TomislavTomislav Stilinovi (Tomislav.Stilinovic@.zg.htnet.hr) writes:
> I have a problem with collation in SQLServer database. My table contains
> few rows in field of type char(20), field and server Collation is set to
> Croatian_CI_AS (Windows collation). When I execute query SELECT field
> FROM table ORDER BY field, return set is ordered as follows
> ---
> field
> ---
> +
> -
> 7
> 7-
> 7+
> 7+1
> 71
> 7-1
> 72
> Why does value 7-1 is placed between values 71 and 72 and not after 7+1.

I think most Unicode sorting algorithms with some level of sophistication
considers hyphen to be an ignorable character, at least on primary level.
This is also how you sort in a dictionary.

> Is there any way to tell SQLServer to set order of returned resultset in
> the same way that windows would set order of files in explorer.

No. And it appears that the one that is really the odd one out is
Explorer. I entered your data in Word, and asked it to sort, and I
got the same result as in SQL Server.

I ran my tests with Finnish_Swedish collations, and regional settings
set to Swedish, but I don't think this makes a difference in this case.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

collation problem

hi

i have a db with hebrew collation.
i now want it to be cyrillic collation.
converting the db collation - wont help.
i tried exporting data to a blank DB created with cyrillic collation -
still, no good.

what is the best way of having a DB with a certain collation so no
matter the destination DB is, the collation of all columns will be
"database default"

thanks
amosCollations are defined a the column level. Create the tables with the
correct collation and then populate them. You can alter the existing
collation with an ALTER TABLE ... ALTER COLUMN statement but you first have
to drop any indexes on the columns and then re-create them afterwards.

--
David Portas
SQL Server MVP
--|||amos (amos@.cvidya.com) writes:
> i have a db with hebrew collation.
> i now want it to be cyrillic collation.
> converting the db collation - wont help.
> i tried exporting data to a blank DB created with cyrillic collation -
> still, no good.

I don't know exactly what you did, but presumably you used some export
wizard which performs too much things behind your back. Since I always
build my databases from scripts under version control, I have no idea
how these export wizard looks like.

If you don't have scripts under version control to build from your best
bet is probably to use the scripting facility in Enterprise Manager. I
don't think you can suppress generation of collation information there.
(You can in QA, but in QA you can only script one table at a time.)
However, once you have the script, you can open it an editor and to
a Replace All on "COLLATE Hebrew_CI_AI" to remove the collation information.

Then you can create the new database. If you need to copy data, you can
use INSERT/SELECT or bulk copy. In the latter case, be sure to export to
Unicode files.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Collation problem

Hello,

I think I'd might have a small collation problem.

Configuration:
Two SQL Srv 2000 SP3 (running on clusters).
Booth servers configured with SQL_Latin1_General_CP1_CI_AS
collation.

On each server, I have one database, which collation is
Latin1_General_CI_AS.

I've created a view on Server1.Database1, which is reading complete
table from Server2.Database2.

Checking the collation, the view has, I was surpriced, the collation
was the same as server collation.
Is it always, that building views between two different servers, the
object created will use default collation of server?

The problem is, this view is intergrated in other join-where query on
server1, where other objects used are from server1 and I get error
message:

select TABLE_FROM_SERVER1.Col1 from
TABLE_FROM_SERVER1,
VIEW_ON_SERVER1_BUT_ACCESSING_COMPLETE_SERVER2
where TABLE_FROM_SERVER1.Col1 = 'bubu_si_lala'

Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.

The sulution, of joining this view will be changed anyway (is not
enought fast) but I would like to know, how is it possible, to solve so
kind of problem.

Is it possible to set the collation for created view, and determine
collation the same the database have?

Greatings

Mateusz[posted and mailed, please reply in news]

Matik (marzec@.sauron.xo.pl) writes:
> Configuration:
> Two SQL Srv 2000 SP3 (running on clusters).
> Booth servers configured with SQL_Latin1_General_CP1_CI_AS
> collation.
> On each server, I have one database, which collation is
> Latin1_General_CI_AS.
> I've created a view on Server1.Database1, which is reading complete
> table from Server2.Database2.
> Checking the collation, the view has, I was surpriced, the collation
> was the same as server collation.
> Is it always, that building views between two different servers, the
> object created will use default collation of server?

Since I don't really know which database that have which collation,
I don't really want to go into speculation. But without looking in
Books Online, my guess is that each column in the view retains the
collation the column has in its source table. And the repro below
appears to confirm this. It also demonstrates how you can modify your
view by using the COLLATE clause to resolve the problem.

create database collate_test collate Polish_CS_AS
go
use collate_test
go
create view nisse_view (PolishCustomerID, CustomerID) as
select CustomerID COLLATE database_default,
CustomerID
from Northwind..Customers
go
-- Succeeds, since CustomerID retains the collation from the
-- Northwind database.
select * from nisse_view n
where not exists (select * from
Northwind..Orders O
where O.CustomerID = n.CustomerID)
go
-- Fails, as we here use the column with a collation
-- of the database.
select * from nisse_view n
where not exists (select * from
Northwind..Orders O
where O.CustomerID = n.PolishCustomerID)

go
use master
go
drop database collate_test

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank You Erland,

As always helpfull answer!

Greatings

Mateusz|||Thanks for your answer!!

Collation Order

How can I change the collation order for a msde db?
With SQL Server I use Rebuildm.exe utility, but with msde
that file is not present.
thanks,
AF
If your release doesnt contain a rebuildm.exe i am afraid that you will
have to setup Sql Server once again, or perhaps get a copy from one of your
non-MSDE SQL Servers (
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
<anonymous@.discussions.microsoft.com> schrieb im Newsbeitrag
news:0bcd01c54689$47bdc490$a401280a@.phx.gbl...
> How can I change the collation order for a msde db?
> With SQL Server I use Rebuildm.exe utility, but with msde
> that file is not present.
> thanks,
> AF
|||hi,
anonymous@.discussions.microsoft.com wrote:
> How can I change the collation order for a msde db?
> With SQL Server I use Rebuildm.exe utility, but with msde
> that file is not present.
> thanks,
> AF
yes, MSDE distributions do not include rebuildm.exe, but you can specify
alternate collation(s) for users' databases.. .. you can incur in some
overhead where different collations are involved in tempdb (as tempdb
inherits system collation), but this scenario is supported...
else you have to uninstall and reinstall MSDE with the desired collation
specifying the
COLLATION="selected_collation"
parameter at install time to the setup.exe boostrap installer (
http://msdn.microsoft.com/library/en...stsql_84xl.asp )
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
sqlsql

collation or local sensitive settings or other configuration properties

I installed the samples and tutoriales on the same server as SQL Server 2005 and VS 2005 Team Suite.

Living in Belgium, the local settings are:

language = Duch (Belgium)

code page = 850

e.g. in the database samples (AdventureWorks database – AdventureWorks Warehouse database)

my date notation is: 26-12-2005

my number notation is: 1,0013016921998599

all demo samples and tutorials are developed using

language = English (United States)

code page 1252

e.g. in SampleCurrencyData.txt

the date notation is: 12/26/2005 00:00:00

the number notation is: 1.0013016921998599

I do not have any problems to test the diferent services (Data Base Engine Services, Analysis Services, reporting & notifications services ..) but I do not succeed to deploy any samples of integration services !

e.g. for Integration Services Tutorial - lesson 1: “Creating the Project and Basic Package”

there are no error messages, 1097 rows are processed

but, checking the result in the database, no data is updated in the FactCurrencyRate table of the AdventureWorksDW database !

Why?

- is the problem related to the local language settings? how to solve this?

- what is the influence of the code page ? is there any compatibility between 1252 and 850 as code page?

- Server collation (e.g. Latin_1_General_CI_AS) is reported as key for the Unicode notation for character strings but what about notation of numbers?

- when to use float data type DT_R4 or DT_R8?

- I have remarked that the DT_DBTimeStamp is undependent from the source time notation – Is this correct?

- what is the difference between DT_Date and DT_DBDate or DT_DBTime, or DT_DBTimeStamp?

- Is Integration Services dependant of the local settings of the database engine?

- how to set / modify additional regional properties in a SSIS and SSRS package?

- how to change the default setting of the Flat File Connection Manager [starting the wizard, the local setting for the language – Dutch appears and this is OK for me but as codepage appears 1252 (ANSI Latin) and this is not OK as my server code page = 850]?

- how to work with e.g. US based data as source and Belgium settings for reports?

I am also struggling with trying to get the SSIS tutorials to work because of the text files having the dates in MM/DD/YYYY format while my local setting expects DD/MM/YYYY (English - New Zealand)

What needs to be done so that the Integration Services tutorials work in countries other than the USA?

|||

OK, got it - the answer is simple...

In the data source definition of the flat file containing the currency data, specify the locale as English(USA).

I suspect there are many users who follow the tutorial instructions to the letter and the instructions do not mention the locale setting - OK if you are in the USA.

|||

I

I have the same issue

My

Locale: Russian(Russia)

Code Page: 1251 (ANSI - кириллица)

Changing it, as you wrote it, to

Locale: English(United States)

does not correct this problem but just invokes more problems!!!!!

I aslo tried
ETL Package Problem
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=603488&SiteID=1

No luck either!

|||OK, I installed just one more instance with default English(US) language and

voilà .. everything started to work...
So, the conclusion is that in order to use SSIS I should migrate everything to a new instance with English(US)!? since default language/collation cannot be reset

I'd like to hear any better solution

|||

Another solution:

on http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en

you find database for case-sensitive collation and case-insensitive collation, a attached the other database and now works.

|||

This link tells that samples download had moved to codeplex

And codeplex has hundreds of samples

Which one?

collation or local sensitive settings or other configuration properties

I installed the samples and tutoriales on the same server as SQL Server 2005 and VS 2005 Team Suite.

Living in Belgium, the local settings are:

language = Duch (Belgium)

code page = 850

e.g. in the database samples (AdventureWorks database – AdventureWorks Warehouse database)

my date notation is: 26-12-2005

my number notation is: 1,0013016921998599

all demo samples and tutorials are developed using

language = English (United States)

code page 1252

e.g. in SampleCurrencyData.txt

the date notation is: 12/26/2005 00:00:00

the number notation is: 1.0013016921998599

I do not have any problems to test the diferent services (Data Base Engine Services, Analysis Services, reporting & notifications services ..) but I do not succeed to deploy any samples of integration services !

e.g. for Integration Services Tutorial - lesson 1: “Creating the Project and Basic Package”

there are no error messages, 1097 rows are processed

but, checking the result in the database, no data is updated in the FactCurrencyRate table of the AdventureWorksDW database !

Why?

- is the problem related to the local language settings? how to solve this?

- what is the influence of the code page ? is there any compatibility between 1252 and 850 as code page?

- Server collation (e.g. Latin_1_General_CI_AS) is reported as key for the Unicode notation for character strings but what about notation of numbers?

- when to use float data type DT_R4 or DT_R8?

- I have remarked that the DT_DBTimeStamp is undependent from the source time notation – Is this correct?

- what is the difference between DT_Date and DT_DBDate or DT_DBTime, or DT_DBTimeStamp?

- Is Integration Services dependant of the local settings of the database engine?

- how to set / modify additional regional properties in a SSIS and SSRS package?

- how to change the default setting of the Flat File Connection Manager [starting the wizard, the local setting for the language – Dutch appears and this is OK for me but as codepage appears 1252 (ANSI Latin) and this is not OK as my server code page = 850]?

- how to work with e.g. US based data as source and Belgium settings for reports?

I am also struggling with trying to get the SSIS tutorials to work because of the text files having the dates in MM/DD/YYYY format while my local setting expects DD/MM/YYYY (English - New Zealand)

What needs to be done so that the Integration Services tutorials work in countries other than the USA?

|||

OK, got it - the answer is simple...

In the data source definition of the flat file containing the currency data, specify the locale as English(USA).

I suspect there are many users who follow the tutorial instructions to the letter and the instructions do not mention the locale setting - OK if you are in the USA.

|||

I

I have the same issue

My

Locale: Russian(Russia)

Code Page: 1251 (ANSI - кириллица)

Changing it, as you wrote it, to

Locale: English(United States)

does not correct this problem but just invokes more problems!!!!!

I aslo tried
ETL Package Problem
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=603488&SiteID=1

No luck either!

|||OK, I installed just one more instance with default English(US) language and

voilà .. everything started to work...
So, the conclusion is that in order to use SSIS I should migrate everything to a new instance with English(US)!? since default language/collation cannot be reset

I'd like to hear any better solution

|||

Another solution:

on http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en

you find database for case-sensitive collation and case-insensitive collation, a attached the other database and now works.

|||

This link tells that samples download had moved to codeplex

And codeplex has hundreds of samples

Which one?

collation or local sensitive settings or other configuration properties

I installed the samples and tutoriales on the same server as SQL Server 2005 and VS 2005 Team Suite.

Living in Belgium, the local settings are:

language = Duch (Belgium)

code page = 850

e.g. in the database samples (AdventureWorks database – AdventureWorks Warehouse database)

my date notation is: 26-12-2005

my number notation is: 1,0013016921998599

all demo samples and tutorials are developed using

language = English (United States)

code page 1252

e.g. in SampleCurrencyData.txt

the date notation is: 12/26/2005 00:00:00

the number notation is: 1.0013016921998599

I do not have any problems to test the diferent services (Data Base Engine Services, Analysis Services, reporting & notifications services ..) but I do not succeed to deploy any samples of integration services !

e.g. for Integration Services Tutorial - lesson 1: “Creating the Project and Basic Package”

there are no error messages, 1097 rows are processed

but, checking the result in the database, no data is updated in the FactCurrencyRate table of the AdventureWorksDW database !

Why?

- is the problem related to the local language settings? how to solve this?

- what is the influence of the code page ? is there any compatibility between 1252 and 850 as code page?

- Server collation (e.g. Latin_1_General_CI_AS) is reported as key for the Unicode notation for character strings but what about notation of numbers?

- when to use float data type DT_R4 or DT_R8?

- I have remarked that the DT_DBTimeStamp is undependent from the source time notation – Is this correct?

- what is the difference between DT_Date and DT_DBDate or DT_DBTime, or DT_DBTimeStamp?

- Is Integration Services dependant of the local settings of the database engine?

- how to set / modify additional regional properties in a SSIS and SSRS package?

- how to change the default setting of the Flat File Connection Manager [starting the wizard, the local setting for the language – Dutch appears and this is OK for me but as codepage appears 1252 (ANSI Latin) and this is not OK as my server code page = 850]?

- how to work with e.g. US based data as source and Belgium settings for reports?

I am also struggling with trying to get the SSIS tutorials to work because of the text files having the dates in MM/DD/YYYY format while my local setting expects DD/MM/YYYY (English - New Zealand)

What needs to be done so that the Integration Services tutorials work in countries other than the USA?

|||

OK, got it - the answer is simple...

In the data source definition of the flat file containing the currency data, specify the locale as English(USA).

I suspect there are many users who follow the tutorial instructions to the letter and the instructions do not mention the locale setting - OK if you are in the USA.

|||

I

I have the same issue

My

Locale: Russian(Russia)

Code Page: 1251 (ANSI - кириллица)

Changing it, as you wrote it, to

Locale: English(United States)

does not correct this problem but just invokes more problems!!!!!

I aslo tried
ETL Package Problem
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=603488&SiteID=1

No luck either!

|||OK, I installed just one more instance with default English(US) language and

voilà .. everything started to work...
So, the conclusion is that in order to use SSIS I should migrate everything to a new instance with English(US)!? since default language/collation cannot be reset

I'd like to hear any better solution

|||

Another solution:

on http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en

you find database for case-sensitive collation and case-insensitive collation, a attached the other database and now works.

|||

This link tells that samples download had moved to codeplex

And codeplex has hundreds of samples

Which one?