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?

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 on nchar column

Hey eb
Whats the point in having a collation attached to a unicode
charecter typed column'
If you want to set a specific collation to character data, why
make it unicode on the first place'
Am I missing something here'
Thanks
ReaCollation isn't only the character set. It is also sorting order, case sensitivity, accent
sensitivity etc.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rea" <Rea@.discussions.microsoft.com> wrote in message
news:CA4DF029-A399-4FE0-B198-C7FC621F0691@.microsoft.com...
> Hey eb
> Whats the point in having a collation attached to a unicode
> charecter typed column'
> If you want to set a specific collation to character data, why
> make it unicode on the first place'
> Am I missing something here'
> Thanks
> Rea
>
>sqlsql

collation on nchar column

Hey eb
Whats the point in having a collation attached to a unicode
charecter typed column'
If you want to set a specific collation to character data, why
make it unicode on the first place'
Am I missing something here'
Thanks
ReaCollation isn't only the character set. It is also sorting order, case sensi
tivity, accent
sensitivity etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rea" <Rea@.discussions.microsoft.com> wrote in message
news:CA4DF029-A399-4FE0-B198-C7FC621F0691@.microsoft.com...
> Hey eb
> Whats the point in having a collation attached to a unicode
> charecter typed column'
> If you want to set a specific collation to character data, why
> make it unicode on the first place'
> Am I missing something here'
> Thanks
> Rea
>
>

Collation of Database and Server

Hello,
I am working a problem according to the collation of MS SQL Server
2000 and a database.
Environment: German MS SQL Server 2000 SP3a
Server Collation: Latin1_General_CI_AS
Collation of the database: SQL_Latin1_General_CP1_CI_AS
(Note: the database was created in an english MSDE and dureing an
upgrade restored on the SQL Server)
During an application upgrade process (refering to the SQL Server
database) I was running into a lot of problems according the datetime
format. So my suggestion was that this problem is related to the
different collations, cause in a different testing environment with
another collation (server collation = database collation) I was able
to perform the upgrade.
So, now I am wondering if its possible to change the database
collation without loosing my data? (hoping that this will solve the
prolem)
Any suggestions or experiences are welcome
tia,
Marcus
Marcus
Yes , you can change COLLATION in the database
Look at ALTER DATABASE in the BOL
> I was running into a lot of problems according the datetime
> format.
Look, without seeing your code/query its hard to suggest something.
"Marcus Schmidt" <Schmidt_Marcus*at*gmx.de> wrote in message
news:t139p0lqkn9219ii7opkjv26tkto4u2528@.4ax.com...
> Hello,
> I am working a problem according to the collation of MS SQL Server
> 2000 and a database.
> Environment: German MS SQL Server 2000 SP3a
> Server Collation: Latin1_General_CI_AS
> Collation of the database: SQL_Latin1_General_CP1_CI_AS
> (Note: the database was created in an english MSDE and dureing an
> upgrade restored on the SQL Server)
> During an application upgrade process (refering to the SQL Server
> database) I was running into a lot of problems according the datetime
> format. So my suggestion was that this problem is related to the
> different collations, cause in a different testing environment with
> another collation (server collation = database collation) I was able
> to perform the upgrade.
> So, now I am wondering if its possible to change the database
> collation without loosing my data? (hoping that this will solve the
> prolem)
> Any suggestions or experiences are welcome
> tia,
> Marcus

Collation of Database and Server

Hello,
I am working a problem according to the collation of MS SQL Server
2000 and a database.
Environment: German MS SQL Server 2000 SP3a
Server Collation: Latin1_General_CI_AS
Collation of the database: SQL_Latin1_General_CP1_CI_AS
(Note: the database was created in an english MSDE and dureing an
upgrade restored on the SQL Server)
During an application upgrade process (refering to the SQL Server
database) I was running into a lot of problems according the datetime
format. So my suggestion was that this problem is related to the
different collations, cause in a different testing environment with
another collation (server collation = database collation) I was able
to perform the upgrade.
So, now I am wondering if its possible to change the database
collation without loosing my data? (hoping that this will solve the
prolem)
Any suggestions or experiences are welcome
tia,
MarcusMarcus
Yes , you can change COLLATION in the database
Look at ALTER DATABASE in the BOL
> I was running into a lot of problems according the datetime
> format.
Look, without seeing your code/query its hard to suggest something.
"Marcus Schmidt" <Schmidt_Marcus*at*gmx.de> wrote in message
news:t139p0lqkn9219ii7opkjv26tkto4u2528@.
4ax.com...
> Hello,
> I am working a problem according to the collation of MS SQL Server
> 2000 and a database.
> Environment: German MS SQL Server 2000 SP3a
> Server Collation: Latin1_General_CI_AS
> Collation of the database: SQL_Latin1_General_CP1_CI_AS
> (Note: the database was created in an english MSDE and dureing an
> upgrade restored on the SQL Server)
> During an application upgrade process (refering to the SQL Server
> database) I was running into a lot of problems according the datetime
> format. So my suggestion was that this problem is related to the
> different collations, cause in a different testing environment with
> another collation (server collation = database collation) I was able
> to perform the upgrade.
> So, now I am wondering if its possible to change the database
> collation without loosing my data? (hoping that this will solve the
> prolem)
> Any suggestions or experiences are welcome
> tia,
> Marcus

Collation Name?

Hi,
I am help a church (chinese) to look at the existing database. When I
first look at it, I found out it is "Chinese_Taiwan_Stroke_CI_AS" under the
database property for the sectin of Collation Name.
I would like to know what is the major different between the default
(SQL_Latin1_General) and the "Chinese_Taiwan"? Also, I found out if i open
up a QA and typed in a Select statement, they are all case sensitive.. e.g.
a
table name called Customer, I have to key in "Select * from Customer", if i
type in "Select * from customer", there would be a syntax error...
Is that related to the Collation Name? or Is there any setting I can
configure to remove the case sensitive?
Thanks
Edyup, That is related to collation names. But syntax is case senisitive ?
that is something you set when you install the server. well collation name
can be differently spefified for db/table/even at column level. see BOL for
details.
--
Regards
R.D
--Knowledge gets doubled when shared
"Ed" wrote:

> Hi,
> I am help a church (chinese) to look at the existing database. When I
> first look at it, I found out it is "Chinese_Taiwan_Stroke_CI_AS" under th
e
> database property for the sectin of Collation Name.
> I would like to know what is the major different between the default
> (SQL_Latin1_General) and the "Chinese_Taiwan"? Also, I found out if i ope
n
> up a QA and typed in a Select statement, they are all case sensitive.. e.g
. a
> table name called Customer, I have to key in "Select * from Customer", if
i
> type in "Select * from customer", there would be a syntax error...
> Is that related to the Collation Name? or Is there any setting I can
> configure to remove the case sensitive?
> Thanks
> Ed|||Ed (Ed@.discussions.microsoft.com) writes:
> I am help a church (chinese) to look at the existing database. When I
> first look at it, I found out it is "Chinese_Taiwan_Stroke_CI_AS" under
> the database property for the sectin of Collation Name.
> I would like to know what is the major different between the default
> (SQL_Latin1_General) and the "Chinese_Taiwan"? Also, I found out if i
> open up a QA and typed in a Select statement, they are all case
> sensitive.. e.g. a table name called Customer, I have to key in "Select
> * from Customer", if i type in "Select * from customer", there would be
> a syntax error.. .
> Is that related to the Collation Name? or Is there any setting I can
> configure to remove the case sensitive?
Whether object names in a database are case sensitive or not depends on
the collation. However, I would expect Chinese_Taiwan_Stroke_CI_AS
to be a case-insensitive collation. (That's what the CI stands for).
The difference between different collations is that they sort and compare
data differently. For instance in SQL_Latin_General1, and co-sort
with A, where as in Finnish_Swedish_CS_AS they sort after Z, as they are
independent letters in Finnish and Swedish.
Since my Chinese is thin, I can't really discuss the peculiarities of
Chinese_Taiwan_Stroke_CI_AS, but obviously it obeys the old rules of
Chinese, and not the modern introduced by the rgime on the mainland.
Stroke would indicate sorting is done according to strokes. How
SQL_Latin1_General sorts Chinese, I don't know.
Anyway, changing the collation requires you to rebuild the database and
is a major work. I would stronly recommend you to keep the collation,
if all you have problem with is the table names. (It's another issue
if the Chinese stuff is handled in some undesired way.)
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 name mismatch - .NET SqlException

I've been doing some minor mods for my client and he's reported a
weird error. Weird, because until recently all was going swimmingly.
It's a Visual Studio .NET 2003 ASP.NET/VB.NET application with a SQL
2000 database.
I couldn't reproduce the problem at my office so I asked them to
provide me with a backup, which I duly restored to a new test
database. Lo and behold, it failed with the same error:
[SqlException: Error converting data type varchar to datetime.]
Microsoft.VisualBasic.CompilerServices.LateBinding .InternalLateCall(Object
o, Type objType, String name, Object[] args, String[] paramnames,
Boolean[] CopyBack, Boolean IgnoreReturn)
Microsoft.VisualBasic.CompilerServices.LateBinding .LateCall(Object
o, Type objType, String name, Object[] args, String[] paramnames,
Boolean[] CopyBack)
However, looking at the tables and stored procedures involved I could
see no differences between my development database and their test
version (table defs and SPROCs all identical).
However, I did note that looking at the properties of the two
databases:
Development DB (no problems) - Collation Name =
SQL_Latin1_General_CP1_Cl_AS
Test DB (SqlException) - Collation Name =
There was no Collation Name.
I realise that there's no way of changing the Collation Name of an
existing database, but if I were to get the client to create a new
blank database with the same Collation Name as the development DB and
then restore the test db over it, would that work, do you think?
Thanks
Edward
> I realise that there's no way of changing the Collation Name of an
> existing database, but if I were to get the client to create a new
> blank database with the same Collation Name as the development DB and
> then restore the test db over it, would that work, do you think?
You can change the database default collation with ALTER DATABASE:
ALTER DATABASE MyDatabase
COLLATE SQL_Latin1_General_CP1_CI_AS;
Hope this helps.
Dan Guzman
SQL Server MVP
<teddysnips@.hotmail.com> wrote in message
news:2ad498ef-fd23-4f18-9c07-81ce8bdd40ac@.s19g2000prg.googlegroups.com...
> I've been doing some minor mods for my client and he's reported a
> weird error. Weird, because until recently all was going swimmingly.
> It's a Visual Studio .NET 2003 ASP.NET/VB.NET application with a SQL
> 2000 database.
> I couldn't reproduce the problem at my office so I asked them to
> provide me with a backup, which I duly restored to a new test
> database. Lo and behold, it failed with the same error:
> [SqlException: Error converting data type varchar to datetime.]
> Microsoft.VisualBasic.CompilerServices.LateBinding .InternalLateCall(Object
> o, Type objType, String name, Object[] args, String[] paramnames,
> Boolean[] CopyBack, Boolean IgnoreReturn)
> Microsoft.VisualBasic.CompilerServices.LateBinding .LateCall(Object
> o, Type objType, String name, Object[] args, String[] paramnames,
> Boolean[] CopyBack)
> However, looking at the tables and stored procedures involved I could
> see no differences between my development database and their test
> version (table defs and SPROCs all identical).
> However, I did note that looking at the properties of the two
> databases:
> Development DB (no problems) - Collation Name =
> SQL_Latin1_General_CP1_Cl_AS
> Test DB (SqlException) - Collation Name =
> There was no Collation Name.
> I realise that there's no way of changing the Collation Name of an
> existing database, but if I were to get the client to create a new
> blank database with the same Collation Name as the development DB and
> then restore the test db over it, would that work, do you think?
> Thanks
> Edward
|||> [SqlException: Error converting data type varchar to datetime.]
I forgot to add that this error is not related to collation. It looks like
varchar data is somewhere being converted to a datetime and the data isn't a
valid datetime string. You can identify problem data with ISDATE:
SELECT *
FROM dbo.MyTable
WHERE ISDATE(MyColumn) = 0
Hope this helps.
Dan Guzman
SQL Server MVP
<teddysnips@.hotmail.com> wrote in message
news:2ad498ef-fd23-4f18-9c07-81ce8bdd40ac@.s19g2000prg.googlegroups.com...
> I've been doing some minor mods for my client and he's reported a
> weird error. Weird, because until recently all was going swimmingly.
> It's a Visual Studio .NET 2003 ASP.NET/VB.NET application with a SQL
> 2000 database.
> I couldn't reproduce the problem at my office so I asked them to
> provide me with a backup, which I duly restored to a new test
> database. Lo and behold, it failed with the same error:
> [SqlException: Error converting data type varchar to datetime.]
> Microsoft.VisualBasic.CompilerServices.LateBinding .InternalLateCall(Object
> o, Type objType, String name, Object[] args, String[] paramnames,
> Boolean[] CopyBack, Boolean IgnoreReturn)
> Microsoft.VisualBasic.CompilerServices.LateBinding .LateCall(Object
> o, Type objType, String name, Object[] args, String[] paramnames,
> Boolean[] CopyBack)
> However, looking at the tables and stored procedures involved I could
> see no differences between my development database and their test
> version (table defs and SPROCs all identical).
> However, I did note that looking at the properties of the two
> databases:
> Development DB (no problems) - Collation Name =
> SQL_Latin1_General_CP1_Cl_AS
> Test DB (SqlException) - Collation Name =
> There was no Collation Name.
> I realise that there's no way of changing the Collation Name of an
> existing database, but if I were to get the client to create a new
> blank database with the same Collation Name as the development DB and
> then restore the test db over it, would that work, do you think?
> Thanks
> Edward
sqlsql

Collation name mismatch - .NET SqlException

I've been doing some minor mods for my client and he's reported a
weird error. Weird, because until recently all was going swimmingly.
It's a Visual Studio .NET 2003 ASP.NET/VB.NET application with a SQL
2000 database.
I couldn't reproduce the problem at my office so I asked them to
provide me with a backup, which I duly restored to a new test
database. Lo and behold, it failed with the same error:
[SqlException: Error converting data type varchar to datetime.]
Microsoft.VisualBasic.CompilerServices.LateBinding.InternalLateCall(Object
o, Type objType, String name, Object[] args, String[] paramnames,
Boolean[] CopyBack, Boolean IgnoreReturn)
Microsoft.VisualBasic.CompilerServices.LateBinding.LateCall(Object
o, Type objType, String name, Object[] args, String[] paramnames,
Boolean[] CopyBack)
However, looking at the tables and stored procedures involved I could
see no differences between my development database and their test
version (table defs and SPROCs all identical).
However, I did note that looking at the properties of the two
databases:
Development DB (no problems) - Collation Name = SQL_Latin1_General_CP1_Cl_AS
Test DB (SqlException) - Collation Name =
There was no Collation Name.
I realise that there's no way of changing the Collation Name of an
existing database, but if I were to get the client to create a new
blank database with the same Collation Name as the development DB and
then restore the test db over it, would that work, do you think?
Thanks
Edward> I realise that there's no way of changing the Collation Name of an
> existing database, but if I were to get the client to create a new
> blank database with the same Collation Name as the development DB and
> then restore the test db over it, would that work, do you think?
You can change the database default collation with ALTER DATABASE:
ALTER DATABASE MyDatabase
COLLATE SQL_Latin1_General_CP1_CI_AS;
--
Hope this helps.
Dan Guzman
SQL Server MVP
<teddysnips@.hotmail.com> wrote in message
news:2ad498ef-fd23-4f18-9c07-81ce8bdd40ac@.s19g2000prg.googlegroups.com...
> I've been doing some minor mods for my client and he's reported a
> weird error. Weird, because until recently all was going swimmingly.
> It's a Visual Studio .NET 2003 ASP.NET/VB.NET application with a SQL
> 2000 database.
> I couldn't reproduce the problem at my office so I asked them to
> provide me with a backup, which I duly restored to a new test
> database. Lo and behold, it failed with the same error:
> [SqlException: Error converting data type varchar to datetime.]
> Microsoft.VisualBasic.CompilerServices.LateBinding.InternalLateCall(Object
> o, Type objType, String name, Object[] args, String[] paramnames,
> Boolean[] CopyBack, Boolean IgnoreReturn)
> Microsoft.VisualBasic.CompilerServices.LateBinding.LateCall(Object
> o, Type objType, String name, Object[] args, String[] paramnames,
> Boolean[] CopyBack)
> However, looking at the tables and stored procedures involved I could
> see no differences between my development database and their test
> version (table defs and SPROCs all identical).
> However, I did note that looking at the properties of the two
> databases:
> Development DB (no problems) - Collation Name => SQL_Latin1_General_CP1_Cl_AS
> Test DB (SqlException) - Collation Name => There was no Collation Name.
> I realise that there's no way of changing the Collation Name of an
> existing database, but if I were to get the client to create a new
> blank database with the same Collation Name as the development DB and
> then restore the test db over it, would that work, do you think?
> Thanks
> Edward|||> [SqlException: Error converting data type varchar to datetime.]
I forgot to add that this error is not related to collation. It looks like
varchar data is somewhere being converted to a datetime and the data isn't a
valid datetime string. You can identify problem data with ISDATE:
SELECT *
FROM dbo.MyTable
WHERE ISDATE(MyColumn) = 0
--
Hope this helps.
Dan Guzman
SQL Server MVP
<teddysnips@.hotmail.com> wrote in message
news:2ad498ef-fd23-4f18-9c07-81ce8bdd40ac@.s19g2000prg.googlegroups.com...
> I've been doing some minor mods for my client and he's reported a
> weird error. Weird, because until recently all was going swimmingly.
> It's a Visual Studio .NET 2003 ASP.NET/VB.NET application with a SQL
> 2000 database.
> I couldn't reproduce the problem at my office so I asked them to
> provide me with a backup, which I duly restored to a new test
> database. Lo and behold, it failed with the same error:
> [SqlException: Error converting data type varchar to datetime.]
> Microsoft.VisualBasic.CompilerServices.LateBinding.InternalLateCall(Object
> o, Type objType, String name, Object[] args, String[] paramnames,
> Boolean[] CopyBack, Boolean IgnoreReturn)
> Microsoft.VisualBasic.CompilerServices.LateBinding.LateCall(Object
> o, Type objType, String name, Object[] args, String[] paramnames,
> Boolean[] CopyBack)
> However, looking at the tables and stored procedures involved I could
> see no differences between my development database and their test
> version (table defs and SPROCs all identical).
> However, I did note that looking at the properties of the two
> databases:
> Development DB (no problems) - Collation Name => SQL_Latin1_General_CP1_Cl_AS
> Test DB (SqlException) - Collation Name => There was no Collation Name.
> I realise that there's no way of changing the Collation Name of an
> existing database, but if I were to get the client to create a new
> blank database with the same Collation Name as the development DB and
> then restore the test db over it, would that work, do you think?
> Thanks
> Edward

Collation name Compatibility_183_406_30003?

Hello!
I may see the collation name Compatibility_183_406_30003 specified for a
database. What is that?
Best regards,
Henrik Dahl
Probabily you restored a database from the SQL7 format to a SQL 2k.
Collations are managed differently.
For this reason, in order to avoid collation conflicts you may want to
rebuild your database changing the collation to a SQL 2k default one.
Ciao
"Henrik Dahl" <q@.q.q.q> wrote in message
news:OTXB8dbXEHA.2840@.TK2MSFTNGP11.phx.gbl...
> Hello!
> I may see the collation name Compatibility_183_406_30003 specified for a
> database. What is that?
>
> Best regards,
> Henrik Dahl
>
|||Adriano is correct that this results from an upgrade of a SQL 7.0
instance that has non-standard collation settings. What you have is
called a "compatibility collation", and is described in more detail in
270042 INF: Description of SQL Server Compatibility Collations
(http://support.microsoft.com/?id=270042).
HTH,
Bart
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no
rights.
From: "Adriano Trevisan" <trevisan.adriano_at_tin.it>
References: <OTXB8dbXEHA.2840@.TK2MSFTNGP11.phx.gbl>
Subject: Re: Collation name Compatibility_183_406_30003?
Date: Wed, 30 Jun 2004 08:46:21 +0200
Lines: 24
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
Message-ID: <#oZs23mXEHA.1144@.TK2MSFTNGP10.phx.gbl>
Newsgroups: microsoft.public.sqlserver.server
NNTP-Posting-Host: host45-231.pool81114.interbusiness.it 81.114.231.45
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP10.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:349291
X-Tomcat-NG: microsoft.public.sqlserver.server
Probabily you restored a database from the SQL7 format to a SQL 2k.
Collations are managed differently.
For this reason, in order to avoid collation conflicts you may want to
rebuild your database changing the collation to a SQL 2k default one.
Ciao
"Henrik Dahl" <q@.q.q.q> wrote in message
news:OTXB8dbXEHA.2840@.TK2MSFTNGP11.phx.gbl...
> Hello!
> I may see the collation name Compatibility_183_406_30003 specified for a
> database. What is that?
>
> Best regards,
> Henrik Dahl
>
|||Thanks Bart, we are experiencing this issue on many customers.
After the upgrade to the SQL 2k, any "join" with a temporary table results
in a collation conflict, thus blocking the application.
Currently, I solve the issue by
1) Build a new native SQL2k database with the preferred collation (normally
I use default one)
2) Script the database structure WITHOUT indexes/fkeys/indexed views and
using SQL7 compatibility (to remove "COLLATE" on columns) and execute them
on the db created previously.
3) DTS import/export from the source SQL7 migrated db to the native SQL2k
one.
4) Script the keys, indexes, views, stored, and so on and apply them to the
SQL2k native one.
I cannot use Transfer SQL objects because sometimes this leads to errors
related to foreign keys not correctly managed by the DTS itself. However,
this was true on the original version of SQL and on the SP1 and 2. I don't
know if current SP solves the issue.
Could you please check and let me know if another way to solve the
"collation conflict" issue exists without using the steps above?
Thank you very much,
Regards,
Adriano
"Bart Duncan [MSFT]" <bartd@.online.microsoft.com> wrote in message
news:RnbarpvXEHA.2244@.cpmsftngxa06.phx.gbl...
> Adriano is correct that this results from an upgrade of a SQL 7.0
> instance that has non-standard collation settings. What you have is
> called a "compatibility collation", and is described in more detail in
> 270042 INF: Description of SQL Server Compatibility Collations
> (http://support.microsoft.com/?id=270042).
> HTH,
> Bart
> --
> Bart Duncan
> Microsoft SQL Server Support
> Please reply to the newsgroup only - thanks.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> --
> From: "Adriano Trevisan" <trevisan.adriano_at_tin.it>
> References: <OTXB8dbXEHA.2840@.TK2MSFTNGP11.phx.gbl>
> Subject: Re: Collation name Compatibility_183_406_30003?
> Date: Wed, 30 Jun 2004 08:46:21 +0200
> Lines: 24
> X-Priority: 3
> X-MSMail-Priority: Normal
> X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
> Message-ID: <#oZs23mXEHA.1144@.TK2MSFTNGP10.phx.gbl>
> Newsgroups: microsoft.public.sqlserver.server
> NNTP-Posting-Host: host45-231.pool81114.interbusiness.it 81.114.231.45
> Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP10.phx.gbl
> Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:349291
> X-Tomcat-NG: microsoft.public.sqlserver.server
> Probabily you restored a database from the SQL7 format to a SQL 2k.
> Collations are managed differently.
> For this reason, in order to avoid collation conflicts you may want to
> rebuild your database changing the collation to a SQL 2k default one.
> Ciao
> "Henrik Dahl" <q@.q.q.q> wrote in message
> news:OTXB8dbXEHA.2840@.TK2MSFTNGP11.phx.gbl...
>
>
|||There are three general ways to solve the issue you are facing:
1. When you join a user db table to a temp table, include "COLLATE
database_default" as a modifier for the join predicate. For example:
SELECT * FROM usrtbl
INNER JOIN #tmptbl ON c1 = c2 COLLATE database_default
2. Change the collation of the existing database to match the
instance-level default collation. The transfer approach you mention is
the currently the easiest way to do this.
3. Ensure that the instance default collation matches the database's
collation. This can be done with rebuildm.exe for most collations, but
unfortunately not for compatibility collations. If you want a SQL 2000
instance to use a compatibility collation for tempdb you must either
perform an unattended install and specify the collation name in the .ISS
(setup answer file), or upgrade an existing SQL 7.0 instance so that the
new SQL 2K instance inherits the 7.0 instance's compatibility collation.
Option #1 is the best long-term solution. Once you make this change,
your code will be insulated from the problem from that point forward and
you won't have to care whether the user database collation matches the
tempdb collation at your customer sites.
HTH,
Bart
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no
rights.
From: "Adriano Trevisan" <trevisan.adriano_at_tin.it>
References: <OTXB8dbXEHA.2840@.TK2MSFTNGP11.phx.gbl>
<#oZs23mXEHA.1144@.TK2MSFTNGP10.phx.gbl>
<RnbarpvXEHA.2244@.cpmsftngxa06.phx.gbl>
Subject: Re: Collation name Compatibility_183_406_30003?
Date: Thu, 1 Jul 2004 09:32:37 +0200
Lines: 92
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
Message-ID: <#LaqX2zXEHA.384@.TK2MSFTNGP10.phx.gbl>
Newsgroups: microsoft.public.sqlserver.server
NNTP-Posting-Host: host45-231.pool81114.interbusiness.it 81.114.231.45
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP10.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:349499
X-Tomcat-NG: microsoft.public.sqlserver.server
Thanks Bart, we are experiencing this issue on many customers.
After the upgrade to the SQL 2k, any "join" with a temporary table results
in a collation conflict, thus blocking the application.
Currently, I solve the issue by
1) Build a new native SQL2k database with the preferred collation
(normally
I use default one)
2) Script the database structure WITHOUT indexes/fkeys/indexed views and
using SQL7 compatibility (to remove "COLLATE" on columns) and execute them
on the db created previously.
3) DTS import/export from the source SQL7 migrated db to the native SQL2k
one.
4) Script the keys, indexes, views, stored, and so on and apply them to
the
SQL2k native one.
I cannot use Transfer SQL objects because sometimes this leads to errors
related to foreign keys not correctly managed by the DTS itself. However,
this was true on the original version of SQL and on the SP1 and 2. I don't
know if current SP solves the issue.
Could you please check and let me know if another way to solve the
"collation conflict" issue exists without using the steps above?
Thank you very much,
Regards,
Adriano
"Bart Duncan [MSFT]" <bartd@.online.microsoft.com> wrote in message
news:RnbarpvXEHA.2244@.cpmsftngxa06.phx.gbl...[vbcol=seagreen]
> Adriano is correct that this results from an upgrade of a SQL 7.0
> instance that has non-standard collation settings. What you have is
> called a "compatibility collation", and is described in more detail in
> 270042 INF: Description of SQL Server Compatibility Collations
> (http://support.microsoft.com/?id=270042).
> HTH,
> Bart
> --
> Bart Duncan
> Microsoft SQL Server Support
> Please reply to the newsgroup only - thanks.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> --
> From: "Adriano Trevisan" <trevisan.adriano_at_tin.it>
> References: <OTXB8dbXEHA.2840@.TK2MSFTNGP11.phx.gbl>
> Subject: Re: Collation name Compatibility_183_406_30003?
> Date: Wed, 30 Jun 2004 08:46:21 +0200
> Lines: 24
> X-Priority: 3
> X-MSMail-Priority: Normal
> X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
> Message-ID: <#oZs23mXEHA.1144@.TK2MSFTNGP10.phx.gbl>
> Newsgroups: microsoft.public.sqlserver.server
> NNTP-Posting-Host: host45-231.pool81114.interbusiness.it 81.114.231.45
> Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP10.phx.gbl
> Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:349291
> X-Tomcat-NG: microsoft.public.sqlserver.server
> Probabily you restored a database from the SQL7 format to a SQL 2k.
> Collations are managed differently.
> For this reason, in order to avoid collation conflicts you may want to
> rebuild your database changing the collation to a SQL 2k default one.
> Ciao
> "Henrik Dahl" <q@.q.q.q> wrote in message
> news:OTXB8dbXEHA.2840@.TK2MSFTNGP11.phx.gbl...
for a
>
>
|||Thank you very much Bart,
Regards,
Adriano
"Bart Duncan [MSFT]" <bartd@.online.microsoft.com> wrote in message
news:eKbtWL8XEHA.1008@.cpmsftngxa06.phx.gbl...
> There are three general ways to solve the issue you are facing:
> 1. When you join a user db table to a temp table, include "COLLATE
> database_default" as a modifier for the join predicate. For example:
> SELECT * FROM usrtbl
> INNER JOIN #tmptbl ON c1 = c2 COLLATE database_default
> 2. Change the collation of the existing database to match the
> instance-level default collation. The transfer approach you mention is
> the currently the easiest way to do this.
> 3. Ensure that the instance default collation matches the database's
> collation. This can be done with rebuildm.exe for most collations, but
> unfortunately not for compatibility collations. If you want a SQL 2000
> instance to use a compatibility collation for tempdb you must either
> perform an unattended install and specify the collation name in the .ISS
> (setup answer file), or upgrade an existing SQL 7.0 instance so that the
> new SQL 2K instance inherits the 7.0 instance's compatibility collation.
> Option #1 is the best long-term solution. Once you make this change,
> your code will be insulated from the problem from that point forward and
> you won't have to care whether the user database collation matches the
> tempdb collation at your customer sites.
> HTH,
> Bart
> --
> Bart Duncan
> Microsoft SQL Server Support
> Please reply to the newsgroup only - thanks.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> --
> From: "Adriano Trevisan" <trevisan.adriano_at_tin.it>
> References: <OTXB8dbXEHA.2840@.TK2MSFTNGP11.phx.gbl>
> <#oZs23mXEHA.1144@.TK2MSFTNGP10.phx.gbl>
> <RnbarpvXEHA.2244@.cpmsftngxa06.phx.gbl>
> Subject: Re: Collation name Compatibility_183_406_30003?
> Date: Thu, 1 Jul 2004 09:32:37 +0200
> Lines: 92
> X-Priority: 3
> X-MSMail-Priority: Normal
> X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
> Message-ID: <#LaqX2zXEHA.384@.TK2MSFTNGP10.phx.gbl>
> Newsgroups: microsoft.public.sqlserver.server
> NNTP-Posting-Host: host45-231.pool81114.interbusiness.it 81.114.231.45
> Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP10.phx.gbl
> Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:349499
> X-Tomcat-NG: microsoft.public.sqlserver.server
> Thanks Bart, we are experiencing this issue on many customers.
> After the upgrade to the SQL 2k, any "join" with a temporary table results
> in a collation conflict, thus blocking the application.
> Currently, I solve the issue by
> 1) Build a new native SQL2k database with the preferred collation
> (normally
> I use default one)
> 2) Script the database structure WITHOUT indexes/fkeys/indexed views and
> using SQL7 compatibility (to remove "COLLATE" on columns) and execute them
> on the db created previously.
> 3) DTS import/export from the source SQL7 migrated db to the native SQL2k
> one.
> 4) Script the keys, indexes, views, stored, and so on and apply them to
> the
> SQL2k native one.
> I cannot use Transfer SQL objects because sometimes this leads to errors
> related to foreign keys not correctly managed by the DTS itself. However,
> this was true on the original version of SQL and on the SP1 and 2. I don't
> know if current SP solves the issue.
> Could you please check and let me know if another way to solve the
> "collation conflict" issue exists without using the steps above?
> Thank you very much,
> Regards,
> Adriano
> "Bart Duncan [MSFT]" <bartd@.online.microsoft.com> wrote in message
> news:RnbarpvXEHA.2244@.cpmsftngxa06.phx.gbl...
> for a
>
>

