MSSQL upgrade from 2000 sp3 to MSSQL 2005 - Hit with collation error

[Former Member]

Hi all,

I have migrated Sap 32bit mssql 2000 sp3 to Sap x64 bit Mssql 2005 running with 4.7x110

Followed attach detach process froms sourec to atrget system & made the system up & running successfully from 32 to 64 bit & SQL 2000 sp3 to SQL 2005..

However we are hit with an few issues in the SAP system when running feew reports with the Database collation errors.

lines from short dump analysis

How to correct the error

Database error text........: "Cannot resolve the collation conflict between

"SQL_Latin1_General_CP850_BIN2" and "SQL_Latin1_General_CP850_BIN" in the

equal to operation.Cannot resolve the collation conflict between

"SQL_Latin1_General_CP850_BIN2" and "SQL_Latin1_General_CP850_BIN" in "

Triggering SQL statement...: "FETCH NEXT "

Internal call code.........: "[DBDS/NEW DSQL]"

Please check the entries in the system log (Transaction SM21).

You may able to find an interim solution to the problem

in the SAP note system. If you have access to the note system yourself,

use the following search criteria:

-

---

"DBIF_DSQL2_SQL_ERROR" C

"SAPLSFMSS_MONITOR" or "LSFMSS_MONITORU15"

"CCMS_MSS_HEALTH_SQLSETUP"

-

---

I have run the following statments

select databasepropertyex('SPD', 'collation')

returns SQL_Latin1_General_CP850_BIN

&

select serverproperty('collation')

returns SQL_Latin1_General_CP850_BIN2

How do i synchronize both ...... I am trying to follow sap notes

505906 - SQL Server: Incorrect collation

600027 - Installing the Corrected MS SQL Server Collation

Does any one come across a situation like this if yes how do i synchronize with the data loss

thanks,

rahul

Edited by: Rahul on Nov 11, 2010 7:21 PM

[sebastian_dusch]

Hi,

if you still have your MSSQL 2000 database then you can change the conversion there and repeat the detach + attach to SQL 2005.

If you don't have your old SQL server or you already have new data in SQL 2005 then you would have to perform a R3load based system copy.

Best,

Sebastian

[Former Member]

Hi Sebastian,

thank you for the reply..

I still have the old system running with MSSQL 2000 with sp3

I understood upgrading to MSSQL 2000 sp4 will be collation BIN2 & therefore i can migrate to MS SQL 2005 with attach / detach process with out any issues

Howeverif you have any information for applying MSSQL sp4 please share it....

But i was wndering if we cant afford the downtime of R3load base export/import & no source system exists of upgrading to MSSQL sp4 what would be the solution..

Thanks,

rahul

[sebastian_dusch]

Hi,

BIN2 collation is a new feature of SQL 2000 SP4 but your collation is not changed during the installation of the service pack.

If your system is still runing on SQL 2000 SP3 you first need to install Service Pack 4.

Then you can change your collation by following SAP Note 600027.

Now you're on SQL 2000 SP4 with the correct collation and you can upgrade to SQL 2005 by detaching the database and attaching it to SQL 2005.

Hope that helps.

Best,

Sebastian

[Former Member]

Hi Rahul,

I've to do the same operation, migrate 47x110 with sql server 2000 (on 32 bit platform) to sql server 2005 (on 64 bit platform).

Do you have use SAP Kernel 6.40 x64 or SAP Kernel 6.40 IA64?

Thanks

[Former Member]

Dear moreno,

My apologies for delayed reply ...I have found the following sap notes as solutions......

960769 - Windows: Migration from 32-bit to 64-bit (x86_64)

899111 Installing R3 4.7SR1 (47X110) on x64 Windows Platform

for running x64 64 bit with release 47x110 SR1...

But appreanlty i have also tried Inst master & Kernel using 47x200 SR1 of x64 64 bit windows

NOTE- there are 7 exports for 470x110 & 8 exports for 47x200 release..

However since i am only doing a system copy i have used the following inst master of 47x200 of x64 64 bit windows

I would suggest you prepare target system & use EXPORT/IMPORT method of system during migration...

this would resulsts m uch better .pl revert back for any issues. aslo refere to sap note 600027

thanks,

rahul

thanks,

Rahul