Collation error

hi,

Our SQL servers have been setup to collate American. However, our pc's are setup for South Africa. This is creating a nightmare for us as we use Great Plains, which is an American product with American date format.

We are having difficulties in doing any development due to this issue.

Can someone please advise me as to whether there is another way around this ?

ThanksI hear you with this problem as i have experienced the exact same thing.

The only way that i know to cope with it is to use the collate keyword which 'casts' the tables to a common collation so the join makes sense.

Collations specify the way values are compared (eg Case sensitive, sort order), so be careful when choosing the one to cast to.
UPDATE tblPrepTemplate
SET Reason = 'Do not contact'
FROM tblPrepTemplate A
inner join [Do Not Contact].DoNotContact.dbo.TblDoNotContacts B
ON a.TeleW = b.ContactNumber
collate Latin1_General_CS_AS
WHERE Reason Is Null

It is possible to change the collation of a table as well, for example


CREATE TABLE MyTable (PrimaryKey int PRIMARY KEY, CharCol varchar(10) COLLATE French_CI_AS NOT NULL )
GO

ALTER TABLE MyTable ALTER COLUMN CharCol varchar(10)COLLATE Latin1_General_CI_AS NOT NULL


I don't think that Collations do not affect the date format

Collation confusion (may be not)

Hello to everyone
I am litlle bit confused with the following sitatuion
OS (Win2000 SERVER) has COLLATE HEBREW_CI_AS (Let me call it A)
I have database (Latin1-General, case-insensitive, accent-sensitive,
kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort
Order 138 on Code Page 1255 for non-Unicode Data) and employee table.
In the table we have Lastname column declared as VARCHAR(50)
Now I'm sitting on another Server with OS (Win2000 SERVER) COLLATE
LATIN-CENERAL...
I registered the 'A' SQL Server and with QA I performed SELECT lastname
From Table ,works fine (I see all lastname's in hebrew) after that I did
exactly the same statement but by EM and instead of lastname's I have seen
question's mark.
What is going on? Can some shed light on the problem
Note: I know that I can declare NVARCHAR(20) and change COLLATION and it
will be worked but my question is
Are there different ways SQL Server perfoming query by QA and EM.? Why I see
my lastname column correctly when I perfom the query with QA and questions
mark by perfomning the same query with EM
(Note: I am sitting on another server and registered to server 'A' with EM)
Thank you.The "show all rows" function in Enterprise Manager uses non-Unicode
controls. This means that Enterprise Manager can't be used to view data
that is not defined in that client machine's ANSI code page (see
HKLM\SYSTEM\CurrentControlSet\Control\Nls\CodePage, value "ACP" to find out
what this is on a given SQL client machine, but don't change the code page
directly from this reg key).
In other words, don't expect that you can view arbitrary Unicode data from
that feature in Enterprise Manager. Query Analyzer, as a fully
Unicode-enabled app, can be used to display or store any Unicode data.
HTH,
Bart
--
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
From: "Uri Dimant" <urid@.iscar.co.il>
Subject: Collation confusion (may be not)
Date: Tue, 11 Nov 2003 10:14:25 +0200
Lines: 28
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
Message-ID: <#Ck$qvCqDHA.372@.TK2MSFTNGP11.phx.gbl>
Newsgroups: microsoft.public.sqlserver.server
NNTP-Posting-Host: bzq-25-106-78.cust.bezeqint.net 212.25.106.78
Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGXA06.phx.gbl!TK2MSFTNGXA05.phx.gbl!TK2MSFTNGP0
8.phx.gbl!TK2MSFTNGP11.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:315781
X-Tomcat-NG: microsoft.public.sqlserver.server
Hello to everyone
I am litlle bit confused with the following sitatuion
OS (Win2000 SERVER) has COLLATE HEBREW_CI_AS (Let me call it A)
I have database (Latin1-General, case-insensitive, accent-sensitive,
kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort
Order 138 on Code Page 1255 for non-Unicode Data) and employee table.
In the table we have Lastname column declared as VARCHAR(50)
Now I'm sitting on another Server with OS (Win2000 SERVER) COLLATE
LATIN-CENERAL...
I registered the 'A' SQL Server and with QA I performed SELECT lastname
From Table ,works fine (I see all lastname's in hebrew) after that I did
exactly the same statement but by EM and instead of lastname's I have seen
question's mark.
What is going on? Can some shed light on the problem
Note: I know that I can declare NVARCHAR(20) and change COLLATION and it
will be worked but my question is
Are there different ways SQL Server perfoming query by QA and EM.? Why I see
my lastname column correctly when I perfom the query with QA and questions
mark by perfomning the same query with EM
(Note: I am sitting on another server and registered to server 'A' with EM)
Thank you.

Collation conflict

I have 2 servers, both of them have the same collation.
Then I create temp table on 1st server:
create table #tmpNovi (country char(3) COLLATE database_default ,datum
datetime)
Then fill the table.
Then I join this table to second server:
select s.* FROM
[SERVER2].[DW_Temp].[dbo].[t_stanje_cube] s INNER JOIN #tmpNovi n
ON s.RCO=n.country
and I get an error message:
Cannot resolve collation conflict for equal to operation.
Why? Both servers have the same collation, also temp table country field has
defined COLLATE database_default and still an error?
Thank you,
SimonRun below statement on both servers and post back the results:
SELECT DATABASEPROPERTYEX('pubs', 'Collation')
Substitute pubs with DW_Temp when you execute it on SERVER2 and with the dat
abase name from where
you create the temp table on the other server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"simon" <simon.zupan@.stud-moderna.si> wrote in message
news:%23P5qSQeNFHA.2520@.tk2msftngp13.phx.gbl...
>I have 2 servers, both of them have the same collation.
> Then I create temp table on 1st server:
> create table #tmpNovi (country char(3) COLLATE database_default ,datum dat
etime)
> Then fill the table.
> Then I join this table to second server:
> select s.* FROM
> [SERVER2].[DW_Temp].[dbo].[t_stanje_cube] s INNER JOIN #tmpNovi n
> ON s.RCO=n.country
> and I get an error message:
> Cannot resolve collation conflict for equal to operation.
> Why? Both servers have the same collation, also temp table country field h
as defined COLLATE
> database_default and still an error?
> Thank you,
> Simon
>|||Dear all,
As far as I know it's very simply: just for that is needed that both level o
f
COLLATION coinciding, i.e, level table, level database.
It's strange.
See you,
"simon" wrote:

> I have 2 servers, both of them have the same collation.
> Then I create temp table on 1st server:
> create table #tmpNovi (country char(3) COLLATE database_default ,datum
> datetime)
> Then fill the table.
> Then I join this table to second server:
> select s.* FROM
> [SERVER2].[DW_Temp].[dbo].[t_stanje_cube] s INNER JOIN #tmpNovi n
> ON s.RCO=n.country
> and I get an error message:
> Cannot resolve collation conflict for equal to operation.
> Why? Both servers have the same collation, also temp table country field h
as
> defined COLLATE database_default and still an error?
> Thank you,
> Simon
>
>|||Hi Tibor,
Great with DATABASEPROPERTYEX.I was wondering if it is possible to have
available a similar sentence but at table level?
thanx
"Tibor Karaszi" wrote:

> Run below statement on both servers and post back the results:
> SELECT DATABASEPROPERTYEX('pubs', 'Collation')
> Substitute pubs with DW_Temp when you execute it on SERVER2 and with the d
atabase name from where
> you create the temp table on the other server.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "simon" <simon.zupan@.stud-moderna.si> wrote in message
> news:%23P5qSQeNFHA.2520@.tk2msftngp13.phx.gbl...
>
>|||Try sp_help.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:3B49DD33-1160-4069-A161-7A5313738DBC@.microsoft.com...
> Hi Tibor,
> Great with DATABASEPROPERTYEX.I was wondering if it is possible to have
> available a similar sentence but at table level?
> thanx
> "Tibor Karaszi" wrote:
>

Collates - CP437 - ADO - Visual Basic

Hello, I am workink with a Database that has the Collate SQL_Latin1_General_CP437_CI_AS (Instance and Database-SQL2000), this convert the Database in a OEM Server.
I Can save Characters From 0 to 256 ASCII code CP 437 very well on the database (I import this Data with an DTS) but when i retrive the information with Visual Basic 6.0 with ADO some character > 127 are changed.
Did you know how I can fix It ?
I know that the problem is that Windows is ANSI and my SQL Server OEM, but .. a dontt know how yo fix it.
Thanks I sorry because my poor english. :(

Walter
ArgentinaThe easy answer is to do the transfer using Unicode (16 bit characters). This allows any character that Windows recognizes to pass through without problem.

If you've already got the file that was produced from the table using the 437 collation and want to import it into a server with a different collation, then things get a bit tricky, but they can still be done.

-PatP|||The problem is that I must work with the Hexadecimal code of the character, because I need to send this code (Hexa of ASCII) to a Printer (Special Printer).
All the program is around this printer and I need manipulate ASCII codes.
Thanks you :D

Walter|||Take the data from the table using the 437 collation in a Unicode (16 bit) text file. This format allows the easy transportation of any character that Windows supports, regardless of the character's collation or character set.

The Unicode format will preserve the exact character, although sometimes moving a character from one 8 bit character set to another will cause the hex value of the character to change even though it preserves the glyph (the printed form) of the character exactly. You need to decide if it is more important to preserve the hex value or the glyph for your purposes.

-PatP|||You can replace DTS transformation task with Win32 task and call a batch that contains BCP. In the BCP call you can include -C <code_page>.

And Pat, it's not collation, it's a code page, and with BCP there are no "tricks" ;)|||A code page is definitely one way to implement part of a collation. I see a collation as a lot more complex and flexible than a code page.

I find it interesting that you suggested one of what I consider the tricks for using BCP (specifying a code page) and noted that you feel that BCP has no tricks in the same posting. I have a hard time understanding that apparent contradiction.

-PatP|||How can it be a contradiction if this is part of the syntax?

I see a collation as a lot more complex and flexible than a code page.One has nothing to do with the other, so do tell, - how do you see them? In blue?|||Gracias a Todos, pude resolver el problema.
En el string de conexion con el servidor SQL Server 2000 hay un parametro que se puede indicar para que al momento de recuperar los caracteres, el servidor no los modifique. Este parametro se llama Auto Translate.
Al poner Auto Translate = No los valores Hexadecimales de los caracteres que se guardan en la base permanecen intactos. Probe de Generar una Tabla con 2 columnas, la primera tiene un valor entero de 0 a 255 y la segunda es un caracter en el cual puse un codigo ASCII de 0 a 255, al tomarlo desde Visual Basic muestro el valor entero de 0 a 255, el caracter y luego con una funcion de visual muestro el valor Hexadecimal del caracter y vi muy felizmente que en los 255 casos permanecia igual.

>You can replace DTS transformation task with Win32 task and call a batch that contains BCP. In the BCP call you can include -C <code_page>.

Con respecto al DST, yo no tengo problemas con el DTS ya que al importar un archivo de texto, el mismo se importa exactamente con los cdigos exadecimales que este tiene.

>You need to decide if it is more important to preserve the hex value or the glyph for your purposes.

Para mi es mucho mas importante preservar el codigo Hexadecimal porque este codigo es un caracter de control para la impresora, independientemente de como se vea visualmente en pantalla, la impresora interpreta el codigo Hexadecimal.

Now, I will try to translate all the text but.. its very hard.
Thanks to all of you, I can fix the problem.
There is a parameter In the String of conexion with the SQL Server 2000 that indicate that don`t change the Hexadecimal code when you retrieve the characters from the server. This parameters is call "Auto Translate"

I don`t have problem with the DTS. The DTS imports Ok the characters from the ASCII file.

For me, it`s more important the Hex value that the glymp value because the printer interpretes the Hex value, not the Glyp.

Sorry because my poor english, I cant translate all exactly but i think that the idea is the same.
Thanks !!!

Walter|||Based on how you've described your needs, my first choice would be to use BCP, using the -c trick that rdjabarov suggested earlier for both export and import. The -c parameter allows you to control exactly which Windows code page is used for the file, and as long as the code page/collation mapping operation supports the charcters you need going both ways, you should be fine.

My second choice would be to extract the data using a view, converting the column to VARBINARY to preserve the hex values exactly. This usually means that you need to import the output of that view into a scratch table, then convert the hexadecimal value back to a character value inside of SQL Server.

If you can extract the data directly from one server to another, using a linked server, that would be my next suggestion.

-PatP

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 Question

Greetings to all. It would thank for much to them if they
could help me with a problem, that is the following one: I
have a data base in a server x with a table with a field
that has the COLLATE in Hungarian_CS_AI, and at the time
of concatenating this field with a field that has the
COLLATE of the SQL_Latin1_General_CP1_CI_AI type and when
executing this marks an error to me and it solved it
adding at the end of the sentence that the COLLATE of a
single type, but that is not what I look for but what I
would like that somebody explains to me in case a type of
COLLATE exists that can support to all the types like a
UNICODE or something similar. It is for this reason that I
need to know if it is possible to do that single collate
accepts all type of characters. Thanks.
Have you tried to bring the data with the different collation types into another table that has only one collation. Do you need the Hungarian collation still? If so I am assuming that the Hungarian collation (due to the need for more special characters) w
ould allow for all Latin1 permissible characters and therefore try to port your Latin collation data to Hungarian collation.
|||So why not use Unicode?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Miramontes" <anonymous@.discussions.microsoft.com> wrote in message
news:1afb001c44f3f$e3dad580$a401280a@.phx.gbl...
> Greetings to all. It would thank for much to them if they
> could help me with a problem, that is the following one: I
> have a data base in a server x with a table with a field
> that has the COLLATE in Hungarian_CS_AI, and at the time
> of concatenating this field with a field that has the
> COLLATE of the SQL_Latin1_General_CP1_CI_AI type and when
> executing this marks an error to me and it solved it
> adding at the end of the sentence that the COLLATE of a
> single type, but that is not what I look for but what I
> would like that somebody explains to me in case a type of
> COLLATE exists that can support to all the types like a
> UNICODE or something similar. It is for this reason that I
> need to know if it is possible to do that single collate
> accepts all type of characters. Thanks.
|||Do you now the name for this type of collate for
unicode ?, because i need that unicode.
Thanks in advanced.

>--Original Message--
>So why not use Unicode?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Miramontes" <anonymous@.discussions.microsoft.com> wrote
in message[vbcol=seagreen]
>news:1afb001c44f3f$e3dad580$a401280a@.phx.gbl...
they[vbcol=seagreen]
one: I[vbcol=seagreen]
when[vbcol=seagreen]
of[vbcol=seagreen]
that I
>
>.
>
|||Unicode is the datatype (actually nvarchar, nchar and ntext). You then pick a collation to go with it. Seems
like Hungarian_CS_AI should be the one to use, as the other is "general", hence shouldn't care how the
"extended" characters are sorted etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Miramontes" <anonymous@.discussions.microsoft.com> wrote in message
news:1b69901c44fba$dffe8fd0$a001280a@.phx.gbl...[vbcol=seagreen]
> Do you now the name for this type of collate for
> unicode ?, because i need that unicode.
> Thanks in advanced.
> in message
> they
> one: I
> when
> of
> that I

COLLATE Question

Greetings to all. It would thank for much to them if they
could help me with a problem, that is the following one: I
have a data base in a server x with a table with a field
that has the COLLATE in Hungarian_CS_AI, and at the time
of concatenating this field with a field that has the
COLLATE of the SQL_Latin1_General_CP1_CI_AI type and when
executing this marks an error to me and it solved it
adding at the end of the sentence that the COLLATE of a
single type, but that is not what I look for but what I
would like that somebody explains to me in case a type of
COLLATE exists that can support to all the types like a
UNICODE or something similar. It is for this reason that I
need to know if it is possible to do that single collate
accepts all type of characters. Thanks.Have you tried to bring the data with the different collation types into ano
ther table that has only one collation. Do you need the Hungarian collation
still? If so I am assuming that the Hungarian collation (due to the need for
more special characters) w
ould allow for all Latin1 permissible characters and therefore try to port y
our Latin collation data to Hungarian collation.|||So why not use Unicode?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Miramontes" <anonymous@.discussions.microsoft.com> wrote in message
news:1afb001c44f3f$e3dad580$a401280a@.phx
.gbl...
> Greetings to all. It would thank for much to them if they
> could help me with a problem, that is the following one: I
> have a data base in a server x with a table with a field
> that has the COLLATE in Hungarian_CS_AI, and at the time
> of concatenating this field with a field that has the
> COLLATE of the SQL_Latin1_General_CP1_CI_AI type and when
> executing this marks an error to me and it solved it
> adding at the end of the sentence that the COLLATE of a
> single type, but that is not what I look for but what I
> would like that somebody explains to me in case a type of
> COLLATE exists that can support to all the types like a
> UNICODE or something similar. It is for this reason that I
> need to know if it is possible to do that single collate
> accepts all type of characters. Thanks.|||Do you now the name for this type of collate for
unicode ?, because i need that unicode.
Thanks in advanced.

>--Original Message--
>So why not use Unicode?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Miramontes" <anonymous@.discussions.microsoft.com> wrote
in message
> news:1afb001c44f3f$e3dad580$a401280a@.phx
.gbl...
they[vbcol=seagreen]
one: I[vbcol=seagreen]
when[vbcol=seagreen]
of[vbcol=seagreen]
that I[vbcol=seagreen]
>
>.
>|||Unicode is the datatype (actually nvarchar, nchar and ntext). You then pick
a collation to go with it. Seems
like Hungarian_CS_AI should be the one to use, as the other is "general", he
nce shouldn't care how the
"extended" characters are sorted etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Miramontes" <anonymous@.discussions.microsoft.com> wrote in message
news:1b69901c44fba$dffe8fd0$a001280a@.phx
.gbl...[vbcol=seagreen]
> Do you now the name for this type of collate for
> unicode ?, because i need that unicode.
> Thanks in advanced.
>
> in message
> they
> one: I
> when
> of
> that I

COLLATE Question

Greetings to all. It would thank for much to them if they
could help me with a problem, that is the following one: I
have a data base in a server x with a table with a field
that has the COLLATE in Hungarian_CS_AI, and at the time
of concatenating this field with a field that has the
COLLATE of the SQL_Latin1_General_CP1_CI_AI type and when
executing this marks an error to me and it solved it
adding at the end of the sentence that the COLLATE of a
single type, but that is not what I look for but what I
would like that somebody explains to me in case a type of
COLLATE exists that can support to all the types like a
UNICODE or something similar. It is for this reason that I
need to know if it is possible to do that single collate
accepts all type of characters. Thanks.Have you tried to bring the data with the different collation types into another table that has only one collation. Do you need the Hungarian collation still? If so I am assuming that the Hungarian collation (due to the need for more special characters) would allow for all Latin1 permissible characters and therefore try to port your Latin collation data to Hungarian collation.|||So why not use Unicode?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Miramontes" <anonymous@.discussions.microsoft.com> wrote in message
news:1afb001c44f3f$e3dad580$a401280a@.phx.gbl...
> Greetings to all. It would thank for much to them if they
> could help me with a problem, that is the following one: I
> have a data base in a server x with a table with a field
> that has the COLLATE in Hungarian_CS_AI, and at the time
> of concatenating this field with a field that has the
> COLLATE of the SQL_Latin1_General_CP1_CI_AI type and when
> executing this marks an error to me and it solved it
> adding at the end of the sentence that the COLLATE of a
> single type, but that is not what I look for but what I
> would like that somebody explains to me in case a type of
> COLLATE exists that can support to all the types like a
> UNICODE or something similar. It is for this reason that I
> need to know if it is possible to do that single collate
> accepts all type of characters. Thanks.|||Do you now the name for this type of collate for
unicode ?, because i need that unicode.
Thanks in advanced.
>--Original Message--
>So why not use Unicode?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Miramontes" <anonymous@.discussions.microsoft.com> wrote
in message
>news:1afb001c44f3f$e3dad580$a401280a@.phx.gbl...
>> Greetings to all. It would thank for much to them if
they
>> could help me with a problem, that is the following
one: I
>> have a data base in a server x with a table with a field
>> that has the COLLATE in Hungarian_CS_AI, and at the time
>> of concatenating this field with a field that has the
>> COLLATE of the SQL_Latin1_General_CP1_CI_AI type and
when
>> executing this marks an error to me and it solved it
>> adding at the end of the sentence that the COLLATE of a
>> single type, but that is not what I look for but what I
>> would like that somebody explains to me in case a type
of
>> COLLATE exists that can support to all the types like a
>> UNICODE or something similar. It is for this reason
that I
>> need to know if it is possible to do that single collate
>> accepts all type of characters. Thanks.
>
>.
>|||Unicode is the datatype (actually nvarchar, nchar and ntext). You then pick a collation to go with it. Seems
like Hungarian_CS_AI should be the one to use, as the other is "general", hence shouldn't care how the
"extended" characters are sorted etc.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Miramontes" <anonymous@.discussions.microsoft.com> wrote in message
news:1b69901c44fba$dffe8fd0$a001280a@.phx.gbl...
> Do you now the name for this type of collate for
> unicode ?, because i need that unicode.
> Thanks in advanced.
> >--Original Message--
> >So why not use Unicode?
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://www.solidqualitylearning.com/
> >
> >
> >"Miramontes" <anonymous@.discussions.microsoft.com> wrote
> in message
> >news:1afb001c44f3f$e3dad580$a401280a@.phx.gbl...
> >> Greetings to all. It would thank for much to them if
> they
> >> could help me with a problem, that is the following
> one: I
> >> have a data base in a server x with a table with a field
> >> that has the COLLATE in Hungarian_CS_AI, and at the time
> >> of concatenating this field with a field that has the
> >> COLLATE of the SQL_Latin1_General_CP1_CI_AI type and
> when
> >> executing this marks an error to me and it solved it
> >> adding at the end of the sentence that the COLLATE of a
> >> single type, but that is not what I look for but what I
> >> would like that somebody explains to me in case a type
> of
> >> COLLATE exists that can support to all the types like a
> >> UNICODE or something similar. It is for this reason
> that I
> >> need to know if it is possible to do that single collate
> >> accepts all type of characters. Thanks.
> >
> >
> >.
> >

Collate problems

Hi group I have a problem with collations.
Traditional_Spanish_CI_AS And Modern_Spanish_CI_AS
Are de same? Because The server has Modern_Spanish_CI_AS And the database
has Traditional_Spanish_CI_AS And somethimes in a stored procedure get an
error but I dont identify where is taht error, The error is "Error near 'S'
" and I supouse that is related with Collate.
Thanks for your helpJuan,
I don't believe "Error near 'S'" is directly a collation error. (A collation
error will not tell you what character it is having trouble with.)
It looks like the type of error message that SQL Server produces on a coding
error. Does your stored procedure create any dynamic SQL? If so, the
problem may be that in some circumstances a piece of code is being created
that is syntactically incorrect. An example can be found in this KB:
http://support.microsoft.com/kb/195979
Now, since this is related to quoted identifiers, I suppose that it is
possible that a collation mismatch inside dynamic-SQL maybe could cause a
problem, but I could not prove it without a good test case.
RLF
"Juan Manuel Alegría B." <jmalegria75@.hotmail.com> wrote in message
news:OCKGP$fLIHA.2064@.TK2MSFTNGP06.phx.gbl...
> Hi group I have a problem with collations.
> Traditional_Spanish_CI_AS And Modern_Spanish_CI_AS
> Are de same? Because The server has Modern_Spanish_CI_AS And the database
> has Traditional_Spanish_CI_AS And somethimes in a stored procedure get an
> error but I dont identify where is taht error, The error is "Error near
> 'S' " and I supouse that is related with Collate.
> Thanks for your help
>

Collate problems

Hi group I have a problem with collations.
Traditional_Spanish_CI_AS And Modern_Spanish_CI_AS
Are de same? Because The server has Modern_Spanish_CI_AS And the database
has Traditional_Spanish_CI_AS And somethimes in a stored procedure get an
error but I dont identify where is taht error, The error is "Error near 'S'
" and I supouse that is related with Collate.
Thanks for your help
Juan,
I don't believe "Error near 'S'" is directly a collation error. (A collation
error will not tell you what character it is having trouble with.)
It looks like the type of error message that SQL Server produces on a coding
error. Does your stored procedure create any dynamic SQL? If so, the
problem may be that in some circumstances a piece of code is being created
that is syntactically incorrect. An example can be found in this KB:
http://support.microsoft.com/kb/195979
Now, since this is related to quoted identifiers, I suppose that it is
possible that a collation mismatch inside dynamic-SQL maybe could cause a
problem, but I could not prove it without a good test case.
RLF
"Juan Manuel Alegra B." <jmalegria75@.hotmail.com> wrote in message
news:OCKGP$fLIHA.2064@.TK2MSFTNGP06.phx.gbl...
> Hi group I have a problem with collations.
> Traditional_Spanish_CI_AS And Modern_Spanish_CI_AS
> Are de same? Because The server has Modern_Spanish_CI_AS And the database
> has Traditional_Spanish_CI_AS And somethimes in a stored procedure get an
> error but I dont identify where is taht error, The error is "Error near
> 'S' " and I supouse that is related with Collate.
> Thanks for your help
>

Collate problems

Hi group I have a problem with collations.
Traditional_Spanish_CI_AS And Modern_Spanish_CI_AS
Are de same? Because The server has Modern_Spanish_CI_AS And the database
has Traditional_Spanish_CI_AS And somethimes in a stored procedure get an
error but I dont identify where is taht error, The error is "Error near 'S'
" and I supouse that is related with Collate.
Thanks for your helpJuan,
I don't believe "Error near 'S'" is directly a collation error. (A collation
error will not tell you what character it is having trouble with.)
It looks like the type of error message that SQL Server produces on a coding
error. Does your stored procedure create any dynamic SQL? If so, the
problem may be that in some circumstances a piece of code is being created
that is syntactically incorrect. An example can be found in this KB:
http://support.microsoft.com/kb/195979
Now, since this is related to quoted identifiers, I suppose that it is
possible that a collation mismatch inside dynamic-SQL maybe could cause a
problem, but I could not prove it without a good test case.
RLF
"Juan Manuel Alegra B." <jmalegria75@.hotmail.com> wrote in message
news:OCKGP$fLIHA.2064@.TK2MSFTNGP06.phx.gbl...
> Hi group I have a problem with collations.
> Traditional_Spanish_CI_AS And Modern_Spanish_CI_AS
> Are de same? Because The server has Modern_Spanish_CI_AS And the database
> has Traditional_Spanish_CI_AS And somethimes in a stored procedure get an
> error but I dont identify where is taht error, The error is "Error near
> 'S' " and I supouse that is related with Collate.
> Thanks for your help
>

COLLATE problem

Why do i get the error below:
Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.
What should i do?
INSERT INTO tkl_proposals(
proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
front_page_tr, front_page_en, notes, owner_company_id, status,
proposal_date, transfer_date, from_server)
SELECT
proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
front_page_tr, front_page_en, notes, owner_company_id, status,
proposal_date, getdate(), 'KIRMIZI'
FROM MD_F.MidasLocal.dbo.tkl_proposals b
WHERE
(NOT EXISTS (SELECT NULL FROM tkl_proposals a WHERE
b.proposal_id=a.proposal_id AND
b.oc_initial=a.oc_initial AND
b.oc_place=a.oc_place)
)
AND b.oc_initial='MD'
AND b.oc_place='F'
AND b.proposal_date >= '1-1-2005'
Zulu
You join two tables on varchar columns which have a different collation.
Table1.LastName=Table2.surname COLLATE HEBREW_CI_AS (Change it for your
needs)
That means Table2 has not the same collation as Table1 and by using above
hints I define to use this praticular collation.
"zulu" <zkendir@.simternet.com> wrote in message
news:O2ZXLyeCFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Why do i get the error below:
> Server: Msg 446, Level 16, State 9, Line 1
> Cannot resolve collation conflict for equal to operation.
> What should i do?
>
>
> INSERT INTO tkl_proposals(
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, transfer_date, from_server)
> SELECT
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, getdate(), 'KIRMIZI'
> FROM MD_F.MidasLocal.dbo.tkl_proposals b
> WHERE
> (NOT EXISTS (SELECT NULL FROM tkl_proposals a WHERE
> b.proposal_id=a.proposal_id AND
> b.oc_initial=a.oc_initial AND
> b.oc_place=a.oc_place)
> )
> AND b.oc_initial='MD'
> AND b.oc_place='F'
> AND b.proposal_date >= '1-1-2005'
>
|||> What should i do?
It's a good practice to provide DDL (CREATE TABLE statements) so that we
can better help you.
This error may be due do different collations on the joined columns. You
can explicitly specify the desired collation using a COLLATE clause in your
query. See the Bools Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"zulu" <zkendir@.simternet.com> wrote in message
news:O2ZXLyeCFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Why do i get the error below:
> Server: Msg 446, Level 16, State 9, Line 1
> Cannot resolve collation conflict for equal to operation.
> What should i do?
>
>
> INSERT INTO tkl_proposals(
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, transfer_date, from_server)
> SELECT
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, getdate(), 'KIRMIZI'
> FROM MD_F.MidasLocal.dbo.tkl_proposals b
> WHERE
> (NOT EXISTS (SELECT NULL FROM tkl_proposals a WHERE
> b.proposal_id=a.proposal_id AND
> b.oc_initial=a.oc_initial AND
> b.oc_place=a.oc_place)
> )
> AND b.oc_initial='MD'
> AND b.oc_place='F'
> AND b.proposal_date >= '1-1-2005'
>
|||ok, got it, thank you
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O$CfLGfCFHA.4052@.TK2MSFTNGP15.phx.gbl...
> Zulu
> You join two tables on varchar columns which have a different collation.
> Table1.LastName=Table2.surname COLLATE HEBREW_CI_AS (Change it for your
> needs)
> That means Table2 has not the same collation as Table1 and by using above
> hints I define to use this praticular collation.
>
> "zulu" <zkendir@.simternet.com> wrote in message
> news:O2ZXLyeCFHA.2876@.TK2MSFTNGP12.phx.gbl...
>

COLLATE problem

Why do i get the error below:
Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.
What should i do?
INSERT INTO tkl_proposals(
proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
front_page_tr, front_page_en, notes, owner_company_id, status,
proposal_date, transfer_date, from_server)
SELECT
proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
front_page_tr, front_page_en, notes, owner_company_id, status,
proposal_date, getdate(), 'KIRMIZI'
FROM MD_F.MidasLocal.dbo.tkl_proposals b
WHERE
(NOT EXISTS (SELECT NULL FROM tkl_proposals a WHERE
b.proposal_id=a.proposal_id AND
b.oc_initial=a.oc_initial AND
b.oc_place=a.oc_place)
)
AND b.oc_initial='MD'
AND b.oc_place='F'
AND b.proposal_date >= '1-1-2005'Zulu
You join two tables on varchar columns which have a different collation.
Table1.LastName=Table2.surname COLLATE HEBREW_CI_AS (Change it for your
needs)
That means Table2 has not the same collation as Table1 and by using above
hints I define to use this praticular collation.
"zulu" <zkendir@.simternet.com> wrote in message
news:O2ZXLyeCFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Why do i get the error below:
> Server: Msg 446, Level 16, State 9, Line 1
> Cannot resolve collation conflict for equal to operation.
> What should i do?
>
>
> INSERT INTO tkl_proposals(
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, transfer_date, from_server)
> SELECT
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, getdate(), 'KIRMIZI'
> FROM MD_F.MidasLocal.dbo.tkl_proposals b
> WHERE
> (NOT EXISTS (SELECT NULL FROM tkl_proposals a WHERE
> b.proposal_id=a.proposal_id AND
> b.oc_initial=a.oc_initial AND
> b.oc_place=a.oc_place)
> )
> AND b.oc_initial='MD'
> AND b.oc_place='F'
> AND b.proposal_date >= '1-1-2005'
>|||> What should i do?
It's a good practice to provide DDL (CREATE TABLE statements) so that we
can better help you.
This error may be due do different collations on the joined columns. You
can explicitly specify the desired collation using a COLLATE clause in your
query. See the Bools Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"zulu" <zkendir@.simternet.com> wrote in message
news:O2ZXLyeCFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Why do i get the error below:
> Server: Msg 446, Level 16, State 9, Line 1
> Cannot resolve collation conflict for equal to operation.
> What should i do?
>
>
> INSERT INTO tkl_proposals(
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, transfer_date, from_server)
> SELECT
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, getdate(), 'KIRMIZI'
> FROM MD_F.MidasLocal.dbo.tkl_proposals b
> WHERE
> (NOT EXISTS (SELECT NULL FROM tkl_proposals a WHERE
> b.proposal_id=a.proposal_id AND
> b.oc_initial=a.oc_initial AND
> b.oc_place=a.oc_place)
> )
> AND b.oc_initial='MD'
> AND b.oc_place='F'
> AND b.proposal_date >= '1-1-2005'
>|||ok, got it, thank you
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O$CfLGfCFHA.4052@.TK2MSFTNGP15.phx.gbl...
> Zulu
> You join two tables on varchar columns which have a different collation.
> Table1.LastName=Table2.surname COLLATE HEBREW_CI_AS (Change it for your
> needs)
> That means Table2 has not the same collation as Table1 and by using above
> hints I define to use this praticular collation.
>
> "zulu" <zkendir@.simternet.com> wrote in message
> news:O2ZXLyeCFHA.2876@.TK2MSFTNGP12.phx.gbl...
>

COLLATE Problem

Hi

Currently i am using the "Latin1_General_CS_AI" Collate

I just need the name of Collate which make my DB as Case sensitive Search but not the object name(or column name of table) sensitive

Like (if my table name is "misc",

all

Select * from misc

Select * from Misc

Select * from MIsc

Select * from MISc

Select * from MISC

should work.

Just as the Oracel does.

I try all the collates, but nothing give both.

Help me?

Regards,

Thanks.

Gurpreet S. Gill

object name collation is defined/controlled by the database collation. If you create your database with Latin1_General_CS_AI then your object names will follow case-sensitive collation.

So, server's collation defines the sort order for server's objects (i.e. logins, database names, object names in the master db, etc.) and is the default collation for database's objects if you do not explicitly define it at the database creation.

Database's collation defines the default sort order for objects within it (i.e. table, view, sproc, udf name, etc.).

Object's collation defines the sort order for the actual data for that specific object/column.

Long story short, if you run the following query and it returns Latin1_General_CS_AI then your table name is case-sensitive.

select databasepropertyex('your_db','collation')|||

oj--

i know all these things but i want the collate who`s

search should be case-sensitive & not the object name(or column name of table) sensitive.

Regards,

Thanks

Gurpreet S. Gill

COLLATE Problem

Hi

Currently i am using the "Latin1_General_CS_AI" Collate

I just need the name of Collate which make my DB as Case sensitive Search but not the object name(or column name of table) sensitive

Like (if my table name is "misc",

all

Select * from misc

Select * from Misc

Select * from MIsc

Select * from MISc

Select * from MISC

should work.

Just as the Oracel does.

I try all the collates, but nothing give both.

Help me?

Regards,

Thanks.

Gurpreet S. Gill

object name collation is defined/controlled by the database collation. If you create your database with Latin1_General_CS_AI then your object names will follow case-sensitive collation.

So, server's collation defines the sort order for server's objects (i.e. logins, database names, object names in the master db, etc.) and is the default collation for database's objects if you do not explicitly define it at the database creation.

Database's collation defines the default sort order for objects within it (i.e. table, view, sproc, udf name, etc.).

Object's collation defines the sort order for the actual data for that specific object/column.

Long story short, if you run the following query and it returns Latin1_General_CS_AI then your table name is case-sensitive.

select databasepropertyex('your_db','collation')|||

oj--

i know all these things but i want the collate who`s

search should be case-sensitive & not the object name(or column name of table) sensitive.

Regards,

Thanks

Gurpreet S. Gill

COLLATE problem

Why do i get the error below:
Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.
What should i do?
INSERT INTO tkl_proposals(
proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
front_page_tr, front_page_en, notes, owner_company_id, status,
proposal_date, transfer_date, from_server)
SELECT
proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
front_page_tr, front_page_en, notes, owner_company_id, status,
proposal_date, getdate(), 'KIRMIZI'
FROM MD_F.MidasLocal.dbo.tkl_proposals b
WHERE
(NOT EXISTS (SELECT NULL FROM tkl_proposals a WHERE
b.proposal_id=a.proposal_id AND
b.oc_initial=a.oc_initial AND
b.oc_place=a.oc_place)
)
AND b.oc_initial='MD'
AND b.oc_place='F'
AND b.proposal_date >= '1-1-2005'Zulu
You join two tables on varchar columns which have a different collation.
Table1.LastName=Table2.surname COLLATE HEBREW_CI_AS (Change it for your
needs)
That means Table2 has not the same collation as Table1 and by using above
hints I define to use this praticular collation.
"zulu" <zkendir@.simternet.com> wrote in message
news:O2ZXLyeCFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Why do i get the error below:
> Server: Msg 446, Level 16, State 9, Line 1
> Cannot resolve collation conflict for equal to operation.
> What should i do?
>
>
> INSERT INTO tkl_proposals(
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, transfer_date, from_server)
> SELECT
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, getdate(), 'KIRMIZI'
> FROM MD_F.MidasLocal.dbo.tkl_proposals b
> WHERE
> (NOT EXISTS (SELECT NULL FROM tkl_proposals a WHERE
> b.proposal_id=a.proposal_id AND
> b.oc_initial=a.oc_initial AND
> b.oc_place=a.oc_place)
> )
> AND b.oc_initial='MD'
> AND b.oc_place='F'
> AND b.proposal_date >= '1-1-2005'
>|||> What should i do?
It's a good practice to provide DDL (CREATE TABLE statements) so that we
can better help you.
This error may be due do different collations on the joined columns. You
can explicitly specify the desired collation using a COLLATE clause in your
query. See the Bools Online for more information.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"zulu" <zkendir@.simternet.com> wrote in message
news:O2ZXLyeCFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Why do i get the error below:
> Server: Msg 446, Level 16, State 9, Line 1
> Cannot resolve collation conflict for equal to operation.
> What should i do?
>
>
> INSERT INTO tkl_proposals(
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, transfer_date, from_server)
> SELECT
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, getdate(), 'KIRMIZI'
> FROM MD_F.MidasLocal.dbo.tkl_proposals b
> WHERE
> (NOT EXISTS (SELECT NULL FROM tkl_proposals a WHERE
> b.proposal_id=a.proposal_id AND
> b.oc_initial=a.oc_initial AND
> b.oc_place=a.oc_place)
> )
> AND b.oc_initial='MD'
> AND b.oc_place='F'
> AND b.proposal_date >= '1-1-2005'
>|||ok, got it, thank you
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O$CfLGfCFHA.4052@.TK2MSFTNGP15.phx.gbl...
> Zulu
> You join two tables on varchar columns which have a different collation.
> Table1.LastName=Table2.surname COLLATE HEBREW_CI_AS (Change it for your
> needs)
> That means Table2 has not the same collation as Table1 and by using above
> hints I define to use this praticular collation.
>
> "zulu" <zkendir@.simternet.com> wrote in message
> news:O2ZXLyeCFHA.2876@.TK2MSFTNGP12.phx.gbl...
> > Why do i get the error below:
> >
> > Server: Msg 446, Level 16, State 9, Line 1
> > Cannot resolve collation conflict for equal to operation.
> >
> > What should i do?
> >
> >
> >
> >
> >
> > INSERT INTO tkl_proposals(
> > proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> > front_page_tr, front_page_en, notes, owner_company_id, status,
> > proposal_date, transfer_date, from_server)
> > SELECT
> > proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> > front_page_tr, front_page_en, notes, owner_company_id, status,
> > proposal_date, getdate(), 'KIRMIZI'
> > FROM MD_F.MidasLocal.dbo.tkl_proposals b
> > WHERE
> > (NOT EXISTS (SELECT NULL FROM tkl_proposals a WHERE
> > b.proposal_id=a.proposal_id AND
> > b.oc_initial=a.oc_initial AND
> > b.oc_place=a.oc_place)
> > )
> > AND b.oc_initial='MD'
> > AND b.oc_place='F'
> > AND b.proposal_date >= '1-1-2005'
> >
> >
>

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
> >
> >