Collation name Compatibility_183_406_30003?

Hello!
I may see the collation name Compatibility_183_406_30003 specified for a
database. What is that?
Best regards,
Henrik DahlProbabily you restored a database from the SQL7 format to a SQL 2k.
Collations are managed differently.
For this reason, in order to avoid collation conflicts you may want to
rebuild your database changing the collation to a SQL 2k default one.
Ciao
"Henrik Dahl" <q@.q.q.q> wrote in message
news:OTXB8dbXEHA.2840@.TK2MSFTNGP11.phx.gbl...
> Hello!
> I may see the collation name Compatibility_183_406_30003 specified for a
> database. What is that?
>
> Best regards,
> Henrik Dahl
>|||Adriano is correct that this results from an upgrade of a SQL 7.0
instance that has non-standard collation settings. What you have is
called a "compatibility collation", and is described in more detail in
270042 INF: Description of SQL Server Compatibility Collations
(http://support.microsoft.com/?id=270042).
HTH,
Bart
--
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no
rights.
From: "Adriano Trevisan" <trevisan.adriano_at_tin.it>
References: <OTXB8dbXEHA.2840@.TK2MSFTNGP11.phx.gbl>
Subject: Re: Collation name Compatibility_183_406_30003?
Date: Wed, 30 Jun 2004 08:46:21 +0200
Lines: 24
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
Message-ID: <#oZs23mXEHA.1144@.TK2MSFTNGP10.phx.gbl>
Newsgroups: microsoft.public.sqlserver.server
NNTP-Posting-Host: host45-231.pool81114.interbusiness.it 81.114.231.45
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:349291
X-Tomcat-NG: microsoft.public.sqlserver.server
Probabily you restored a database from the SQL7 format to a SQL 2k.
Collations are managed differently.
For this reason, in order to avoid collation conflicts you may want to
rebuild your database changing the collation to a SQL 2k default one.
Ciao
"Henrik Dahl" <q@.q.q.q> wrote in message
news:OTXB8dbXEHA.2840@.TK2MSFTNGP11.phx.gbl...
> Hello!
> I may see the collation name Compatibility_183_406_30003 specified for a
> database. What is that?
>
> Best regards,
> Henrik Dahl
>|||Thanks Bart, we are experiencing this issue on many customers.
After the upgrade to the SQL 2k, any "join" with a temporary table results
in a collation conflict, thus blocking the application.
Currently, I solve the issue by
1) Build a new native SQL2k database with the preferred collation (normally
I use default one)
2) Script the database structure WITHOUT indexes/fkeys/indexed views and
using SQL7 compatibility (to remove "COLLATE" on columns) and execute them
on the db created previously.
3) DTS import/export from the source SQL7 migrated db to the native SQL2k
one.
4) Script the keys, indexes, views, stored, and so on and apply them to the
SQL2k native one.
I cannot use Transfer SQL objects because sometimes this leads to errors
related to foreign keys not correctly managed by the DTS itself. However,
this was true on the original version of SQL and on the SP1 and 2. I don't
know if current SP solves the issue.
Could you please check and let me know if another way to solve the
"collation conflict" issue exists without using the steps above?
Thank you very much,
Regards,
Adriano
"Bart Duncan [MSFT]" <bartd@.online.microsoft.com> wrote in message
news:RnbarpvXEHA.2244@.cpmsftngxa06.phx.gbl...
> Adriano is correct that this results from an upgrade of a SQL 7.0
> instance that has non-standard collation settings. What you have is
> called a "compatibility collation", and is described in more detail in
> 270042 INF: Description of SQL Server Compatibility Collations
> (http://support.microsoft.com/?id=270042).
> HTH,
> Bart
> --
> Bart Duncan
> Microsoft SQL Server Support
> Please reply to the newsgroup only - thanks.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> --
> From: "Adriano Trevisan" <trevisan.adriano_at_tin.it>
> References: <OTXB8dbXEHA.2840@.TK2MSFTNGP11.phx.gbl>
> Subject: Re: Collation name Compatibility_183_406_30003?
> Date: Wed, 30 Jun 2004 08:46:21 +0200
> Lines: 24
> X-Priority: 3
> X-MSMail-Priority: Normal
> X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
> Message-ID: <#oZs23mXEHA.1144@.TK2MSFTNGP10.phx.gbl>
> Newsgroups: microsoft.public.sqlserver.server
> NNTP-Posting-Host: host45-231.pool81114.interbusiness.it 81.114.231.45
> Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
> Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:349291
> X-Tomcat-NG: microsoft.public.sqlserver.server
> Probabily you restored a database from the SQL7 format to a SQL 2k.
> Collations are managed differently.
> For this reason, in order to avoid collation conflicts you may want to
> rebuild your database changing the collation to a SQL 2k default one.
> Ciao
> "Henrik Dahl" <q@.q.q.q> wrote in message
> news:OTXB8dbXEHA.2840@.TK2MSFTNGP11.phx.gbl...
> > Hello!
> >
> > I may see the collation name Compatibility_183_406_30003 specified for a
> > database. What is that?
> >
> >
> > Best regards,
> >
> > Henrik Dahl
> >
> >
>
>|||There are three general ways to solve the issue you are facing:
1. When you join a user db table to a temp table, include "COLLATE
database_default" as a modifier for the join predicate. For example:
SELECT * FROM usrtbl
INNER JOIN #tmptbl ON c1 = c2 COLLATE database_default
2. Change the collation of the existing database to match the
instance-level default collation. The transfer approach you mention is
the currently the easiest way to do this.
3. Ensure that the instance default collation matches the database's
collation. This can be done with rebuildm.exe for most collations, but
unfortunately not for compatibility collations. If you want a SQL 2000
instance to use a compatibility collation for tempdb you must either
perform an unattended install and specify the collation name in the .ISS
(setup answer file), or upgrade an existing SQL 7.0 instance so that the
new SQL 2K instance inherits the 7.0 instance's compatibility collation.
Option #1 is the best long-term solution. Once you make this change,
your code will be insulated from the problem from that point forward and
you won't have to care whether the user database collation matches the
tempdb collation at your customer sites.
HTH,
Bart
--
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no
rights.
From: "Adriano Trevisan" <trevisan.adriano_at_tin.it>
References: <OTXB8dbXEHA.2840@.TK2MSFTNGP11.phx.gbl>
<#oZs23mXEHA.1144@.TK2MSFTNGP10.phx.gbl>
<RnbarpvXEHA.2244@.cpmsftngxa06.phx.gbl>
Subject: Re: Collation name Compatibility_183_406_30003?
Date: Thu, 1 Jul 2004 09:32:37 +0200
Lines: 92
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
Message-ID: <#LaqX2zXEHA.384@.TK2MSFTNGP10.phx.gbl>
Newsgroups: microsoft.public.sqlserver.server
NNTP-Posting-Host: host45-231.pool81114.interbusiness.it 81.114.231.45
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:349499
X-Tomcat-NG: microsoft.public.sqlserver.server
Thanks Bart, we are experiencing this issue on many customers.
After the upgrade to the SQL 2k, any "join" with a temporary table results
in a collation conflict, thus blocking the application.
Currently, I solve the issue by
1) Build a new native SQL2k database with the preferred collation
(normally
I use default one)
2) Script the database structure WITHOUT indexes/fkeys/indexed views and
using SQL7 compatibility (to remove "COLLATE" on columns) and execute them
on the db created previously.
3) DTS import/export from the source SQL7 migrated db to the native SQL2k
one.
4) Script the keys, indexes, views, stored, and so on and apply them to
the
SQL2k native one.
I cannot use Transfer SQL objects because sometimes this leads to errors
related to foreign keys not correctly managed by the DTS itself. However,
this was true on the original version of SQL and on the SP1 and 2. I don't
know if current SP solves the issue.
Could you please check and let me know if another way to solve the
"collation conflict" issue exists without using the steps above?
Thank you very much,
Regards,
Adriano
"Bart Duncan [MSFT]" <bartd@.online.microsoft.com> wrote in message
news:RnbarpvXEHA.2244@.cpmsftngxa06.phx.gbl...
> Adriano is correct that this results from an upgrade of a SQL 7.0
> instance that has non-standard collation settings. What you have is
> called a "compatibility collation", and is described in more detail in
> 270042 INF: Description of SQL Server Compatibility Collations
> (http://support.microsoft.com/?id=270042).
> HTH,
> Bart
> --
> Bart Duncan
> Microsoft SQL Server Support
> Please reply to the newsgroup only - thanks.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> --
> From: "Adriano Trevisan" <trevisan.adriano_at_tin.it>
> References: <OTXB8dbXEHA.2840@.TK2MSFTNGP11.phx.gbl>
> Subject: Re: Collation name Compatibility_183_406_30003?
> Date: Wed, 30 Jun 2004 08:46:21 +0200
> Lines: 24
> X-Priority: 3
> X-MSMail-Priority: Normal
> X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
> Message-ID: <#oZs23mXEHA.1144@.TK2MSFTNGP10.phx.gbl>
> Newsgroups: microsoft.public.sqlserver.server
> NNTP-Posting-Host: host45-231.pool81114.interbusiness.it 81.114.231.45
> Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
> Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:349291
> X-Tomcat-NG: microsoft.public.sqlserver.server
> Probabily you restored a database from the SQL7 format to a SQL 2k.
> Collations are managed differently.
> For this reason, in order to avoid collation conflicts you may want to
> rebuild your database changing the collation to a SQL 2k default one.
> Ciao
> "Henrik Dahl" <q@.q.q.q> wrote in message
> news:OTXB8dbXEHA.2840@.TK2MSFTNGP11.phx.gbl...
> > Hello!
> >
> > I may see the collation name Compatibility_183_406_30003 specified
for a
> > database. What is that?
> >
> >
> > Best regards,
> >
> > Henrik Dahl
> >
> >
>
>|||Thank you very much Bart,
Regards,
Adriano
"Bart Duncan [MSFT]" <bartd@.online.microsoft.com> wrote in message
news:eKbtWL8XEHA.1008@.cpmsftngxa06.phx.gbl...
> There are three general ways to solve the issue you are facing:
> 1. When you join a user db table to a temp table, include "COLLATE
> database_default" as a modifier for the join predicate. For example:
> SELECT * FROM usrtbl
> INNER JOIN #tmptbl ON c1 = c2 COLLATE database_default
> 2. Change the collation of the existing database to match the
> instance-level default collation. The transfer approach you mention is
> the currently the easiest way to do this.
> 3. Ensure that the instance default collation matches the database's
> collation. This can be done with rebuildm.exe for most collations, but
> unfortunately not for compatibility collations. If you want a SQL 2000
> instance to use a compatibility collation for tempdb you must either
> perform an unattended install and specify the collation name in the .ISS
> (setup answer file), or upgrade an existing SQL 7.0 instance so that the
> new SQL 2K instance inherits the 7.0 instance's compatibility collation.
> Option #1 is the best long-term solution. Once you make this change,
> your code will be insulated from the problem from that point forward and
> you won't have to care whether the user database collation matches the
> tempdb collation at your customer sites.
> HTH,
> Bart
> --
> Bart Duncan
> Microsoft SQL Server Support
> Please reply to the newsgroup only - thanks.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> --
> From: "Adriano Trevisan" <trevisan.adriano_at_tin.it>
> References: <OTXB8dbXEHA.2840@.TK2MSFTNGP11.phx.gbl>
> <#oZs23mXEHA.1144@.TK2MSFTNGP10.phx.gbl>
> <RnbarpvXEHA.2244@.cpmsftngxa06.phx.gbl>
> Subject: Re: Collation name Compatibility_183_406_30003?
> Date: Thu, 1 Jul 2004 09:32:37 +0200
> Lines: 92
> X-Priority: 3
> X-MSMail-Priority: Normal
> X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
> Message-ID: <#LaqX2zXEHA.384@.TK2MSFTNGP10.phx.gbl>
> Newsgroups: microsoft.public.sqlserver.server
> NNTP-Posting-Host: host45-231.pool81114.interbusiness.it 81.114.231.45
> Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
> Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:349499
> X-Tomcat-NG: microsoft.public.sqlserver.server
> Thanks Bart, we are experiencing this issue on many customers.
> After the upgrade to the SQL 2k, any "join" with a temporary table results
> in a collation conflict, thus blocking the application.
> Currently, I solve the issue by
> 1) Build a new native SQL2k database with the preferred collation
> (normally
> I use default one)
> 2) Script the database structure WITHOUT indexes/fkeys/indexed views and
> using SQL7 compatibility (to remove "COLLATE" on columns) and execute them
> on the db created previously.
> 3) DTS import/export from the source SQL7 migrated db to the native SQL2k
> one.
> 4) Script the keys, indexes, views, stored, and so on and apply them to
> the
> SQL2k native one.
> I cannot use Transfer SQL objects because sometimes this leads to errors
> related to foreign keys not correctly managed by the DTS itself. However,
> this was true on the original version of SQL and on the SP1 and 2. I don't
> know if current SP solves the issue.
> Could you please check and let me know if another way to solve the
> "collation conflict" issue exists without using the steps above?
> Thank you very much,
> Regards,
> Adriano
> "Bart Duncan [MSFT]" <bartd@.online.microsoft.com> wrote in message
> news:RnbarpvXEHA.2244@.cpmsftngxa06.phx.gbl...
> > Adriano is correct that this results from an upgrade of a SQL 7.0
> > instance that has non-standard collation settings. What you have is
> > called a "compatibility collation", and is described in more detail in
> > 270042 INF: Description of SQL Server Compatibility Collations
> > (http://support.microsoft.com/?id=270042).
> >
> > HTH,
> > Bart
> > --
> > Bart Duncan
> > Microsoft SQL Server Support
> >
> > Please reply to the newsgroup only - thanks.
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >
> >
> > --
> > From: "Adriano Trevisan" <trevisan.adriano_at_tin.it>
> > References: <OTXB8dbXEHA.2840@.TK2MSFTNGP11.phx.gbl>
> > Subject: Re: Collation name Compatibility_183_406_30003?
> > Date: Wed, 30 Jun 2004 08:46:21 +0200
> > Lines: 24
> > X-Priority: 3
> > X-MSMail-Priority: Normal
> > X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
> > X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
> > Message-ID: <#oZs23mXEHA.1144@.TK2MSFTNGP10.phx.gbl>
> > Newsgroups: microsoft.public.sqlserver.server
> > NNTP-Posting-Host: host45-231.pool81114.interbusiness.it 81.114.231.45
> > Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
> > Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:349291
> > X-Tomcat-NG: microsoft.public.sqlserver.server
> >
> > Probabily you restored a database from the SQL7 format to a SQL 2k.
> >
> > Collations are managed differently.
> >
> > For this reason, in order to avoid collation conflicts you may want to
> > rebuild your database changing the collation to a SQL 2k default one.
> >
> > Ciao
> >
> > "Henrik Dahl" <q@.q.q.q> wrote in message
> > news:OTXB8dbXEHA.2840@.TK2MSFTNGP11.phx.gbl...
> > > Hello!
> > >
> > > I may see the collation name Compatibility_183_406_30003 specified
> for a
> > > database. What is that?
> > >
> > >
> > > Best regards,
> > >
> > > Henrik Dahl
> > >
> > >
> >
> >
> >
> >
>
>

Collation name Compatibility_183_406_30003?

Hello!
I may see the collation name Compatibility_183_406_30003 specified for a
database. What is that?
Best regards,
Henrik DahlProbabily you restored a database from the SQL7 format to a SQL 2k.
Collations are managed differently.
For this reason, in order to avoid collation conflicts you may want to
rebuild your database changing the collation to a SQL 2k default one.
Ciao
"Henrik Dahl" <q@.q.q.q> wrote in message
news:OTXB8dbXEHA.2840@.TK2MSFTNGP11.phx.gbl...
> Hello!
> I may see the collation name Compatibility_183_406_30003 specified for a
> database. What is that?
>
> Best regards,
> Henrik Dahl
>|||Adriano is correct that this results from an upgrade of a SQL 7.0
instance that has non-standard collation settings. What you have is
called a "compatibility collation", and is described in more detail in
270042 INF: Description of SQL Server Compatibility Collations
(http://support.microsoft.com/?id=270042).
HTH,
Bart
--
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no
rights.
From: "Adriano Trevisan" <trevisan.adriano_at_tin.it>
References: <OTXB8dbXEHA.2840@.TK2MSFTNGP11.phx.gbl>
Subject: Re: Collation name Compatibility_183_406_30003?
Date: Wed, 30 Jun 2004 08:46:21 +0200
Lines: 24
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
Message-ID: <#oZs23mXEHA.1144@.TK2MSFTNGP10.phx.gbl>
Newsgroups: microsoft.public.sqlserver.server
NNTP-Posting-Host: host45-231.pool81114.interbusiness.it 81.114.231.45
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:349291
X-Tomcat-NG: microsoft.public.sqlserver.server
Probabily you restored a database from the SQL7 format to a SQL 2k.
Collations are managed differently.
For this reason, in order to avoid collation conflicts you may want to
rebuild your database changing the collation to a SQL 2k default one.
Ciao
"Henrik Dahl" <q@.q.q.q> wrote in message
news:OTXB8dbXEHA.2840@.TK2MSFTNGP11.phx.gbl...
> Hello!
> I may see the collation name Compatibility_183_406_30003 specified for a
> database. What is that?
>
> Best regards,
> Henrik Dahl
>|||Thanks Bart, we are experiencing this issue on many customers.
After the upgrade to the SQL 2k, any "join" with a temporary table results
in a collation conflict, thus blocking the application.
Currently, I solve the issue by
1) Build a new native SQL2k database with the preferred collation (normally
I use default one)
2) Script the database structure WITHOUT indexes/fkeys/indexed views and
using SQL7 compatibility (to remove "COLLATE" on columns) and execute them
on the db created previously.
3) DTS import/export from the source SQL7 migrated db to the native SQL2k
one.
4) Script the keys, indexes, views, stored, and so on and apply them to the
SQL2k native one.
I cannot use Transfer SQL objects because sometimes this leads to errors
related to foreign keys not correctly managed by the DTS itself. However,
this was true on the original version of SQL and on the SP1 and 2. I don't
know if current SP solves the issue.
Could you please check and let me know if another way to solve the
"collation conflict" issue exists without using the steps above?
Thank you very much,
Regards,
Adriano
"Bart Duncan [MSFT]" <bartd@.online.microsoft.com> wrote in message
news:RnbarpvXEHA.2244@.cpmsftngxa06.phx.gbl...
> Adriano is correct that this results from an upgrade of a SQL 7.0
> instance that has non-standard collation settings. What you have is
> called a "compatibility collation", and is described in more detail in
> 270042 INF: Description of SQL Server Compatibility Collations
> (http://support.microsoft.com/?id=270042).
> HTH,
> Bart
> --
> Bart Duncan
> Microsoft SQL Server Support
> Please reply to the newsgroup only - thanks.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> --
> From: "Adriano Trevisan" <trevisan.adriano_at_tin.it>
> References: <OTXB8dbXEHA.2840@.TK2MSFTNGP11.phx.gbl>
> Subject: Re: Collation name Compatibility_183_406_30003?
> Date: Wed, 30 Jun 2004 08:46:21 +0200
> Lines: 24
> X-Priority: 3
> X-MSMail-Priority: Normal
> X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
> Message-ID: <#oZs23mXEHA.1144@.TK2MSFTNGP10.phx.gbl>
> Newsgroups: microsoft.public.sqlserver.server
> NNTP-Posting-Host: host45-231.pool81114.interbusiness.it 81.114.231.45
> Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
> Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:349291
> X-Tomcat-NG: microsoft.public.sqlserver.server
> Probabily you restored a database from the SQL7 format to a SQL 2k.
> Collations are managed differently.
> For this reason, in order to avoid collation conflicts you may want to
> rebuild your database changing the collation to a SQL 2k default one.
> Ciao
> "Henrik Dahl" <q@.q.q.q> wrote in message
> news:OTXB8dbXEHA.2840@.TK2MSFTNGP11.phx.gbl...
>
>|||There are three general ways to solve the issue you are facing:
1. When you join a user db table to a temp table, include "COLLATE
database_default" as a modifier for the join predicate. For example:
SELECT * FROM usrtbl
INNER JOIN #tmptbl ON c1 = c2 COLLATE database_default
2. Change the collation of the existing database to match the
instance-level default collation. The transfer approach you mention is
the currently the easiest way to do this.
3. Ensure that the instance default collation matches the database's
collation. This can be done with rebuildm.exe for most collations, but
unfortunately not for compatibility collations. If you want a SQL 2000
instance to use a compatibility collation for tempdb you must either
perform an unattended install and specify the collation name in the .ISS
(setup answer file), or upgrade an existing SQL 7.0 instance so that the
new SQL 2K instance inherits the 7.0 instance's compatibility collation.
Option #1 is the best long-term solution. Once you make this change,
your code will be insulated from the problem from that point forward and
you won't have to care whether the user database collation matches the
tempdb collation at your customer sites.
HTH,
Bart
--
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no
rights.
From: "Adriano Trevisan" <trevisan.adriano_at_tin.it>
References: <OTXB8dbXEHA.2840@.TK2MSFTNGP11.phx.gbl>
<#oZs23mXEHA.1144@.TK2MSFTNGP10.phx.gbl>
<RnbarpvXEHA.2244@.cpmsftngxa06.phx.gbl>
Subject: Re: Collation name Compatibility_183_406_30003?
Date: Thu, 1 Jul 2004 09:32:37 +0200
Lines: 92
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
Message-ID: <#LaqX2zXEHA.384@.TK2MSFTNGP10.phx.gbl>
Newsgroups: microsoft.public.sqlserver.server
NNTP-Posting-Host: host45-231.pool81114.interbusiness.it 81.114.231.45
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:349499
X-Tomcat-NG: microsoft.public.sqlserver.server
Thanks Bart, we are experiencing this issue on many customers.
After the upgrade to the SQL 2k, any "join" with a temporary table results
in a collation conflict, thus blocking the application.
Currently, I solve the issue by
1) Build a new native SQL2k database with the preferred collation
(normally
I use default one)
2) Script the database structure WITHOUT indexes/fkeys/indexed views and
using SQL7 compatibility (to remove "COLLATE" on columns) and execute them
on the db created previously.
3) DTS import/export from the source SQL7 migrated db to the native SQL2k
one.
4) Script the keys, indexes, views, stored, and so on and apply them to
the
SQL2k native one.
I cannot use Transfer SQL objects because sometimes this leads to errors
related to foreign keys not correctly managed by the DTS itself. However,
this was true on the original version of SQL and on the SP1 and 2. I don't
know if current SP solves the issue.
Could you please check and let me know if another way to solve the
"collation conflict" issue exists without using the steps above?
Thank you very much,
Regards,
Adriano
"Bart Duncan [MSFT]" <bartd@.online.microsoft.com> wrote in message
news:RnbarpvXEHA.2244@.cpmsftngxa06.phx.gbl...
> Adriano is correct that this results from an upgrade of a SQL 7.0
> instance that has non-standard collation settings. What you have is
> called a "compatibility collation", and is described in more detail in
> 270042 INF: Description of SQL Server Compatibility Collations
> (http://support.microsoft.com/?id=270042).
> HTH,
> Bart
> --
> Bart Duncan
> Microsoft SQL Server Support
> Please reply to the newsgroup only - thanks.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> --
> From: "Adriano Trevisan" <trevisan.adriano_at_tin.it>
> References: <OTXB8dbXEHA.2840@.TK2MSFTNGP11.phx.gbl>
> Subject: Re: Collation name Compatibility_183_406_30003?
> Date: Wed, 30 Jun 2004 08:46:21 +0200
> Lines: 24
> X-Priority: 3
> X-MSMail-Priority: Normal
> X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
> Message-ID: <#oZs23mXEHA.1144@.TK2MSFTNGP10.phx.gbl>
> Newsgroups: microsoft.public.sqlserver.server
> NNTP-Posting-Host: host45-231.pool81114.interbusiness.it 81.114.231.45
> Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
> Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:349291
> X-Tomcat-NG: microsoft.public.sqlserver.server
> Probabily you restored a database from the SQL7 format to a SQL 2k.
> Collations are managed differently.
> For this reason, in order to avoid collation conflicts you may want to
> rebuild your database changing the collation to a SQL 2k default one.
> Ciao
> "Henrik Dahl" <q@.q.q.q> wrote in message
> news:OTXB8dbXEHA.2840@.TK2MSFTNGP11.phx.gbl...
for a[vbcol=seagreen]
>
>|||Thank you very much Bart,
Regards,
Adriano
"Bart Duncan [MSFT]" <bartd@.online.microsoft.com> wrote in message
news:eKbtWL8XEHA.1008@.cpmsftngxa06.phx.gbl...
> There are three general ways to solve the issue you are facing:
> 1. When you join a user db table to a temp table, include "COLLATE
> database_default" as a modifier for the join predicate. For example:
> SELECT * FROM usrtbl
> INNER JOIN #tmptbl ON c1 = c2 COLLATE database_default
> 2. Change the collation of the existing database to match the
> instance-level default collation. The transfer approach you mention is
> the currently the easiest way to do this.
> 3. Ensure that the instance default collation matches the database's
> collation. This can be done with rebuildm.exe for most collations, but
> unfortunately not for compatibility collations. If you want a SQL 2000
> instance to use a compatibility collation for tempdb you must either
> perform an unattended install and specify the collation name in the .ISS
> (setup answer file), or upgrade an existing SQL 7.0 instance so that the
> new SQL 2K instance inherits the 7.0 instance's compatibility collation.
> Option #1 is the best long-term solution. Once you make this change,
> your code will be insulated from the problem from that point forward and
> you won't have to care whether the user database collation matches the
> tempdb collation at your customer sites.
> HTH,
> Bart
> --
> Bart Duncan
> Microsoft SQL Server Support
> Please reply to the newsgroup only - thanks.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> --
> From: "Adriano Trevisan" <trevisan.adriano_at_tin.it>
> References: <OTXB8dbXEHA.2840@.TK2MSFTNGP11.phx.gbl>
> <#oZs23mXEHA.1144@.TK2MSFTNGP10.phx.gbl>
> <RnbarpvXEHA.2244@.cpmsftngxa06.phx.gbl>
> Subject: Re: Collation name Compatibility_183_406_30003?
> Date: Thu, 1 Jul 2004 09:32:37 +0200
> Lines: 92
> X-Priority: 3
> X-MSMail-Priority: Normal
> X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
> Message-ID: <#LaqX2zXEHA.384@.TK2MSFTNGP10.phx.gbl>
> Newsgroups: microsoft.public.sqlserver.server
> NNTP-Posting-Host: host45-231.pool81114.interbusiness.it 81.114.231.45
> Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
> Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:349499
> X-Tomcat-NG: microsoft.public.sqlserver.server
> Thanks Bart, we are experiencing this issue on many customers.
> After the upgrade to the SQL 2k, any "join" with a temporary table results
> in a collation conflict, thus blocking the application.
> Currently, I solve the issue by
> 1) Build a new native SQL2k database with the preferred collation
> (normally
> I use default one)
> 2) Script the database structure WITHOUT indexes/fkeys/indexed views and
> using SQL7 compatibility (to remove "COLLATE" on columns) and execute them
> on the db created previously.
> 3) DTS import/export from the source SQL7 migrated db to the native SQL2k
> one.
> 4) Script the keys, indexes, views, stored, and so on and apply them to
> the
> SQL2k native one.
> I cannot use Transfer SQL objects because sometimes this leads to errors
> related to foreign keys not correctly managed by the DTS itself. However,
> this was true on the original version of SQL and on the SP1 and 2. I don't
> know if current SP solves the issue.
> Could you please check and let me know if another way to solve the
> "collation conflict" issue exists without using the steps above?
> Thank you very much,
> Regards,
> Adriano
> "Bart Duncan [MSFT]" <bartd@.online.microsoft.com> wrote in message
> news:RnbarpvXEHA.2244@.cpmsftngxa06.phx.gbl...
> for a
>
>