SQL Server 2005 CodePage Issue

Question

I have a problem I am trying to resolve. We have a SQL Server 2005 running a commercial ERP system. The implication for this is that we cannot change the database structure and all of the character fields are CHAR or VARCHAR rather than Unicode types (NCHAR, NVARCHAR).

We also have multiple instances of the ERP software, based on country. Each country has it's own database on the same database server, which results in variations in the table names based on the instance of the ERP software that is running. For example, the US customer table is called US_CUSTOMER and the UK one is GB_CUSTOMER. We have created a separate database that essentially mirrors the ERP system tables with synonyms, and then views that handle all of our SQL transactions against these synonyms. This was done to use LINQ TO SQL. Thanks for reading this far :)

The issue we have is we are now implementing Simplified Chinese for the application. In the customer ERP system, they set the code page for the ERP system so that when the ERP system writes to the base tables, the data is written as multi-byte. My question is how can I get this multi-byte information translated back to Simplified chinese? I would like to be able to do this at the database level, since I have both a web application and SSRS reports that need to take advantage of it.

Any ideas or directions? I don't think I can change the codepage, since multiple countries are using the same database server (though different databases).

Thanks ahead of time

Answer 1

Are we saying that 2 varchar characters are being using to store 1 unicode character?

If so, try CAST to binary to nvarchar etc (or something similar)

Otherwise, look at COLLATE clauses to coerce data?

Edit:

A CLR function might be your only bet to use Remus' suggestion of MultiByteToWideChar

Answer 2

What we ended up doing for this is writing a CLR function that can be called from our SQL statement. We pass in the string and the desired code page and get a converted string returned. The performance is not what we hoped for, but it seemed to be the only path we could find.