Change collation in SS7

How can I make just one particular column on a table in SS7 be case-sensitive? I know it can be done in SQL 2000.Afraid you can't. This is a new feature in v2000.

You can code in case sensitivity by

where fld = @.fld
and convert(varbinary,fld) = convert(varbinary,@.fld)

the 'where fld = @.fld' is just so it can use any indexes|||Thanks for the reply.

Case-sensitive stored procedures

I have a SQL 2000 database server with the server collation set to
SQL_Latin1_General_CP850_BIN and one of the databases with a collation of
SQL_Latin1_General_CP1_CI_AS which has been created by an external company.
Problem I have is that on this server stored procedures are case sensitive
and the database (SQL_Latin1_General_CP1_CI_AS) has stored procedures that
have been written without case sensitivity in mind (the procedures do not
work). So I have a few questions.
1. Is the reason for the stored procedure case sensitivity due to the server
collation? ie. It is server specific rather than DB specific.
2. Can this be changed?
thanks
Gav
The collation that is CI is a default configuration, so that's probably why
the external company gave it that way.
You can modify the collation of the database to be the same as yours.
If it won't affect the sp's speed, you could put collation hints on any
joins between the databases without changing the db collation.
"Gav" <gav@.nospam.com> wrote in message
news:%231EwxS0jIHA.3940@.TK2MSFTNGP05.phx.gbl...
>I have a SQL 2000 database server with the server collation set to
>SQL_Latin1_General_CP850_BIN and one of the databases with a collation of
>SQL_Latin1_General_CP1_CI_AS which has been created by an external company.
>Problem I have is that on this server stored procedures are case sensitive
>and the database (SQL_Latin1_General_CP1_CI_AS) has stored procedures that
>have been written without case sensitivity in mind (the procedures do not
>work). So I have a few questions.
> 1. Is the reason for the stored procedure case sensitivity due to the
> server collation? ie. It is server specific rather than DB specific.
> 2. Can this be changed?
> thanks
> Gav
>

Case-sensitive stored procedures

I have a SQL 2000 database server with the server collation set to
SQL_Latin1_General_CP850_BIN and one of the databases with a collation of
SQL_Latin1_General_CP1_CI_AS which has been created by an external company.
Problem I have is that on this server stored procedures are case sensitive
and the database (SQL_Latin1_General_CP1_CI_AS) has stored procedures that
have been written without case sensitivity in mind (the procedures do not
work). So I have a few questions.
1. Is the reason for the stored procedure case sensitivity due to the server
collation? ie. It is server specific rather than DB specific.
2. Can this be changed?
thanks
GavThe collation that is CI is a default configuration, so that's probably why
the external company gave it that way.
You can modify the collation of the database to be the same as yours.
If it won't affect the sp's speed, you could put collation hints on any
joins between the databases without changing the db collation.
"Gav" <gav@.nospam.com> wrote in message
news:%231EwxS0jIHA.3940@.TK2MSFTNGP05.phx.gbl...
>I have a SQL 2000 database server with the server collation set to
>SQL_Latin1_General_CP850_BIN and one of the databases with a collation of
>SQL_Latin1_General_CP1_CI_AS which has been created by an external company.
>Problem I have is that on this server stored procedures are case sensitive
>and the database (SQL_Latin1_General_CP1_CI_AS) has stored procedures that
>have been written without case sensitivity in mind (the procedures do not
>work). So I have a few questions.
> 1. Is the reason for the stored procedure case sensitivity due to the
> server collation? ie. It is server specific rather than DB specific.
> 2. Can this be changed?
> thanks
> Gav
>

case-sensitive searching in case-insensitive database

I recall reading about a way to check for a case-sensitive value in a
database that has a case-insensitive collation. Could anyone shed light on
this?
Thanks in advance.You could convert to binary, or use the COLLATE statement:
CREATE TABLE [dbo].[testCollation] (
[Col1Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
insert into testCollation (Col1Name)
values('FredBloggs')
go
select * from testCollation
where Col1Name = 'fredBloggs' collate latin1_general_ci_as
select * from testCollation
where Col1Name = 'fredBloggs' collate latin1_general_cs_as
HTH,
Paul Ibison|||"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O19w0TVVEHA.1152@.TK2MSFTNGP09.phx.gbl...
> You could convert to binary, or use the COLLATE statement:
> CREATE TABLE [dbo].[testCollation] (
> [Col1Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> insert into testCollation (Col1Name)
> values('FredBloggs')
> go
> select * from testCollation
> where Col1Name = 'fredBloggs' collate latin1_general_ci_as
> select * from testCollation
> where Col1Name = 'fredBloggs' collate latin1_general_cs_as
> HTH,
> Paul Ibison
>
If the table is large and the column indexed you may want to add a search
predicate for case-insensitive collation so you can use the index, and then
narrow it down further with the case-sensitive collation predicate.
select * from testCollation
where col1Name = 'fredBloggs'
and Col1Name = 'fredBloggs' collate latin1_general_cs_as
David

case-sensitive searching in case-insensitive database

I recall reading about a way to check for a case-sensitive value in a
database that has a case-insensitive collation. Could anyone shed light on
this?
Thanks in advance.
You could convert to binary, or use the COLLATE statement:
CREATE TABLE [dbo].[testCollation] (
[Col1Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
insert into testCollation (Col1Name)
values('FredBloggs')
go
select * from testCollation
where Col1Name = 'fredBloggs' collate latin1_general_ci_as
select * from testCollation
where Col1Name = 'fredBloggs' collate latin1_general_cs_as
HTH,
Paul Ibison
|||"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O19w0TVVEHA.1152@.TK2MSFTNGP09.phx.gbl...
> You could convert to binary, or use the COLLATE statement:
> CREATE TABLE [dbo].[testCollation] (
> [Col1Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> insert into testCollation (Col1Name)
> values('FredBloggs')
> go
> select * from testCollation
> where Col1Name = 'fredBloggs' collate latin1_general_ci_as
> select * from testCollation
> where Col1Name = 'fredBloggs' collate latin1_general_cs_as
> HTH,
> Paul Ibison
>
If the table is large and the column indexed you may want to add a search
predicate for case-insensitive collation so you can use the index, and then
narrow it down further with the case-sensitive collation predicate.
select * from testCollation
where col1Name = 'fredBloggs'
and Col1Name = 'fredBloggs' collate latin1_general_cs_as
David

case-sensitive searching in case-insensitive database

I recall reading about a way to check for a case-sensitive value in a
database that has a case-insensitive collation. Could anyone shed light on
this?
Thanks in advance.You could convert to binary, or use the COLLATE statement:
CREATE TABLE [dbo].[testCollation] (
[Col1Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
insert into testCollation (Col1Name)
values('FredBloggs')
go
select * from testCollation
where Col1Name = 'fredBloggs' collate latin1_general_ci_as
select * from testCollation
where Col1Name = 'fredBloggs' collate latin1_general_cs_as
HTH,
Paul Ibison|||"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O19w0TVVEHA.1152@.TK2MSFTNGP09.phx.gbl...
> You could convert to binary, or use the COLLATE statement:
> CREATE TABLE [dbo].[testCollation] (
> [Col1Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L
> ) ON [PRIMARY]
> GO
> insert into testCollation (Col1Name)
> values('FredBloggs')
> go
> select * from testCollation
> where Col1Name = 'fredBloggs' collate latin1_general_ci_as
> select * from testCollation
> where Col1Name = 'fredBloggs' collate latin1_general_cs_as
> HTH,
> Paul Ibison
>
If the table is large and the column indexed you may want to add a search
predicate for case-insensitive collation so you can use the index, and then
narrow it down further with the case-sensitive collation predicate.
select * from testCollation
where col1Name = 'fredBloggs'
and Col1Name = 'fredBloggs' collate latin1_general_cs_as
David

case-sensitive search in sql 7

Hi,

I have yet to find an answer for this:

I want to do a case-sensitive query using "like" on a table in sql 7.
Currently, "like" performs case-insensitive query.

I understand that you can use the following query in sql 2000:

SELECT *
FROM table_x
WHERE col1 collate SQL_Latin1_General_CP1_CS_AS LIKE '% AVE %'

However, is there a similar method for sql 7?

Any answer would be appreciated.

Thanks,
JayI assume you mean that you want a case-sensitive search on a
case-insensitive server. Obviously the ideal is to have a sort order that
meets your requirements. You can change the sort-order in 7.0 but only at
the server-level.

This should do it under any sort order:

SELECT col1
FROM Table_X
WHERE col1 LIKE '% AVE %'
AND CAST(SUBSTRING(col1,PATINDEX('% AVE %',col1),5) AS VARBINARY)
= CAST(' AVE ' AS VARBINARY)

--
David Portas
SQL Server MVP
--

Case-Sensitive Search

Hi,
We have two systems, a Financial system and a Professional Service Automated
system. The database currently has a collation of case-insensitive
(MSSQL2K). The Financial system happened to have incorrectly exported a
ProjectCode value that was in all lower case to the other system where in
expects a ProjectCode value to be in UPPERCASE. I would to be able to write
a query that will identify all rows in a table in the financial system where
the ProjectCode is in lowercase in the case-insensitive environment.
TIA,
bpdee
See "collate" clause in BOL.
Example:
select
*
from
(
select 1, cast('microsoft sql server 2000' as varchar(25))
union all
select 2, cast('MICROSOFT SQL SERVER 2000' as varchar(25))
) t1(c1, c2)
where
c2 like 'microsoft sql server 2000' collate SQL_Latin1_General_CP1_CS_AS
AMB
"bpdee" wrote:

> Hi,
> We have two systems, a Financial system and a Professional Service Automated
> system. The database currently has a collation of case-insensitive
> (MSSQL2K). The Financial system happened to have incorrectly exported a
> ProjectCode value that was in all lower case to the other system where in
> expects a ProjectCode value to be in UPPERCASE. I would to be able to write
> a query that will identify all rows in a table in the financial system where
> the ProjectCode is in lowercase in the case-insensitive environment.
> TIA,
> bpdee

Case-Sensitive Search

Hi,
We have two systems, a Financial system and a Professional Service Automated
system. The database currently has a collation of case-insensitive
(MSSQL2K). The Financial system happened to have incorrectly exported a
ProjectCode value that was in all lower case to the other system where in
expects a ProjectCode value to be in UPPERCASE. I would to be able to write
a query that will identify all rows in a table in the financial system where
the ProjectCode is in lowercase in the case-insensitive environment.
TIA,
bpdeeSee "collate" clause in BOL.
Example:
select
*
from
(
select 1, cast('microsoft sql server 2000' as varchar(25))
union all
select 2, cast('MICROSOFT SQL SERVER 2000' as varchar(25))
) t1(c1, c2)
where
c2 like 'microsoft sql server 2000' collate SQL_Latin1_General_CP1_CS_AS
AMB
"bpdee" wrote:

> Hi,
> We have two systems, a Financial system and a Professional Service Automat
ed
> system. The database currently has a collation of case-insensitive
> (MSSQL2K). The Financial system happened to have incorrectly exported a
> ProjectCode value that was in all lower case to the other system where in
> expects a ProjectCode value to be in UPPERCASE. I would to be able to wri
te
> a query that will identify all rows in a table in the financial system whe
re
> the ProjectCode is in lowercase in the case-insensitive environment.
> TIA,
> bpdee

Case-Sensitive Search

Hi,
We have two systems, a Financial system and a Professional Service Automated
system. The database currently has a collation of case-insensitive
(MSSQL2K). The Financial system happened to have incorrectly exported a
ProjectCode value that was in all lower case to the other system where in
expects a ProjectCode value to be in UPPERCASE. I would to be able to write
a query that will identify all rows in a table in the financial system where
the ProjectCode is in lowercase in the case-insensitive environment.
TIA,
bpdeeSee "collate" clause in BOL.
Example:
select
*
from
(
select 1, cast('microsoft sql server 2000' as varchar(25))
union all
select 2, cast('MICROSOFT SQL SERVER 2000' as varchar(25))
) t1(c1, c2)
where
c2 like 'microsoft sql server 2000' collate SQL_Latin1_General_CP1_CS_AS
AMB
"bpdee" wrote:
> Hi,
> We have two systems, a Financial system and a Professional Service Automated
> system. The database currently has a collation of case-insensitive
> (MSSQL2K). The Financial system happened to have incorrectly exported a
> ProjectCode value that was in all lower case to the other system where in
> expects a ProjectCode value to be in UPPERCASE. I would to be able to write
> a query that will identify all rows in a table in the financial system where
> the ProjectCode is in lowercase in the case-insensitive environment.
> TIA,
> bpdee