change case sensitivity after database set up

I have a SQL Server database hosted with a web hosting company. The
SQL Server was clearly set up to be case sensitive, however, I want
this particular database to be case-insensitive.

I have searched high and low, the best suggestion I can find is to
reinstall SQL Server and select case-insensitive. But since this is
the web host's SQL, that isn't an option here.

With default language I can use the sp_defaultlanguage to change to
British settings (for example). Is there something similar I can use
to make just this database case insensitive?

--

Popular uprising?
http://www.blairfacedlies.org/statue.htm

captain(underscore)flack(squirlything)hotmail(you know what)comIf you are using SQL 2000, you can change the default database collation
with ALTER DATABASE:

ALTER DATABASE MyDatabase
COLLATE SQL_Latin1_General_CP1_CI_AS

Note that a column collation is determined when the column is created so
changing the database setting will not affect existing tables. You'll
need to alter existing columns to the desired collation or recreate the
tables. The collation used for identifiers (table names, column names,
etc.) will take affect immediately.

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"Captain Flack" <captain.flack@.trumpton-firebrigade.dontevenbother>
wrote in message news:o7qhqv8tpvglr61idpqj24kkaqf8p1cruq@.4ax.com...
> I have a SQL Server database hosted with a web hosting company. The
> SQL Server was clearly set up to be case sensitive, however, I want
> this particular database to be case-insensitive.
> I have searched high and low, the best suggestion I can find is to
> reinstall SQL Server and select case-insensitive. But since this is
> the web host's SQL, that isn't an option here.
> With default language I can use the sp_defaultlanguage to change to
> British settings (for example). Is there something similar I can use
> to make just this database case insensitive?
>
>
> --
> Popular uprising?
> http://www.blairfacedlies.org/statue.htm
> captain(underscore)flack(squirlything)hotmail(you know what)com|||On Wed, 05 Nov 2003 13:36:34 GMT, "Dan Guzman"
<danguzman@.nospam-earthlink.net> wrote:

>If you are using SQL 2000, you can change the default database collation
>with ALTER DATABASE:
> ALTER DATABASE MyDatabase
> COLLATE SQL_Latin1_General_CP1_CI_AS
>Note that a column collation is determined when the column is created so
>changing the database setting will not affect existing tables. You'll
>need to alter existing columns to the desired collation or recreate the
>tables. The collation used for identifiers (table names, column names,
>etc.) will take affect immediately.

Thanks, I will test this out. It is SQL 2000 so it looks to be exactly
what I was looking for :)

--

Popular uprising?
http://www.blairfacedlies.org/statue.htm

captain(underscore)flack(squirlything)hotmail(you know what)com

CASTing a datatype returned by CASE Statement

I realize that the CASE statement doesn't like different datatypes as return values but if I want to format the "0" in the second WHEN condition below to "000", how do I do that? I have a "Region" that is "000" and would like it to show up that way at the very top of my report. I have the GROUP BY and ORDER BY to work fine, it just shows up as "0" and I can't change it. I realize it is being read as an int but am having trouble with the CAST and where to place it. Thanks again, you guys are great.

ddave

