Changing Collations...

I have looked thru the forum but have a couple of questions:
1) data base was created with case insensitive collation
2) all the tables were then create (72 tables) and by default got the CI collation on all columns
3) lots of data was added 2GB
4) discovered mistake and altered the database to have case sensitive collation
5).... how to change all the collations for all the columns without doing them all manually
can i backup the database and change some settings and restore it?
export all the data, drop and recreate tables and import data?
??Why would you want case sensitive?

99.999% of every database I work with use the default out of the box collation.

It's only when I deal with 3rd party vendors that I have to mess with a different one.

This makes life painful at times.|||and the database has to be case sensitive because the customer wants to be able to use lower and upper case codes in some of the files.|||Case insensetive doesn't mean you can't have mixed case, it just means that 'a' = 'A'. That would be false in Case Insesitive.

I would leave it alone.|||fwiw i've used binary sort order (case-sensitive) for vldb implementations that use integer keys for joins and gotten tested 30% gains in join performance.|||The apps being used for the database is an OLD cobol based programs. all thru the programs we have tests for lower and upper case values that must NOT equate 'a' as equal to 'A'. so that is why i MUST change the collation. it is not an option to change anything else about the database/tables.
anyways, i was hoping that someone here might know of a better way or have a tool that does it.|||I know you can not do a restore...

And you should probably create an instance on the box that is set up the way you want it.

Script out the database, create it in that instance, and bcp everything out and back in. Use Native format

If exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[isp_bcp_out_database]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[isp_bcp_out_database]
GO

CREATE PROC isp_bcp_out_database
@.dbName sysname
, @.fp varchar(255)
, @.User varchar(255)
, @.Pwd varchar(255)
AS
/*
EXEC isp_bcp_out_database
'Northwind'
, 'd:\Data\Northwind\'
, 'sa'
, ''

*/

SET NOCOUNT ON

DECLARE bcpout CURSOR FOR
SELECT -- 'EXEC Master..xp_cmdshell ' +
-- '"D:\MSSQL7\Binn\bcp.exe ' + db_Name() + '.[' + TABLE_SCHEMA + '].[' + TABLE_NAME+'] '
'bcp ' + db_Name() + '.[' + TABLE_SCHEMA + '].[' + TABLE_NAME+'] '
+ 'out ' + @.fp + '\DATA\'+TABLE_SCHEMA +'_'+ REPLACE(TABLE_NAME,' ','_') + '.dat '
+ '-S'+@.@.SERVERNAME+' -U'+@.User+' -P'+@.Pwd+' '
+ '-f'+@.fp+'FORMAT\'+TABLE_SCHEMA +'_'+REPLACE(TABLE_NAME,' ','_')+'.fmt '
+ ' > ' + @.fp + 'DATA\'+TABLE_SCHEMA +'_'+ REPLACE(TABLE_NAME,' ','_') + '.log'
-- + ', no_output' AS CMD
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_SCHEMA, TABLE_NAME

DECLARE @.CMD varchar(8000)

--create table a (id int identity(1,1), Add_Dt datetime DEFAULT GetDate(), s varchar(1000))
-- DROP TABLE a
OPEN bcpout

FETCH NEXT FROM bcpout INTO @.CMD

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.CMD
SELECT @.CMD = 'ECHO ' + @.CMD + ' > ' + @.fp + '\bcpout.bat'
EXEC master..xp_cmdshell @.CMD
SELECT @.CMD = @.fp + '\bcpout.bat'
SELECT @.CMD
insert a (s)
exec master..xp_cmdshell @.cmd

FETCH NEXT FROM bcpout INTO @.CMD
END

CLOSE bcpout
DEALLOCATE bcpout

select id, ouputtmp = s from a

SET NOCOUNT OFF

drop table emp2|||The general sequence is this:

1) Research and find all columns that are defined as char, varchar, text or ntext.
2) Find all indexes that include any of these columns
3) Drop all indexes found in step 2
4) Issue the "alter table alter column" command to change the collation of each char column.
5) drop the index you missed in step 3 (always happens to me, anyway)
6) Finish altering tables
7) recreate all indexes that have been dropped.

Before you go through this, you may want to get a script of all of the indexes, in case you miss one here or there. A backup of the whole database is a handy thing, as well. Good luck.

Changing collation in SQL 2000

I have a database in SQL 2000 on which I am trying to convert the collation to case insensitive from case sensitive. I am trying to use DTS to do this. The master database is already case insensitive. I have created a new database on the same server with the proper collation. I am then trying to use the DTS to import the data by replacing all objects. I am essentially trying to copy all the database objects changing only the collation. Everytime I try the process, I get errors and the process stops. It does not tell me what the errors are.

Could someone please give me some guidance on what may be causing this problem, and where I can find what kind of errors are occurring?

Thank you
GarryHi Garry, I am sorry but this is the wrong forum to ask this question. I hope you can or did ask in http://forums.microsoft.com/msdn/ShowForum.aspx?ForumID=80 (the SSIS/DTS forum).|||You can change the database collation using the statement ALTER DATABASE ... COLLATE ... - see Books Online for more information.

Make sure tempdb has a compatible collation. Tempdb is used for several operations (e.g. sorting intermediate results) and a discrepency will result in incorrect or failed execution. In that case, you may want to change the server's collation. This is done by rebuilding the master database specifying the new collation in the SQLCOLLATION property of the setup command. Books online contains more information.

Thank you.|||

Thanks Shankar,

I was able to change the collation of the database using the ALTER DATABASE, however, in the the accounting system, for which SQL 2000 serves as the backend, we are getting an error on a report query we are trying to run. The error states: "Collation cannot be resolved". I checked the KB and there is an article stating that it could be a conflict with the collation in the Tempdb and the accounting database, and that is what the software support for the accounting system is telling me. When I check the properties of the accounting database and the tempdb, however, they are the same. That was the purpose of running the ALTER DATABASE command in the first place.

Do you know of any remaining conflicts in the database that I could resolve to eliminate this problem?

Thank you,
Garry

|||How are you ensuring that the two database collations are the same? You could try to copy some of the data into a temp table and see whether you get the behavior of the old collation. This would be a verification.

Changing the server's collation would be the more reliable way if that is a choice.

Thank you,

Shankar
Program Manager, Microsoft SQL Server

This posting is provided "AS IS" with no warranties, and confers no rights|||

Hi Shankar,

Can we change collation per query, while keeping a standard collation for the table. I just asking whether is it possible to specify the collation when running a SQL query, without altering collation on table or column.

Thanks,

Roshan.

Changing collation in SQL 2000

I have a database in SQL 2000 on which I am trying to convert the collation to case insensitive from case sensitive. I am trying to use DTS to do this. The master database is already case insensitive. I have created a new database on the same server with the proper collation. I am then trying to use the DTS to import the data by replacing all objects. I am essentially trying to copy all the database objects changing only the collation. Everytime I try the process, I get errors and the process stops. It does not tell me what the errors are.

Could someone please give me some guidance on what may be causing this problem, and where I can find what kind of errors are occurring?

Thank you
GarryHi Garry, I am sorry but this is the wrong forum to ask this question. I hope you can or did ask in http://forums.microsoft.com/msdn/ShowForum.aspx?ForumID=80 (the SSIS/DTS forum).|||You can change the database collation using the statement ALTER DATABASE ... COLLATE ... - see Books Online for more information.

Make sure tempdb has a compatible collation. Tempdb is used for several operations (e.g. sorting intermediate results) and a discrepency will result in incorrect or failed execution. In that case, you may want to change the server's collation. This is done by rebuilding the master database specifying the new collation in the SQLCOLLATION property of the setup command. Books online contains more information.

Thank you.|||

Thanks Shankar,

I was able to change the collation of the database using the ALTER DATABASE, however, in the the accounting system, for which SQL 2000 serves as the backend, we are getting an error on a report query we are trying to run. The error states: "Collation cannot be resolved". I checked the KB and there is an article stating that it could be a conflict with the collation in the Tempdb and the accounting database, and that is what the software support for the accounting system is telling me. When I check the properties of the accounting database and the tempdb, however, they are the same. That was the purpose of running the ALTER DATABASE command in the first place.

Do you know of any remaining conflicts in the database that I could resolve to eliminate this problem?

Thank you,
Garry

|||How are you ensuring that the two database collations are the same? You could try to copy some of the data into a temp table and see whether you get the behavior of the old collation. This would be a verification.

Changing the server's collation would be the more reliable way if that is a choice.

Thank you,

Shankar
Program Manager, Microsoft SQL Server

This posting is provided "AS IS" with no warranties, and confers no rights|||

Hi Shankar,

Can we change collation per query, while keeping a standard collation for the table. I just asking whether is it possible to specify the collation when running a SQL query, without altering collation on table or column.

Thanks,

Roshan.

Changing Character Case

Does anyone have a solution to changing the case of characters in VARCHAR fields? I have several vendor names that have historically all been capitalized, and I would like to change them to Upper and Lower Case (as custom). For example:

WIDGET, INC would become Widget, Inc
ACME LTD would become Acme LTD
JOHNSON & JOHNSON would become Johnson & Johnson

I don' t mind if I have a stray character that inadvertantly gets changed to the wrong case... I can manage to the exceptions. I'm looking at roughly 11,000 records to change.Hello,

that depends on the database that you use:

for Oracle and MySQL and MSSQL : UPDATE table SET company = UPPER(company);

If you use another database, please let us know ...

Hope that helps ?

Best regards
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Originally posted by acg_ray
Does anyone have a solution to changing the case of characters in VARCHAR fields? I have several vendor names that have historically all been capitalized, and I would like to change them to Upper and Lower Case (as custom). For example:

WIDGET, INC would become Widget, Inc
ACME LTD would become Acme LTD
JOHNSON & JOHNSON would become Johnson & Johnson

I don' t mind if I have a stray character that inadvertantly gets changed to the wrong case... I can manage to the exceptions. I'm looking at roughly 11,000 records to change.
In Oracle, use the INITCAP function. However, this will convert "LTD" to "Ltd" in your example above.|||UPPER would set all characters to Upper Case, would it not? (as LOWER would set all characters to Lower Case).

I'm looking for the ability to set the first characters of strings to Upper, making rest lower, similar to the INITCAP function in Oracle.

I'm guessing I could use a combination of UPPER, LOWER, and LEFT, which would get me to most of the changes, but that would leave me with lower case letters starting the second or third words in a name.|||Originally posted by acg_ray
UPPER would set all characters to Upper Case, would it not? (as LOWER would set all characters to Lower Case).

I'm looking for the ability to set the first characters of strings to Upper, making rest lower, similar to the INITCAP function in Oracle.

I'm guessing I could use a combination of UPPER, LOWER, and LEFT, which would get me to most of the changes, but that would leave me with lower case letters starting the second or third words in a name.
I just took a look at some SQL Server documentation, and see that there seems to be no equivalent of INITCAP. I guess you could write your own function, using UPPER, LOWER, SUBSTRING and CHARINDEX.
You would use CHARINDEX to find the spaces, then UPPER the character following each space.|||Thanks. That's where I figured this was heading. That's a good direction, and I should be able to proceed from here. I appreciate the info.|||I was able to get this to work (for the most part) with a combination of LEFT, RIGHT, SUBSTRING, LEN, CHARINDEX, UPPER, and LOWER commands. So it's not a bad tool for me to have on hand.

I discovered as an afternote, that if I exported my results to Excel, I could use a command Proper(field) that would automatically do this for me. I could have then brought the results back into MSSQL. Really, a much easier process, since my data sets are small enough to be handled by Excel (15,000 rows).sql

Change TSQl statement using case

Hello all, Thanks in advance to contributions.
I would like to be able to manipulate myTSQL in a stored procedure, based
on a value in a parameter, using the case statement.
I want to avoid Begin..End constructs
My code is such...
select field1,field2 etc etc
from Order o
inner join tablex oa on o.order_id = oa.order_id
-- etc etc p to 8 joins
where
-- some predicate
-- then the bit I want to case
case @.MyParameter
when 'IS_MANAGER' then (and o.reason = 'T1' or o.reason = 'T2')
when 'DE_MANAGER' then (and o.reason = 'T3')
end
So, what I am trying to do is change the overall selection predicate based
on the
value of the @.MyParameter. I am trying to avoid wrapping the whole code
block up into seperate Begin..End constructs if possible as it will make the
SP very big and ugly.
Amy I trying to break SLQ rules here?
Cheers
Scotchy
eg.Scotchy wrote:

> So, what I am trying to do is change the overall selection predicate
> based on the
> value of the @.MyParameter. I am trying to avoid wrapping the whole
> code block up into seperate Begin..End constructs if possible as it
> will make the SP very big and ugly.
> Amy I trying to break SLQ rules here?
That is indeed not possible. You have the same problem as a couple of
threads above, check "Stored procedure using dynamic WHERE statement"
for the offered solutions.
HTH,
Stijn Verrept.|||yep, I was clutching a straws and hoping :-)
cheers
"Stijn Verrept" wrote:

> Scotchy wrote:
>
> That is indeed not possible. You have the same problem as a couple of
> threads above, check "Stored procedure using dynamic WHERE statement"
> for the offered solutions.
> --
> HTH,
> Stijn Verrept.
>|||declare @.parm int
declare @.sql nvarchar(400)
set @.parm = 1
set @.sql = 'select * from table1 where Type IS NOT NULL and '
if ( @.parm = 1 )
set @.sql = @.sql + 'type = ''t1'''
else if ( @.parm = 2 )
set @.sql = @.sql + 'type = ''t2'''
else
print 'error'
exec (@.sql)
William Stacey [MVP]
"Scotchy" <Scotchy@.discussions.microsoft.com> wrote in message
news:5CB3CA78-0973-4ED8-B308-7B3028CBB8F2@.microsoft.com...
> Hello all, Thanks in advance to contributions.
> I would like to be able to manipulate myTSQL in a stored procedure, based
> on a value in a parameter, using the case statement.
> I want to avoid Begin..End constructs
> My code is such...
> select field1,field2 etc etc
> from Order o
> inner join tablex oa on o.order_id = oa.order_id
> -- etc etc p to 8 joins
> where
> -- some predicate
> -- then the bit I want to case
> case @.MyParameter
> when 'IS_MANAGER' then (and o.reason = 'T1' or o.reason = 'T2')
> when 'DE_MANAGER' then (and o.reason = 'T3')
> end
> So, what I am trying to do is change the overall selection predicate based
> on the
> value of the @.MyParameter. I am trying to avoid wrapping the whole code
> block up into seperate Begin..End constructs if possible as it will make
> the
> SP very big and ugly.
> Amy I trying to break SLQ rules here?
> Cheers
> Scotchy
>
>
>
> eg.|||-- Couple of things: do you need to CASE? Can't you do something like this
?
USE Northwind
GO
DECLARE @.EmployeeID INT
SET @.EmployeeID = 2
-- Normal query
SELECT *
FROM orders
WHERE EmployeeID = @.EmployeeID
-- You can use CASE in queries. Think of them like a function which returns
a value, so you have to put it on one side of an equals:
SELECT *
FROM orders
WHERE CustomerID =
CASE @.EmployeeID
WHEN 1 THEN 'ERNSH'
WHEN 2 THEN 'BLONP'
ELSE ''
END
-- Or multiple criteria. You could even nest your case statements.
SELECT *
FROM orders
WHERE CustomerID =
CASE
WHEN @.EmployeeID = 1 AND OrderDate < '19970101' THEN 'ERNSH'
WHEN @.EmployeeID = 2 AND OrderDate < '19970101' THEN 'BLONP'
ELSE ''
END
-- Let me know how you get on.
--
--
-- Damien
"Scotchy" wrote:

> Hello all, Thanks in advance to contributions.
> I would like to be able to manipulate myTSQL in a stored procedure, based
> on a value in a parameter, using the case statement.
> I want to avoid Begin..End constructs
> My code is such...
> select field1,field2 etc etc
> from Order o
> inner join tablex oa on o.order_id = oa.order_id
> -- etc etc p to 8 joins
> where
> -- some predicate
> -- then the bit I want to case
> case @.MyParameter
> when 'IS_MANAGER' then (and o.reason = 'T1' or o.reason = 'T2')
> when 'DE_MANAGER' then (and o.reason = 'T3')
> end
> So, what I am trying to do is change the overall selection predicate based
> on the
> value of the @.MyParameter. I am trying to avoid wrapping the whole code
> block up into seperate Begin..End constructs if possible as it will make t
he
> SP very big and ugly.
> Amy I trying to break SLQ rules here?
> Cheers
> Scotchy
>
>
>
> eg.|||On Tue, 20 Dec 2005 18:07:01 -0800, Scotchy wrote:

>Hello all, Thanks in advance to contributions.
>I would like to be able to manipulate myTSQL in a stored procedure, based
>on a value in a parameter, using the case statement.
>I want to avoid Begin..End constructs
>My code is such...
>select field1,field2 etc etc
>from Order o
>inner join tablex oa on o.order_id = oa.order_id
>-- etc etc p to 8 joins
>where
> -- some predicate
> -- then the bit I want to case
> case @.MyParameter
> when 'IS_MANAGER' then (and o.reason = 'T1' or o.reason = 'T2')
> when 'DE_MANAGER' then (and o.reason = 'T3')
> end
>So, what I am trying to do is change the overall selection predicate based
>on the
>value of the @.MyParameter. I am trying to avoid wrapping the whole code
>block up into seperate Begin..End constructs if possible as it will make th
e
>SP very big and ugly.
>Amy I trying to break SLQ rules here?
Hi Scotchy,
CASE is an expression, not a statement. Check the examples in Books
Online to get a feeling for what you can accomplish with CASE. And never
think that CASE in SQL Server is similar to CASE in languages such as C.
For your situation, a combination of OR and AND works better than a
CASE:
WHERE ...
AND ( (@.MyParameter = 'IS_MANAGER' AND o.reason IN ('T1', 'T2') )
OR (@.MyParameter = 'DE_MANAGER' AND o.reason = 'T3' ))
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

change sql server from CS to Case Insensitive

Hi,
I setup a SQL server with case sensitive. How can I change
it into case insensitive?
If I use rebuildm, would it destroy my existing db?
When I ran rebuildm.exe, I got error message "Rebuild
Master failed with error -1:". Any hint?
Thank you for your help.|
| Hi,
| I setup a SQL server with case sensitive. How can I change
| it into case insensitive?
--
I will assume that you are using SQL Server 2000. In this version, sort
order can be different up to column level.
To change the server-wide sort order, you need to perform the following:
1. Use rebuildm to rebuild the master database and specify the desired case
insensitive sort order.
2. Use Alter database to specify the sort order per database.
One concern when moving from case sensitive to case sensitive is you might
encounter duplicate object names. For instance, when you move from case
sensitive to case insensitive sort order a table named "tAbLe1" will be the
same as a table named "TaBlE1".
Hope this helps,
--
Eric Cárdenas
SQL Server support|||> 1. Use rebuildm to rebuild the master database and specify the desired
case
> insensitive sort order.
1.5 Restore the databases.
> 2. Use Alter database to specify the sort order per database.
3. Use ALTER TABLE ... ALTER COLUMN to change collation for all existing
columns. And drop constraints and indexes to be able to do this.
Changing collation is (still) not a trivial task, unfortunately.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Eric Cardenas" <ecard@.anonymous.com> wrote in message
news:AXhdme7vDHA.2772@.cpmsftngxa07.phx.gbl...
> |
> | Hi,
> | I setup a SQL server with case sensitive. How can I change
> | it into case insensitive?
> --
> I will assume that you are using SQL Server 2000. In this version, sort
> order can be different up to column level.
> To change the server-wide sort order, you need to perform the following:
> 1. Use rebuildm to rebuild the master database and specify the desired
case
> insensitive sort order.
> 2. Use Alter database to specify the sort order per database.
> One concern when moving from case sensitive to case sensitive is you might
> encounter duplicate object names. For instance, when you move from case
> sensitive to case insensitive sort order a table named "tAbLe1" will be
the
> same as a table named "TaBlE1".
> Hope this helps,
> --
> Eric Cárdenas
> SQL Server support
>