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.

Code and diff sql servers

I'm running out of ideas...
Given the following example of code:
set @.partialname = 'u'
SELECT col1, col2, col3
FROM tbl1
WHERE col1 LIKE @.partialName + '%'
ORDER BY col1
Why would two different sql servers, having exactly the same data, give
different results? One server returns an empty set, while the other returns
all rows where col1 starts with 'u'. A server setting I'm guessing, but
can't find what it is.
Can you help?crud - I messed up my explanation- it works with the 'u' but not when
@.partialname is blank ('').
Its this code that works on one server, but not the other:
set @.partialname = ''
SELECT col1, col2, col3
FROM tbl1
WHERE col1 LIKE @.partialName + '%'
ORDER BY col1
If @.partialname = 'u' -- both servers process correctly.
Sorry about that.
'
"mikeb" <mike@.nohostanywhere.com> wrote in message
news:uMmFbzANGHA.3908@.TK2MSFTNGP10.phx.gbl...
> I'm running out of ideas...
> Given the following example of code:
> set @.partialname = 'u'
> SELECT col1, col2, col3
> FROM tbl1
> WHERE col1 LIKE @.partialName + '%'
> ORDER BY col1
> Why would two different sql servers, having exactly the same data, give
> different results? One server returns an empty set, while the other
> returns all rows where col1 starts with 'u'. A server setting I'm
> guessing, but can't find what it is.
> Can you help?
>
>|||My guess is that the one thing you do not show - the data type of
partialname - is the problem. Is it, by any chance, CHAR(1)? If so
you are matching on ' %' when it is blank. Try making it varchar.
And you might have tried a little research of your own:
set @.partialname = ''
SELECT @.partialName + '%'
Roy
On Fri, 17 Feb 2006 14:03:14 -0800, "mikeb" <mike@.nohostanywhere.com>
wrote:

>crud - I messed up my explanation- it works with the 'u' but not when
>@.partialname is blank ('').
>Its this code that works on one server, but not the other:
>set @.partialname = ''
>SELECT col1, col2, col3
>FROM tbl1
>WHERE col1 LIKE @.partialName + '%'
>ORDER BY col1
>If @.partialname = 'u' -- both servers process correctly.
>Sorry about that.
>'
>"mikeb" <mike@.nohostanywhere.com> wrote in message
>news:uMmFbzANGHA.3908@.TK2MSFTNGP10.phx.gbl...
>|||TRIED RESEARCH OF MY OWN? I've done tons of searches Roy, spent the last
couple hours trying different options. ALL before posting.
You might want to get your crystal ball in for repair - it doesn't seem to
be working today...
@.partialname is VarChar(50)
It appears that the other database is SQL7, versus SQL2000 (which works)
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:31mcv1lrouvu2dri9u7b0rbt19a91i4gcv@.
4ax.com...
> My guess is that the one thing you do not show - the data type of
> partialname - is the problem. Is it, by any chance, CHAR(1)? If so
> you are matching on ' %' when it is blank. Try making it varchar.
> And you might have tried a little research of your own:
> set @.partialname = ''
> SELECT @.partialName + '%'
> Roy
>
> On Fri, 17 Feb 2006 14:03:14 -0800, "mikeb" <mike@.nohostanywhere.com>
> wrote:
>|||It seems that the difference was that even though @.partialName, a
varchar(50), was passed an empty string ('') to the s.proc, SQL7 somehow
converted it to a single blank char (' '). Where SQL2000 left it empty. I
could very well be doing something wrong here - I'm just trying to fix an
error in what code we were left with. Open to suggestions if its bad form.
Wow. I even kept researching after my hand was slapped for not (sic)...
pomposity gets really tiring sometimes.
"mikeb" <mike@.nohostanywhere.com> wrote in message
news:udUKw1BNGHA.2752@.TK2MSFTNGP14.phx.gbl...
> TRIED RESEARCH OF MY OWN? I've done tons of searches Roy, spent the last
> couple hours trying different options. ALL before posting.
> You might want to get your crystal ball in for repair - it doesn't seem to
> be working today...
> @.partialname is VarChar(50)
> It appears that the other database is SQL7, versus SQL2000 (which works)
>
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:31mcv1lrouvu2dri9u7b0rbt19a91i4gcv@.
4ax.com...
>|||Sorry it came across that way. My apologies.
You should be able to get around the problem with:
RTRIM(@.partialName) + '%'
Roy|||Yep, thats exactly what I did to get it working - I meant to mention that
too in the previous post. thx.
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:27scv1l4fqtv3hrvuf0msr6q6elv7fl5ej@.
4ax.com...
> Sorry it came across that way. My apologies.
> You should be able to get around the problem with:
> RTRIM(@.partialName) + '%'
> Roy