Collation confusion

I need transactional replication to replicate data between a database with a
collation of SQL_Latin1_General_CP1_CI_AS and another with
Latin1_General_BIN. Can this be done with any explicit conversion?
Reading the BOL and searching the internet yeilded no definitive answer.
Grant,
in the article preoprties (elipsis button) you will find the checkbox that
allows you to use the collation of the published column rather thanthe
default database collation on the subscriber.
HTH,
Paul Ibison
"Grant Marlow" <gmarlow@.yahoo.com> wrote in message
news:5aKXc.5738$ZC7.3697@.newssvr19.news.prodigy.co m...
> I need transactional replication to replicate data between a database with
a
> collation of SQL_Latin1_General_CP1_CI_AS and another with
> Latin1_General_BIN. Can this be done with any explicit conversion?
> Reading the BOL and searching the internet yeilded no definitive answer.
>

Collation

Hi,
What is the difference between SQL_Latin1_General_CP1_CI_AS and
Latin1_General_CI_AS ?
ThanksJaco wrote:
> Hi,
> What is the difference between SQL_Latin1_General_CP1_CI_AS and
> Latin1_General_CI_AS ?
> Thanks
Some info here;
http://www.examnotes.net/article54071.html
David Gugick - SQL Server MVP
Quest Softwaresqlsql

Collation

Hi,
what diff between SQL_Latin1_General_CP1_CI_AS and SQL_Latin1_General_CI_AS?
Why on win2003 install msde, the master database will have
SQL_Latin1_General_CI_AS instead of
SQL_Latin1_General_CP1_CI_AS?
Please advice. Thanks.> Why on win2003 install msde, the master database will have
> SQL_Latin1_General_CI_AS instead of
> SQL_Latin1_General_CP1_CI_AS?
SQL_Latin1_General_CI_AS is not a valid collation. Can you explain where
you are seeing this?
On 8.00.2039 I ran the following script:
SELECT 'foo' COLLATE SQL_Latin1_General_CI_AS
GO
--
Server: Msg 448, Level 16, State 1, Line 1
Invalid collation 'SQL_Latin1_General_CI_AS'.
To explain why databases have different *valid* collations, keep in mind
that you can create a database and specify a specific collation, otherwise
it will get the server default (which you set when you install SQL Server).
CREATE DATABASE foobar1 COLLATE SQL_Latin1_General_CP1_CI_AS
GO
CREATE DATABASE foobar2 COLLATE SQL_Latin1_General_CI_AS
GO
--
The CREATE DATABASE process is allocating 0.63 MB on disk 'foobar1'.
The CREATE DATABASE process is allocating 0.49 MB on disk 'foobar1_log'.
Server: Msg 448, Level 16, State 3, Line 2
Invalid collation 'SQL_Latin1_General_CI_AS'.
My suggestion is to use the server default when possible (which means
leaving the COLLATE keyword off of the CREATE DATABASE statement).|||Sorry, it is: Latin1_General_CI_AS on win2003,
but it will be SQL_Latin1_General_CP1_CI_AS on win2000. Why? I don't specify
any option during the installtion.
Please help.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ubLzwSIuFHA.3864@.TK2MSFTNGP12.phx.gbl...
> SQL_Latin1_General_CI_AS is not a valid collation. Can you explain where
> you are seeing this?
> On 8.00.2039 I ran the following script:
> SELECT 'foo' COLLATE SQL_Latin1_General_CI_AS
> GO
> --
> Server: Msg 448, Level 16, State 1, Line 1
> Invalid collation 'SQL_Latin1_General_CI_AS'.
> To explain why databases have different *valid* collations, keep in mind
> that you can create a database and specify a specific collation, otherwise
> it will get the server default (which you set when you install SQL
> Server).
> CREATE DATABASE foobar1 COLLATE SQL_Latin1_General_CP1_CI_AS
> GO
> CREATE DATABASE foobar2 COLLATE SQL_Latin1_General_CI_AS
> GO
> --
> The CREATE DATABASE process is allocating 0.63 MB on disk 'foobar1'.
> The CREATE DATABASE process is allocating 0.49 MB on disk 'foobar1_log'.
> Server: Msg 448, Level 16, State 3, Line 2
> Invalid collation 'SQL_Latin1_General_CI_AS'.
> My suggestion is to use the server default when possible (which means
> leaving the COLLATE keyword off of the CREATE DATABASE statement).
>|||> but it will be SQL_Latin1_General_CP1_CI_AS on win2000. Why?
I'm not sure, I don't have any Win2000 servers around, only Windows 2003.
It may be the default collation when installing on Windows 2000, or it may
be the product of a SQL Server 7.0 upgrade.
The collations themselves are essentially the same, see the following:
SELECT name,description
FROM ::fn_helpcollations()
WHERE name IN
(
'Latin1_General_CI_AS',
'SQL_Latin1_General_CP1_CI_AS'
)
However, note that the SQL_Latin1 variation is used for backward
compatibility only. Going forward, SQL Server will be moving toward the
less verbose Latin1_ variations. See Go|URL|architec.chm::/8_ar_da_3xbn.htm
in Books Online for more information.

> Please help.
What exactly are trying to solve? If you are having collation conflicts
when merging/retrieving data across a linked server, you can set the
collations to be "compatible" using the following statement on each side:
EXEC master..sp_serveroption
@.server = 'Other_Linked_Server_Name',
@.optname = 'Collation Compatible',
@.optvalue = 'true'
You can use the same technique temporarily if you want to migrate the data
from the existing "badly collated" database to a new database with the right
collation.|||Thanks Aaron,
That's I'm looking for...
"Aaron Bertrand [SQL Server MVP]" wrote in message:
> What exactly are trying to solve? If you are having collation conflicts
> when merging/retrieving data across a linked server, you can set the
> collations to be "compatible" using the following statement on each side:
> EXEC master..sp_serveroption
> @.server = 'Other_Linked_Server_Name',
> @.optname = 'Collation Compatible',
> @.optvalue = 'true'
> You can use the same technique temporarily if you want to migrate the data
> from the existing "badly collated" database to a new database with the
> right collation.
>|||"Aaron Bertrand wrote:
> What exactly are trying to solve? If you are having collation conflicts
> when merging/retrieving data across a linked server, you can set the
> collations to be "compatible" using the following statement on each side:
> EXEC master..sp_serveroption
> @.server = 'Other_Linked_Server_Name',
> @.optname = 'Collation Compatible',
> @.optvalue = 'true'
>
Can I chanage the master, model, tempdb, msdb's collation without
rebuild(through option or property)? Thaks|||> Can I chanage the master, model, tempdb, msdb's collation without
> rebuild(through option or property)?
I don't think so.
If you can, I doubt it's supported.
I would feel much safer recommending detaching your database(s) and
reinstalling SQL Server. You're going to have to migrate the data to the
new collation anyway.|||Thanks Aaron.

collate SQL_Latin1_General_CP850_BIN to SQL_Latin1_General_CP1_CI_AS

Hi, I have a SQL Server for a SAP database with the collation
SQL_Latin1_General_CP850_BIN. When I connect to that server (or, in
this example, to another server with the SQL_Latin1_General_CP850_BIN
collation) and execute a select, the accents seem weird:

select t.TEXT
from [GC-SAP02].P01.p01.AGR_TEXTS t
where t.MANDT = '300'
and t.SPRAS = 'E'
and t.AGR_NAME = 'ZCD_GEST_DEUDA_PATENTE_SD'

TEXT
-----------
Gestin de deudas patentes en SD UP

(1 row(s) affected)

But if I connect to a "normal" SQL Server with a
SQL_Latin1_General_CP1_CI_AS collation, and execute the same select
thru a linked server:

TEXT
-----------
Gestin de deudas patentes en SD UP

(1 row(s) affected)

I need to perform the select connected to the
SQL_Latin1_General_CP850_BIN and get the results as I were connected to
the SQL_Latin1_General_CP1_CI_AS server. I tried with cast and collate,
but I can't get it to work:

select t.TEXT collate SQL_Latin1_General_CP1_CI_AS
from [GC-SAP02].P01.p01.AGR_TEXTS t
where t.MANDT = '300'
and t.SPRAS = 'E'
and t.AGR_NAME = 'ZCD_GEST_DEUDA_PATENTE_SD'

Any ideas? Thanks in advance for your help !!!

Manuel DaponteMADS (mdaponte@.gmail.com) writes:
> Hi, I have a SQL Server for a SAP database with the collation
> SQL_Latin1_General_CP850_BIN. When I connect to that server (or, in
> this example, to another server with the SQL_Latin1_General_CP850_BIN
> collation) and execute a select, the accents seem weird:
> select t.TEXT
> from [GC-SAP02].P01.p01.AGR_TEXTS t
> where t.MANDT = '300'
> and t.SPRAS = 'E'
> and t.AGR_NAME = 'ZCD_GEST_DEUDA_PATENTE_SD'
> TEXT
> -----------
> Gestin de deudas patentes en SD UP
> (1 row(s) affected)
> But if I connect to a "normal" SQL Server with a
> SQL_Latin1_General_CP1_CI_AS collation, and execute the same select
> thru a linked server:
> TEXT
> -----------
> Gestin de deudas patentes en SD UP

It looks as if the problem is that the data in the CP850 database is
wrong. Or that the collation is wrong.

in Latin-1 is 0xF4. In CP850 is at 0xA2. And guess what character
that is at 0xF4 in CP850? Yup, .

So what's happening when you select the data is that it get's converted
to Latin-1. Problem is that is already Latin-1, but labeled incorrectly.

Back in 6.5 days it was pretty easy to turn conversion on and off, but
I think it's more difficult these days. The best bet may be to run
your queries from the command-line tool OSQL. Since OSQL is an command-
line tool, it's character set is CP850, so you will not get any
conversion in this case. The output looks poor in the command-line
window, but if you save to file, the file will look good in an Windows app.

Of course, it would be a good idea to fix that database. But this may
require some care. I would not be surprised if there is a mix of
CP850 and Latin1 data in that database.

In the long run, try to get rid of the CP850 databases.

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

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

Collate

Hi,
Could anyone tell me what does COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL/NULL do in the following code?
CREATE TABLE [TABLE1] (
[ProjectID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL )
Why do I need COLLATE? What does COLLATION mean in table creation?
Thanks a lot!
Mike
Collate sets the order that items are compared with. It determines how an
order by is handled. It also determines whether two items are the same or
different. The collation you have there states that "APPLE" = "Apple" A
different collation will make these different.
This is one of the annoying quirks of the tool to create SQL. Since
individual columns can have their own collation, the tool exports all of the
collations even if the collation is the default for the database.
Unless you have a good reason to use a specific collation, I would delete
the whole collate clause.
Russel Loski, MCSD.Net
"Michael" wrote:

> Hi,
> Could anyone tell me what does COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL/NULL do in the following code?
> CREATE TABLE [TABLE1] (
> [ProjectID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL )
> Why do I need COLLATE? What does COLLATION mean in table creation?
> Thanks a lot!
> Mike
>
|||Thanks a lot!
RLoski wrote:[vbcol=seagreen]
> Collate sets the order that items are compared with. It determines how an
> order by is handled. It also determines whether two items are the same or
> different. The collation you have there states that "APPLE" = "Apple" A
> different collation will make these different.
> This is one of the annoying quirks of the tool to create SQL. Since
> individual columns can have their own collation, the tool exports all of the
> collations even if the collation is the default for the database.
> Unless you have a good reason to use a specific collation, I would delete
> the whole collate clause.
> --
> Russel Loski, MCSD.Net
>
> "Michael" wrote:
|||RLoski,
While generating your script, use unicode option. Then it will not generate
the collate... statements. so you don't need to go and delete them manually.
Venkat
"RLoski" wrote:
[vbcol=seagreen]
> Collate sets the order that items are compared with. It determines how an
> order by is handled. It also determines whether two items are the same or
> different. The collation you have there states that "APPLE" = "Apple" A
> different collation will make these different.
> This is one of the annoying quirks of the tool to create SQL. Since
> individual columns can have their own collation, the tool exports all of the
> collations even if the collation is the default for the database.
> Unless you have a good reason to use a specific collation, I would delete
> the whole collate clause.
> --
> Russel Loski, MCSD.Net
>
> "Michael" wrote:

Collate

Hi,
Could anyone tell me what does COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL/NULL do in the following code?
CREATE TABLE [TABLE1] (
[ProjectID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL )
Why do I need COLLATE? What does COLLATION mean in table creation?
Thanks a lot!
MikeCollate sets the order that items are compared with. It determines how an
order by is handled. It also determines whether two items are the same or
different. The collation you have there states that "APPLE" = "Apple" A
different collation will make these different.
This is one of the annoying quirks of the tool to create SQL. Since
individual columns can have their own collation, the tool exports all of the
collations even if the collation is the default for the database.
Unless you have a good reason to use a specific collation, I would delete
the whole collate clause.
Russel Loski, MCSD.Net
"Michael" wrote:

> Hi,
> Could anyone tell me what does COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL/NULL do in the following code?
> CREATE TABLE [TABLE1] (
> [ProjectID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT
> NULL )
> Why do I need COLLATE? What does COLLATION mean in table creation?
> Thanks a lot!
> Mike
>|||Thanks a lot!
RLoski wrote:[vbcol=seagreen]
> Collate sets the order that items are compared with. It determines how an
> order by is handled. It also determines whether two items are the same or
> different. The collation you have there states that "APPLE" = "Apple" A
> different collation will make these different.
> This is one of the annoying quirks of the tool to create SQL. Since
> individual columns can have their own collation, the tool exports all of t
he
> collations even if the collation is the default for the database.
> Unless you have a good reason to use a specific collation, I would delete
> the whole collate clause.
> --
> Russel Loski, MCSD.Net
>
> "Michael" wrote:
>|||RLoski,
While generating your script, use unicode option. Then it will not generate
the collate... statements. so you don't need to go and delete them manually.
Venkat
"RLoski" wrote:
[vbcol=seagreen]
> Collate sets the order that items are compared with. It determines how an
> order by is handled. It also determines whether two items are the same or
> different. The collation you have there states that "APPLE" = "Apple" A
> different collation will make these different.
> This is one of the annoying quirks of the tool to create SQL. Since
> individual columns can have their own collation, the tool exports all of t
he
> collations even if the collation is the default for the database.
> Unless you have a good reason to use a specific collation, I would delete
> the whole collate clause.
> --
> Russel Loski, MCSD.Net
>
> "Michael" wrote:
>

Collate

Hi,
Could anyone tell me what does COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL/NULL do in the following code?
CREATE TABLE [TABLE1] (
[ProjectID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL )
Why do I need COLLATE? What does COLLATION mean in table creation?
Thanks a lot!
MikeThanks a lot!
RLoski wrote:
> Collate sets the order that items are compared with. It determines how an
> order by is handled. It also determines whether two items are the same or
> different. The collation you have there states that "APPLE" = "Apple" A
> different collation will make these different.
> This is one of the annoying quirks of the tool to create SQL. Since
> individual columns can have their own collation, the tool exports all of the
> collations even if the collation is the default for the database.
> Unless you have a good reason to use a specific collation, I would delete
> the whole collate clause.
> --
> Russel Loski, MCSD.Net
>
> "Michael" wrote:
> > Hi,
> >
> > Could anyone tell me what does COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> > NULL/NULL do in the following code?
> >
> > CREATE TABLE [TABLE1] (
> > [ProjectID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> > NULL )
> >
> > Why do I need COLLATE? What does COLLATION mean in table creation?
> >
> > Thanks a lot!
> > Mike
> >
> >|||RLoski,
While generating your script, use unicode option. Then it will not generate
the collate... statements. so you don't need to go and delete them manually.
Venkat
"RLoski" wrote:
> Collate sets the order that items are compared with. It determines how an
> order by is handled. It also determines whether two items are the same or
> different. The collation you have there states that "APPLE" = "Apple" A
> different collation will make these different.
> This is one of the annoying quirks of the tool to create SQL. Since
> individual columns can have their own collation, the tool exports all of the
> collations even if the collation is the default for the database.
> Unless you have a good reason to use a specific collation, I would delete
> the whole collate clause.
> --
> Russel Loski, MCSD.Net
>
> "Michael" wrote:
> > Hi,
> >
> > Could anyone tell me what does COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> > NULL/NULL do in the following code?
> >
> > CREATE TABLE [TABLE1] (
> > [ProjectID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> > NULL )
> >
> > Why do I need COLLATE? What does COLLATION mean in table creation?
> >
> > Thanks a lot!
> > Mike
> >
> >