Collation in SQL Server - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Collation in SQL Server

Author

Topic

benildusmuerling
Yak Posting Veteran

81 Posts

Posted - 2012-04-19 : 19:51:38

Hi All,

in my server under system databases my master database collation says as - Latin1_General_CI_AS and my working database says as - SQL_Latin1_General_CP1_CI_AS, how could I bring this to consistency by bringing my working database as Latin1_General_CI_AS.

My application frontt end does not work due to this.

Thanks,

Ben

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-20 : 15:01:46

you need to use ALTER DATABASE COLLATE <collation name> for that.

But you need to take care of few things before you change collation for db.

see below for more details

http://msdn.microsoft.com/en-us/library/ms174269.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

benildusmuerling
Yak Posting Veteran

81 Posts

Posted - 2012-04-22 : 20:22:19

Hi Vikash / All,

Would anyone be in a position to say, am I to proceed with it, I do not get what does it meant by you, to consider the stuffs before proceeding, well and truly am gonna have a backup of the database before hand, is there anything else that I should be eyeing on before changing the collation.

Thanks,

Antonio Benildus (AB)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-22 : 21:34:13

if you're backuping database thats definitely good.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

benildusmuerling
Yak Posting Veteran

81 Posts

Posted - 2012-04-22 : 22:07:43

Hi Visakh,

can you please tell whether I could proceed as below.

ALTER DATABASE testDB
COLLATE Latin1_General_CI_AS;
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-22 : 22:11:43

yep...but keep in mind that this gets applied only to new columns added to database.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

benildusmuerling
Yak Posting Veteran

81 Posts

Posted - 2012-04-25 : 22:57:45

Hi vikash,

is there a 100 percent guarantee, that I would be able to amend old records, which has been held back, as a result of altering the database with the new collation.

Thanks,

AB

benildusmuerling
Yak Posting Veteran

81 Posts

Posted - 2012-05-10 : 23:29:17

Thanks a million in reading this post

Is there anyone please, to help me with the collation issue that am having, it indeed would be a great help, I am holding so many things and my backlog grows, please can you help me in sorting this..
issue is the system database which is the master database has a different collation that the working database, which prevents from working together, and am not in a position to work with the application to insert records, via the front end application, so as I am unable in updating records as well..

I have tried, doing altering the database setting up the single mode and still it does not work - this is for your information

Thanks,

AB

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-10 : 23:36:04

quote:
Originally posted by benildusmuerling

Hi vikash,

is there a 100 percent guarantee, that I would be able to amend old records, which has been held back, as a result of altering the database with the new collation.

Thanks,

AB

you will still be able to amend the old columns but it will still be in old collation unless you alter it explicitly by means of ALTER TABLE statement

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

benildusmuerling
Yak Posting Veteran

81 Posts

Posted - 2012-05-10 : 23:50:07

Hi Vikash and all,

Thanks Vikash yet again, in helping me, and to other viewers seeing ma post

amending the database with the altering query does not work, even with the setting mode on with the single user, I want further suggestions like you know
maybe import the data into an excel sheet as per to the number of tables and then, export it back into the database back again, with the use of SSIS package

does than make sense..

cuase I am sick of hitting the alter database query now, as it does not work in by any of the way.

Thanks,

AB

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-11 : 00:07:25

why it doesnt work? did you get some error?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources