Linker server to AS/400 DB2 - character translation problems
-
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.
-
Try setting Force translate=0
-
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.
-
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.
-
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]
-
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. 😉
-
***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
-
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! 🙁
-
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>) 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?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply