SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Linker server to AS/400 DB2 - character translation problems


Linker server to AS/400 DB2 - character translation problems

Author
Message
JarJar
JarJar
SSC Eights!
SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)

Group: General Forum Members
Points: 814 Visits: 1075
I am trying to set up a linked server to an AS/400 DB2 database but the results are garbage for special characters (accents). I have tried using a provider string from a working web connection that we have, however, not all the options in the provider string are compatible in the addlinkedserver sp in SQL, so i had to go through an exercise of trying each option one at a time and throwing away the options that do not work. Below is the provider string after throwing out all the invalid options. It still does not work.

I found some info on another forum regarding the Force Translate option, but it does not seem to help: http://www.experts-exchange.com/Database/Miscellaneous/Q_24518322.html


Can anyone shed some light on how to resolve this problem? THANKS


I'm using the OLE DB provider from IBM. I have tried the IBM ODBC driver as well but I get the same results.

EXEC master.dbo.sp_addlinkedserver @server = N'TEST1', @srvproduct=N'AS400', @provider=N'IBMDASQL', @datasrc=N'XXXXXXXX',
@provstr=N'User ID=USERxxx;Password=pwdxxx;Transport Product=Client Access;SSL=DEFAULT;Force Translate=65535;Default Collection=zzzzzzzz;Convert Date Time To Char=TRUE;Catalog Library List="";Use SQL Packages=False;SQL Package Library Name="";SQL Package Name="";Add Statements To SQL Package=True;Unusable SQL Package Action=1;Block Fetch=True;Data Compression=True;Sort Sequence=0;Sort Language ID="";Query Options File Library="";Trace=0;Hex Parser Option=1;Maximum Decimal Precision=31;Maximum Decimal Scale=31;Minimum Divide Scale=0'


SELECT CNME FROM OPENQUERY ([TEST1], 'SELECT * FROM zzzzz.zzzzz') WHERE CCUST ='xxxxxx'

CNME
------------------------------
GR┴FICAS XXXXXX, S.L.

(1 row(s) affected)


Should be:

GRÁFICAS XXXXXX, S.L.



jmelnick
jmelnick
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 196
Try setting Force translate=0
JarJar
JarJar
SSC Eights!
SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)

Group: General Forum Members
Points: 814 Visits: 1075
That was actually a mistake in the above cut and paste. It was from one of my later experimental tries. I have tried Force Translate = 0 (and = 1) with no difference.



JarJar
JarJar
SSC Eights!
SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)

Group: General Forum Members
Points: 814 Visits: 1075
More strangeness -

I use the IBM Data Transfer from Iseries program to test out a direct transfer outside of SQL. When I connect and transfer to 'display', I see the characters correctly. When I transfer to a text file, they are garbage in the text file AND the garbage is different than what I see in SQL.









Attachments
Clipboard01.jpg (1.4K views, 15.00 KB)
Clipboard02.jpg (1.4K views, 12.00 KB)
Clipboard03.jpg (1.4K views, 7.00 KB)
Ed Zann
Ed Zann
SSC Eights!
SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)

Group: General Forum Members
Points: 855 Visits: 1391
I use the iSeries ODBC driver provided by IBM. Nothing fancy in the ODBC data source set up. Just the IP address, login info, and default db - everything else is left at default setting. There is a check box on the Translation tab which is not checked on our linked server.

I scripted out my linked server below - maybe you will see something....

I'm curious why you use the OPENQUERY syntax. When I query the AS400 via the linked server, I use the 4 part syntax lnksrv.db.schema.table which has always worked fine for me.

[/code="sql"] EXEC master.dbo.sp_addlinkedserver @server = N'linksvr', @srvproduct=N'dsnname', @provider=N'MSDASQL', @datasrc=N'dsnname', @catalog=N'dbname'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'linksvr',@useself=N'False',@locallogin=NULL,@rmtuser=N'login',@rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @server=N'linksvr', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'linksvr', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'linksvr', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'linksvr', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'linksvr', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'linksvr', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'linksvr', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'linksvr', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'linksvr', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'linksvr', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'linksvr', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'linksvr', @optname=N'use remote collation', @optvalue=N'true'[/code]



JarJar
JarJar
SSC Eights!
SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)

Group: General Forum Members
Points: 814 Visits: 1075
Ed, thanks for the help but sadly, I still have the same problem.

The OPENQUERY syntax is from our developer. I let him run with it as it doesn't matter to me. Wink



JarJar
JarJar
SSC Eights!
SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)

Group: General Forum Members
Points: 814 Visits: 1075
***SOLVED***

The problem is that there seems to be a bug in the IBM 64bit ODBC driver. We have ODBC connections working properly to this AS/400 database for other applications (MS Access) so I installed Access on this server and setup a 32bit ODBC connection. It worked perfectly. The same ODBC connection params on the 64bit driver does not work. No matter what combination of params I tweaked on the 64bit driver, it does not work. And since SQL is 64bit, I cannot use the 32bit driver.

IBM has a utility called "Data Access Tool" which is basically a wizard to setup an OLEDB provider. I used this tool to create the provider, which also gives me a text provider string which I used to create my linked server with. It works fine now.

Provider=DB2OLEDB;User ID=xxxxxx;Password=xxxxxx;Initial Catalog=xxxxxx;Network Transport Library=TCP;Host CCSID=37;PC Code Page=1208;Network Address=xxxxxx;Network Port=446;Package Collection=xxxxxx;Process Binary as Character=False;Units of Work=RUW;DBMS Platform=DB2/AS400;Defer Prepare=False;Persist Security Info=True;Mode=Read;Connection Pooling=False;Derive Parameters=False;

(do not need "Provider=DBOLEDB;" in provider string for linked server definition)

EXEC master.dbo.sp_addlinkedserver @server = N'BPCS_OLEDB', @srvproduct=N'BPCS', @provider=N'DB2OLEDB', @datasrc=N'BPCS_OLEDB',
@provstr=N'User ID=xxxxxx;Password=xxxxxx;Initial Catalog=xxxxxx;Network Transport Library=TCP;Host CCSID=37;PC Code Page=850;Network Address=xxxxxx;Network Port=446;Package Collection=xxxxxx;Process Binary as Character=False;Units of Work=RUW;DBMS Platform=DB2/AS400;Defer Prepare=False;Persist Security Info=True;Mode=Read;Connection Pooling=False;Derive Parameters=False;Force Translate=0;'
GO



munchkinshunny
munchkinshunny
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 72
I'm having problems displaying Thai characters from my Linked Server.
I'm using iSeries Access for Windows ODBC data source and MSDASQL provider
I'm getting garbage characters. CCSID from AS400 is 838, but still not recognised.
Help! Sad
Krishnan pazhayanoor
Krishnan pazhayanoor
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 23
Not sure if this thread is still active but i would appreciate any help with this - I have connected to an AS400 DB2 server through an SQL Server Linked Server connection. I'm trying this through the IBMDA400 as well as IBMDASQL providers. Through both providers, i have absolutely no issues querying data from a file. However, while "writing to" a file, (i'm assuming this translates to an insert statement into a table), i'm having a tough time executing the insert. "I get the error message Implicit conversion from data type varchar to varbinary is not allowed. Use the CONVERT function to run this query.". While I could and did use the CONVERT(varbinary, <column/data>Wink function to convert it to varbinary, the data gets garbled at the other end. When i try to select from the same table, I get values that are also not quite right. For instance, i get the value of 0x40 for a blank/empty space instead of 0x20 (which is CONVERT(varbinary, ' ')). Has anyone faced this issue and has solved it?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search