Is there a way in sql server 2k, of making an existing database case sensitive.
Thank
AjayHi,
You can do it using ALter database statement, Sample is shown below to
convert to Case sentive, Accent sensitive.
Alter Database adm collate Latin1_General_CS_AS
Note:
No user should be accessing the database during this time.
Thanks
Hari
MCDBA
"Ajay" <anonymous@.discussions.microsoft.com> wrote in message
news:91ACBF73-7821-4B33-A055-AA703ED2FA8C@.microsoft.com...
> Is there a way in sql server 2k, of making an existing database case
sensitive.
> Thanks
> Ajay|||Hari
I executed this command but even after restarting the database, it is case insensitive. I am trying the following in QA to verify case sensitivity
declare @.p in
select @.
Thank
Ajay|||Ajay,
I believe that the case-sensitivity of variable names depends on the
collation of the SQL Server instance. You can check the database
collation with a table column identifier. Just in case it's a
consideration, changing the database collation will not change the
collation of existing varchar, char, text, or ntext columns, even if
they were declared with COLLATE database_default. Here's a repro that
shows both that changing the collation works, but that it doesn't change
column collations:
create database testAlterCollation collate Latin1_General_CI_AS
GO
use testAlterCollation
GO
create table t(i char(1) collate database_default primary key )
GO
insert into t values ('a')
insert into t values ('A')
GO
select I from t
GO
alter database testAlterCollation collate Latin1_General_CS_AS
GO
insert into t values ('A')
GO
select I from t
GO
use master
GO
drop database testAlterCollation
-- Steve Kass
-- Drew University
-- Ref: 55AC66F6-4FE2-43E1-9CCE-3536D7D7BA45
Ajay wrote:
>Hari,
>I executed this command but even after restarting the database, it is case insensitive. I am trying the following in QA to verify case sensitivity.
>declare @.p int
>select @.P
>Thanks
>Ajay
>|||Hi Ajay,
I agree with Steve. Just check with table column, there you can see the
difference.
Thanks
Hari
MCDBA
"Steve Kass" <skass@.drew.edu> wrote in message
news:uz1d22v6DHA.488@.TK2MSFTNGP12.phx.gbl...
> Ajay,
> I believe that the case-sensitivity of variable names depends on the
> collation of the SQL Server instance. You can check the database
> collation with a table column identifier. Just in case it's a
> consideration, changing the database collation will not change the
> collation of existing varchar, char, text, or ntext columns, even if
> they were declared with COLLATE database_default. Here's a repro that
> shows both that changing the collation works, but that it doesn't change
> column collations:
> create database testAlterCollation collate Latin1_General_CI_AS
> GO
> use testAlterCollation
> GO
> create table t(i char(1) collate database_default primary key )
> GO
> insert into t values ('a')
> insert into t values ('A')
> GO
> select I from t
> GO
> alter database testAlterCollation collate Latin1_General_CS_AS
> GO
> insert into t values ('A')
> GO
> select I from t
> GO
> use master
> GO
> drop database testAlterCollation
> -- Steve Kass
> -- Drew University
> -- Ref: 55AC66F6-4FE2-43E1-9CCE-3536D7D7BA45
> Ajay wrote:
> >Hari,
> >
> >I executed this command but even after restarting the database, it is
case insensitive. I am trying the following in QA to verify case
sensitivity.
> >
> >declare @.p int
> >select @.P
> >
> >Thanks
> >Ajay
> >
> >
>
Showing posts with label existing. Show all posts
Showing posts with label existing. Show all posts
Sunday, March 25, 2012
Case sensitive database
Is there a way in sql server 2k, of making an existing database case sensiti
ve.
Thanks
AjayHi,
You can do it using ALter database statement, Sample is shown below to
convert to Case sentive, Accent sensitive.
Alter Database adm collate Latin1_General_CS_AS
Note:
No user should be accessing the database during this time.
Thanks
Hari
MCDBA
"Ajay" <anonymous@.discussions.microsoft.com> wrote in message
news:91ACBF73-7821-4B33-A055-AA703ED2FA8C@.microsoft.com...
sensitive.
I executed this command but even after restarting the database, it is case i
nsensitive. I am trying the following in QA to verify case sensitivity.
declare @.p int
select @.P
Thanks
Ajay|||Ajay,
I believe that the case-sensitivity of variable names depends on the
collation of the SQL Server instance. You can check the database
collation with a table column identifier. Just in case it's a
consideration, changing the database collation will not change the
collation of existing varchar, char, text, or ntext columns, even if
they were declared with COLLATE database_default. Here's a repro that
shows both that changing the collation works, but that it doesn't change
column collations:
create database testAlterCollation collate Latin1_General_CI_AS
GO
use testAlterCollation
GO
create table t(i char(1) collate database_default primary key )
GO
insert into t values ('a')
insert into t values ('A')
GO
select I from t
GO
alter database testAlterCollation collate Latin1_General_CS_AS
GO
insert into t values ('A')
GO
select I from t
GO
use master
GO
drop database testAlterCollation
-- Steve Kass
-- Drew University
-- Ref: 55AC66F6-4FE2-43E1-9CCE-3536D7D7BA45
Ajay wrote:
I agree with Steve. Just check with table column, there you can see the
difference.
Thanks
Hari
MCDBA
"Steve Kass" <skass@.drew.edu> wrote in message
news:uz1d22v6DHA.488@.TK2MSFTNGP12.phx.gbl...
ve.
Thanks
AjayHi,
You can do it using ALter database statement, Sample is shown below to
convert to Case sentive, Accent sensitive.
Alter Database adm collate Latin1_General_CS_AS
Note:
No user should be accessing the database during this time.
Thanks
Hari
MCDBA
"Ajay" <anonymous@.discussions.microsoft.com> wrote in message
news:91ACBF73-7821-4B33-A055-AA703ED2FA8C@.microsoft.com...
quote:
> Is there a way in sql server 2k, of making an existing database case
sensitive.
quote:|||Hari,
> Thanks
> Ajay
I executed this command but even after restarting the database, it is case i
nsensitive. I am trying the following in QA to verify case sensitivity.
declare @.p int
select @.P
Thanks
Ajay|||Ajay,
I believe that the case-sensitivity of variable names depends on the
collation of the SQL Server instance. You can check the database
collation with a table column identifier. Just in case it's a
consideration, changing the database collation will not change the
collation of existing varchar, char, text, or ntext columns, even if
they were declared with COLLATE database_default. Here's a repro that
shows both that changing the collation works, but that it doesn't change
column collations:
create database testAlterCollation collate Latin1_General_CI_AS
GO
use testAlterCollation
GO
create table t(i char(1) collate database_default primary key )
GO
insert into t values ('a')
insert into t values ('A')
GO
select I from t
GO
alter database testAlterCollation collate Latin1_General_CS_AS
GO
insert into t values ('A')
GO
select I from t
GO
use master
GO
drop database testAlterCollation
-- Steve Kass
-- Drew University
-- Ref: 55AC66F6-4FE2-43E1-9CCE-3536D7D7BA45
Ajay wrote:
quote:|||Hi Ajay,
>Hari,
>I executed this command but even after restarting the database, it is case
insensitive. I am trying the following in QA to verify case sensitivity.
>declare @.p int
>select @.P
>Thanks
>Ajay
>
I agree with Steve. Just check with table column, there you can see the
difference.
Thanks
Hari
MCDBA
"Steve Kass" <skass@.drew.edu> wrote in message
news:uz1d22v6DHA.488@.TK2MSFTNGP12.phx.gbl...
quote:
> Ajay,
> I believe that the case-sensitivity of variable names depends on the
> collation of the SQL Server instance. You can check the database
> collation with a table column identifier. Just in case it's a
> consideration, changing the database collation will not change the
> collation of existing varchar, char, text, or ntext columns, even if
> they were declared with COLLATE database_default. Here's a repro that
> shows both that changing the collation works, but that it doesn't change
> column collations:
> create database testAlterCollation collate Latin1_General_CI_AS
> GO
> use testAlterCollation
> GO
> create table t(i char(1) collate database_default primary key )
> GO
> insert into t values ('a')
> insert into t values ('A')
> GO
> select I from t
> GO
> alter database testAlterCollation collate Latin1_General_CS_AS
> GO
> insert into t values ('A')
> GO
> select I from t
> GO
> use master
> GO
> drop database testAlterCollation
> -- Steve Kass
> -- Drew University
> -- Ref: 55AC66F6-4FE2-43E1-9CCE-3536D7D7BA45
> Ajay wrote:
>
case insensitive. I am trying the following in QA to verify case
sensitivity.[QUOTE]
>
Tuesday, March 20, 2012
CASE does not give correct result
How do I automatically assign a new cardcode-number? (according to the following formula: highest existing number + 1)
Scenario:
-There are two types of business partners: Customers and Suppliers.
-Customers have the value 'C' in the colomn CardType.
-Suppliers have the value 'S' in the colomn CardType.
-Customers have the following syntax 'C123456' in the colomn CardCode.
-Suppliers have the following syntax 'S123456' in the colomn CardCode.
-Existing CardCode-values in the DB for the Customers: C000001 - C100599.
-Existing CardCode-values in the DB for the Suppliers: S000001 - S200199.
The idea is that when a user creates a new business partner, the CardCode should be automatically filled when a new assigned number (highest existing number + 1), according to the value that is selected in CardType (either the letter 'C' or 'S').
What's been done so far:
SELECT top 1
(CASE
WHEN CardType='C' THEN (SELECT top 1 'C' + '' + cast((substring(T0.CardCode, 2, 7) + 1) as varchar) as [nummer]
FROM OCRD T0
WHERE T0. CardCode like 'C%' AND T0. CardType='C'
order BY T0.CardCode desc FOR BROWSE)
WHEN CardType='S' THEN (SELECT top 1 'S' + '' + cast((substring(T0.CardCode, 2, 7) + 1) as varchar) as [nummer]
FROM OCRD T0
WHERE T0. CardCode like 'S%' AND T0. CardType='S'
order BY T0.CardCode desc FOR BROWSE)
END)
FROM OCRD T0
The current result:
The result that it gives is 'C100600'.
The problem however is that it always gives this result and does not take into account what has been selected in CardType.
When I add the following: "order BY T0.CardCode desc FOR BROWSE" it gives the result 'S200200'.
So, what does work is that it takes the highest existing value and adds 1. But what doesn't work is the taking account what value is selected in CardType.
Does anyone know how I can make this work?Read this topic
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67923|||You said you wanted iron clad?
-- prepare test data
declare @.test table (t varchar(50))
insert @.test
select 'ov1' union all
select 'ov2' union all
select 'ov4' union all
select 'ov31' union all
select 'ssss1' union all
select 'ssss99'
-- show data before inserting new values
select * from @.test
-- do the work
declare @.prefix varchar(50)
select @.prefix = 'ov' -- Throw anything in!
insert @.test
select @.prefix + convert(varchar, t.n + 1)
from (
select max(convert(int, substring(t, 1 + len(@.prefix), 50))) n
from @.test
where t like @.prefix + '%'
and not substring(t, 1 + len(@.prefix), 50) like '%[^0-9]%'
) t
where not @.prefix like '%[0-9]%'
and not t.n like '%[^0-9]%'
-- show data after inserting new values
select * from @.test|||I honestly tried to fit/convert your solution to my problem but I simply do not understand what you mean with things like "@.test" and "@.prefix".
This is mainly because I am merely a functional consultant in SAP Software and not a technical specialist like you. Is it therefore possible to explain your solution more in my context? :-)|||@.test is the table used for this test.
@.prefix is the first couple of characters used for searching a client number. you set @.prefix to the client number you want, in your case "c" or "s".
Scenario:
-There are two types of business partners: Customers and Suppliers.
-Customers have the value 'C' in the colomn CardType.
-Suppliers have the value 'S' in the colomn CardType.
-Customers have the following syntax 'C123456' in the colomn CardCode.
-Suppliers have the following syntax 'S123456' in the colomn CardCode.
-Existing CardCode-values in the DB for the Customers: C000001 - C100599.
-Existing CardCode-values in the DB for the Suppliers: S000001 - S200199.
The idea is that when a user creates a new business partner, the CardCode should be automatically filled when a new assigned number (highest existing number + 1), according to the value that is selected in CardType (either the letter 'C' or 'S').
What's been done so far:
SELECT top 1
(CASE
WHEN CardType='C' THEN (SELECT top 1 'C' + '' + cast((substring(T0.CardCode, 2, 7) + 1) as varchar) as [nummer]
FROM OCRD T0
WHERE T0. CardCode like 'C%' AND T0. CardType='C'
order BY T0.CardCode desc FOR BROWSE)
WHEN CardType='S' THEN (SELECT top 1 'S' + '' + cast((substring(T0.CardCode, 2, 7) + 1) as varchar) as [nummer]
FROM OCRD T0
WHERE T0. CardCode like 'S%' AND T0. CardType='S'
order BY T0.CardCode desc FOR BROWSE)
END)
FROM OCRD T0
The current result:
The result that it gives is 'C100600'.
The problem however is that it always gives this result and does not take into account what has been selected in CardType.
When I add the following: "order BY T0.CardCode desc FOR BROWSE" it gives the result 'S200200'.
So, what does work is that it takes the highest existing value and adds 1. But what doesn't work is the taking account what value is selected in CardType.
Does anyone know how I can make this work?Read this topic
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67923|||You said you wanted iron clad?
-- prepare test data
declare @.test table (t varchar(50))
insert @.test
select 'ov1' union all
select 'ov2' union all
select 'ov4' union all
select 'ov31' union all
select 'ssss1' union all
select 'ssss99'
-- show data before inserting new values
select * from @.test
-- do the work
declare @.prefix varchar(50)
select @.prefix = 'ov' -- Throw anything in!
insert @.test
select @.prefix + convert(varchar, t.n + 1)
from (
select max(convert(int, substring(t, 1 + len(@.prefix), 50))) n
from @.test
where t like @.prefix + '%'
and not substring(t, 1 + len(@.prefix), 50) like '%[^0-9]%'
) t
where not @.prefix like '%[0-9]%'
and not t.n like '%[^0-9]%'
-- show data after inserting new values
select * from @.test|||I honestly tried to fit/convert your solution to my problem but I simply do not understand what you mean with things like "@.test" and "@.prefix".
This is mainly because I am merely a functional consultant in SAP Software and not a technical specialist like you. Is it therefore possible to explain your solution more in my context? :-)|||@.test is the table used for this test.
@.prefix is the first couple of characters used for searching a client number. you set @.prefix to the client number you want, in your case "c" or "s".
Sunday, February 19, 2012
Capture existing table
I trashed SQL Server2000 when I added the WINXP SP2, and had to reinstall
it. When I did, my database(DEV_DATA) remained intact, but when I go in
thru Enterprise Manager, it is not located in the system tree, and is
therefore inaccessible. Can anyone suggest how I could get this database
included.Have you tried attaching the database?
it. When I did, my database(DEV_DATA) remained intact, but when I go in
thru Enterprise Manager, it is not located in the system tree, and is
therefore inaccessible. Can anyone suggest how I could get this database
included.Have you tried attaching the database?
On Mon, 6 Sep 2004 21:17:03 -0400, "William F. O'Neill"
<wfoneill@.bellsouth.net> wrote:
>I trashed SQL Server2000 when I added the WINXP SP2, and had to reinstall
>it. When I did, my database(DEV_DATA) remained intact, but when I go in
>thru Enterprise Manager, it is not located in the system tree, and is
>therefore inaccessible. Can anyone suggest how I could get this database
>included.|||Hi Steve:
Thank you for replying. Using EM, how would I attach, since I don't see my
database in the tree at all. All the other databases are there from the
installation, eg. Master, Tempdb, Northwind, etc.
"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
news:no3qj0hlgn4pmp743j6pmm18ne733ao1j6@.4ax.com...
> Have you tried attaching the database?
> On Mon, 6 Sep 2004 21:17:03 -0400, "William F. O'Neill"
> <wfoneill@.bellsouth.net> wrote:
>>I trashed SQL Server2000 when I added the WINXP SP2, and had to reinstall
>>it. When I did, my database(DEV_DATA) remained intact, but when I go in
>>thru Enterprise Manager, it is not located in the system tree, and is
>>therefore inaccessible. Can anyone suggest how I could get this database
>>included.
>|||"William F. O'Neill" <wfoneill@.bellsouth.net> wrote in message
news:zCb%c.74507$N11.62157@.bignews5.bellsouth.net. ..
> Hi Steve:
> Thank you for replying. Using EM, how would I attach, since I don't see
my
> database in the tree at all. All the other databases are there from the
> installation, eg. Master, Tempdb, Northwind, etc.
Best bet, don't use EM. Use query analyzer and use sp_attach_db
Otherwise, in EM on the machine in question, right click on databases and
select ALL TASKS and attach Database.
> "Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
> news:no3qj0hlgn4pmp743j6pmm18ne733ao1j6@.4ax.com...
> > Have you tried attaching the database?
> > On Mon, 6 Sep 2004 21:17:03 -0400, "William F. O'Neill"
> > <wfoneill@.bellsouth.net> wrote:
> >>I trashed SQL Server2000 when I added the WINXP SP2, and had to
reinstall
> >>it. When I did, my database(DEV_DATA) remained intact, but when I go in
> >>thru Enterprise Manager, it is not located in the system tree, and is
> >>therefore inaccessible. Can anyone suggest how I could get this
database
> >>included.
> >>|||Thank you, Greg. Will give it a try!.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:f3c%c.13044$2s.12051@.twister.nyroc.rr.com...
> "William F. O'Neill" <wfoneill@.bellsouth.net> wrote in message
> news:zCb%c.74507$N11.62157@.bignews5.bellsouth.net. ..
>> Hi Steve:
>>
>> Thank you for replying. Using EM, how would I attach, since I don't see
> my
>> database in the tree at all. All the other databases are there from the
>> installation, eg. Master, Tempdb, Northwind, etc.
>>
> Best bet, don't use EM. Use query analyzer and use sp_attach_db
> Otherwise, in EM on the machine in question, right click on databases and
> select ALL TASKS and attach Database.
>
>>
>> "Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
>> news:no3qj0hlgn4pmp743j6pmm18ne733ao1j6@.4ax.com...
>> > Have you tried attaching the database?
>>> > On Mon, 6 Sep 2004 21:17:03 -0400, "William F. O'Neill"
>> > <wfoneill@.bellsouth.net> wrote:
>>> >>I trashed SQL Server2000 when I added the WINXP SP2, and had to
> reinstall
>> >>it. When I did, my database(DEV_DATA) remained intact, but when I go
>> >>in
>> >>thru Enterprise Manager, it is not located in the system tree, and is
>> >>therefore inaccessible. Can anyone suggest how I could get this
> database
>> >>included.
>> >>
>>>
>>
Subscribe to:
Posts (Atom)