changing collations - Microsoft SQL Server

Sai

Hi,

We have around 150 databases as case sensitive, and we are planning to
change it to case insensitive. Each database has around 180 tables, I
have changed the collation on DB, but changing collation manually on
each column is a daunting process. Is there any script or tool which
can assist in doing this.

Appreciate your help.

Thanks
SAI

Dec 19 '05 #1

Subscribe Reply

2291

Erland Sommarskog

Sai (sb********@gma il.com) writes:

We have around 150 databases as case sensitive, and we are planning to
change it to case insensitive. Each database has around 180 tables, I
have changed the collation on DB, but changing collation manually on
each column is a daunting process. Is there any script or tool which
can assist in doing this.

I would probably bulk out data, and build the databases from scripts and
bulk back.

If you want to use ALTER TABLE, I think you still need to script all
indexes, as you cannot change the collation on an indexed column, if I
recall correctly. You could run queries on the system catalog to generate
ALTER TABLE ALTER COLUMN command. I'm not including this right now, as I
am in bit of hurry.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Dec 19 '05 #2

Sai

Bulk out data is also an option, but its around 150 DBs and I am trying
to do it in minimum time, this would take long time. I am planning to
minimize the impact to customers while doing so. ALTER TABLE ALTER
COLUMN is also an option, but its very risky as it will drop the
primary keys and indexes which we have to create manually. Thats why I
am looking for an automated tool or some script which does it.

Anyway thanks for the options.

Dec 20 '05 #3

Erland Sommarskog

Sai (sb********@gma il.com) writes:

Bulk out data is also an option, but its around 150 DBs and I am trying
to do it in minimum time, this would take long time. I am planning to
minimize the impact to customers while doing so. ALTER TABLE ALTER
COLUMN is also an option, but its very risky as it will drop the
primary keys and indexes which we have to create manually. Thats why I
am looking for an automated tool or some script which does it.

As far as I know, ALTER TABLE ALTER COLUMN does not drop the indexes,
you will have to drop them manually.

I would not expect there any tool to be available for anything of this
large scale. Doing it for once database is not that difficult: script the
indexes, drop them, create the ALTER TABLE commands, and run the index
scripts. But of course doing this 150 times manually, is error-prone.

It could be worth the investment to look into using DMO for the task.
This is probably the best bet, as I believe DMO includes scripting
functions. (I have never used DMO myself, though.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Dec 21 '05 #4

Alex

I wrote a tool that generates a script to change the collation of each
column in the database

http://www.codeproject.com/useritems...eCollation.asp

I ran this agains a 20 GB data warehouse and it worked fine for me.

You may want to enhance the code a little to script all 150 databases
automatically

Alex

Jan 18 '06 #5

Sai

Thanks for the tool, but I have lot of problems running it on my
current database. Its good I have the source code, I will modify the
code and send it to you back.

SAI

Jan 27 '06 #6

Similar topics

3437

changing cursors for maps

by: Geoff Soper | last post by:

I'm trying to dynamically change the cursor of a couple of maps over an image. Basically the image is in an online photo system where there is a rotate mode. In this mode clicking on the left or right half of the image rotates it 90 degrees to the left or right. I want to change the cursor to indicate this but only while in the rotate mode. After looking at it in the Mozilla DOM inspector it seems as if the style is changing but neither...

HTML / CSS

551

Changing collation for tempdb and model

by: polinaskulski | last post by:

Hi, I've got a production server with the default collation and User DBs with the different collation. Now when I create temporary tables and compare with the permanent ones we have collation problems. Is it possible to change tempdb ( and model) collations ? Would I need to change master and msdb collations as well? Can I change the master collation? What about all the tables that already exist in these system databases? Is it fairly...

Microsoft SQL Server

3766

Changing collation when installing SQL2000

by: polinaskulski | last post by:

Hi, I'm installing SQL2000 on a new cluster server. I selected a 'custom installation' and was presented with a 'Collation settings' screen. I need it to be SQL_Latin1_General_CP1_CI_AS collation as I will be restoring databases with this collation. What should I choose from the 'Collation designator' and 'SQL Collations' box. Is it 'Dictionary order, case-insensitive, for use with 1252 characters'?

Microsoft SQL Server

1938

Changing Array[][] changes all copies

by: Eric | last post by:

I have an array, result. I populate the array and add it to an ArrayList. I then change result and add the new version to the ArrayList. However, when I go to review the ArrayList, all of the result 's are the same. How can I stop this from happening? I tried copying the array but that didn't work.

C# / C Sharp

1693

Mysteriously changing variable

by: Altman | last post by:

I have only done a little programming in C++ so I am still learning but I am having a problem with a variable that is changing on me. I have tried this 2 ways with the same result. I have a private long variable that keeps changing for no reason. I have a trace file running to tell me what is going on. Here is kind of what is happening l_myVariable send l_myVariable out to tracefile Call a method send l_myVariable out to tracefile

.NET Framework

1209

Changing Collations

by: Mike C# | last post by:

Hi all, I have a strange little issue. I need to load some data from VC++ into SQL Server but I need to "map" (for lack of a better word) some of the strings to a different collation. Is there a VC++ function to convert strings from one collation to another? And would anyone know if Windows collations are compatible with SQL Server collations? Thanks

.NET Framework

2195

help with changing C light bulb (regulars please read)

by: Chris Hills | last post by:

A lesson in Posting How many C.L.C group posters does it take to change a C light bulb? 1 to change the light bulb and to post that the light bulb has been changed 14 to share similar experiences of changing light bulbs and how the light bulb could have been changed differently

C / C++

2586

Very odd?? Illegel mix of collations error

by: ryanmhuc | last post by:

I have a table (depth as int, name as varchar). If I run the following query I get NO ERROR SELECT CONCAT(REPEAT(' ', 2) , display_name) as name FROM test If I run this: SELECT CONCAT(REPEAT(' ', depth) , display_name) as name FROM test

MySQL Database

3234

illegal mix of collations

by: JRough | last post by:

We got a dedicated hosted server to replace a shared hosted server. I reloaded the database tables and data and scripts. I noticed that most of the tables had latin1_swedish_collation but some had latin1_general_collation so I did a search and replace to make them all the swedish collation. The problem is one script isn't running. I'm getting a SQL query error about an illegal mix of collations while running the script. Here is the...

PHP

8376

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

8815

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

8708

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

7307

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

6161

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

5622

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

2716

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

1916

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

1596

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General