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 sensitivity. Show all posts
Showing posts with label sensitivity. 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 in SQL -- ignore
Hi,
I think by changing the collation of your database to case
insenstitvity may help
but this may cause problems when you create #temp tables as tempdb
will have server collation
Life is tough when someone screws up. You need to just do it right
and stop looking for kludges. Oh, and if yuou can kill them guy that
did this, you will probably improve the quality of your company's
software in other places as well.
I believe my SQL server was configured as Case sensitivity. I have a
number of stored procedures which were moved from a non-Case
sensitivity SQL server. Because of the Case sensitivity, I have to do
a lot of editing in those stored procedures. Is there a quick way to
avoid the editing?
Something like ignoring the case in one statement?
Thanks in advance, your advice will be greatly appreciated.On Mar 14, 11:59 pm, sweetpota...@.yahoo.com wrote:
Quote:
Originally Posted by
Hi,
>
I believe my SQL server was configured as Case sensitivity. I have a
number of stored procedures which were moved from a non-Case
sensitivity SQL server. Because of the Case sensitivity, I have to do
a lot of editing in those stored procedures. Is there a quick way to
avoid the editing?
>
Something like ignoring the case in one statement?
>
Thanks in advance, your advice will be greatly appreciated.
I think by changing the collation of your database to case
insenstitvity may help
but this may cause problems when you create #temp tables as tempdb
will have server collation
If your column and table names are in lower case , you can modify the
SP by selecting the SP and changing to lowercase (SHIFT+CTRL+L) . But
if you have string constants which need to be in uppercase , you need
to change it accordingly
M A Srinivas|||>I believe my SQL server was configured as Case sensitivity. <<
As it should be; that is how Standard SQL is defined
Quote:
Originally Posted by
Quote:
Originally Posted by
>Because of the Case sensitivity, I have to do a lot of editing in those stored procedures. <<
Life is tough when someone screws up. You need to just do it right
and stop looking for kludges. Oh, and if yuou can kill them guy that
did this, you will probably improve the quality of your company's
software in other places as well.
Case sensitivity error!
Hi,
1] I have a domain user group 'Domain_name \my group' added into my SQL
Server.
2] When I execute the following code ..
if not exists (select * from master.dbo.syslogins where loginname = N'Domain_name\My Group')
exec sp_grantlogin N'Domain_name\My Group'
exec sp_defaultdb N'Domain_name\My Group', N'master'
exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
GO
I receive a error..
Error 15401
Windows NT user or group 'Domain_name\My Group not found.
Check the name again.
I just changed the code where ever *My Group* was there to *my group* then
the query was success.
I have gone through the article below but it dint answer my doubt.
http://support.microsoft.com/kb/q245768/
The server collation is Latin1_General_BIN
What is happening and what is the other way if I dont have to modify the code?
Thanks
ReddiYou have a binary collation (very unusual, btw) which is also case sensitive.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:D0ACD235-3F0D-4D56-BAA5-562E88C22939@.microsoft.com...
> Hi,
> 1] I have a domain user group 'Domain_name \my group' added into my SQL
> Server.
> 2] When I execute the following code ..
> if not exists (select * from master.dbo.syslogins where loginname => N'Domain_name\My Group')
> exec sp_grantlogin N'Domain_name\My Group'
> exec sp_defaultdb N'Domain_name\My Group', N'master'
> exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
> GO
> I receive a error..
> Error 15401
> Windows NT user or group 'Domain_name\My Group not found.
> Check the name again.
> I just changed the code where ever *My Group* was there to *my group* then
> the query was success.
> I have gone through the article below but it dint answer my doubt.
> http://support.microsoft.com/kb/q245768/
> The server collation is Latin1_General_BIN
> What is happening and what is the other way if I dont have to modify the code?
> Thanks
> Reddi
>|||> The server collation is Latin1_General_BIN
I'm not completely sure about this, but it looks as if the BIN collation
means that you're dealing with a SQLServer instance which is case sensitive.
It's rare that this is required, as Windows isn't case sensitive.
As for turning SQLServer from case sensitive to case insensitive, I've never
tried it. I'd be surprised if it were possible though...
Griff|||> As for turning SQLServer from case sensitive to case insensitive, I've never
> tried it. I'd be surprised if it were possible though...
You need to rebuild the system databases using rebuildm.exe, which means you lose all stuff in the
system databases. Also, this doesn't change the collation for the user databases.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Griff" <Howling@.The.Moon> wrote in message news:uiyxm0w8EHA.3988@.TK2MSFTNGP10.phx.gbl...
>> The server collation is Latin1_General_BIN
> I'm not completely sure about this, but it looks as if the BIN collation
> means that you're dealing with a SQLServer instance which is case sensitive.
> It's rare that this is required, as Windows isn't case sensitive.
> As for turning SQLServer from case sensitive to case insensitive, I've never
> tried it. I'd be surprised if it were possible though...
> Griff
>|||Hi Tibor,
Thanks for the response. Correct me if i am off track. If the issue is with
collaltion (case sensitivity), SQL server should not have allowed adding the
Windows NT group with wrong case in the first place as pointed by me in the
URL.
I have other server with same collation settings but it has Windows NT group
as Domain_name\My Group. I need not have to make any code change as what is
mentioned in code is matching with the group.
Thanks
Reddi
"Tibor Karaszi" wrote:
> > As for turning SQLServer from case sensitive to case insensitive, I've never
> > tried it. I'd be surprised if it were possible though...
> You need to rebuild the system databases using rebuildm.exe, which means you lose all stuff in the
> system databases. Also, this doesn't change the collation for the user databases.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Griff" <Howling@.The.Moon> wrote in message news:uiyxm0w8EHA.3988@.TK2MSFTNGP10.phx.gbl...
> >> The server collation is Latin1_General_BIN
> >
> > I'm not completely sure about this, but it looks as if the BIN collation
> > means that you're dealing with a SQLServer instance which is case sensitive.
> >
> > It's rare that this is required, as Windows isn't case sensitive.
> >
> > As for turning SQLServer from case sensitive to case insensitive, I've never
> > tried it. I'd be surprised if it were possible though...
> >
> > Griff
> >
> >
>
>|||You could try rewriting your code to make it case-insensitive. How about
the following (not tested at all):
DECLARE @.ln nvarchar(100)
select @.ln = loginname from master.dbo.syslogins where loginname COLLATE
SQL_Latin1_General_CP1_CI_AS = N'Domain_name\My Group'
if not @.ln is null
BEGIN
exec sp_grantlogin @.ln
exec sp_defaultdb @.ln, N'master'
exec sp_defaultlanguage @.ln, N'us_english'
END
Note that I added BEGIN and END because in the original the second and third
lines were being executed unconditionally.
HTH,
Mike
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:D0ACD235-3F0D-4D56-BAA5-562E88C22939@.microsoft.com...
> Hi,
> 1] I have a domain user group 'Domain_name \my group' added into my SQL
> Server.
> 2] When I execute the following code ..
> if not exists (select * from master.dbo.syslogins where loginname => N'Domain_name\My Group')
> exec sp_grantlogin N'Domain_name\My Group'
> exec sp_defaultdb N'Domain_name\My Group', N'master'
> exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
> GO
> I receive a error..
> Error 15401
> Windows NT user or group 'Domain_name\My Group not found.
> Check the name again.
> I just changed the code where ever *My Group* was there to *my group* then
> the query was success.
> I have gone through the article below but it dint answer my doubt.
> http://support.microsoft.com/kb/q245768/
> The server collation is Latin1_General_BIN
> What is happening and what is the other way if I dont have to modify the
> code?
> Thanks
> Reddi
>
1] I have a domain user group 'Domain_name \my group' added into my SQL
Server.
2] When I execute the following code ..
if not exists (select * from master.dbo.syslogins where loginname = N'Domain_name\My Group')
exec sp_grantlogin N'Domain_name\My Group'
exec sp_defaultdb N'Domain_name\My Group', N'master'
exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
GO
I receive a error..
Error 15401
Windows NT user or group 'Domain_name\My Group not found.
Check the name again.
I just changed the code where ever *My Group* was there to *my group* then
the query was success.
I have gone through the article below but it dint answer my doubt.
http://support.microsoft.com/kb/q245768/
The server collation is Latin1_General_BIN
What is happening and what is the other way if I dont have to modify the code?
Thanks
ReddiYou have a binary collation (very unusual, btw) which is also case sensitive.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:D0ACD235-3F0D-4D56-BAA5-562E88C22939@.microsoft.com...
> Hi,
> 1] I have a domain user group 'Domain_name \my group' added into my SQL
> Server.
> 2] When I execute the following code ..
> if not exists (select * from master.dbo.syslogins where loginname => N'Domain_name\My Group')
> exec sp_grantlogin N'Domain_name\My Group'
> exec sp_defaultdb N'Domain_name\My Group', N'master'
> exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
> GO
> I receive a error..
> Error 15401
> Windows NT user or group 'Domain_name\My Group not found.
> Check the name again.
> I just changed the code where ever *My Group* was there to *my group* then
> the query was success.
> I have gone through the article below but it dint answer my doubt.
> http://support.microsoft.com/kb/q245768/
> The server collation is Latin1_General_BIN
> What is happening and what is the other way if I dont have to modify the code?
> Thanks
> Reddi
>|||> The server collation is Latin1_General_BIN
I'm not completely sure about this, but it looks as if the BIN collation
means that you're dealing with a SQLServer instance which is case sensitive.
It's rare that this is required, as Windows isn't case sensitive.
As for turning SQLServer from case sensitive to case insensitive, I've never
tried it. I'd be surprised if it were possible though...
Griff|||> As for turning SQLServer from case sensitive to case insensitive, I've never
> tried it. I'd be surprised if it were possible though...
You need to rebuild the system databases using rebuildm.exe, which means you lose all stuff in the
system databases. Also, this doesn't change the collation for the user databases.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Griff" <Howling@.The.Moon> wrote in message news:uiyxm0w8EHA.3988@.TK2MSFTNGP10.phx.gbl...
>> The server collation is Latin1_General_BIN
> I'm not completely sure about this, but it looks as if the BIN collation
> means that you're dealing with a SQLServer instance which is case sensitive.
> It's rare that this is required, as Windows isn't case sensitive.
> As for turning SQLServer from case sensitive to case insensitive, I've never
> tried it. I'd be surprised if it were possible though...
> Griff
>|||Hi Tibor,
Thanks for the response. Correct me if i am off track. If the issue is with
collaltion (case sensitivity), SQL server should not have allowed adding the
Windows NT group with wrong case in the first place as pointed by me in the
URL.
I have other server with same collation settings but it has Windows NT group
as Domain_name\My Group. I need not have to make any code change as what is
mentioned in code is matching with the group.
Thanks
Reddi
"Tibor Karaszi" wrote:
> > As for turning SQLServer from case sensitive to case insensitive, I've never
> > tried it. I'd be surprised if it were possible though...
> You need to rebuild the system databases using rebuildm.exe, which means you lose all stuff in the
> system databases. Also, this doesn't change the collation for the user databases.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Griff" <Howling@.The.Moon> wrote in message news:uiyxm0w8EHA.3988@.TK2MSFTNGP10.phx.gbl...
> >> The server collation is Latin1_General_BIN
> >
> > I'm not completely sure about this, but it looks as if the BIN collation
> > means that you're dealing with a SQLServer instance which is case sensitive.
> >
> > It's rare that this is required, as Windows isn't case sensitive.
> >
> > As for turning SQLServer from case sensitive to case insensitive, I've never
> > tried it. I'd be surprised if it were possible though...
> >
> > Griff
> >
> >
>
>|||You could try rewriting your code to make it case-insensitive. How about
the following (not tested at all):
DECLARE @.ln nvarchar(100)
select @.ln = loginname from master.dbo.syslogins where loginname COLLATE
SQL_Latin1_General_CP1_CI_AS = N'Domain_name\My Group'
if not @.ln is null
BEGIN
exec sp_grantlogin @.ln
exec sp_defaultdb @.ln, N'master'
exec sp_defaultlanguage @.ln, N'us_english'
END
Note that I added BEGIN and END because in the original the second and third
lines were being executed unconditionally.
HTH,
Mike
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:D0ACD235-3F0D-4D56-BAA5-562E88C22939@.microsoft.com...
> Hi,
> 1] I have a domain user group 'Domain_name \my group' added into my SQL
> Server.
> 2] When I execute the following code ..
> if not exists (select * from master.dbo.syslogins where loginname => N'Domain_name\My Group')
> exec sp_grantlogin N'Domain_name\My Group'
> exec sp_defaultdb N'Domain_name\My Group', N'master'
> exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
> GO
> I receive a error..
> Error 15401
> Windows NT user or group 'Domain_name\My Group not found.
> Check the name again.
> I just changed the code where ever *My Group* was there to *my group* then
> the query was success.
> I have gone through the article below but it dint answer my doubt.
> http://support.microsoft.com/kb/q245768/
> The server collation is Latin1_General_BIN
> What is happening and what is the other way if I dont have to modify the
> code?
> Thanks
> Reddi
>
Case sensitivity error!
Hi,
1] I have a domain user group 'Domain_name \my group' added into my SQL
Server.
2] When I execute the following code ..
if not exists (select * from master.dbo.syslogins where loginname =
N'Domain_name\My Group')
exec sp_grantlogin N'Domain_name\My Group'
exec sp_defaultdb N'Domain_name\My Group', N'master'
exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
GO
I receive a error..
Error 15401
Windows NT user or group 'Domain_name\My Group not found.
Check the name again.
I just changed the code where ever *My Group* was there to *my group* then
the query was success.
I have gone through the article below but it dint answer my doubt.
http://support.microsoft.com/kb/q245768/
The server collation is Latin1_General_BIN
What is happening and what is the other way if I dont have to modify the code?
Thanks
Reddi
You have a binary collation (very unusual, btw) which is also case sensitive.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:D0ACD235-3F0D-4D56-BAA5-562E88C22939@.microsoft.com...
> Hi,
> 1] I have a domain user group 'Domain_name \my group' added into my SQL
> Server.
> 2] When I execute the following code ..
> if not exists (select * from master.dbo.syslogins where loginname =
> N'Domain_name\My Group')
> exec sp_grantlogin N'Domain_name\My Group'
> exec sp_defaultdb N'Domain_name\My Group', N'master'
> exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
> GO
> I receive a error..
> Error 15401
> Windows NT user or group 'Domain_name\My Group not found.
> Check the name again.
> I just changed the code where ever *My Group* was there to *my group* then
> the query was success.
> I have gone through the article below but it dint answer my doubt.
> http://support.microsoft.com/kb/q245768/
> The server collation is Latin1_General_BIN
> What is happening and what is the other way if I dont have to modify the code?
> Thanks
> Reddi
>
|||> The server collation is Latin1_General_BIN
I'm not completely sure about this, but it looks as if the BIN collation
means that you're dealing with a SQLServer instance which is case sensitive.
It's rare that this is required, as Windows isn't case sensitive.
As for turning SQLServer from case sensitive to case insensitive, I've never
tried it. I'd be surprised if it were possible though...
Griff
|||> As for turning SQLServer from case sensitive to case insensitive, I've never
> tried it. I'd be surprised if it were possible though...
You need to rebuild the system databases using rebuildm.exe, which means you lose all stuff in the
system databases. Also, this doesn't change the collation for the user databases.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Griff" <Howling@.The.Moon> wrote in message news:uiyxm0w8EHA.3988@.TK2MSFTNGP10.phx.gbl...
> I'm not completely sure about this, but it looks as if the BIN collation
> means that you're dealing with a SQLServer instance which is case sensitive.
> It's rare that this is required, as Windows isn't case sensitive.
> As for turning SQLServer from case sensitive to case insensitive, I've never
> tried it. I'd be surprised if it were possible though...
> Griff
>
|||Hi Tibor,
Thanks for the response. Correct me if i am off track. If the issue is with
collaltion (case sensitivity), SQL server should not have allowed adding the
Windows NT group with wrong case in the first place as pointed by me in the
URL.
I have other server with same collation settings but it has Windows NT group
as Domain_name\My Group. I need not have to make any code change as what is
mentioned in code is matching with the group.
Thanks
Reddi
"Tibor Karaszi" wrote:
> You need to rebuild the system databases using rebuildm.exe, which means you lose all stuff in the
> system databases. Also, this doesn't change the collation for the user databases.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Griff" <Howling@.The.Moon> wrote in message news:uiyxm0w8EHA.3988@.TK2MSFTNGP10.phx.gbl...
>
>
|||You could try rewriting your code to make it case-insensitive. How about
the following (not tested at all):
DECLARE @.ln nvarchar(100)
select @.ln = loginname from master.dbo.syslogins where loginname COLLATE
SQL_Latin1_General_CP1_CI_AS = N'Domain_name\My Group'
if not @.ln is null
BEGIN
exec sp_grantlogin @.ln
exec sp_defaultdb @.ln, N'master'
exec sp_defaultlanguage @.ln, N'us_english'
END
Note that I added BEGIN and END because in the original the second and third
lines were being executed unconditionally.
HTH,
Mike
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:D0ACD235-3F0D-4D56-BAA5-562E88C22939@.microsoft.com...
> Hi,
> 1] I have a domain user group 'Domain_name \my group' added into my SQL
> Server.
> 2] When I execute the following code ..
> if not exists (select * from master.dbo.syslogins where loginname =
> N'Domain_name\My Group')
> exec sp_grantlogin N'Domain_name\My Group'
> exec sp_defaultdb N'Domain_name\My Group', N'master'
> exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
> GO
> I receive a error..
> Error 15401
> Windows NT user or group 'Domain_name\My Group not found.
> Check the name again.
> I just changed the code where ever *My Group* was there to *my group* then
> the query was success.
> I have gone through the article below but it dint answer my doubt.
> http://support.microsoft.com/kb/q245768/
> The server collation is Latin1_General_BIN
> What is happening and what is the other way if I dont have to modify the
> code?
> Thanks
> Reddi
>
1] I have a domain user group 'Domain_name \my group' added into my SQL
Server.
2] When I execute the following code ..
if not exists (select * from master.dbo.syslogins where loginname =
N'Domain_name\My Group')
exec sp_grantlogin N'Domain_name\My Group'
exec sp_defaultdb N'Domain_name\My Group', N'master'
exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
GO
I receive a error..
Error 15401
Windows NT user or group 'Domain_name\My Group not found.
Check the name again.
I just changed the code where ever *My Group* was there to *my group* then
the query was success.
I have gone through the article below but it dint answer my doubt.
http://support.microsoft.com/kb/q245768/
The server collation is Latin1_General_BIN
What is happening and what is the other way if I dont have to modify the code?
Thanks
Reddi
You have a binary collation (very unusual, btw) which is also case sensitive.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:D0ACD235-3F0D-4D56-BAA5-562E88C22939@.microsoft.com...
> Hi,
> 1] I have a domain user group 'Domain_name \my group' added into my SQL
> Server.
> 2] When I execute the following code ..
> if not exists (select * from master.dbo.syslogins where loginname =
> N'Domain_name\My Group')
> exec sp_grantlogin N'Domain_name\My Group'
> exec sp_defaultdb N'Domain_name\My Group', N'master'
> exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
> GO
> I receive a error..
> Error 15401
> Windows NT user or group 'Domain_name\My Group not found.
> Check the name again.
> I just changed the code where ever *My Group* was there to *my group* then
> the query was success.
> I have gone through the article below but it dint answer my doubt.
> http://support.microsoft.com/kb/q245768/
> The server collation is Latin1_General_BIN
> What is happening and what is the other way if I dont have to modify the code?
> Thanks
> Reddi
>
|||> The server collation is Latin1_General_BIN
I'm not completely sure about this, but it looks as if the BIN collation
means that you're dealing with a SQLServer instance which is case sensitive.
It's rare that this is required, as Windows isn't case sensitive.
As for turning SQLServer from case sensitive to case insensitive, I've never
tried it. I'd be surprised if it were possible though...
Griff
|||> As for turning SQLServer from case sensitive to case insensitive, I've never
> tried it. I'd be surprised if it were possible though...
You need to rebuild the system databases using rebuildm.exe, which means you lose all stuff in the
system databases. Also, this doesn't change the collation for the user databases.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Griff" <Howling@.The.Moon> wrote in message news:uiyxm0w8EHA.3988@.TK2MSFTNGP10.phx.gbl...
> I'm not completely sure about this, but it looks as if the BIN collation
> means that you're dealing with a SQLServer instance which is case sensitive.
> It's rare that this is required, as Windows isn't case sensitive.
> As for turning SQLServer from case sensitive to case insensitive, I've never
> tried it. I'd be surprised if it were possible though...
> Griff
>
|||Hi Tibor,
Thanks for the response. Correct me if i am off track. If the issue is with
collaltion (case sensitivity), SQL server should not have allowed adding the
Windows NT group with wrong case in the first place as pointed by me in the
URL.
I have other server with same collation settings but it has Windows NT group
as Domain_name\My Group. I need not have to make any code change as what is
mentioned in code is matching with the group.
Thanks
Reddi
"Tibor Karaszi" wrote:
> You need to rebuild the system databases using rebuildm.exe, which means you lose all stuff in the
> system databases. Also, this doesn't change the collation for the user databases.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Griff" <Howling@.The.Moon> wrote in message news:uiyxm0w8EHA.3988@.TK2MSFTNGP10.phx.gbl...
>
>
|||You could try rewriting your code to make it case-insensitive. How about
the following (not tested at all):
DECLARE @.ln nvarchar(100)
select @.ln = loginname from master.dbo.syslogins where loginname COLLATE
SQL_Latin1_General_CP1_CI_AS = N'Domain_name\My Group'
if not @.ln is null
BEGIN
exec sp_grantlogin @.ln
exec sp_defaultdb @.ln, N'master'
exec sp_defaultlanguage @.ln, N'us_english'
END
Note that I added BEGIN and END because in the original the second and third
lines were being executed unconditionally.
HTH,
Mike
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:D0ACD235-3F0D-4D56-BAA5-562E88C22939@.microsoft.com...
> Hi,
> 1] I have a domain user group 'Domain_name \my group' added into my SQL
> Server.
> 2] When I execute the following code ..
> if not exists (select * from master.dbo.syslogins where loginname =
> N'Domain_name\My Group')
> exec sp_grantlogin N'Domain_name\My Group'
> exec sp_defaultdb N'Domain_name\My Group', N'master'
> exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
> GO
> I receive a error..
> Error 15401
> Windows NT user or group 'Domain_name\My Group not found.
> Check the name again.
> I just changed the code where ever *My Group* was there to *my group* then
> the query was success.
> I have gone through the article below but it dint answer my doubt.
> http://support.microsoft.com/kb/q245768/
> The server collation is Latin1_General_BIN
> What is happening and what is the other way if I dont have to modify the
> code?
> Thanks
> Reddi
>
Case sensitivity error!
Hi,
1] I have a domain user group 'Domain_name \my group' added into my SQL
Server.
2] When I execute the following code ..
if not exists (select * from master.dbo.syslogins where loginname =
N'Domain_name\My Group')
exec sp_grantlogin N'Domain_name\My Group'
exec sp_defaultdb N'Domain_name\My Group', N'master'
exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
GO
I receive a error..
Error 15401
Windows NT user or group 'Domain_name\My Group not found.
Check the name again.
I just changed the code where ever *My Group* was there to *my group* then
the query was success.
I have gone through the article below but it dint answer my doubt.
http://support.microsoft.com/kb/q245768/
The server collation is Latin1_General_BIN
What is happening and what is the other way if I dont have to modify the cod
e?
Thanks
ReddiYou have a binary collation (very unusual, btw) which is also case sensitive
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:D0ACD235-3F0D-4D56-BAA5-562E88C22939@.microsoft.com...
> Hi,
> 1] I have a domain user group 'Domain_name \my group' added into my SQL
> Server.
> 2] When I execute the following code ..
> if not exists (select * from master.dbo.syslogins where loginname =
> N'Domain_name\My Group')
> exec sp_grantlogin N'Domain_name\My Group'
> exec sp_defaultdb N'Domain_name\My Group', N'master'
> exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
> GO
> I receive a error..
> Error 15401
> Windows NT user or group 'Domain_name\My Group not found.
> Check the name again.
> I just changed the code where ever *My Group* was there to *my group* then
> the query was success.
> I have gone through the article below but it dint answer my doubt.
> http://support.microsoft.com/kb/q245768/
> The server collation is Latin1_General_BIN
> What is happening and what is the other way if I dont have to modify the c
ode?
> Thanks
> Reddi
>|||> The server collation is Latin1_General_BIN
I'm not completely sure about this, but it looks as if the BIN collation
means that you're dealing with a SQLServer instance which is case sensitive.
It's rare that this is required, as Windows isn't case sensitive.
As for turning SQLServer from case sensitive to case insensitive, I've never
tried it. I'd be surprised if it were possible though...
Griff|||> As for turning SQLServer from case sensitive to case insensitive, I've nevern">
> tried it. I'd be surprised if it were possible though...
You need to rebuild the system databases using rebuildm.exe, which means you
lose all stuff in the
system databases. Also, this doesn't change the collation for the user datab
ases.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Griff" <Howling@.The.Moon> wrote in message news:uiyxm0w8EHA.3988@.TK2MSFTNGP10.phx.gbl...[vb
col=seagreen]
> I'm not completely sure about this, but it looks as if the BIN collation
> means that you're dealing with a SQLServer instance which is case sensitiv
e.
> It's rare that this is required, as Windows isn't case sensitive.
> As for turning SQLServer from case sensitive to case insensitive, I've nev
er
> tried it. I'd be surprised if it were possible though...
> Griff
>[/vbcol]|||Hi Tibor,
Thanks for the response. Correct me if i am off track. If the issue is with
collaltion (case sensitivity), SQL server should not have allowed adding the
Windows NT group with wrong case in the first place as pointed by me in the
URL.
I have other server with same collation settings but it has Windows NT group
as Domain_name\My Group. I need not have to make any code change as what is
mentioned in code is matching with the group.
Thanks
Reddi
"Tibor Karaszi" wrote:
> You need to rebuild the system databases using rebuildm.exe, which means y
ou lose all stuff in the
> system databases. Also, this doesn't change the collation for the user dat
abases.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Griff" <Howling@.The.Moon> wrote in message news:uiyxm0w8EHA.3988@.TK2MSFTN
GP10.phx.gbl...
>
>|||You could try rewriting your code to make it case-insensitive. How about
the following (not tested at all):
DECLARE @.ln nvarchar(100)
select @.ln = loginname from master.dbo.syslogins where loginname COLLATE
SQL_Latin1_General_CP1_CI_AS = N'Domain_name\My Group'
if not @.ln is null
BEGIN
exec sp_grantlogin @.ln
exec sp_defaultdb @.ln, N'master'
exec sp_defaultlanguage @.ln, N'us_english'
END
Note that I added BEGIN and END because in the original the second and third
lines were being executed unconditionally.
HTH,
Mike
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:D0ACD235-3F0D-4D56-BAA5-562E88C22939@.microsoft.com...
> Hi,
> 1] I have a domain user group 'Domain_name \my group' added into my SQL
> Server.
> 2] When I execute the following code ..
> if not exists (select * from master.dbo.syslogins where loginname =
> N'Domain_name\My Group')
> exec sp_grantlogin N'Domain_name\My Group'
> exec sp_defaultdb N'Domain_name\My Group', N'master'
> exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
> GO
> I receive a error..
> Error 15401
> Windows NT user or group 'Domain_name\My Group not found.
> Check the name again.
> I just changed the code where ever *My Group* was there to *my group* then
> the query was success.
> I have gone through the article below but it dint answer my doubt.
> http://support.microsoft.com/kb/q245768/
> The server collation is Latin1_General_BIN
> What is happening and what is the other way if I dont have to modify the
> code?
> Thanks
> Reddi
>
1] I have a domain user group 'Domain_name \my group' added into my SQL
Server.
2] When I execute the following code ..
if not exists (select * from master.dbo.syslogins where loginname =
N'Domain_name\My Group')
exec sp_grantlogin N'Domain_name\My Group'
exec sp_defaultdb N'Domain_name\My Group', N'master'
exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
GO
I receive a error..
Error 15401
Windows NT user or group 'Domain_name\My Group not found.
Check the name again.
I just changed the code where ever *My Group* was there to *my group* then
the query was success.
I have gone through the article below but it dint answer my doubt.
http://support.microsoft.com/kb/q245768/
The server collation is Latin1_General_BIN
What is happening and what is the other way if I dont have to modify the cod
e?
Thanks
ReddiYou have a binary collation (very unusual, btw) which is also case sensitive
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:D0ACD235-3F0D-4D56-BAA5-562E88C22939@.microsoft.com...
> Hi,
> 1] I have a domain user group 'Domain_name \my group' added into my SQL
> Server.
> 2] When I execute the following code ..
> if not exists (select * from master.dbo.syslogins where loginname =
> N'Domain_name\My Group')
> exec sp_grantlogin N'Domain_name\My Group'
> exec sp_defaultdb N'Domain_name\My Group', N'master'
> exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
> GO
> I receive a error..
> Error 15401
> Windows NT user or group 'Domain_name\My Group not found.
> Check the name again.
> I just changed the code where ever *My Group* was there to *my group* then
> the query was success.
> I have gone through the article below but it dint answer my doubt.
> http://support.microsoft.com/kb/q245768/
> The server collation is Latin1_General_BIN
> What is happening and what is the other way if I dont have to modify the c
ode?
> Thanks
> Reddi
>|||> The server collation is Latin1_General_BIN
I'm not completely sure about this, but it looks as if the BIN collation
means that you're dealing with a SQLServer instance which is case sensitive.
It's rare that this is required, as Windows isn't case sensitive.
As for turning SQLServer from case sensitive to case insensitive, I've never
tried it. I'd be surprised if it were possible though...
Griff|||> As for turning SQLServer from case sensitive to case insensitive, I've nevern">
> tried it. I'd be surprised if it were possible though...
You need to rebuild the system databases using rebuildm.exe, which means you
lose all stuff in the
system databases. Also, this doesn't change the collation for the user datab
ases.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Griff" <Howling@.The.Moon> wrote in message news:uiyxm0w8EHA.3988@.TK2MSFTNGP10.phx.gbl...[vb
col=seagreen]
> I'm not completely sure about this, but it looks as if the BIN collation
> means that you're dealing with a SQLServer instance which is case sensitiv
e.
> It's rare that this is required, as Windows isn't case sensitive.
> As for turning SQLServer from case sensitive to case insensitive, I've nev
er
> tried it. I'd be surprised if it were possible though...
> Griff
>[/vbcol]|||Hi Tibor,
Thanks for the response. Correct me if i am off track. If the issue is with
collaltion (case sensitivity), SQL server should not have allowed adding the
Windows NT group with wrong case in the first place as pointed by me in the
URL.
I have other server with same collation settings but it has Windows NT group
as Domain_name\My Group. I need not have to make any code change as what is
mentioned in code is matching with the group.
Thanks
Reddi
"Tibor Karaszi" wrote:
> You need to rebuild the system databases using rebuildm.exe, which means y
ou lose all stuff in the
> system databases. Also, this doesn't change the collation for the user dat
abases.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Griff" <Howling@.The.Moon> wrote in message news:uiyxm0w8EHA.3988@.TK2MSFTN
GP10.phx.gbl...
>
>|||You could try rewriting your code to make it case-insensitive. How about
the following (not tested at all):
DECLARE @.ln nvarchar(100)
select @.ln = loginname from master.dbo.syslogins where loginname COLLATE
SQL_Latin1_General_CP1_CI_AS = N'Domain_name\My Group'
if not @.ln is null
BEGIN
exec sp_grantlogin @.ln
exec sp_defaultdb @.ln, N'master'
exec sp_defaultlanguage @.ln, N'us_english'
END
Note that I added BEGIN and END because in the original the second and third
lines were being executed unconditionally.
HTH,
Mike
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:D0ACD235-3F0D-4D56-BAA5-562E88C22939@.microsoft.com...
> Hi,
> 1] I have a domain user group 'Domain_name \my group' added into my SQL
> Server.
> 2] When I execute the following code ..
> if not exists (select * from master.dbo.syslogins where loginname =
> N'Domain_name\My Group')
> exec sp_grantlogin N'Domain_name\My Group'
> exec sp_defaultdb N'Domain_name\My Group', N'master'
> exec sp_defaultlanguage N'Domain_name\My Group', N'us_english'
> GO
> I receive a error..
> Error 15401
> Windows NT user or group 'Domain_name\My Group not found.
> Check the name again.
> I just changed the code where ever *My Group* was there to *my group* then
> the query was success.
> I have gone through the article below but it dint answer my doubt.
> http://support.microsoft.com/kb/q245768/
> The server collation is Latin1_General_BIN
> What is happening and what is the other way if I dont have to modify the
> code?
> Thanks
> Reddi
>
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 sensitivity
Hello,
I'm writing a function witch from an string (nvarchar) gets the characters
and converts them. I have the problem with case sensitivity. I get the
results in small letters but I need them to be in the exact case as the inpu
t
values, and must not change the SQL Server's settings. I've tryed with
char(number), nchar(number), but results are small letters. Can somebody
please help me with my problem?
ThanksHi,
see the following code.. use this logic in your function
DECLARE @.c CHAR
DECLARE @.d CHAR
SET @.c = 'a'
SET @.d = 'B'
DECLARE @.a VARCHAR(126)
SET @.a = ''
SET @.a = @.a + @.c + @.d
PRINT @.a
I hope that this will help you
Regards
Sivakumar
"RioDD" wrote:
> Hello,
> I'm writing a function witch from an string (nvarchar) gets the characters
> and converts them. I have the problem with case sensitivity. I get the
> results in small letters but I need them to be in the exact case as the in
put
> values, and must not change the SQL Server's settings. I've tryed with
> char(number), nchar(number), but results are small letters. Can somebody
> please help me with my problem?
> Thanks|||Check out COLLATE in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"RioDD" <RioDD@.discussions.microsoft.com> wrote in message
news:D1E3AE60-365A-4B64-8408-E7C118D9F84C@.microsoft.com...
Hello,
I'm writing a function witch from an string (nvarchar) gets the characters
and converts them. I have the problem with case sensitivity. I get the
results in small letters but I need them to be in the exact case as the
input
values, and must not change the SQL Server's settings. I've tryed with
char(number), nchar(number), but results are small letters. Can somebody
please help me with my problem?
Thanks|||Sorry it didn't help me. The problem is when I use
if @.c='a'
it returns true for both 'a' and 'A'
"Subramaniam Sivakumar" wrote:
> Hi,
> see the following code.. use this logic in your function
> DECLARE @.c CHAR
> DECLARE @.d CHAR
> SET @.c = 'a'
> SET @.d = 'B'
> DECLARE @.a VARCHAR(126)
> SET @.a = ''
> SET @.a = @.a + @.c + @.d
> PRINT @.a
> I hope that this will help you
> Regards
> Sivakumar
> "RioDD" wrote:
>|||try this
IF CONVERT(varbinary(64), @.c) = CONVERT(varbinary(64), 'A')
"RioDD" wrote:
> Hello,
> I'm writing a function witch from an string (nvarchar) gets the characters
> and converts them. I have the problem with case sensitivity. I get the
> results in small letters but I need them to be in the exact case as the in
put
> values, and must not change the SQL Server's settings. I've tryed with
> char(number), nchar(number), but results are small letters. Can somebody
> please help me with my problem?
> Thanks|||Thanks, this helped me
"Subramaniam Sivakumar" wrote:
> try this
> IF CONVERT(varbinary(64), @.c) = CONVERT(varbinary(64), 'A')
>
> "RioDD" wrote:
>|||This will only work if the string is less than 64 bits. You really should
look up the various collations in books online, that is the correct way to d
o
this.|||Hi,
no... you can use varbinary upto 8000.
"Scott Simons" wrote:
> This will only work if the string is less than 64 bits. You really should
> look up the various collations in books online, that is the correct way to
do
> this.
I'm writing a function witch from an string (nvarchar) gets the characters
and converts them. I have the problem with case sensitivity. I get the
results in small letters but I need them to be in the exact case as the inpu
t
values, and must not change the SQL Server's settings. I've tryed with
char(number), nchar(number), but results are small letters. Can somebody
please help me with my problem?
ThanksHi,
see the following code.. use this logic in your function
DECLARE @.c CHAR
DECLARE @.d CHAR
SET @.c = 'a'
SET @.d = 'B'
DECLARE @.a VARCHAR(126)
SET @.a = ''
SET @.a = @.a + @.c + @.d
PRINT @.a
I hope that this will help you
Regards
Sivakumar
"RioDD" wrote:
> Hello,
> I'm writing a function witch from an string (nvarchar) gets the characters
> and converts them. I have the problem with case sensitivity. I get the
> results in small letters but I need them to be in the exact case as the in
put
> values, and must not change the SQL Server's settings. I've tryed with
> char(number), nchar(number), but results are small letters. Can somebody
> please help me with my problem?
> Thanks|||Check out COLLATE in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"RioDD" <RioDD@.discussions.microsoft.com> wrote in message
news:D1E3AE60-365A-4B64-8408-E7C118D9F84C@.microsoft.com...
Hello,
I'm writing a function witch from an string (nvarchar) gets the characters
and converts them. I have the problem with case sensitivity. I get the
results in small letters but I need them to be in the exact case as the
input
values, and must not change the SQL Server's settings. I've tryed with
char(number), nchar(number), but results are small letters. Can somebody
please help me with my problem?
Thanks|||Sorry it didn't help me. The problem is when I use
if @.c='a'
it returns true for both 'a' and 'A'
"Subramaniam Sivakumar" wrote:
> Hi,
> see the following code.. use this logic in your function
> DECLARE @.c CHAR
> DECLARE @.d CHAR
> SET @.c = 'a'
> SET @.d = 'B'
> DECLARE @.a VARCHAR(126)
> SET @.a = ''
> SET @.a = @.a + @.c + @.d
> PRINT @.a
> I hope that this will help you
> Regards
> Sivakumar
> "RioDD" wrote:
>|||try this
IF CONVERT(varbinary(64), @.c) = CONVERT(varbinary(64), 'A')
"RioDD" wrote:
> Hello,
> I'm writing a function witch from an string (nvarchar) gets the characters
> and converts them. I have the problem with case sensitivity. I get the
> results in small letters but I need them to be in the exact case as the in
put
> values, and must not change the SQL Server's settings. I've tryed with
> char(number), nchar(number), but results are small letters. Can somebody
> please help me with my problem?
> Thanks|||Thanks, this helped me
"Subramaniam Sivakumar" wrote:
> try this
> IF CONVERT(varbinary(64), @.c) = CONVERT(varbinary(64), 'A')
>
> "RioDD" wrote:
>|||This will only work if the string is less than 64 bits. You really should
look up the various collations in books online, that is the correct way to d
o
this.|||Hi,
no... you can use varbinary upto 8000.
"Scott Simons" wrote:
> This will only work if the string is less than 64 bits. You really should
> look up the various collations in books online, that is the correct way to
do
> this.
Subscribe to:
Posts (Atom)