Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2005
    Posts
    71

    Unanswered: 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?
    ????

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Mar 2005
    Posts
    71

    Well this is a 4GL deal...

    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.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jul 2004
    Posts
    60

    re: why use case-sensitive

    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.

  6. #6
    Join Date
    Mar 2005
    Posts
    71

    i have to evaluate 'a' = 'A' as false...

    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.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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

    Code:
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •