Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Linker server to AS/400 DB2 - character translation problems Expand / Collapse
Author
Message
Posted Monday, October 4, 2010 1:18 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, August 25, 2014 2:53 AM
Points: 321, Visits: 606
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.



Post #997384
Posted Wednesday, October 6, 2010 5:24 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, August 1, 2011 4:37 PM
Points: 72, Visits: 196
Try setting Force translate=0
Post #1000059
Posted Thursday, October 7, 2010 12:07 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, August 25, 2014 2:53 AM
Points: 321, Visits: 606
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.




Post #1000133
Posted Thursday, October 7, 2010 2:29 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, August 25, 2014 2:53 AM
Points: 321, Visits: 606
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.










  Post Attachments 
Clipboard01.jpg (503 views, 15.26 KB)
Clipboard02.jpg (498 views, 12.78 KB)
Clipboard03.jpg (490 views, 7.91 KB)
Post #1000186
Posted Friday, October 8, 2010 7:32 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, August 25, 2014 9:03 AM
Points: 486, Visits: 1,221
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]



Post #1001293
Posted Friday, October 8, 2010 8:09 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, August 25, 2014 2:53 AM
Points: 321, Visits: 606
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. ;)




Post #1001331
Posted Tuesday, October 12, 2010 4:00 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, August 25, 2014 2:53 AM
Points: 321, Visits: 606
***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









Post #1002649
Posted Monday, July 2, 2012 5:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 1, 2012 3:35 AM
Points: 3, 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!
Post #1323741
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse