Click here to Skip to main content
Click here to Skip to main content

Solving the collation mix with SAP

, 20 Jun 2006
Rate this:
Please Sign up or sign in to vote.
Solving the collation mix with SAP (SQL_Latin1_General_CP850_BIN vs. Latin1_General_CI_AS).

Introduction

If you run your SAP installation on SQL Server (does not matter if you use version 2000 or 2005), you have to use the SQL_Latin1_General_CP850_BIN collation (see e.g., http://searchsap.techtarget.com/generic/0,295582,sid21_gci1169892,00.html?bucket=REF). This is not the default collation in SQL Server; it would use Latin1_General_CI_AS if you follow the suggestions of the installer. This leads into problems if you want to correctly transfer data from your SAP database into, e.g., your DWH database running on SQL Server's standard collation via a linked server.

Fortunately, you can solve this problem quite easily. Before we discuss the solutions, let me give you more details about the problem. As a starting point, I execute the following SQL SELECT statement on a SAP database running on SQL_Latin1_General_CP850_BIN:

select MTEXT, cast(MTEXT as varbinary) from T000

It returns all rows from table T000 and displays the content of the varchar column MTEXT as text as well as in binary format. In my example, I have one row which contains an umlaut (German "Umlaut O" character: Ö): Österr.[...]. If we take a look at the binary format of this text, it contains the following ASCII-codes:

 Ö  s  t  e  r  r  . 
D6 73 74 65 72 72 2E 

If you like, you can check the character codes yourself. You can look up a character map for codepage 850 in Wikipedia at http://en.wikipedia.org/wiki/CP850. When I did that, I was quite surprised that the code for Umlaut O was not correct (it should be 99; it is D6). In fact, D6 is the character code of Umlaut O in codepage 1252 (=Latin1_General_CI_AS; see http://en.wikipedia.org/wiki/CP1252)! It seams that SAP demands codepage 850 but stores values using codepage 1252. So let's see how this affects us when transferring data using a linked server.

In this article, I assume that the destination server is running on SQL Server version 2005. If you create a linked server there, you have two options concerning collations:

  • Use Remote Collation = True or
  • Use Remote Collation = False

Create the linked server and try to execute the following query one time with Use Remote Collation set to true and the other time set to false:

select x1.MTEXT, cast(x1.MTEXT as varbinary)
from SAP01SQL.P01.dbo.T000 x1 
inner join SAP01SQL.P01.dbo.T000 x2 on 
x1.MANDT=x2.MANDT 
and x1.MTEXT=x2.MTEXT 
and x2.MTEXT like '_sterr%'

The result with Use Remote Collation set to false looks like this:

As you can see, the result is correct! The Umlaut O is displayed correctly. Why? Simple reason: The setting Use Remote Collation: false tells SQL Server to retrieve the content of the linked server without any codepage conversion. As we saw before, SAP stores ANSI character codes (=codepage 1252); therefore ignoring all conversions leads to the correct result. However, this solution leads to one important disadvantage: SQL Server cannot pass joins and where-clauses to the remote server because the remote server would use its codepage and we have told SQL Server not to use the remote collation. Therefore the content of all tables is completely transferred to the target server and joins, as well as where-clauses are executed on the target server -> poor performance. Here is the execution plan that shows what's happening:

Now let's change the setting of Use Remote Collation to true and execute the SELECT statement again. Here is the result:

This time the result is wrong. The reason is that SQL Server tries to convert the content of the column in codpage 850 to ANSI (=codpage 1252). Remember that SAP writes ANSI character codes into an ASCII column -> the conversion leads to the wrong result shown above. The advantage is that joins and where-clauses are executed on the SAP server -> good performance:

It seems that we have to choose between the correct results with poor performance or wrong results with good performance?! No, there are ways that ensure correct results and good performance.

The first solution is to encapsulate joins and where-clauses on the SAP server in views. You can access this views with Use Remote Collation set to true and you will get the correct results. The joins and where-clauses are executed on the SAP server. As an alternative, you could also use OPENQUERY instead of views.

If you do not want to put business logic on your SAP server but put the logic into the target server, you could change the collation manually. For this, I wrote a small C# assembly that you can use in SQL Server 2005. Here is the code:

using System; 
using System.Text; 
using System.Data; 
using System.Data.SqlClient; 
using System.Data.SqlTypes; 
using Microsoft.SqlServer.Server; 

public partial class UserDefinedFunctions 
{ 
  [Microsoft.SqlServer.Server.SqlFunction] 
  public static SqlString ConvertCodepage( byte[] binString, SqlInt32 len ) 
  { 
    if (len.IsNull) 
      return SqlString.Null; 
    else 
    { 
      Encoding ai = Encoding.GetEncoding(1252); 
      return new SqlString(ai.GetString(binString, 0, len.Value)); 
    } 
  } 
};

You can deploy this function and use it in the SELECT-statement shown before:

select x1.MTEXT, cast(x1.MTEXT as varbinary), 
  dbo.ConvertCodepage( cast(x1.MTEXT as varbinary), len(x1.MTEXT) ) 
from SAP01SQL.P01.dbo.T000 x1 
inner join SAP01SQL.P01.dbo.T000 x2 on 
x1.MANDT=x2.MANDT 
and x1.MTEXT=x2.MTEXT 
and x2.MTEXT like '_sterr%' 

Check the result and you will see that it works:

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

r.stropek
software architects
Austria Austria
Hi, my name is Rainer Stropek. I am living a small city named Traun in Austria. Since 1993 I have worked as a developer and IT consultant focusing on building database oriented solutions. After being a freelancer for more than six years I founded a small IT consulting company together with some partners in 1999. In 2007 my friend Karin and I decided that we wanted to build a business based on COTS (component off-the-shelf) software. As a result we founded "software architects" and developed the time tracking software "time cockpit" (http://www.timecockpit.com). If you want to know more about our companies check out my blogs at http://www.software-architects.com and http://www.timecockpit.com or take a look at my profile in XING (http://www.openbc.com/hp/Rainer_Stropek2/).
 
I graduated the Higher Technical School for MIS at Leonding (A) in 1993. After that I started to study MIS at the Johannes Kepler University Linz (A). Unfortunately I had to stop my study because at that time it was incompatible with my work. In 2005 I finally finished my BSc (Hons) in Computing at the University of Derby (UK). Currently I focus on IT consulting, development, training and giving speeches in the area of .NET and WPF, SQL Server and Data Warehousing.

Comments and Discussions

 
Generalquite useful thank you Pinmembermatro22-Aug-06 15:02 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140821.2 | Last Updated 20 Jun 2006
Article Copyright 2006 by r.stropek
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid