Sql server 2005, R/3 4.6c and compability mode

[juhaniemi]

Hi

I doing upgrade from 4.6c --> ECC 6.0 on sql server 2005. And i get followin error

SCRIPT MSSINCHK

=================

Msg 468, Level 16, State 9, Server MEDIASP10, Line 55

Cannot resolve the collation conflict between "SQL_Latin1_General_CP850_BIN" and

"SQL_Latin1_General_CP850_BIN2" in the equal to operation.

Msg 468, Level 16, State 9, Server MEDIASP10, Line 66

Cannot resolve the collation conflict between "SQL_Latin1_General_CP850_BIN" and

"SQL_Latin1_General_CP850_BIN2" in the equal to operation.

Execution completed successfully

I have checked that collation order is correct.

I think that problem that somehow sql server's compability mode is recognized wrong (sql server 2000 insted sql server 2005).

Any colution on this ?

Br, Juha Niemi

[Former Member]

Hi Juha,

have you checked Note 600027?

What do you get as a result running this statement:

select serverproperty('collation')

Regards,

Sven

[juhaniemi]

Yes i have. It returns BIN2 collation order.

[Former Member]

What do you get running this:

exec sp_dbcmptlevel <SID>

[clas_hortien]

What do you get, when you run this script:

use <SID> -- put in your DBName here

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')))

[juhaniemi]

Here is answer

Server SQL_Latin1_General_CP850_BIN2

Master SQL_Latin1_General_CP850_BIN2

Model SQL_Latin1_General_CP850_BIN2

TempDB SQL_Latin1_General_CP850_BIN2

AUT SQL_Latin1_General_CP850_BIN2

And

exec sp_dbcmptlevel AUT returns

The current compability level is 90

[Former Member]

Hi,

Did you check Note 1019320 - sap_update_MSSDBSTATT Cannot resolve collation conflict

regards,

kaushal

[juhaniemi]

Sorry that note is not relevant here. This system is 4.6c and that note is 620 -->

Edited by: Juha Niemi on Jan 23, 2008 12:09 PM

[Former Member]

You should be able find that script in
usr\sap\put\bin\MSSINCHK.SQL.

Please add the following code to the very beginning of that script and run it again:

use AUT

go

setuser 'aut'

go

What happens now?

[clas_hortien]

Hi,

what do you get with this :

use AUT

go

select object_name(object_id), column_id, name, collation_name

from sys.columns

where

user_type_id in (35,99,167,256,239,231) and

object_id > 100 and

collation_name != 'SQL_Latin1_General_CP850_BIN2'

[juhaniemi]

Hi

Same error

SCRIPT MSSINCHK

=================

Msg 468, Level 16, State 9, Line 55

Cannot resolve the collation conflict between "SQL_Latin1_General_CP850_BIN" and "SQL_Latin1_General_CP850_BIN2" in the equal to operation.

Msg 468, Level 16, State 9, Line 66

Cannot resolve the collation conflict between "SQL_Latin1_General_CP850_BIN" and "SQL_Latin1_General_CP850_BIN2" in the equal to operation.

Execution completed successfully

Br, Juha

[juhaniemi]

Hi

It shows

ENT5864 1 MANDT SQL_Latin1_General_CP850_BIN

ENT5864 2 VEINN SQL_Latin1_General_CP850_BIN

ENT5864 3 MEINN SQL_Latin1_General_CP850_BIN

ENT5864 4 ASWKZ SQL_Latin1_General_CP850_BIN

ENT5864 5 SORME SQL_Latin1_General_CP850_BIN

TAI06 1 MANDT SQL_Latin1_General_CP850_BIN

Br, Juha

[Former Member]

Hi,

it means some object having different collation

check https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=287215 might help you

regards,

kaushal

[clas_hortien]

Ahhh.

So you have two tables that were not converted correctly.

This is the principal procedure to fix it:

- make a backup of your database

- script the tables ENT5864 and TAI06 with the SQL DEV. Studio (rightclick on the table..).

Include the indexes, collation and defaults as well

- rename the existing table with

setuser 'aut'

exec sp_rename ENT5864, ENT5864_SIK -- renaming the table

exec sp_rename [ENT5864_{0], [ENT5864}0_SIK] -- renaming the clustered index

exec sp_rename TAI06 , TAI06 _SIK -- renaming the table

exec sp_rename [TAI06 ~0], [TAI06 ~0_SIK] -- renaming the clustered index

- change the script and replace SQL_Latin1_General_CP850_BIN with

SQL_Latin1_General_CP850_BIN2

- run the script to create the tables correctly

- pump the data from the SIK table into the new one with

insert into ENT5864 select * from ENT5864_SIK

insert into TAI06 select * from TAI06_SIK

Best regards

Clas

[juhaniemi]

Hi

Thanks for info. Actually there are lot more tables with wrong collation order.

Any way to correct them all ?

Br, Juha

[clas_hortien]

Hi,

when did you perform the BIN2 conversion ?

Did you move from SQL 2000 lately ?

If you still have the SQL 2000 BIN database you can convert it with the instcoll tool from note 600027. If you only have the SQL 2005 DB left, you have to perform a homogenouse system copy with r3load (unload/load the data).

Regards

Clas

[juhaniemi]

Hi

No we have no sql server 2000 anymore and we did run instcoll.exe in old system.

It's funny that this client is not going to unicode ever because they are local operator and BIN2 is only nnede for unicode.

Br, Juha

[juhaniemi]

Hi

It check few SAP tables and they all show that BIN2 collation order is on. But that tables colums has BIN collation order. How is that possible because old system (sql server 2000) we installed SP4 and run instcoll.exe) ?

Br, Juha

Edited by: Juha Niemi on Jan 23, 2008 4:55 PM

[clas_hortien]

Hi,

i have no idea how this could happen, if the conversion finished successfully.

But if you only have these two tables wrong you can easily fix them as described.

Regards

Clas

[juhaniemi]

Hi

But i think every table colum has wrong collation order (i checked few random tables).

Sql server collation order BIN2

Database AUT collation order BIN2

Tables in database collation order BIN2

But only table colums has wrong order BIN

Is it possible to rebuild entire database ?

Could i export whole database and import again using sql server own tools ? Dowes this correct the problem ?

Br, Juha

Edited by: Juha Niemi on Jan 23, 2008 6:07 PM

[Former Member]

It sounds like the problem originated before you started the upgrade.

In the table definition, when they were created, it probably specified BIN instead of using the server collation.

Now, when you do the conversion, only those columns with the server collation (default) are being converted to BIN2.

In the past did this data ever get migrated out of the DB to text and reloaded? You may have to script a change of collation for every table, defining all columns use the server collation... depending on DB size, this may take a long time.

Here is some more information:

http://msdn2.microsoft.com/en-us/library/ms190920.aspx

[former_member352619]

Hi there,

I'm running into this same issue when trying to run PREPARE for the 4.6c to ERP/ECC 6.0 upgrade in phase INITPUT_PRE. I ran several of the queries mentioned in this thread and discovered that the database and instance are both BIN2, and 116230 columns are BIN2, but another 319398 columns are still BIN. Without reverting the database (eek, somehow) back to MSSQL2000 and rerunning instcoll.exe (again, I already did once) or building an alter table query for each one that is out of spec (which according to MSDN won't work on certain cols anyway), how can this be resolved?

for my SID database I have the following column types in count:

SQL_Latin1_General_CP850_BIN2 116230

Latin1_General_BIN 29

NULL 58467

Latin1_General_CI_AS_KS_WS 11

SQL_Latin1_General_CP850_BIN 319398

But the databases are all running BIN2 as such:

Server SQL_Latin1_General_CP850_BIN2

Master SQL_Latin1_General_CP850_BIN2

Model SQL_Latin1_General_CP850_BIN2

TempDB SQL_Latin1_General_CP850_BIN2

SID SQL_Latin1_General_CP850_BIN2

The checkdb procedure came back with no errors:

CHECKDB found 0 allocation errors and 0 consistency errors in database 'PD1'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

And the system appears to be functioning correctly, except that the Upgrade Assistant won't let me continue with the mismatched column collation types.

Suggestions? I noticed there was never a resolution posted.

Thanks,

--ben

[clas_hortien]

Hi,

if you don't have the SQL 2000 database anymore (to rerun the collation conversion) and you are already on SQL 2005 you have to do a homogenouse system copy with R3load to get this fixed. So you have to unload the data, drop the database and then reload the data. The r3load will create the tables and columns in the correct collation then.

Regards

Clas