Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
badkneecap
Starting Member
10 Posts |
 Posted - 2012-09-28 : 18:05:26
|
| 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 INCAA0012 ???????(??)???AB0011 CARBONATE INCThe 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 INCAA0012 ?y<0+O(?(s)!=(.AB0011 CARBONATE INCSimilar, 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
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-09-29 : 07:51:49
|
| 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? |
 |
|
|
badkneecap
Starting Member
10 Posts |
Posted - 2012-09-29 : 08:56:37
|
| 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. |
|
|
|
|
|
|
|
|
Link Server to DB2 and Chinese Characters