How to change SQL Server Collation

Question

How to change SQL Server Collation

Viewing 15 posts - 46 through 60 (of 76 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

murali100 (11/7/2010)
can someone tell what is wrong?

Without you telling us what the error was, no.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

manoj100 SSChasing Mays Points: 601 More actions · Answer 2

Hi Gail Shaw: here is the error message:

Msg 5075, Level 16, State 1, Line 1

The object 'ufnLeadingZeros' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.

Msg 5075, Level 16, State 1, Line 1

The object 'CK_ProductReview_Rating' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.

Msg 5075, Level 16, State 1, Line 1

The object 'CK_TransactionHistory_TransactionType' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.

Msg 5075, Level 16, State 1, Line 1

The object 'CK_ProductVendor_AverageLeadTime' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.

Msg 5075, Level 16, State 1, Line 1

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

The errors are pretty clear. You've got objects that depend on the collation. You cannot change the collation without removing the objects first. Where's the problem?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

manoj100 SSChasing Mays Points: 601 More actions · Answer 4

Yes. I have AdventureWorks database and I want to change the collation for this database. what is the procedure? I am kinda new to SQL Server.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

You need to drop the four objects listed in the error messages. Query sys.objects to see what they are, then look up in Books Online how to script and drop them. Then change the collation. Then recreate the objects.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

azad maratha Default port Points: 1468 More actions · Answer 6

azad maratha

Default port

Points: 1468

November 10, 2010 at 5:05 am

#1248769

as a matter of fact u can change it

manoj100 SSChasing Mays Points: 601 More actions · Answer 7

I have AdventureWorks database. I want to change its collation to SQL_Latin1_General_CP1_CI_AS

I am trying with this command:

ALTER DATABASE AdventureWorks COLLATE SQL_Latin1_General_CP1_CI_AS

getting hell lot of error message: some of them -->

Msg 5075, Level 16, State 1, Line 1

The object 'ufnLeadingZeros' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.

Msg 5075, Level 16, State 1, Line 1

The object 'CK_ProductReview_Rating' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.

Msg 5075, Level 16, State 1, Line 1

The object 'CK_TransactionHistory_TransactionType' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.

Msg 5075, Level 16, State 1, Line 1

The object 'CK_ProductVendor_AverageLeadTime' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.

Msg 5075, Level 16, State 1, Line 1

The object 'CK_TransactionHistoryArchive_TransactionType' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.

Msg 5075, Level 16, State 1, Line 1

any help please?

manoj100 SSChasing Mays Points: 601 More actions · Answer 8

Hello Azad--- can you tell me what is the procedure to do it? Thanks

manoj100 SSChasing Mays Points: 601 More actions · Answer 9

Hello Gail,

There are not just these 4 objects. I could not paste the entire error message here. There are about 100 objects are there with this same error message.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Then you need to script and drop all of them, change the collation and recreate them

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

azad maratha Default port Points: 1468 More actions · Answer 11

azad maratha

Default port

Points: 1468

November 17, 2010 at 6:08 am

#1251848

hi,

can u please sen send me ur email-id

eduardo.pin SSC Eights! Points: 996 More actions · Answer 12

Stop the running server.

2. Open a Command Prompt and go to the SQL Server directory.

3. Type:

sqlservr -m -T4022 -T3659 -q"new collation"

Example: sqlservr -m -T4022 -T3659 -q"latin1_general_ci_ai"

I don't know How, but it works just fine. Easy, clean, fast.

Anybody knows how this command works?

I think that it is a good case for an article.

azad maratha Default port Points: 1468 More actions · Answer 13

yes ,

mine was the same solution.

but q is a undocumented start-up parameter.i tried this with sql 2008.it gives me an error.i think it has been removed from sql server.

Michael Meierruth SSChampion Points: 10051 More actions · Answer 14

eduardo.pin (11/17/2010)
Stop the running server.
2. Open a Command Prompt and go to the SQL Server directory.
3. Type:
sqlservr -m -T4022 -T3659 -q"new collation"
Example: sqlservr -m -T4022 -T3659 -q"latin1_general_ci_ai"
I don't know How, but it works just fine. Easy, clean, fast.
Anybody knows how this command works?
I think that it is a good case for an article.

I think this only changes the collation of the server instance. And I don't see in your example where you are telling it which instance you want this for. In any case, I tried it on SS2005 and I get a very ugly error message.

To change the collation of a database, I agree with Gail, there is only one way: the hard way. I had to do it once:

1. delete constraints and indexes,

2. issue the magic command 'alter database NAME collate ...'

3. alter the varchar columns of all tables

4. recreate the constraints and indexes

Step 3 can be fairly easily automated by generating a script that reads syscolumns/sysobjects.

For steps 1 and 4 I used Management Studio and manually did a

a. Script Index as/CREATE to/DROP to

b. Script Constraint as/CREATE to/DROP to

for each Index and Constraint after having examined if they involve a varchar column.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 15

eduardo.pin (11/17/2010)
I don't know How, but it works just fine. Easy, clean, fast.
Anybody knows how this command works?

You're running and recommending commands that you don't know what they do?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass