Different Collation designator Settings

Greetings,
In our company we have different kinds of SQL databases and SQL servers. We
are trying to consolidate those servers under a couple of powerful servers.
But when we began to tell this project to software houses and ask their
idea; we met a big problem .
Some of those companies are using SQL databases with Collation Designator =
Latin1_General setting. But the majority is using with Collation Designator
= Turkish setting. We want to consolidate them under a big MS SQL Server
2000. Anybody that we asked said that is impossible to consolidate those
databases under a server.
So my questions are ;
1- Is is really impossible to consolidate SQL dbases with different
character sets under a single server ? (Latin1_General + Turkish )
2- If no, how should I setup this MS SQL Server to support those char.sets ?
I will be very happy if anybody can help me .
Best regards.
Shadowfax
Hi Shadow,
You can have databases (and even columns) with different collations on SQL
Server 2000. That wasn't possible on SQL Server 7.
Whether it is going to work with 3rd party application is uncertain though.
The problem is that temporary tables are created with the collation of
tempdb on your server. And unless the 3rd party applications are good
quality code (which unfortunately is quite unlikely), there is a good chance
that they will compare character values in permanent tables with character
values in temporary tables while assuming that tempdb has the same collation
as the database that application uses. And if that is not the case, you will
get collation conflicts.
This can be avoided by specifying COLLATE DATABASE_DEFAULT with each
character column when creating temporary tables so that the default
collation of the database the user is currently connected to is used. But as
I said earlier, applications very rarely get up to this level of code
quality.
You would probably best off to have 2 servers, one with Latin1_General
collation and one with Turkish collation.
Jacco Schalkwijk
SQL Server MVP
"Shadow" <shadowfaxx001@.hotmail.com> wrote in message
news:ePhKpPlAFHA.2112@.TK2MSFTNGP14.phx.gbl...
> Greetings,
> In our company we have different kinds of SQL databases and SQL servers.
> We are trying to consolidate those servers under a couple of powerful
> servers. But when we began to tell this project to software houses and ask
> their idea; we met a big problem .
> Some of those companies are using SQL databases with Collation Designator
> = Latin1_General setting. But the majority is using with Collation
> Designator = Turkish setting. We want to consolidate them under a big MS
> SQL Server 2000. Anybody that we asked said that is impossible to
> consolidate those databases under a server.
> So my questions are ;
> 1- Is is really impossible to consolidate SQL dbases with different
> character sets under a single server ? (Latin1_General + Turkish )
> 2- If no, how should I setup this MS SQL Server to support those char.sets
> ?
> I will be very happy if anybody can help me .
> Best regards.
> Shadowfax
>
>
|||Thanks for your help Jacco.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:%23EJi6DmAFHA.2428@.TK2MSFTNGP14.phx.gbl...
> Hi Shadow,
> You can have databases (and even columns) with different collations on SQL
> Server 2000. That wasn't possible on SQL Server 7.
>
|||You could also consider having two instances of SQL (one w/Latin1_General,
the other with Turkish collation) on the same server.
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: "Shadow" <shadowfaxx001@.hotmail.com>
| References: <ePhKpPlAFHA.2112@.TK2MSFTNGP14.phx.gbl>
<#EJi6DmAFHA.2428@.TK2MSFTNGP14.phx.gbl>
| Subject: Re: Different Collation designator Settings
| Date: Tue, 25 Jan 2005 07:09:04 +0200
| Lines: 11
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| X-RFC2646: Format=Flowed; Response
| Message-ID: <#Slv$vpAFHA.1392@.tk2msftngp13.phx.gbl>
| Newsgroups:
microsoft.public.sqlserver.connect,microsoft.publi c.sqlserver.odbc,microsoft
.public.sqlserver.server,microsoft.public.sqlserve r.setup
| NNTP-Posting-Host: dsl85-97-7802.ttnet.net.tr 85.97.30.122
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFT NGP08.phx.gbl!tk2msftngp13
.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.odbc:43188
microsoft.public.sqlserver.server:375538
microsoft.public.sqlserver.setup:68919
microsoft.public.sqlserver.connect:44240
| X-Tomcat-NG: microsoft.public.sqlserver.odbc
|
| Thanks for your help Jacco.
|
| "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid >
wrote
| in message news:%23EJi6DmAFHA.2428@.TK2MSFTNGP14.phx.gbl...
| > Hi Shadow,
| >
| > You can have databases (and even columns) with different collations on
SQL
| > Server 2000. That wasn't possible on SQL Server 7.
| >
|
|
|