Linked server returning garbage for special charac

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.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Linked server returning garbage for special charac

Author

Topic

OLDCHAPPY
Starting Member

6 Posts

Posted - 2010-10-05 : 03:33:47

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.

Sachin.Nand

2937 Posts

Posted - 2010-10-05 : 06:05:10

Maybe this


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

PBUH

OLDCHAPPY
Starting Member

6 Posts

Posted - 2010-10-06 : 02:29:40

Nope, same results.

Sachin.Nand

2937 Posts

Posted - 2010-10-06 : 04:44:13

Try this


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

PBUH

OLDCHAPPY
Starting Member

6 Posts

Posted - 2010-10-06 : 04:53:06

Bigger error now, it doesn't like the nvarchar...same with ODBC.

OLE DB provider "IBMDASQL" for linked server "TEST1" returned message "SQL0204: NVARCHAR in *LIBL type *N not found.
Cause . . . . . : NVARCHAR in *LIBL type *N was not found. If this is an ALTER TABLE statement and the type is *N, a constraint or partition was not found. If this is not an ALTER TABLE statement and the type is *N, a function, procedure, or trigger was not found. If a function was not found, NVARCHAR is the service program that contains the function. The function will not be found unless the external name and usage name do not match exactly. Examine the job log for a message that gives more details on which function name is being searched for and the name that did not match. Recovery . . . : Change the name and try the request again. If the object is a node group, ensure that the DB2 Multisystem product is installed on your system and create a nodegroup with the CRTNODGRP CL command. If an external function was not found, be sure that the case of the EXTERNAL NAME on the CREATE FUNCTION statement exactly matches the case of the name exported by the service program.".

Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT convert(nvarchar(100),CNME) FROM zzzzz.zzzzz1" for execution against OLE DB provider "IBMDASQL" for linked server "TEST1".

Sachin.Nand

2937 Posts

Posted - 2010-10-06 : 04:56:41

What is the nvarchar equivalent for AS/400 DB2 database?

PBUH

OLDCHAPPY
Starting Member

6 Posts

Posted - 2010-10-06 : 06:59:45

Good question. But I'm no expert on the AS/400 side of this equation.

It's strange that in Excel it returns results ok but for SQL it seems to have a different behaviour.

OLDCHAPPY
Starting Member

6 Posts

Posted - 2010-10-07 : 04:32:51

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.

OLDCHAPPY
Starting Member

6 Posts

Posted - 2010-10-12 : 05:59:12

***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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources