Unicode Handling for MS SQL Server
An old healthy interfaces recently moved to error zone due to a requirement pop-up that sender R/3 system would now be sending Greek characters to receiver MS SQL Server database. The Idoc would have some entries like below.
The problem was however very well solved by editing the RFC destination at R/3 from non-unicode to unicode. After the change, the message was as below:
Quite happy with our swift resolution of problem, we monitored the message in sxmb_moni and then in RWB for jdbc receiver. As expected, it results didn’t turned us down. So far so good.
Then the complaint initiated from receiver systems hosting MS SQL Server database. The problem was that they seems to receive all Greek characters sent by PI replaced by a new character – “?” this time. The shy database unveils the following view:
First we thought that it should be database related setting, but they confirmed that manually inserting the characters work for them. The target of our initial suspicion and investigation was the connection URL, but unfortunately we couldn’t prove the case against him. Slowly, we were accepting that something needs to be done at PI. And then we found out what exactly has to be done.
The important point to be added in mapping is as shown below.
- We need to set an indicator ‘N’ in front of the actual special character string while performing the mapping. The special characters in this case should be surrounded by single quotes.
2. We need to edit the datatype and add the attribute “hasQuotes”
The new datatype would look like this:
And thats it. We are done. Now the database seems to be shining in full glow:
We concluded the following:
- MS SQL Server database does not behave as expected if we just change only the connection URL. The connection URL solution usually works for other databases.
- There is no way PI could understand that the data is not placed in proper format (in integration engine monitoring or message monitoring) unless a specific check is made at the receiver side.