SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Link Server to DB2 and Chinese Characters
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

badkneecap
Starting Member

USA
10 Posts

Posted - 09/28/2012 :  18:05:26  Show Profile  Reply with Quote
I've been using SQL for many years as a novice user, not a DBA. I know enought to get around. I've run into a problem that has gone way beyond my knowledge of SQL and how data is stored and I have no idea how this problem can be resolved. I've been working on this for 3 days now and cannot seem to get anywhere.

I'm trying to query data from a DB2 iSeries database. I successfully added a linked server and can connect and query data. The problem is some of the data is in Chinese. When I run my simple query, the English characters come through, but the Chinese show as question marks. I've tried changing Collation values, specifying codepages, but cannot find the right combination to get this working.

I have the following SQL:

SELECT vendornum, vendorname FROM OPENQUERY(OLE, 'select CAST(vendorname AS VARCHAR(30) CCSID 935) AS vendorname, vendornum from CHINA.SQLVIEWS.VENDORTEST')

This is what I get:

vendornum vendorname
---------- ------------------------------
AA0011 ARBING INC
AA0012 ???????(??)???
AB0011 CARBONATE INC

The parentheses are correct, but the question marks should be displaying chinese characters.

As you can see from the SQL statement, the VENDOR field is 30 characters long (double byte from what I'm told) and using codepage 935.

If I remove the cast and just reference the field as is, I get the following:

vendornum vendorname
---------- ------------------------------
AA0011 ARBING INC
AA0012 ?y<0+O(?(s)!=(.
AB0011 CARBONATE INC

Similar, but not the same.

I can query DBs on other servers with Chinese code and it displays just fine, so I know if it was correct, that I can see it.

We've tried downloading 3rd party drivers, etc. and nothing seems to be working. I'm really hoping someone else has come across this problem and knows exactly what to do. Any help would be greatly appreciated.

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/29/2012 :  07:51:49  Show Profile  Reply with Quote
I am not familiar with DB2, so this may be completely off. If you were querying a SQL Server, casting to VARCHAR would cause a problem. You would need unicode characters, which in SQL Server is NVARCHAR. You may want to investigate if there is something equivalent in DB2 that you need to cast it to.

You said you were able to query other DB's on other servers - did you mean the same combination i.e., querying DB2 server from SQL Server?
Go to Top of Page

badkneecap
Starting Member

USA
10 Posts

Posted - 09/29/2012 :  08:56:37  Show Profile  Reply with Quote
You are correct, that might be the issue. Casting to a VARCHAR might be the incorrect type. I know very little about DB2, so that was just a guess.

When I said tiehr DBs, I was referring to other SQL DBs that had Chinese characters, so I know I can view them if they came across correctly.

Your response helps. I think I need to talk with someone who is more of an expert with DB2.

I'd still welome any other suggestions on this if anyone else has any ideas.

Thanks again.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000