I know that SQL SERVER is by default case insensitive but all my searches are case sensitive.....I also tried the ALTER DATABASE COLLATE ..... Still no luck...Please help!!!!
thanks in advance,
jPlease clarify: do you mean that SQL server is using case-sensitive searches despite the default, or do you mean that you WANT all your searches to be case-sensitive?
blindman
Showing posts with label sensitive. Show all posts
Showing posts with label sensitive. Show all posts
Tuesday, March 27, 2012
Case Sensitivity on a non case sensitive DB
Hi,
I'm running into an issue with case sensitivity. Here is the setup: I have
SQL Server instance that is CS AS by default. Create new database that is
CI AS. Run a file of SQL against this database to create triggers and get
an error on a variable name in one of the triggers. The variable is
declared as @.szName but used as @.szname. I would have thought that since
this is a trigger on the CI database that it would not matter but the
trigger create must somehow interact with Master or MSDB and since they are
CS this causes a problem? Any options on how to easily make this go away
other than the obvious - changing all triggers (and I would guess stored
procedure code as well). Thanks in advance for any help.
Wayne Antinore> SQL Server instance that is CS AS by default. Create new database that is
> CI AS.
I think you should decide to either (a) use CS only or CI only or (b) use a
different instance for CI. Just wait until you start doing any work that
requires tempdb... you will get collation conflicts all over the place.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Thanks Aaron,
Yikes! Never even got into using tempdb yet. I can only imagine what I
would come across there.
Thanks again,
Wayne
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:%23rGSiMsBEHA.1380@.TK2MSFTNGP10.phx.gbl...
is
> I think you should decide to either (a) use CS only or CI only or (b) use
a
> different instance for CI. Just wait until you start doing any work that
> requires tempdb... you will get collation conflicts all over the place.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
I'm running into an issue with case sensitivity. Here is the setup: I have
SQL Server instance that is CS AS by default. Create new database that is
CI AS. Run a file of SQL against this database to create triggers and get
an error on a variable name in one of the triggers. The variable is
declared as @.szName but used as @.szname. I would have thought that since
this is a trigger on the CI database that it would not matter but the
trigger create must somehow interact with Master or MSDB and since they are
CS this causes a problem? Any options on how to easily make this go away
other than the obvious - changing all triggers (and I would guess stored
procedure code as well). Thanks in advance for any help.
Wayne Antinore> SQL Server instance that is CS AS by default. Create new database that is
> CI AS.
I think you should decide to either (a) use CS only or CI only or (b) use a
different instance for CI. Just wait until you start doing any work that
requires tempdb... you will get collation conflicts all over the place.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Thanks Aaron,
Yikes! Never even got into using tempdb yet. I can only imagine what I
would come across there.
Thanks again,
Wayne
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:%23rGSiMsBEHA.1380@.TK2MSFTNGP10.phx.gbl...
is
> I think you should decide to either (a) use CS only or CI only or (b) use
a
> different instance for CI. Just wait until you start doing any work that
> requires tempdb... you will get collation conflicts all over the place.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
Case sensitivity of SQL selects
Hi all
Can anybody tell me how to give a case sensitive select on a non case sensitive instance of sql server .Is there any setting to be set up ( Which needs to be done programtically not through the interface )
Thanks
RoshanAssuming SQL 2000, you can specify a case-sensitive collation for a
case-sensitive query. For example:
USE Northwind
SELECT *
FROM Customers
WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
SELECT *
FROM Customers
WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
You can also add the case-insensitive condition so that indexes can be used
efficiently.
SELECT *
FROM Customers
WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
AND CustomerID = 'alfki'
SELECT *
FROM Customers
WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
AND CustomerID = 'ALFKI'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Roshan Jayalath" <anonymous@.discussions.microsoft.com> wrote in message
news:648CCD5F-2615-4730-B735-3AED0EEEB4CB@.microsoft.com...
> Hi all,
> Can anybody tell me how to give a case sensitive select on a non case
sensitive instance of sql server .Is there any setting to be set up ( Which
needs to be done programtically not through the interface ) ?
> Thanks
> Roshan|||In addition Dan's post
This should work on SQL 7 too
create table ABCD
(
courceid smallint not null,
description varchar(20) null
)
insert into ABCD(courceid,description)values (1,'DFh2AcZ')
insert into ABCD(courceid,description)values (2,'dHZ3')
)
SELECT description FROM ABCD where charindex(cast('H' as
varbinary(20)),cast(description as varbinary(20)))> 0
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:u2gDRbBKEHA.3596@.tk2msftngp13.phx.gbl...
> Assuming SQL 2000, you can specify a case-sensitive collation for a
> case-sensitive query. For example:
> USE Northwind
> SELECT *
> FROM Customers
> WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
> SELECT *
> FROM Customers
> WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
> You can also add the case-insensitive condition so that indexes can be
used
> efficiently.
> SELECT *
> FROM Customers
> WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
> AND CustomerID = 'alfki'
> SELECT *
> FROM Customers
> WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
> AND CustomerID = 'ALFKI'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Roshan Jayalath" <anonymous@.discussions.microsoft.com> wrote in message
> news:648CCD5F-2615-4730-B735-3AED0EEEB4CB@.microsoft.com...
> > Hi all,
> >
> > Can anybody tell me how to give a case sensitive select on a non case
> sensitive instance of sql server .Is there any setting to be set up (
Which
> needs to be done programtically not through the interface ) ?
> >
> > Thanks
> > Roshan
>sql
Can anybody tell me how to give a case sensitive select on a non case sensitive instance of sql server .Is there any setting to be set up ( Which needs to be done programtically not through the interface )
Thanks
RoshanAssuming SQL 2000, you can specify a case-sensitive collation for a
case-sensitive query. For example:
USE Northwind
SELECT *
FROM Customers
WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
SELECT *
FROM Customers
WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
You can also add the case-insensitive condition so that indexes can be used
efficiently.
SELECT *
FROM Customers
WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
AND CustomerID = 'alfki'
SELECT *
FROM Customers
WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
AND CustomerID = 'ALFKI'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Roshan Jayalath" <anonymous@.discussions.microsoft.com> wrote in message
news:648CCD5F-2615-4730-B735-3AED0EEEB4CB@.microsoft.com...
> Hi all,
> Can anybody tell me how to give a case sensitive select on a non case
sensitive instance of sql server .Is there any setting to be set up ( Which
needs to be done programtically not through the interface ) ?
> Thanks
> Roshan|||In addition Dan's post
This should work on SQL 7 too
create table ABCD
(
courceid smallint not null,
description varchar(20) null
)
insert into ABCD(courceid,description)values (1,'DFh2AcZ')
insert into ABCD(courceid,description)values (2,'dHZ3')
)
SELECT description FROM ABCD where charindex(cast('H' as
varbinary(20)),cast(description as varbinary(20)))> 0
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:u2gDRbBKEHA.3596@.tk2msftngp13.phx.gbl...
> Assuming SQL 2000, you can specify a case-sensitive collation for a
> case-sensitive query. For example:
> USE Northwind
> SELECT *
> FROM Customers
> WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
> SELECT *
> FROM Customers
> WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
> You can also add the case-insensitive condition so that indexes can be
used
> efficiently.
> SELECT *
> FROM Customers
> WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
> AND CustomerID = 'alfki'
> SELECT *
> FROM Customers
> WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
> AND CustomerID = 'ALFKI'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Roshan Jayalath" <anonymous@.discussions.microsoft.com> wrote in message
> news:648CCD5F-2615-4730-B735-3AED0EEEB4CB@.microsoft.com...
> > Hi all,
> >
> > Can anybody tell me how to give a case sensitive select on a non case
> sensitive instance of sql server .Is there any setting to be set up (
Which
> needs to be done programtically not through the interface ) ?
> >
> > Thanks
> > Roshan
>sql
Case sensitivity of SQL selects
Hi all,
Can anybody tell me how to give a case sensitive select on a non case sensitive instance of sql server .Is there any setting to be set up ( Which needs to be done programtically not through the interface ) ?
Thanks
Roshan
Assuming SQL 2000, you can specify a case-sensitive collation for a
case-sensitive query. For example:
USE Northwind
SELECT *
FROM Customers
WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
SELECT *
FROM Customers
WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
You can also add the case-insensitive condition so that indexes can be used
efficiently.
SELECT *
FROM Customers
WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
AND CustomerID = 'alfki'
SELECT *
FROM Customers
WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
AND CustomerID = 'ALFKI'
Hope this helps.
Dan Guzman
SQL Server MVP
"Roshan Jayalath" <anonymous@.discussions.microsoft.com> wrote in message
news:648CCD5F-2615-4730-B735-3AED0EEEB4CB@.microsoft.com...
> Hi all,
> Can anybody tell me how to give a case sensitive select on a non case
sensitive instance of sql server .Is there any setting to be set up ( Which
needs to be done programtically not through the interface ) ?
> Thanks
> Roshan
|||In addition Dan's post
This should work on SQL 7 too
create table ABCD
(
courceid smallint not null,
description varchar(20) null
)
insert into ABCD(courceid,description)values (1,'DFh2AcZ')
insert into ABCD(courceid,description)values (2,'dHZ3')
)
SELECT description FROM ABCD where charindex(cast('H' as
varbinary(20)),cast(description as varbinary(20)))> 0
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:u2gDRbBKEHA.3596@.tk2msftngp13.phx.gbl...
> Assuming SQL 2000, you can specify a case-sensitive collation for a
> case-sensitive query. For example:
> USE Northwind
> SELECT *
> FROM Customers
> WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
> SELECT *
> FROM Customers
> WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
> You can also add the case-insensitive condition so that indexes can be
used
> efficiently.
> SELECT *
> FROM Customers
> WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
> AND CustomerID = 'alfki'
> SELECT *
> FROM Customers
> WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
> AND CustomerID = 'ALFKI'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Roshan Jayalath" <anonymous@.discussions.microsoft.com> wrote in message
> news:648CCD5F-2615-4730-B735-3AED0EEEB4CB@.microsoft.com...
> sensitive instance of sql server .Is there any setting to be set up (
Which
> needs to be done programtically not through the interface ) ?
>
Can anybody tell me how to give a case sensitive select on a non case sensitive instance of sql server .Is there any setting to be set up ( Which needs to be done programtically not through the interface ) ?
Thanks
Roshan
Assuming SQL 2000, you can specify a case-sensitive collation for a
case-sensitive query. For example:
USE Northwind
SELECT *
FROM Customers
WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
SELECT *
FROM Customers
WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
You can also add the case-insensitive condition so that indexes can be used
efficiently.
SELECT *
FROM Customers
WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
AND CustomerID = 'alfki'
SELECT *
FROM Customers
WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
AND CustomerID = 'ALFKI'
Hope this helps.
Dan Guzman
SQL Server MVP
"Roshan Jayalath" <anonymous@.discussions.microsoft.com> wrote in message
news:648CCD5F-2615-4730-B735-3AED0EEEB4CB@.microsoft.com...
> Hi all,
> Can anybody tell me how to give a case sensitive select on a non case
sensitive instance of sql server .Is there any setting to be set up ( Which
needs to be done programtically not through the interface ) ?
> Thanks
> Roshan
|||In addition Dan's post
This should work on SQL 7 too
create table ABCD
(
courceid smallint not null,
description varchar(20) null
)
insert into ABCD(courceid,description)values (1,'DFh2AcZ')
insert into ABCD(courceid,description)values (2,'dHZ3')
)
SELECT description FROM ABCD where charindex(cast('H' as
varbinary(20)),cast(description as varbinary(20)))> 0
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:u2gDRbBKEHA.3596@.tk2msftngp13.phx.gbl...
> Assuming SQL 2000, you can specify a case-sensitive collation for a
> case-sensitive query. For example:
> USE Northwind
> SELECT *
> FROM Customers
> WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
> SELECT *
> FROM Customers
> WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
> You can also add the case-insensitive condition so that indexes can be
used
> efficiently.
> SELECT *
> FROM Customers
> WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
> AND CustomerID = 'alfki'
> SELECT *
> FROM Customers
> WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
> AND CustomerID = 'ALFKI'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Roshan Jayalath" <anonymous@.discussions.microsoft.com> wrote in message
> news:648CCD5F-2615-4730-B735-3AED0EEEB4CB@.microsoft.com...
> sensitive instance of sql server .Is there any setting to be set up (
Which
> needs to be done programtically not through the interface ) ?
>
Case sensitivity of SQL selects
Hi all,
Can anybody tell me how to give a case sensitive select on a non case sensit
ive instance of sql server .Is there any setting to be set up ( Which needs
to be done programtically not through the interface ) ?
Thanks
RoshanAssuming SQL 2000, you can specify a case-sensitive collation for a
case-sensitive query. For example:
USE Northwind
SELECT *
FROM Customers
WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
SELECT *
FROM Customers
WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
You can also add the case-insensitive condition so that indexes can be used
efficiently.
SELECT *
FROM Customers
WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
AND CustomerID = 'alfki'
SELECT *
FROM Customers
WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
AND CustomerID = 'ALFKI'
Hope this helps.
Dan Guzman
SQL Server MVP
"Roshan Jayalath" <anonymous@.discussions.microsoft.com> wrote in message
news:648CCD5F-2615-4730-B735-3AED0EEEB4CB@.microsoft.com...
> Hi all,
> Can anybody tell me how to give a case sensitive select on a non case
sensitive instance of sql server .Is there any setting to be set up ( Which
needs to be done programtically not through the interface ) ?
> Thanks
> Roshan|||In addition Dan's post
This should work on SQL 7 too
create table ABCD
(
courceid smallint not null,
description varchar(20) null
)
insert into ABCD(courceid,description)values (1,'DFh2AcZ')
insert into ABCD(courceid,description)values (2,'dHZ3')
)
SELECT description FROM ABCD where charindex(cast('H' as
varbinary(20)),cast(description as varbinary(20)))> 0
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:u2gDRbBKEHA.3596@.tk2msftngp13.phx.gbl...
> Assuming SQL 2000, you can specify a case-sensitive collation for a
> case-sensitive query. For example:
> USE Northwind
> SELECT *
> FROM Customers
> WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
> SELECT *
> FROM Customers
> WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
> You can also add the case-insensitive condition so that indexes can be
used
> efficiently.
> SELECT *
> FROM Customers
> WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
> AND CustomerID = 'alfki'
> SELECT *
> FROM Customers
> WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
> AND CustomerID = 'ALFKI'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Roshan Jayalath" <anonymous@.discussions.microsoft.com> wrote in message
> news:648CCD5F-2615-4730-B735-3AED0EEEB4CB@.microsoft.com...
> sensitive instance of sql server .Is there any setting to be set up (
Which
> needs to be done programtically not through the interface ) ?
>
Can anybody tell me how to give a case sensitive select on a non case sensit
ive instance of sql server .Is there any setting to be set up ( Which needs
to be done programtically not through the interface ) ?
Thanks
RoshanAssuming SQL 2000, you can specify a case-sensitive collation for a
case-sensitive query. For example:
USE Northwind
SELECT *
FROM Customers
WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
SELECT *
FROM Customers
WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
You can also add the case-insensitive condition so that indexes can be used
efficiently.
SELECT *
FROM Customers
WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
AND CustomerID = 'alfki'
SELECT *
FROM Customers
WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
AND CustomerID = 'ALFKI'
Hope this helps.
Dan Guzman
SQL Server MVP
"Roshan Jayalath" <anonymous@.discussions.microsoft.com> wrote in message
news:648CCD5F-2615-4730-B735-3AED0EEEB4CB@.microsoft.com...
> Hi all,
> Can anybody tell me how to give a case sensitive select on a non case
sensitive instance of sql server .Is there any setting to be set up ( Which
needs to be done programtically not through the interface ) ?
> Thanks
> Roshan|||In addition Dan's post
This should work on SQL 7 too
create table ABCD
(
courceid smallint not null,
description varchar(20) null
)
insert into ABCD(courceid,description)values (1,'DFh2AcZ')
insert into ABCD(courceid,description)values (2,'dHZ3')
)
SELECT description FROM ABCD where charindex(cast('H' as
varbinary(20)),cast(description as varbinary(20)))> 0
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:u2gDRbBKEHA.3596@.tk2msftngp13.phx.gbl...
> Assuming SQL 2000, you can specify a case-sensitive collation for a
> case-sensitive query. For example:
> USE Northwind
> SELECT *
> FROM Customers
> WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
> SELECT *
> FROM Customers
> WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
> You can also add the case-insensitive condition so that indexes can be
used
> efficiently.
> SELECT *
> FROM Customers
> WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
> AND CustomerID = 'alfki'
> SELECT *
> FROM Customers
> WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
> AND CustomerID = 'ALFKI'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Roshan Jayalath" <anonymous@.discussions.microsoft.com> wrote in message
> news:648CCD5F-2615-4730-B735-3AED0EEEB4CB@.microsoft.com...
> sensitive instance of sql server .Is there any setting to be set up (
Which
> needs to be done programtically not through the interface ) ?
>
Case Sensitivity
I am wondering if T SQL in SQL Server 2005 is case sensitive.I running am running a query in a stored procedure whchi compares a passed in value with that in a field in the database, as such, is their a need to do this
SELECT * FROM table WHERE UPPER(column_name) = UPPER(@.var)
or will this return the same results
SELECT * FROM table WHERE column_name = @.var
1) A default SQL Server installation is case insensitive, which means that SQL Server will not differentiate between upper and lower case characters/letters
2) T-SQL in also case insensitive.
|||To see which type your database is, go into SQL Server Management Studio, right-click your database and choose Properties. Select 'General' on the left-hand side, and look at the Collation property (under the 'Maintenance' heading). Somewhere in the name of the property, you'll have either a CS or a CI, standing for Case Sensitive or Case Insensitive, respectively.
I.e., mine is "SQL_Latin1_General_CP1_CI_AS" (SQL Server 2000-compatible). The _CI_ denotes the database is case insensitive.
sqlSunday, March 25, 2012
case sensitivity
Is SQL server defaults to case insensitive? I am sure there are ways to
install case sensitive SQL server instance but coming from Sybase
(which is always case sensitive) case insensitivity is something new to
me (it requires coding change etc).
Besides, is there any option I can set to turn on case sensitivity or I
am stuck with it? (I can not reinstall SQL server).
Thanks.Case sensitivity is defined by Collation. Read a bit about collations and
youll see what are your options.
install case sensitive SQL server instance but coming from Sybase
(which is always case sensitive) case insensitivity is something new to
me (it requires coding change etc).
Besides, is there any option I can set to turn on case sensitivity or I
am stuck with it? (I can not reinstall SQL server).
Thanks.Case sensitivity is defined by Collation. Read a bit about collations and
youll see what are your options.
MC
<othellomy@.yahoo.comwrote in message
news:1163396241.216022.146940@.b28g2000cwb.googlegr oups.com...
Quote:
Originally Posted by
Is SQL server defaults to case insensitive? I am sure there are ways to
install case sensitive SQL server instance but coming from Sybase
(which is always case sensitive) case insensitivity is something new to
me (it requires coding change etc).
Besides, is there any option I can set to turn on case sensitivity or I
am stuck with it? (I can not reinstall SQL server).
Thanks.
>
Case Sensitive?
Dear everyone,
try running sp_help to see the current settings.
Passwords shouldn't be stored in plaintext in the database
anyway. I suggest you have a look at the hashing functions
in .Net and use them to calculate a hash and then save that
in the database.
Then you wouldn't have to worry about case-sensitivity either.|||Thanks you for reply!!
I am doing Login webform (C# .NET web application) with SQL Server 2000.
The staff table is to store authenticated user info.
But when I test it, I found that the password can be case insensitive, i.e. 'A0001' should be correct password, but 'a0001' can allow login.
Could anyone tell me how to solve this problem??
Thanks you very much!!
You can alter the database to be case sensitive, and I think you can also do that on a per connection basis - but you'd have to check that. The other way could be to return the passwords that have matched and then double check them in c#. I sure someone has a better method.|||Case-sesitivity is determined when installing SQL Server,
private void btnLogin_Click(object sender, System.EventArgs e)
{
//instantiate SQL connection
SqlConnection sqlConnect = new SqlConnection(connectStg);
SqlCommand selectLogin = sqlConnect.CreateCommand();selectLogin.CommandText = "SELECT sid, type from STAFF Where sid= '" + txtId.Text + "' and pwd= '" + txtPwd.Text + "' ";
//open connectin for execution
sqlConnect.Open();//instantiate the SqlDataReader reader
SqlDataReader loginReader = selectLogin.ExecuteReader();//try and catch SqlException error
try
{
if(loginReader.Read())
{// check whether the user is the role of administrator or operator
// I use GetValue(1) i.e. type field from the above select statement // if "O' then go operator page, else go to administrator page.
if (loginReader.GetValue(1).ToString().ToUpper().Equals("O"))
{
Server.Transfer("//SMS/LoginUser/SuccessLoginOper.aspx");}
else if (loginReader.GetValue(1).ToString().ToUpper().Equals("A"))
{
Server.Transfer("//SMS/LoginUser/SuccessLoginAdmin.aspx");
}}
else
{
//clear content of textbox and display error message
txtId.Text="";
txtPwd.Text="";
lblLoginFail.Visible = true;
lblLoginFail.Text="Login Failed!<br>" + "Ensure that ID and Password are correct!";
}}
catch (SqlException se)
{
if (se.Number == 17)
{
lblLoginFail.Visible = true;
lblLoginFail.Text = "Could not connect to the database";
}else
{
lblLoginFail.Visible = true;
lblLoginFail.Text = se.Message;
}}
//close SqlDataReader and SqlConnection
loginReader.Close();
sqlConnect.Close();
try running sp_help to see the current settings.
Passwords shouldn't be stored in plaintext in the database
anyway. I suggest you have a look at the hashing functions
in .Net and use them to calculate a hash and then save that
in the database.
Then you wouldn't have to worry about case-sensitivity either.|||Thanks you for reply!!
As you said running sp_help to see the current settings, how to change the current settings of case-sensitive problems.
I recognise that the passwords should be better stored in encrypted forms. But how to encrypt it in SQL Server. I am new in web development. Could you briefly tell me how to do? Or any web reference provided?
Waiting for reply! Thanks
Roy|||::As you said running sp_help to see the current settings, how to change the current settings
::of case-sensitive problems
He DID tell you it is determined on install time. So you can not change it.
::I recognise that the passwords should be better stored in encrypted forms.
Good. You are wrong, though. Storing encrypted passwords in SQL Server is as bad as storing them plain text. Hashing is not encryption.
::But how to encrypt it in SQL Server.
Why should you?
Hash (not encrypt) the passwords on the website, then store he hashed passwords in the server.
In the SQL only ask for the user's data by user name, retrieve the password hash from the server, hash the user input and compare. Do not forget to salt your hashes, as otherwise you are totally open to a dictionary attack.
::I am new in web development.
Not to development in general? Sounds more like this. I would suggest you invest heavily into some books.|||First of all you should be aware that the case-sensitivity settings are GLOBAL to the entire SQL Server and all databases on it.
If you really want to to the change you have to rebuild the master database using
Rebuildm.exe.
Do look it up in the books online first, and don't forget to backup your database before!
For hashing password have a look at the classes:
System.Security.Cryptography.MD5
or preferrably
System.Security.Cryptography.SHA1
Case sensitive/insensitive
Hi, I configure the SQL Server as case-insensitive. Can I configure a particular table's column to be case-sensitive
Thank you.During the instalation you can choose the default collation for all
databases, for a particular database you can define this durin the database
creation process.
Look at bol for COLLATE clause.
HTH
Wandenkolk T. Neto
MCSE, MCDBA, MCP
"Keith" <anonymous@.discussions.microsoft.com> wrote in message
news:10435BC6-7691-4655-8D52-CAD92F45FBD0@.microsoft.com...
> Hi, I configure the SQL Server as case-insensitive. Can I configure a
particular table's column to be case-sensitive?
> Thank you.|||To add to Wandenkolk's response, in SQL Server 2000 you can specify
COLLATE at the column level to control case sensitivity.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Keith" <anonymous@.discussions.microsoft.com> wrote in message
news:10435BC6-7691-4655-8D52-CAD92F45FBD0@.microsoft.com...
> Hi, I configure the SQL Server as case-insensitive. Can I configure a
particular table's column to be case-sensitive?
> Thank you.|||I was also wondering about case senitivity on columns only.
Under EM on each table I found where I can change the collate... but
there is about 50 different ones listed. Where can I find out about
the differences. Mine is currently set to
"SQL_Latin1_General_CP1_CI_AI"
Whatever that is...
Al.
On Sun, 16 Nov 2003 21:47:25 -0600, "Dan Guzman"
<danguzman@.nospam-earthlink.net> wrote:
>To add to Wandenkolk's response, in SQL Server 2000 you can specify
>COLLATE at the column level to control case sensitivity.|||You can get a list of available collations with fn_helpcollations:
SELECT * FROM ::fn_helpcollations()
This is described in the COLLATE topic in the Books Online
<tsqlref.chm::/ts_ca-co_5z55.htm>.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Harag" <harag@.REMOVETHESECAPITALSsofthome.net> wrote in message
news:7m7hrv4toku4433mpmfqf343ultn44ntuu@.4ax.com...
> I was also wondering about case senitivity on columns only.
> Under EM on each table I found where I can change the collate... but
> there is about 50 different ones listed. Where can I find out about
> the differences. Mine is currently set to
> "SQL_Latin1_General_CP1_CI_AI"
> Whatever that is...
> Al.
> On Sun, 16 Nov 2003 21:47:25 -0600, "Dan Guzman"
> <danguzman@.nospam-earthlink.net> wrote:
> >To add to Wandenkolk's response, in SQL Server 2000 you can specify
> >COLLATE at the column level to control case sensitivity.
>sql
Thank you.During the instalation you can choose the default collation for all
databases, for a particular database you can define this durin the database
creation process.
Look at bol for COLLATE clause.
HTH
Wandenkolk T. Neto
MCSE, MCDBA, MCP
"Keith" <anonymous@.discussions.microsoft.com> wrote in message
news:10435BC6-7691-4655-8D52-CAD92F45FBD0@.microsoft.com...
> Hi, I configure the SQL Server as case-insensitive. Can I configure a
particular table's column to be case-sensitive?
> Thank you.|||To add to Wandenkolk's response, in SQL Server 2000 you can specify
COLLATE at the column level to control case sensitivity.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Keith" <anonymous@.discussions.microsoft.com> wrote in message
news:10435BC6-7691-4655-8D52-CAD92F45FBD0@.microsoft.com...
> Hi, I configure the SQL Server as case-insensitive. Can I configure a
particular table's column to be case-sensitive?
> Thank you.|||I was also wondering about case senitivity on columns only.
Under EM on each table I found where I can change the collate... but
there is about 50 different ones listed. Where can I find out about
the differences. Mine is currently set to
"SQL_Latin1_General_CP1_CI_AI"
Whatever that is...
Al.
On Sun, 16 Nov 2003 21:47:25 -0600, "Dan Guzman"
<danguzman@.nospam-earthlink.net> wrote:
>To add to Wandenkolk's response, in SQL Server 2000 you can specify
>COLLATE at the column level to control case sensitivity.|||You can get a list of available collations with fn_helpcollations:
SELECT * FROM ::fn_helpcollations()
This is described in the COLLATE topic in the Books Online
<tsqlref.chm::/ts_ca-co_5z55.htm>.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Harag" <harag@.REMOVETHESECAPITALSsofthome.net> wrote in message
news:7m7hrv4toku4433mpmfqf343ultn44ntuu@.4ax.com...
> I was also wondering about case senitivity on columns only.
> Under EM on each table I found where I can change the collate... but
> there is about 50 different ones listed. Where can I find out about
> the differences. Mine is currently set to
> "SQL_Latin1_General_CP1_CI_AI"
> Whatever that is...
> Al.
> On Sun, 16 Nov 2003 21:47:25 -0600, "Dan Guzman"
> <danguzman@.nospam-earthlink.net> wrote:
> >To add to Wandenkolk's response, in SQL Server 2000 you can specify
> >COLLATE at the column level to control case sensitivity.
>sql
Case Sensitive vs Insensitive
After all the pain I've been going through with code pages and collation, I was asked how, when sql server does it's joins and predicate searches, how does it actual (internals now) know the an "A" = "a" in an insensitive search?
I didn't have the answer.
Damn, Now I really have to pick up Kelans book.I've seen tricks suchs as uppercasing or lowercasing both prior the compare. I think formatting routines such as these can be very quick because they only have lookup's to do.|||Well, the questions stems from an insensetive server
You don't need to use CASE functions in this case
"A" = "a"
"A" = "A"
"a" = "A", and
"a" = "a"
Internally each has it's on ASCII represenation.
How does an insesitive ("the big jerk") server resolve this?|||Perhaps it "ands" 32 to both characters if either are in the ASCII range 65-90? Probably a number of ways of doing it. Is this one of those questions where an IT Director thinks he has the better of you, because he found a question you can not answer?|||Perhaps it "ands" 32 to both characters if either are in the ASCII range 65-90? Probably a number of ways of doing it. Is this one of those questions where an IT Director thinks he has the better of you, because he found a question you can not answer?
No, they don't know, and because DB2 OS/390 is case sensitive, he didn't understand...and because I've got sql boxes built six ways to sunday, it's been bugging me...and that's how it arose...
But now it's bugging me as well...|||helluva question - 1st i'd smack the guy who asked.
it's just like asking how does SQL server know 1 != 2?
not a technically valid question, IMHO, but thats way the cookie crumbles sometimes, i reckon.
my understanding is that collation is a 3 legged stool
the sort order + the code page + the character set or "dictionary".
if the sort order is binary, my personal favorite,
its pretty simple: the bit pattern mapped to 'a' is not the same as 'A'. what ends up writing to disk is simply as different between 'a' and 'A' as it is between '1' and '2'. it knows 'a' is not 'A' because they are completely differnt patterns stored on the disk.
i dont think i've ever totally understood how non-binary case-sensitive sort orders know the difference - but i do know it involves 'interrogating' the character set dictionary at a very very low level to get the info it needs - which is why it is a noticably slower way to store/join/compare your data.
both binary and dictionary sorts 'technically' use bit patterns, but sometimes a particular character set stored in non-binary can have funky characters and stuff that make it not "always" work.
in either scenario, depending on the level of the person asking the question - i'd think the bit pattern explanation is close enough for gov't work, after the back-hand, of course.|||I think ascii comparisons are from the old days. If case-insensitive is set, I would think it'll look at the codepage and have the codepage decide which character is its uppercase or lowercase equivalent.|||helluva question - 1st i'd smack the guy who asked.
Now do you seriously think I'd entertain this question from someone who didn't SIGN my check?
Anyway, it'a valid question, and from what ya'll (no I'm not from the south) have mentioned, it makes a lot of sense, and was thinking in those terms...
Still gotta google some details...
Thanks|||LOL - i know what you mean.
Gotta make that mortgage payment.|||The problem is that the actual process isn't that simple. There is bitmap that shows which charcters are mapped, and a hash of arrays that allows each unicode character set to "remap" characters as needed. Each array contains the hash signature, a value for comparison, and a value for sorting. Most characters don't have lookup entries in the hash, so they use the actual binary value.
Aren't you glad that you asked?
-PatP|||hey pat -
does the 'array' you refer to only store the unicode, and special non-unicode characters for languages that use them (tilde, etc)? or all characters?
sounds as though the premise that the code page has all the info in it and SQL services communicate with its contents for sorting and comparison rules is the basic idea for dictionary sorts - or it uses the binary value directly for binary sorts.
is that your take, at a high level?|||The hash is the actual lookup mechanism. The array that is returned from the hash contains values that are used for comparisons and sorting.
You can think of it something like this: When processing a character, it gets expanded to 16 bits if it wasn't already, then that 16 bit value is used as a lookup into the bitmask to see if this character gets special processing (most don't). If it needs processing, a hash lookup is done which returns the character itself, the value to use for comparison purposes, and a value to use for sorting purposes. If the character didn't need processing, it is copied into all three values in an empty array.
-PatP|||thanks!
'splains a little better why binary sorts are mo'better, IMO.
they need no translation and/or re-sorting.
of course, some users expect charater ordering to work like it did for them in 3rd grade, but, eh, NMFP. the database is faster, that IS my problem.
i'm starting to think i'm an evil dba...
:confused:|||Once you get past the surface...it's all about the internals and undersatanding how things work...
Thanks again Pat...
(He has a signed copy of Kelans book no doubt)|||quite true.
there's so much to learn i'll be busy the rest of my life.
might be useful for this thread and/or compiling a detailed answer for your bossman:
master..syscharsets is where SQL server goes to get the binary definitions for character sets and sort orders.
peace.|||(He has a signed copy of Kelans book no doubt)That I do! If you mean Kalen's Inside Microsoft SQL Server 2000 (http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=UM7Mwdc9jn&isbn=0735609985&itm=1).
-PatP
I didn't have the answer.
Damn, Now I really have to pick up Kelans book.I've seen tricks suchs as uppercasing or lowercasing both prior the compare. I think formatting routines such as these can be very quick because they only have lookup's to do.|||Well, the questions stems from an insensetive server
You don't need to use CASE functions in this case
"A" = "a"
"A" = "A"
"a" = "A", and
"a" = "a"
Internally each has it's on ASCII represenation.
How does an insesitive ("the big jerk") server resolve this?|||Perhaps it "ands" 32 to both characters if either are in the ASCII range 65-90? Probably a number of ways of doing it. Is this one of those questions where an IT Director thinks he has the better of you, because he found a question you can not answer?|||Perhaps it "ands" 32 to both characters if either are in the ASCII range 65-90? Probably a number of ways of doing it. Is this one of those questions where an IT Director thinks he has the better of you, because he found a question you can not answer?
No, they don't know, and because DB2 OS/390 is case sensitive, he didn't understand...and because I've got sql boxes built six ways to sunday, it's been bugging me...and that's how it arose...
But now it's bugging me as well...|||helluva question - 1st i'd smack the guy who asked.
it's just like asking how does SQL server know 1 != 2?
not a technically valid question, IMHO, but thats way the cookie crumbles sometimes, i reckon.
my understanding is that collation is a 3 legged stool
the sort order + the code page + the character set or "dictionary".
if the sort order is binary, my personal favorite,
its pretty simple: the bit pattern mapped to 'a' is not the same as 'A'. what ends up writing to disk is simply as different between 'a' and 'A' as it is between '1' and '2'. it knows 'a' is not 'A' because they are completely differnt patterns stored on the disk.
i dont think i've ever totally understood how non-binary case-sensitive sort orders know the difference - but i do know it involves 'interrogating' the character set dictionary at a very very low level to get the info it needs - which is why it is a noticably slower way to store/join/compare your data.
both binary and dictionary sorts 'technically' use bit patterns, but sometimes a particular character set stored in non-binary can have funky characters and stuff that make it not "always" work.
in either scenario, depending on the level of the person asking the question - i'd think the bit pattern explanation is close enough for gov't work, after the back-hand, of course.|||I think ascii comparisons are from the old days. If case-insensitive is set, I would think it'll look at the codepage and have the codepage decide which character is its uppercase or lowercase equivalent.|||helluva question - 1st i'd smack the guy who asked.
Now do you seriously think I'd entertain this question from someone who didn't SIGN my check?
Anyway, it'a valid question, and from what ya'll (no I'm not from the south) have mentioned, it makes a lot of sense, and was thinking in those terms...
Still gotta google some details...
Thanks|||LOL - i know what you mean.
Gotta make that mortgage payment.|||The problem is that the actual process isn't that simple. There is bitmap that shows which charcters are mapped, and a hash of arrays that allows each unicode character set to "remap" characters as needed. Each array contains the hash signature, a value for comparison, and a value for sorting. Most characters don't have lookup entries in the hash, so they use the actual binary value.
Aren't you glad that you asked?
-PatP|||hey pat -
does the 'array' you refer to only store the unicode, and special non-unicode characters for languages that use them (tilde, etc)? or all characters?
sounds as though the premise that the code page has all the info in it and SQL services communicate with its contents for sorting and comparison rules is the basic idea for dictionary sorts - or it uses the binary value directly for binary sorts.
is that your take, at a high level?|||The hash is the actual lookup mechanism. The array that is returned from the hash contains values that are used for comparisons and sorting.
You can think of it something like this: When processing a character, it gets expanded to 16 bits if it wasn't already, then that 16 bit value is used as a lookup into the bitmask to see if this character gets special processing (most don't). If it needs processing, a hash lookup is done which returns the character itself, the value to use for comparison purposes, and a value to use for sorting purposes. If the character didn't need processing, it is copied into all three values in an empty array.
-PatP|||thanks!
'splains a little better why binary sorts are mo'better, IMO.
they need no translation and/or re-sorting.
of course, some users expect charater ordering to work like it did for them in 3rd grade, but, eh, NMFP. the database is faster, that IS my problem.
i'm starting to think i'm an evil dba...
:confused:|||Once you get past the surface...it's all about the internals and undersatanding how things work...
Thanks again Pat...
(He has a signed copy of Kelans book no doubt)|||quite true.
there's so much to learn i'll be busy the rest of my life.
might be useful for this thread and/or compiling a detailed answer for your bossman:
master..syscharsets is where SQL server goes to get the binary definitions for character sets and sort orders.
peace.|||(He has a signed copy of Kelans book no doubt)That I do! If you mean Kalen's Inside Microsoft SQL Server 2000 (http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=UM7Mwdc9jn&isbn=0735609985&itm=1).
-PatP
Case sensitive variables SQL Express
Hi,
I suddenly ran into a serious problem. We installed a new SQL Express 2005
SP1 on a different server and now it seems to be case sensitive with the
variables in stored procedures:
----
ALTER PROCEDURE [dbo].[GetUserID] (@.UserName varchar(50))
AS
BEGIN
DECLARE @.ret bigint;
SET NOCOUNT ON;
SELECT @.ret = UserID FROM Users WHERE UserName Like @.Username;
RETURN ISNULL(@.ret,-1);
END
----
It fails on the @.Username which is declared as @.UserName
This did not happen on our other SQL Express server. It does not have SP1
either.
Please help!!
DavidHi David,
Most likely your problem is related to different instance collation on the
different servers. All identifiers for variables, GOTO labels and temp
tables are in the default collation of the instance. Probably your new
instance of SQL Server Express is installed with a case sensitive collation.
Here is how to change it if needed:
http://msdn2.microsoft.com/en-us/library/ms179254.aspx
HTH,
Plamen Ratchev
http://www.SQLStudio.com
I suddenly ran into a serious problem. We installed a new SQL Express 2005
SP1 on a different server and now it seems to be case sensitive with the
variables in stored procedures:
----
ALTER PROCEDURE [dbo].[GetUserID] (@.UserName varchar(50))
AS
BEGIN
DECLARE @.ret bigint;
SET NOCOUNT ON;
SELECT @.ret = UserID FROM Users WHERE UserName Like @.Username;
RETURN ISNULL(@.ret,-1);
END
----
It fails on the @.Username which is declared as @.UserName
This did not happen on our other SQL Express server. It does not have SP1
either.
Please help!!
DavidHi David,
Most likely your problem is related to different instance collation on the
different servers. All identifiers for variables, GOTO labels and temp
tables are in the default collation of the instance. Probably your new
instance of SQL Server Express is installed with a case sensitive collation.
Here is how to change it if needed:
http://msdn2.microsoft.com/en-us/library/ms179254.aspx
HTH,
Plamen Ratchev
http://www.SQLStudio.com
Case sensitive variables SQL Express
Hi,
I suddenly ran into a serious problem. We installed a new SQL Express 2005
SP1 on a different server and now it seems to be case sensitive with the
variables in stored procedures:
----
ALTER PROCEDURE [dbo].[GetUserID] (@.UserName varchar(50))
AS
BEGIN
DECLARE @.ret bigint;
SET NOCOUNT ON;
SELECT @.ret = UserID FROM Users WHERE UserName Like @.Username;
RETURN ISNULL(@.ret,-1);
END
----
It fails on the @.Username which is declared as @.UserName
This did not happen on our other SQL Express server. It does not have SP1
either.
Please help!!
DavidHi David,
Most likely your problem is related to different instance collation on the
different servers. All identifiers for variables, GOTO labels and temp
tables are in the default collation of the instance. Probably your new
instance of SQL Server Express is installed with a case sensitive collation.
Here is how to change it if needed:
http://msdn2.microsoft.com/en-us/library/ms179254.aspx
HTH,
Plamen Ratchev
http://www.SQLStudio.com
I suddenly ran into a serious problem. We installed a new SQL Express 2005
SP1 on a different server and now it seems to be case sensitive with the
variables in stored procedures:
----
ALTER PROCEDURE [dbo].[GetUserID] (@.UserName varchar(50))
AS
BEGIN
DECLARE @.ret bigint;
SET NOCOUNT ON;
SELECT @.ret = UserID FROM Users WHERE UserName Like @.Username;
RETURN ISNULL(@.ret,-1);
END
----
It fails on the @.Username which is declared as @.UserName
This did not happen on our other SQL Express server. It does not have SP1
either.
Please help!!
DavidHi David,
Most likely your problem is related to different instance collation on the
different servers. All identifiers for variables, GOTO labels and temp
tables are in the default collation of the instance. Probably your new
instance of SQL Server Express is installed with a case sensitive collation.
Here is how to change it if needed:
http://msdn2.microsoft.com/en-us/library/ms179254.aspx
HTH,
Plamen Ratchev
http://www.SQLStudio.com
Case Sensitive VarChar comparsion???
Hi all,
I wonder if it is possible to use case sensitive varchar comparsion in a query?
As I have tried and the varchar comparsion is non-case sensitive.
Thanks
MEif (convert (varbinary,'Enigma')= convert (varbinary,'enigma'))
Print 'Done'
else
Print 'Not Done'
if (convert (varbinary,'Enigma')= convert (varbinary,'Enigma'))
Print 'Done'
else
Print 'Not Done'|||For a case-sensitive compare, I'd use:IF 'PatP' = 'patp' COLLATE SQL_Latin1_General_CP1250_CS_AS
PRINT 'Match'
ELSE
PRINT 'Different'You can get a list of collations that a given server will support using:SELECT *
FROM ::fn_helpcollations()-PatP|||Pat ... any reason you would not recommend a cast to varbinary|||Two reasons.
First of all, if you want a string compare, I think you should do a string compare... There are lots of ways to compare strings, and keeping that flexibility is important to me. Today the users want case sensitivity, tomorrow they'll want the strings ordered using the Spanish dictionary, which is easy using a collation.
Second, casting to BINARY has a cost, and in large jobs that cost can get high. Why incur overhead that gives no real benefit?
-PatP|||Thanks ...
Learned something new again ...
I wonder if it is possible to use case sensitive varchar comparsion in a query?
As I have tried and the varchar comparsion is non-case sensitive.
Thanks
MEif (convert (varbinary,'Enigma')= convert (varbinary,'enigma'))
Print 'Done'
else
Print 'Not Done'
if (convert (varbinary,'Enigma')= convert (varbinary,'Enigma'))
Print 'Done'
else
Print 'Not Done'|||For a case-sensitive compare, I'd use:IF 'PatP' = 'patp' COLLATE SQL_Latin1_General_CP1250_CS_AS
PRINT 'Match'
ELSE
PRINT 'Different'You can get a list of collations that a given server will support using:SELECT *
FROM ::fn_helpcollations()-PatP|||Pat ... any reason you would not recommend a cast to varbinary|||Two reasons.
First of all, if you want a string compare, I think you should do a string compare... There are lots of ways to compare strings, and keeping that flexibility is important to me. Today the users want case sensitivity, tomorrow they'll want the strings ordered using the Spanish dictionary, which is easy using a collation.
Second, casting to BINARY has a cost, and in large jobs that cost can get high. Why incur overhead that gives no real benefit?
-PatP|||Thanks ...
Learned something new again ...
case sensitive to case insensitive
I've just restored a db (which relies on CASE INSENSITIVE) to another db
server which happens to have been set to CASE SENSITIVE.
Is it possible to change the settings of just 1 db within the overall sql
server instance, to allow it to continue as case insensitive.?
How can I get around this problem?
Thanks
Jack
ALTER DATABASE ..,statement allows you to change a collation , however
only on new created objects
Can you re-intsall the instance?
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:f96dnRzj3NqRq6vYnZ2dnUVZ8tadnZ2d@.bt.com...
> I've just restored a db (which relies on CASE INSENSITIVE) to another db
> server which happens to have been set to CASE SENSITIVE.
> Is it possible to change the settings of just 1 db within the overall sql
> server instance, to allow it to continue as case insensitive.?
> How can I get around this problem?
> Thanks
>
>
|||Hi
At the moment I can't reinstall the instance.
Are you saying with the ALTER DATABASE that I can retain the case
insensitive of the 1 db , within the Server which is set at CASE SENSITIVE ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uHxMzBs8GHA.1496@.TK2MSFTNGP05.phx.gbl...
> Jack
> ALTER DATABASE ..,statement allows you to change a collation , however
> only on new created objects
> Can you re-intsall the instance?
>
>
> "Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
> news:f96dnRzj3NqRq6vYnZ2dnUVZ8tadnZ2d@.bt.com...
>
|||Jack
I said that only new cteated objects will have a new collation
(insensitive)
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:PLCdneW5la6cpKvYnZ2dnUVZ8s2dnZ2d@.bt.com...
> Hi
> At the moment I can't reinstall the instance.
> Are you saying with the ALTER DATABASE that I can retain the case
> insensitive of the 1 db , within the Server which is set at CASE SENSITIVE
> ?
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uHxMzBs8GHA.1496@.TK2MSFTNGP05.phx.gbl...
>
|||Do you know of any way apart from reinstalling the server , which would
allow the previously installed objects to retain their collation ?
For example, instead of doing a BACKUP/RESTORE , is it possible to create
the DB with the relevant collation and then transfer the objects over?
Thanks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OESvuLs8GHA.1496@.TK2MSFTNGP05.phx.gbl...
> Jack
> I said that only new cteated objects will have a new collation
> (insensitive)
> "Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
> news:PLCdneW5la6cpKvYnZ2dnUVZ8s2dnZ2d@.bt.com...
>
|||Jack
> Do you know of any way apart from reinstalling the server , which would
> allow the previously installed objects to retain their collation ?
It is not an easy task , because you need to rebuild indexes on the table
as well
> For example, instead of doing a BACKUP/RESTORE , is it possible to create
> the DB with the relevant collation and then transfer the objects over?
Yes , create a database .. COLLATE ... (see in the bol for details)
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:RqednUq0eZUz3KvYRVnygA@.bt.com...
> Do you know of any way apart from reinstalling the server , which would
> allow the previously installed objects to retain their collation ?
> For example, instead of doing a BACKUP/RESTORE , is it possible to create
> the DB with the relevant collation and then transfer the objects over?
> Thanks
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OESvuLs8GHA.1496@.TK2MSFTNGP05.phx.gbl...
>
sql
server which happens to have been set to CASE SENSITIVE.
Is it possible to change the settings of just 1 db within the overall sql
server instance, to allow it to continue as case insensitive.?
How can I get around this problem?
Thanks
Jack
ALTER DATABASE ..,statement allows you to change a collation , however
only on new created objects
Can you re-intsall the instance?
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:f96dnRzj3NqRq6vYnZ2dnUVZ8tadnZ2d@.bt.com...
> I've just restored a db (which relies on CASE INSENSITIVE) to another db
> server which happens to have been set to CASE SENSITIVE.
> Is it possible to change the settings of just 1 db within the overall sql
> server instance, to allow it to continue as case insensitive.?
> How can I get around this problem?
> Thanks
>
>
|||Hi
At the moment I can't reinstall the instance.
Are you saying with the ALTER DATABASE that I can retain the case
insensitive of the 1 db , within the Server which is set at CASE SENSITIVE ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uHxMzBs8GHA.1496@.TK2MSFTNGP05.phx.gbl...
> Jack
> ALTER DATABASE ..,statement allows you to change a collation , however
> only on new created objects
> Can you re-intsall the instance?
>
>
> "Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
> news:f96dnRzj3NqRq6vYnZ2dnUVZ8tadnZ2d@.bt.com...
>
|||Jack
I said that only new cteated objects will have a new collation
(insensitive)
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:PLCdneW5la6cpKvYnZ2dnUVZ8s2dnZ2d@.bt.com...
> Hi
> At the moment I can't reinstall the instance.
> Are you saying with the ALTER DATABASE that I can retain the case
> insensitive of the 1 db , within the Server which is set at CASE SENSITIVE
> ?
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uHxMzBs8GHA.1496@.TK2MSFTNGP05.phx.gbl...
>
|||Do you know of any way apart from reinstalling the server , which would
allow the previously installed objects to retain their collation ?
For example, instead of doing a BACKUP/RESTORE , is it possible to create
the DB with the relevant collation and then transfer the objects over?
Thanks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OESvuLs8GHA.1496@.TK2MSFTNGP05.phx.gbl...
> Jack
> I said that only new cteated objects will have a new collation
> (insensitive)
> "Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
> news:PLCdneW5la6cpKvYnZ2dnUVZ8s2dnZ2d@.bt.com...
>
|||Jack
> Do you know of any way apart from reinstalling the server , which would
> allow the previously installed objects to retain their collation ?
It is not an easy task , because you need to rebuild indexes on the table
as well
> For example, instead of doing a BACKUP/RESTORE , is it possible to create
> the DB with the relevant collation and then transfer the objects over?
Yes , create a database .. COLLATE ... (see in the bol for details)
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:RqednUq0eZUz3KvYRVnygA@.bt.com...
> Do you know of any way apart from reinstalling the server , which would
> allow the previously installed objects to retain their collation ?
> For example, instead of doing a BACKUP/RESTORE , is it possible to create
> the DB with the relevant collation and then transfer the objects over?
> Thanks
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OESvuLs8GHA.1496@.TK2MSFTNGP05.phx.gbl...
>
sql
case sensitive to case insensitive
I've just restored a db (which relies on CASE INSENSITIVE) to another db
server which happens to have been set to CASE SENSITIVE.
Is it possible to change the settings of just 1 db within the overall sql
server instance, to allow it to continue as case insensitive.?
How can I get around this problem?
ThanksJack
ALTER DATABASE ..,statement allows you to change a collation , however
only on new created objects
Can you re-intsall the instance?
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:f96dnRzj3NqRq6vYnZ2dnUVZ8tadnZ2d@.bt
.com...
> I've just restored a db (which relies on CASE INSENSITIVE) to another db
> server which happens to have been set to CASE SENSITIVE.
> Is it possible to change the settings of just 1 db within the overall sql
> server instance, to allow it to continue as case insensitive.?
> How can I get around this problem?
> Thanks
>
>|||Hi
At the moment I can't reinstall the instance.
Are you saying with the ALTER DATABASE that I can retain the case
insensitive of the 1 db , within the Server which is set at CASE SENSITIVE ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uHxMzBs8GHA.1496@.TK2MSFTNGP05.phx.gbl...
> Jack
> ALTER DATABASE ..,statement allows you to change a collation , however
> only on new created objects
> Can you re-intsall the instance?
>
>
> "Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
> news:f96dnRzj3NqRq6vYnZ2dnUVZ8tadnZ2d@.bt
.com...
>|||Jack
I said that only new cteated objects will have a new collation
(insensitive)
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:PLCdneW5la6cpKvYnZ2dnUVZ8s2dnZ2d@.bt
.com...
> Hi
> At the moment I can't reinstall the instance.
> Are you saying with the ALTER DATABASE that I can retain the case
> insensitive of the 1 db , within the Server which is set at CASE SENSITIVE
> ?
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uHxMzBs8GHA.1496@.TK2MSFTNGP05.phx.gbl...
>|||Do you know of any way apart from reinstalling the server , which would
allow the previously installed objects to retain their collation ?
For example, instead of doing a BACKUP/RESTORE , is it possible to create
the DB with the relevant collation and then transfer the objects over?
Thanks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OESvuLs8GHA.1496@.TK2MSFTNGP05.phx.gbl...
> Jack
> I said that only new cteated objects will have a new collation
> (insensitive)
> "Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
> news:PLCdneW5la6cpKvYnZ2dnUVZ8s2dnZ2d@.bt
.com...
>|||Jack
> Do you know of any way apart from reinstalling the server , which would
> allow the previously installed objects to retain their collation ?
It is not an easy task , because you need to rebuild indexes on the table
as well
> For example, instead of doing a BACKUP/RESTORE , is it possible to create
> the DB with the relevant collation and then transfer the objects over?
Yes , create a database .. COLLATE ... (see in the bol for details)
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:RqednUq0eZUz3KvYRVnygA@.bt.com...
> Do you know of any way apart from reinstalling the server , which would
> allow the previously installed objects to retain their collation ?
> For example, instead of doing a BACKUP/RESTORE , is it possible to create
> the DB with the relevant collation and then transfer the objects over?
> Thanks
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OESvuLs8GHA.1496@.TK2MSFTNGP05.phx.gbl...
>
server which happens to have been set to CASE SENSITIVE.
Is it possible to change the settings of just 1 db within the overall sql
server instance, to allow it to continue as case insensitive.?
How can I get around this problem?
ThanksJack
ALTER DATABASE ..,statement allows you to change a collation , however
only on new created objects
Can you re-intsall the instance?
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:f96dnRzj3NqRq6vYnZ2dnUVZ8tadnZ2d@.bt
.com...
> I've just restored a db (which relies on CASE INSENSITIVE) to another db
> server which happens to have been set to CASE SENSITIVE.
> Is it possible to change the settings of just 1 db within the overall sql
> server instance, to allow it to continue as case insensitive.?
> How can I get around this problem?
> Thanks
>
>|||Hi
At the moment I can't reinstall the instance.
Are you saying with the ALTER DATABASE that I can retain the case
insensitive of the 1 db , within the Server which is set at CASE SENSITIVE ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uHxMzBs8GHA.1496@.TK2MSFTNGP05.phx.gbl...
> Jack
> ALTER DATABASE ..,statement allows you to change a collation , however
> only on new created objects
> Can you re-intsall the instance?
>
>
> "Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
> news:f96dnRzj3NqRq6vYnZ2dnUVZ8tadnZ2d@.bt
.com...
>|||Jack
I said that only new cteated objects will have a new collation
(insensitive)
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:PLCdneW5la6cpKvYnZ2dnUVZ8s2dnZ2d@.bt
.com...
> Hi
> At the moment I can't reinstall the instance.
> Are you saying with the ALTER DATABASE that I can retain the case
> insensitive of the 1 db , within the Server which is set at CASE SENSITIVE
> ?
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uHxMzBs8GHA.1496@.TK2MSFTNGP05.phx.gbl...
>|||Do you know of any way apart from reinstalling the server , which would
allow the previously installed objects to retain their collation ?
For example, instead of doing a BACKUP/RESTORE , is it possible to create
the DB with the relevant collation and then transfer the objects over?
Thanks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OESvuLs8GHA.1496@.TK2MSFTNGP05.phx.gbl...
> Jack
> I said that only new cteated objects will have a new collation
> (insensitive)
> "Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
> news:PLCdneW5la6cpKvYnZ2dnUVZ8s2dnZ2d@.bt
.com...
>|||Jack
> Do you know of any way apart from reinstalling the server , which would
> allow the previously installed objects to retain their collation ?
It is not an easy task , because you need to rebuild indexes on the table
as well
> For example, instead of doing a BACKUP/RESTORE , is it possible to create
> the DB with the relevant collation and then transfer the objects over?
Yes , create a database .. COLLATE ... (see in the bol for details)
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:RqednUq0eZUz3KvYRVnygA@.bt.com...
> Do you know of any way apart from reinstalling the server , which would
> allow the previously installed objects to retain their collation ?
> For example, instead of doing a BACKUP/RESTORE , is it possible to create
> the DB with the relevant collation and then transfer the objects over?
> Thanks
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OESvuLs8GHA.1496@.TK2MSFTNGP05.phx.gbl...
>
case sensitive to case insensitive
I've just restored a db (which relies on CASE INSENSITIVE) to another db
server which happens to have been set to CASE SENSITIVE.
Is it possible to change the settings of just 1 db within the overall sql
server instance, to allow it to continue as case insensitive.?
How can I get around this problem?
ThanksJack
ALTER DATABASE ..,statement allows you to change a collation , however
only on new created objects
Can you re-intsall the instance?
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:f96dnRzj3NqRq6vYnZ2dnUVZ8tadnZ2d@.bt.com...
> I've just restored a db (which relies on CASE INSENSITIVE) to another db
> server which happens to have been set to CASE SENSITIVE.
> Is it possible to change the settings of just 1 db within the overall sql
> server instance, to allow it to continue as case insensitive.?
> How can I get around this problem?
> Thanks
>
>|||Hi
At the moment I can't reinstall the instance.
Are you saying with the ALTER DATABASE that I can retain the case
insensitive of the 1 db , within the Server which is set at CASE SENSITIVE ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uHxMzBs8GHA.1496@.TK2MSFTNGP05.phx.gbl...
> Jack
> ALTER DATABASE ..,statement allows you to change a collation , however
> only on new created objects
> Can you re-intsall the instance?
>
>
> "Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
> news:f96dnRzj3NqRq6vYnZ2dnUVZ8tadnZ2d@.bt.com...
>> I've just restored a db (which relies on CASE INSENSITIVE) to another db
>> server which happens to have been set to CASE SENSITIVE.
>> Is it possible to change the settings of just 1 db within the overall sql
>> server instance, to allow it to continue as case insensitive.?
>> How can I get around this problem?
>> Thanks
>>
>|||Jack
I said that only new cteated objects will have a new collation
(insensitive)
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:PLCdneW5la6cpKvYnZ2dnUVZ8s2dnZ2d@.bt.com...
> Hi
> At the moment I can't reinstall the instance.
> Are you saying with the ALTER DATABASE that I can retain the case
> insensitive of the 1 db , within the Server which is set at CASE SENSITIVE
> ?
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uHxMzBs8GHA.1496@.TK2MSFTNGP05.phx.gbl...
>> Jack
>> ALTER DATABASE ..,statement allows you to change a collation , however
>> only on new created objects
>> Can you re-intsall the instance?
>>
>>
>> "Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
>> news:f96dnRzj3NqRq6vYnZ2dnUVZ8tadnZ2d@.bt.com...
>> I've just restored a db (which relies on CASE INSENSITIVE) to another db
>> server which happens to have been set to CASE SENSITIVE.
>> Is it possible to change the settings of just 1 db within the overall
>> sql server instance, to allow it to continue as case insensitive.?
>> How can I get around this problem?
>> Thanks
>>
>>
>|||Do you know of any way apart from reinstalling the server , which would
allow the previously installed objects to retain their collation ?
For example, instead of doing a BACKUP/RESTORE , is it possible to create
the DB with the relevant collation and then transfer the objects over?
Thanks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OESvuLs8GHA.1496@.TK2MSFTNGP05.phx.gbl...
> Jack
> I said that only new cteated objects will have a new collation
> (insensitive)
> "Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
> news:PLCdneW5la6cpKvYnZ2dnUVZ8s2dnZ2d@.bt.com...
>> Hi
>> At the moment I can't reinstall the instance.
>> Are you saying with the ALTER DATABASE that I can retain the case
>> insensitive of the 1 db , within the Server which is set at CASE
>> SENSITIVE ?
>>
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:uHxMzBs8GHA.1496@.TK2MSFTNGP05.phx.gbl...
>> Jack
>> ALTER DATABASE ..,statement allows you to change a collation ,
>> however only on new created objects
>> Can you re-intsall the instance?
>>
>>
>> "Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
>> news:f96dnRzj3NqRq6vYnZ2dnUVZ8tadnZ2d@.bt.com...
>> I've just restored a db (which relies on CASE INSENSITIVE) to another
>> db server which happens to have been set to CASE SENSITIVE.
>> Is it possible to change the settings of just 1 db within the overall
>> sql server instance, to allow it to continue as case insensitive.?
>> How can I get around this problem?
>> Thanks
>>
>>
>>
>|||Jack
> Do you know of any way apart from reinstalling the server , which would
> allow the previously installed objects to retain their collation ?
It is not an easy task , because you need to rebuild indexes on the table
as well
> For example, instead of doing a BACKUP/RESTORE , is it possible to create
> the DB with the relevant collation and then transfer the objects over?
Yes , create a database .. COLLATE ... (see in the bol for details)
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:RqednUq0eZUz3KvYRVnygA@.bt.com...
> Do you know of any way apart from reinstalling the server , which would
> allow the previously installed objects to retain their collation ?
> For example, instead of doing a BACKUP/RESTORE , is it possible to create
> the DB with the relevant collation and then transfer the objects over?
> Thanks
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OESvuLs8GHA.1496@.TK2MSFTNGP05.phx.gbl...
>> Jack
>> I said that only new cteated objects will have a new collation
>> (insensitive)
>> "Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
>> news:PLCdneW5la6cpKvYnZ2dnUVZ8s2dnZ2d@.bt.com...
>> Hi
>> At the moment I can't reinstall the instance.
>> Are you saying with the ALTER DATABASE that I can retain the case
>> insensitive of the 1 db , within the Server which is set at CASE
>> SENSITIVE ?
>>
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:uHxMzBs8GHA.1496@.TK2MSFTNGP05.phx.gbl...
>> Jack
>> ALTER DATABASE ..,statement allows you to change a collation ,
>> however only on new created objects
>> Can you re-intsall the instance?
>>
>>
>> "Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
>> news:f96dnRzj3NqRq6vYnZ2dnUVZ8tadnZ2d@.bt.com...
>> I've just restored a db (which relies on CASE INSENSITIVE) to another
>> db server which happens to have been set to CASE SENSITIVE.
>> Is it possible to change the settings of just 1 db within the overall
>> sql server instance, to allow it to continue as case insensitive.?
>> How can I get around this problem?
>> Thanks
>>
>>
>>
>>
>
server which happens to have been set to CASE SENSITIVE.
Is it possible to change the settings of just 1 db within the overall sql
server instance, to allow it to continue as case insensitive.?
How can I get around this problem?
ThanksJack
ALTER DATABASE ..,statement allows you to change a collation , however
only on new created objects
Can you re-intsall the instance?
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:f96dnRzj3NqRq6vYnZ2dnUVZ8tadnZ2d@.bt.com...
> I've just restored a db (which relies on CASE INSENSITIVE) to another db
> server which happens to have been set to CASE SENSITIVE.
> Is it possible to change the settings of just 1 db within the overall sql
> server instance, to allow it to continue as case insensitive.?
> How can I get around this problem?
> Thanks
>
>|||Hi
At the moment I can't reinstall the instance.
Are you saying with the ALTER DATABASE that I can retain the case
insensitive of the 1 db , within the Server which is set at CASE SENSITIVE ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uHxMzBs8GHA.1496@.TK2MSFTNGP05.phx.gbl...
> Jack
> ALTER DATABASE ..,statement allows you to change a collation , however
> only on new created objects
> Can you re-intsall the instance?
>
>
> "Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
> news:f96dnRzj3NqRq6vYnZ2dnUVZ8tadnZ2d@.bt.com...
>> I've just restored a db (which relies on CASE INSENSITIVE) to another db
>> server which happens to have been set to CASE SENSITIVE.
>> Is it possible to change the settings of just 1 db within the overall sql
>> server instance, to allow it to continue as case insensitive.?
>> How can I get around this problem?
>> Thanks
>>
>|||Jack
I said that only new cteated objects will have a new collation
(insensitive)
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:PLCdneW5la6cpKvYnZ2dnUVZ8s2dnZ2d@.bt.com...
> Hi
> At the moment I can't reinstall the instance.
> Are you saying with the ALTER DATABASE that I can retain the case
> insensitive of the 1 db , within the Server which is set at CASE SENSITIVE
> ?
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uHxMzBs8GHA.1496@.TK2MSFTNGP05.phx.gbl...
>> Jack
>> ALTER DATABASE ..,statement allows you to change a collation , however
>> only on new created objects
>> Can you re-intsall the instance?
>>
>>
>> "Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
>> news:f96dnRzj3NqRq6vYnZ2dnUVZ8tadnZ2d@.bt.com...
>> I've just restored a db (which relies on CASE INSENSITIVE) to another db
>> server which happens to have been set to CASE SENSITIVE.
>> Is it possible to change the settings of just 1 db within the overall
>> sql server instance, to allow it to continue as case insensitive.?
>> How can I get around this problem?
>> Thanks
>>
>>
>|||Do you know of any way apart from reinstalling the server , which would
allow the previously installed objects to retain their collation ?
For example, instead of doing a BACKUP/RESTORE , is it possible to create
the DB with the relevant collation and then transfer the objects over?
Thanks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OESvuLs8GHA.1496@.TK2MSFTNGP05.phx.gbl...
> Jack
> I said that only new cteated objects will have a new collation
> (insensitive)
> "Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
> news:PLCdneW5la6cpKvYnZ2dnUVZ8s2dnZ2d@.bt.com...
>> Hi
>> At the moment I can't reinstall the instance.
>> Are you saying with the ALTER DATABASE that I can retain the case
>> insensitive of the 1 db , within the Server which is set at CASE
>> SENSITIVE ?
>>
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:uHxMzBs8GHA.1496@.TK2MSFTNGP05.phx.gbl...
>> Jack
>> ALTER DATABASE ..,statement allows you to change a collation ,
>> however only on new created objects
>> Can you re-intsall the instance?
>>
>>
>> "Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
>> news:f96dnRzj3NqRq6vYnZ2dnUVZ8tadnZ2d@.bt.com...
>> I've just restored a db (which relies on CASE INSENSITIVE) to another
>> db server which happens to have been set to CASE SENSITIVE.
>> Is it possible to change the settings of just 1 db within the overall
>> sql server instance, to allow it to continue as case insensitive.?
>> How can I get around this problem?
>> Thanks
>>
>>
>>
>|||Jack
> Do you know of any way apart from reinstalling the server , which would
> allow the previously installed objects to retain their collation ?
It is not an easy task , because you need to rebuild indexes on the table
as well
> For example, instead of doing a BACKUP/RESTORE , is it possible to create
> the DB with the relevant collation and then transfer the objects over?
Yes , create a database .. COLLATE ... (see in the bol for details)
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:RqednUq0eZUz3KvYRVnygA@.bt.com...
> Do you know of any way apart from reinstalling the server , which would
> allow the previously installed objects to retain their collation ?
> For example, instead of doing a BACKUP/RESTORE , is it possible to create
> the DB with the relevant collation and then transfer the objects over?
> Thanks
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OESvuLs8GHA.1496@.TK2MSFTNGP05.phx.gbl...
>> Jack
>> I said that only new cteated objects will have a new collation
>> (insensitive)
>> "Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
>> news:PLCdneW5la6cpKvYnZ2dnUVZ8s2dnZ2d@.bt.com...
>> Hi
>> At the moment I can't reinstall the instance.
>> Are you saying with the ALTER DATABASE that I can retain the case
>> insensitive of the 1 db , within the Server which is set at CASE
>> SENSITIVE ?
>>
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:uHxMzBs8GHA.1496@.TK2MSFTNGP05.phx.gbl...
>> Jack
>> ALTER DATABASE ..,statement allows you to change a collation ,
>> however only on new created objects
>> Can you re-intsall the instance?
>>
>>
>> "Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
>> news:f96dnRzj3NqRq6vYnZ2dnUVZ8tadnZ2d@.bt.com...
>> I've just restored a db (which relies on CASE INSENSITIVE) to another
>> db server which happens to have been set to CASE SENSITIVE.
>> Is it possible to change the settings of just 1 db within the overall
>> sql server instance, to allow it to continue as case insensitive.?
>> How can I get around this problem?
>> Thanks
>>
>>
>>
>>
>
case sensitive sql server
in sql server 2000 or 2003 how can i tell if a database is case sensitive or not??select case when 'a' = 'A' then 'not case sensitive' else 'case sensitive' end|||in sql server 2000 or 2003 how can i tell if a database is case sensitive or not??
sql server 2003?
well, for your question,check this ...
http://vyaskn.tripod.com/case_sensitive_search_in_sql_server.htm
sql server 2003?
well, for your question,check this ...
http://vyaskn.tripod.com/case_sensitive_search_in_sql_server.htm
case sensitive SQL - pls help a noob
I just created my first Asp.net app. I had to install it to a corporate server. What I found is that the corporate SQL Server 2000 was case sensitive in the stored procedures while my installation was not!
How can I set my SQL Server 2000 to be case sensitive as well?Case sensitivity depends on the codepage you select at install.
If you want to do it afterwards you need to rebuild the master table.
See 'Rebuilding the master database' in the SQL Server
books online.
How can I set my SQL Server 2000 to be case sensitive as well?Case sensitivity depends on the codepage you select at install.
If you want to do it afterwards you need to rebuild the master table.
See 'Rebuilding the master database' in the SQL Server
books online.
Regards
Fredr!k
case sensitive sql
Hi
how can i use the sensive case in a select field from table where fild='GhhY' ?
I think you'd have to do character by character comparison using SUBSTRINGs and UPPER/LOWERs unless someone else here has a better solution.|||select * from TABLE where cast(fild as varbinary) = cast('GhhY' as varbinary)
Nicksql
how can i use the sensive case in a select field from table where fild='GhhY' ?
I think you'd have to do character by character comparison using SUBSTRINGs and UPPER/LOWERs unless someone else here has a better solution.|||select * from TABLE where cast(fild as varbinary) = cast('GhhY' as varbinary)
Nicksql
case sensitive server - problems running scripts
I have just been given a new SQL Server 2000 box to look after in production. I just tried to run a standard t-sql script I use for setting up backup jobs and so on. However, it failed with a long list of errors - quite a surprise at first since I have run the same script on many other servers wihtout a hitch. On close examination, the problem appears to be that the new serer is setup with a server default collation... Latin1_General_BIN (I think a binary based collation makes this a case sensitive server).
This is quite an urgenet one since I have to get this wrapped up today. I don't think I can change the server's default collation without a lot of red-tape. Is there a quick way to run my scripts in a 'case insenstive' context within Query Analyzer? If so, how?
Thanks in advance,
CliveWe have that problem all of the time with the PeopleSoft servers. They can only run with binary sort orders.
If you find a way to work around the case sensitivity, please let me know!
-PatP|||Ok, well thanks anyway. It seems a bit over the top for a server to be set to case senstive by default. Even the master database is case sensitive as a result. I can't see why the s/w vendor couldn't have left the default server collation alone and just been a bit more selective about what tables/columns actually needed a _BIN collation. Laziness I suppose.
Clive|||Binary sort order os a bit faster than case sensitive, as it bypasses all of the checks for 'A' = 'a'. That aside, you should probably go over the script and change the identifiers (table names, column names) to be the proper case (fortunately this is all lowercase for system tables), and change all of your variables to be the same case throughout. The variables can be doe with find/replace very easily. After that, you would have a new script that would work on Latin1_General_CI_AS, _BIN, and _CS_AS servers.
Since i have a couple of case sensitive servers around, I have had to write all of my own scripts to be able to handle case sensitivity. Even the ones that "should" only run on the case insensitive ones. It is a good habit to get into.|||All of our main production servers are Latin1_General_BIN_437 which is actually what yours probably is. This was recommended back in the old days when it was based on 6.5. Since they reworked the architecture, It doesn't really make any difference on speed. Since it's not the industry standard to use this, it's just a big pain. Anyway, I've learned to use all upper-case for my commands and lower-case for my objects. We have a program at work that will automagically convert all your code for you. I'll see if I can "share" it with you. Saves soooooooo much time.
This is quite an urgenet one since I have to get this wrapped up today. I don't think I can change the server's default collation without a lot of red-tape. Is there a quick way to run my scripts in a 'case insenstive' context within Query Analyzer? If so, how?
Thanks in advance,
CliveWe have that problem all of the time with the PeopleSoft servers. They can only run with binary sort orders.
If you find a way to work around the case sensitivity, please let me know!
-PatP|||Ok, well thanks anyway. It seems a bit over the top for a server to be set to case senstive by default. Even the master database is case sensitive as a result. I can't see why the s/w vendor couldn't have left the default server collation alone and just been a bit more selective about what tables/columns actually needed a _BIN collation. Laziness I suppose.
Clive|||Binary sort order os a bit faster than case sensitive, as it bypasses all of the checks for 'A' = 'a'. That aside, you should probably go over the script and change the identifiers (table names, column names) to be the proper case (fortunately this is all lowercase for system tables), and change all of your variables to be the same case throughout. The variables can be doe with find/replace very easily. After that, you would have a new script that would work on Latin1_General_CI_AS, _BIN, and _CS_AS servers.
Since i have a couple of case sensitive servers around, I have had to write all of my own scripts to be able to handle case sensitivity. Even the ones that "should" only run on the case insensitive ones. It is a good habit to get into.|||All of our main production servers are Latin1_General_BIN_437 which is actually what yours probably is. This was recommended back in the old days when it was based on 6.5. Since they reworked the architecture, It doesn't really make any difference on speed. Since it's not the industry standard to use this, it's just a big pain. Anyway, I've learned to use all upper-case for my commands and lower-case for my objects. We have a program at work that will automagically convert all your code for you. I'll see if I can "share" it with you. Saves soooooooo much time.
Subscribe to:
Posts (Atom)