sql 2k installation collation issue

[MaffewW]

anyone come across this or can help?

Ok, I have server A with SQL2k, service pack4

The default collation on it is
SQL_Latin1_General_CP1_CI_AS
(SELECT SERVERPROPERTY ('Collation'))

We need to move some databases to server B, which is a new install, and need to get the same server collation, though the default settings I am getting is
Latin1_General_CI_AS

I think server A was a new install (not upgrade) 4 years ago, any ideas on how to install with SQL_Latin1_General_CP1_CI_AS

cheers

Matt

Brighton, UK

 

[MaffewW]

sorry, maybe for ths admin forum will leave it here for now...

Matt

Brighton, UK

 

[hmckillop]

Have a look at I posted before on this and no point in re-inventing the wheel .

http://www.tek-tips.com/viewthread.cfm?qid=1142975

"I'm living so far beyond my income that we may almost be said to be living apart

 

[Catadmin]

Yeah, admin forum would have been better. @=)

Well, with SQL Server 2000, your options are limited. 1) Change the collation on every single table/column in question. 2) Rebuild your Server and/or database. 3) Create a new database, do NOT use the default Server collation, and import all the databases & objects into it.

However, there is a fourth option I found cruising the internet. (I googled: "Change SQL server 2000 collation" if you want to do this yourself). Check out this link for VB code and see if it helps you out:

http://www.freevbcode.com/ShowCode.asp?ID=5448

Mind you, I don't know this site very well and I have NEVER used this code, so you'd best test it on a sandbox server before propogating it to your real machines. If you do the google, you might find other options too.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mrdenny]

If you want to change the Collation of the server, your best bet is to reinstall.

While installing You get to the page which has an advanced button. Click it. You will then be prompted to change the TCP/IP port, and the Collation and some other stuff. Only change the Collation to the one that you are looking for.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com