SELECT Region =
CASE WHEN branch_num IN(48,53,78,173,186,198,208,212,257,286,287,317,35 3,398,440,
478,571,572,610,1069) THEN 44
WHEN branch_num IN(484,532,841,864,7001,7101,7102,7103,7104,9031) THEN 0
ELSE 999
ENDThat depends (doesn't it always?) on what you really want. If you want the other regions to show using normal INT formatting, but 0 to be a special case That is one thing, if you want all the region numbers to be zero filled, that is something different. If you want something I haven't thought of yet, then that's probably different too.

The quick and dirty would be to use:SELECT Region =
CASE
WHEN branch_num IN(48,53,78,173,186,198,208,212,257,286,287,317,35 3,398,440,
478,571,572,610,1069) THEN ' 44'
WHEN branch_num IN(484,532,841,864,7001,7101,7102,7103,7104,9031) THEN '000'
ELSE '999'
END

-PatP|||Pat,

Once again, "You da Man!!". It works perfectly. I decided to use '000', ' 1', ' 78', etc. I spent over an hour on it and I knew it was something easy. I mean I don't expect a medal or anything but you can be lost w/o "the little details". Thanks again.

ddave|||If I want the format to show the Region field just once, is there a way to do that? My current report has a Region field immediately to the left of BranchNo. Branches are contained within the Regions. I got it to list Region every time I show a record but just in case the manager wants it formatted the way I mention I want to be prepared. The example I was to follow has Region just once.

This is an example of what I have now:

code:--------------------
Region BranchNo OrderNo ErrorCode1 ErrorCode2 ErrorCode3
000 478 111 0 1 1
000 478 112 0 0 0
000 478 113 1 0 0
001 610 119 0 0 0
001 610 120 1 0 0
----------------------

This is an example of what I wish to try:

code:--------------------
Region BranchNo OrderNo ErrorCode1 ErrorCode2 ErrorCode3
000 478 111 0 1 1
478 112 0 0 0
478 113 1 0 0
001 610 119 0 0 0
610 120 1 0 0
----------------------

ddave|||What reporting tool are you using? Hopefully this isn't 100% Transact-SQL based, right?

-PatP|||Well, I am looking at the data in Query Analyzer but that is a good question. I guess the real answer is that we haven't decided yet. I can use Access though I have to figure out the mechanics which I know won't be difficult. I can even stick it on an Excel spreadsheet as long as it looks good. I say Access because that is "what the others did" but it is not an issue.

ddave|||It's a presntation issue, and Access is very good at it, and can easily do what your asking...

I'd love to setup reporting services though...

Anyone seen it?

What's the installation like?

What's the interface?

Can you use the same box as sql server?

PS. If they say Crystal...run...|||Reporting Services is quite cool, but it is rather complex and it requires Visual Studio to develop reports.

MS-Access would be beauteous, and would make the formatting, grouping, etc rather simple. I'm not nearly as alergic to Crystal Reports as most folks around here seem to be, but I would STRONGLY advise using Access unless you have another tool of choice.

-PatP

cast a date to string

Hi,
how to cast a date field to a string:
select case(TrxDate, char(20)) & ' Add me' AS newfield from tb'
Thanks.Hopefully this example gets you started:
select convert(char(20),getdate()) + ' add me' as newcolumn
select convert(char(20),getdate(),101) + ' add me' as newcolumn
select convert(char(20),getdate(),113) + ' add me' as newcolumn
select convert(char(20),getdate(),111) + ' add me' as newcolumn
Keith
"js" <js@.someone@.hotmail.com> wrote in message
news:uyPTlxHBFHA.1084@.tk2msftngp13.phx.gbl...
> Hi,
> how to cast a date field to a string:
> select case(TrxDate, char(20)) & ' Add me' AS newfield from tb'
> Thanks.
>|||Use function CAST or CONVERT.
Example:
use northwind
go
select orderid, convert(char(8), orderdate, 112) + ' whatever'
from orders
go
AMB
"js" wrote:

> Hi,
> how to cast a date field to a string:
> select case(TrxDate, char(20)) & ' Add me' AS newfield from tb'
> Thanks.
>
>|||Thanks Keith,
But has one requirement: need to format the date using a outside predefined
format(gDateFormat = "dd-mmm-yyyy", can be changed by the user)
How to do that? Do it in a query or in application level? Pls advice.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:%23xyoY0HBFHA.1388@.TK2MSFTNGP09.phx.gbl...
> Hopefully this example gets you started:
> select convert(char(20),getdate()) + ' add me' as newcolumn
> select convert(char(20),getdate(),101) + ' add me' as newcolumn
> select convert(char(20),getdate(),113) + ' add me' as newcolumn
> select convert(char(20),getdate(),111) + ' add me' as newcolumn
>|||Read up on CAST and CONVERT within (SQL Server) Books Online (within the SQL
Server program group). If all the user definable predefined formats map to
styles that can be specified by the user perhaps you could use T-SQL.
The easier approach might be to simply return the datetime from SQL Server
and then convert it as requested within your application's business logic.
Keith
"js" <js@.someone@.hotmail.com> wrote in message
news:OpPLvKIBFHA.824@.TK2MSFTNGP11.phx.gbl...
> Thanks Keith,
> But has one requirement: need to format the date using a outside
predefined
> format(gDateFormat = "dd-mmm-yyyy", can be changed by the user)
> How to do that? Do it in a query or in application level? Pls advice.
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:%23xyoY0HBFHA.1388@.TK2MSFTNGP09.phx.gbl...
>|||Thanks Keith,
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:ubF5ZhIBFHA.3708@.TK2MSFTNGP14.phx.gbl...
> Read up on CAST and CONVERT within (SQL Server) Books Online (within the
> SQL
> Server program group). If all the user definable predefined formats map
> to
> styles that can be specified by the user perhaps you could use T-SQL.
>
What's T-SQL, how to do that? Pls advice.

Casing

What is the accepted way of Casing Table,Fields and Stored Procedures?
This is a real problem when mixing and matching.
For example, C uses lower case for everything, but when calling .Net
functions - you have to use Pascal Casing (1st Character of all words
Capitalized). In VB.Net, you use Pascal style for everthing but variables
where you use Camel Casing (1st Character of all ll words capitalized,
except the 1st word).
I tend to use Microsofts guidelines where everything but variables are
Pascal. Variables are Camel Casing. I never use and never liked Hungarian
(prefixed with variable type).
Celko mentioned the problem with Camel Casing being harder to read.
I have seen various ways of casing in Sql Server.
I have also seen various opinions on whether to pluralize table names
(Employees table instead of Employee table). I tend to pluralize myself,
but have seen guidlines that suggest it is better to use the singular form.
I see Sql statements both as SELECT and select, but haven't really seen any
definitive agreement on this.
Much of this just seems to be personal preference - which would be a real
problem in C as case is important.
Just curious.
Thanks,
TomSee my rules in SQL PROGRAMMING STYLE. They are based on ISO-11179,
human factors and readability research. It is a mater of a few decades
of research, not personal taste.
1) For table names, in order of preference
industry standard name - best
collective nouns (Personnel) -better
plural nouns (Employees) - bad
singluar (employee) - not usable, less the set has just one member
Capitalize it, as you would in English, German, etc. for proper names.
2) uppercase reserved words because they are read as a Bouma (single
unit of eye scan). The compiler will take care of typos.
3) lowercase scalars because they are longer and this easier to read
and less likely to be misspelt. Never use "camelCase" because the eye
jumps to the capital letter, then back to the start of the word.
The reason C is a "lowercase" language actually has to do with
teletypes! It is a physical effort to hold down the shift key and when
you are a two-finger typists, you don't want to write anything very
long.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1125333962.715849.259610@.g47g2000cwa.googlegroups.com...
> See my rules in SQL PROGRAMMING STYLE. They are based on ISO-11179,
> human factors and readability research. It is a mater of a few decades
> of research, not personal taste.
> 1) For table names, in order of preference
> industry standard name - best
> collective nouns (Personnel) -better
> plural nouns (Employees) - bad
> singluar (employee) - not usable, less the set has just one member
> Capitalize it, as you would in English, German, etc. for proper names.
I assume you are speaking Pascal casing here.

>
> 2) uppercase reserved words because they are read as a Bouma (single
> unit of eye scan). The compiler will take care of typos.
> 3) lowercase scalars because they are longer and this easier to read
> and less likely to be misspelt. Never use "camelCase" because the eye
> jumps to the capital letter, then back to the start of the word.
What about Pascal style for variables (fields), scalars?
CamelCase is used quite a bit and is the what was used for Hungarian
(although it has been pretty much being phased out of MS notation).

> The reason C is a "lowercase" language actually has to do with
> teletypes! It is a physical effort to hold down the shift key and when
> you are a two-finger typists, you don't want to write anything very
> long.
>
True.
Thanks,
Tom