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.

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
>