Database Collation Conversion

[Former Member]

Hi Guru's,

I am trying to copy Production to a new Test server. Production is 32-bit SQL 2000 the new Test Server is x64 bit and SQL 2005.

The problem I face is with the database Collation. Production database is SQL_Latin1_General_CP850_BIN and I'm trying to get it to SQL_Latin1_General_CP850_BIN2 before attaching it to SQL 2005 on the new Test server. I used INSTCOLL.EXE as advised in OSS 600027 and recieved the following message :

Trying to connect...

Connected to <<SERVERNAME>>

Executing conversion of database <<SID>>

Database <<SID>> has been converted to collation SQL_Latin1_General_CP850_BIN2.

However, select databasepropertyex('<<SID>>', 'collation') still returns SQL_Latin1_General_CP850_BIN

The 'server collation' is SQL_Latin1_General_CP850_BIN. Would this affect how the INSTCOLL.EXE would work?

I tried attaching the database to SQL 2005 as SQL_Latin1_General_CP850_BIN and converting it from there and got this message :

Trying to connect...

Connected to <<SERVERNAME>>

This program will only run against SQL Server 2000 newer or equal to version 8.00.780.

Your server is 9.0.3175

You must install SQL 2000, SP3 and the Unicode Hotfix (QFE).

NOTHING DONE!

Can someone please suggest another way to get the SQL 2000 database to BIN2? Or perhaps offer a suggestion to where I'm going wrong with the conversion?

Thanks,

Kye

[Former Member]

Hi,

instcoll.exe is not working for MSSQL 2005

I am sure there should be a way to change the collation on MSSQL 2005, but I just installed 2000 and applied instcoll, and then upgraded to 2005. This is the easiest way.

Thanks.

[Former Member]

Hi Nikunj

I am aware of the permitted collation combinations. The problem I have is with trying to convert a BIN database to a BIN2 using INSTCOLL.

INSTCOLL.EXE runs succesfully and produces a message saying the collation has been changed to BIN2. But when checking the collation through MS SQL it is still listed as BIN.

1. Can INSTCOLL.EXE be run from an instance of SQL Server that has a Server Collation of BIN to convert a database to BIN2?? The converted database would then be detached / re-attached to BIN2 server (with SQL 2005).

2. If not, Does anyone know if there is another way to change the collation of a MS SQL 2000 Database from BIN to BIN2?

Cheers,

Kye

[clas_hortien]

Hi,

can you send us the exact command you used for running the instcoll and the generated errorlog from the SQL Server log directory.

regards

Clas

[Former Member]

Hi Chris,

The exact command i'm running is :

instcoll -Siblonc4308 -DY75

The log entries in the ERRORLOG just after running this command are :

2008-03-05 09:11:11.83 spid142 Non-clustered index restored for Y75.sysobjects.

2008-03-05 09:11:12.24 spid142 Non-clustered index restored for Y75.sysobjects.

2008-03-05 09:15:55.77 spid142 index restored for Y75.syscolumns.

2008-03-05 09:15:55.83 spid142 index restored for Y75.systypes.

2008-03-05 09:15:55.85 spid142 index restored for Y75.sysusers.

2008-03-05 09:15:55.91 spid142 index restored for Y75.sysproperties.

2008-03-05 09:15:55.91 spid142 index restored for Y75.sysfulltextcatalogs.

2008-03-05 09:15:55.98 spid142 index restored for Y75.sysfilegroups.

2008-03-05 09:15:56.79 spid142 index restored for Y75.sap_perfcntr.

2008-03-05 09:16:11.54 spid142 index restored for Y75.sap_perfinfo.

2008-03-05 09:16:22.41 backup Log backed up: Database: Y75, creation date(time): 2008/02/20(09:25:57), first LSN: 87019:39779:1, last LSN: 87033:4184:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'VDI_9E0910C6-F86C-411F-9481-9ACA7A7C3440_0'}).

2008-03-05 09:16:23.33 backup Log backed up: Database: model, creation date(time): 2000/08/06(01:40:52), first LSN: 10:349:1, last LSN: 10:349:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'VDI_A3F68E07-8FF6-47EB-8811-773B4408554B_0'}).

[clas_hortien]

Hi,

please run the instacoll without the database clause

instcoll -Siblonc4308

Furthermore please run this script and send me the result:

declare @dbn sysname

set @dbn = db_name()

select 'Server' as [Type ],(select convert(varchar(30),serverproperty('collation'))) as [Collation] union

select 'Master',(select convert(varchar(30),databasepropertyex('master','collation'))) union

select 'Model',(select convert(varchar(30),databasepropertyex('model','collation'))) union

select 'TempDB',(select convert(varchar(30),databasepropertyex('tempdb','collation'))) union

select 'SID',(select convert(varchar(30),databasepropertyex(@dbn,'collation')))

Regards

Clas

[Former Member]

I am using the -D option because there is another database in this instance of SQL that I do not want to change as it is being used for UAT. The Y75 database is only here temporarily to allow me to change the collation within SQL 2000 before moving it to a SQL 2005 box.

The results of the script are :

Master SQL_Latin1_General_CP850_BIN

Model SQL_Latin1_General_CP850_BIN

SID SQL_Latin1_General_CP850_BIN

Server SQL_Latin1_General_CP850_BIN

TempDB SQL_Latin1_General_CP850_BIN

[clas_hortien]

Have you run the script in your SID database ?

Lets check if you have columns in BIN2 already:

use <SID>

select collation,count(*) from syscolumns group by collation

Regards

Clas

[Former Member]

Hi,

Please copy paste result of below query.

select serverproperty('collation')

SELECT DATABASEPROPERTYEX('Y75', 'Collation')

Regards,

Nikunj Thaker

[Former Member]

Hi Chris,

I did run the script for SID. (results above)

I don't think that the INSTCOLL program is actually making any changes. Here is the results of the query on syscolumns

SQL_Latin1_General_CP850_BIN 3715625

NULL 222315

Cheers,

Kye

[Former Member]

Hi,

you are not applied SP3 and hotfix to SQl 2000 server.

you can direct apply SP4 to sql server 2000 than after run INSTCOLL program and check the result.

regards,

kaushal

[Former Member]

Hi Kaushal,

We can't update the SP level on this instance of SQL as it will have to be done on a DEV system first.

I have also used INSTCOLL on a diferent server (one that had a Server collation of BIN2) with the same patch level and was able to convert a BIN database to BIN2. This was on a DEV server that doesn't have anywhere near enough storage space to put the Y75 database on to attempt the conversion there.

Cheers,

Kye

[Former Member]

Hi,

i did not think so, instcoll program required atleast SP3 with hotfix or higher of sql2000 server.

check Note 600027 - Installing the Corrected MS SQL Server Collation

regards,

kaushal

[Former Member]

Hi,

The permitted collations for SQL Server 2000 are as follows:

o SQL_Latin1_General_CP850_BIN (BIN) and

o SQL_Latin1_General_CP850_BIN2 (BIN2).

Only the following 3 combinations are supported by SAP:

.SAP-Basis................	.SQL.Server....	.<SAP.DATABASE>.
.non-Unicode.and.non-Java.	.BIN...........	.BIN............
.non-Unicode.and.non-Java.	.BIN2..........	.BIN2...........
.Unicode.or.Java..........	.BIN2..........	.BIN2...........

The same collation must always be installed for the SQL Server and the

database.

For a non-Unicode system that does not contain any Java components, you can

select both BIN and BIN2. With a Unicode system or a system that contains

Java components, you must select BIN2 as the collation for the server and

database.

For more details refer SAP Note : 505906.

Regards,

Nikunj