oledb >> Linked Server - Force Translate

by Q2xhdWRl » Mon, 21 Feb 2005 07:17:02 GMT

I have created a linked server using the "IBM DB2 UDB for iSeries for OLE DB
Provider" and query it with OPENQUERY. All numeric and date fields come thru
successfully however all text is returned as binary values. I had the same
issue when using a connection string with same provider which was resolved
when I set the Force translate to False.

How can I set the Linked server to not translate ?

oledb >> Linked Server - Force Translate

by Erland Sommarskog » Mon, 21 Feb 2005 07:58:11 GMT


Claude ( XXXX@XXXXX.COM ) writes:

You can specify a connection string when you define your linked server,
see sp_addlinkedserver in Books Online for details.

You can also use OPENROWSET and specify a connection string with it.


--
Erland Sommarskog, SQL Server MVP, XXXX@XXXXX.COM

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

oledb >> Linked Server - Force Translate

by Q2xhdWRl » Mon, 21 Feb 2005 11:59:01 GMT

Thanks Erland,

At first I created it through Enterprise Manger.

After your post, I created it with :
exec sp_addlinkedserver 'JDE',
'',
'IBMDA400',
'Betty',
'',
'Protection Level=None;Force Translate=0;Cursor Sensitivity=3;Initial
Catalog="";Transport Product=Client Access;Catalog Library
List=JDFADAT;Default Collection="";Convert Date Time To Char=TRUE;Extended
Properties="";SSL=DEFAULT;Provider=IBMDA400.DataSource.1',
''

It seem to ignore the Force Translate=0 and still returns binary. Any
suggestions ?

oledb >> Linked Server - Force Translate

by Erland Sommarskog » Tue, 22 Feb 2005 07:36:18 GMT

Claude ( XXXX@XXXXX.COM ) writes:

Not knowing DB2, so this translation business is pretty obscure to me.

Did you try OPENROWSET? I would not be surprised to learn that it
gives the same result.

Hm, what happens if you convert the binary data to varchar, as in

select convert(varchar, 0x41424344)

--
Erland Sommarskog, SQL Server MVP, XXXX@XXXXX.COM

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

oledb >> Linked Server - Force Translate

by Q2xhdWRl » Tue, 22 Feb 2005 12:13:02 GMT


It returns some strange characters when using CONVERT.

I am having problems initializating the properties for the provider when
using OPENROWSET so I am trying to get some more documentation.

oledb >> Linked Server - Force Translate

by Erland Sommarskog » Tue, 22 Feb 2005 16:21:07 GMT

=?Utf-8?B?Q2xhdWRl?= ( XXXX@XXXXX.COM ) writes:

EBCDIC?

No idea, but maybe you should change to Force translate=true and apply
a convert again. Maybe then the EBCDIC will comeback as ASCII then.


Sounds like a very good idea.


--
Erland Sommarskog, SQL Server MVP, XXXX@XXXXX.COM

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

oledb >> Linked Server - Force Translate

by Q2xhdWRl » Wed, 23 Feb 2005 05:29:04 GMT


I'm not sure if it is EBCDIC but was able to get a successful translation by
doing:
SELECT . . . CAST(fieldname as varchar(10) ccsid 037) AS Alias . . .
on all the text fields.

The ccsid 037 represents the DB2 codepage. Painful but it works !

Thanks for you help.
Claude

oledb >> Linked Server - Force Translate

by Erland Sommarskog » Wed, 23 Feb 2005 07:07:35 GMT

Claude ( XXXX@XXXXX.COM ) writes:

That syntax had me taken aback for some seconds, but then I realized
that this is something you do in the DB2 side.

Yes, it looks really kludgy. But I am glad to hear that you got it
working!


--
Erland Sommarskog, SQL Server MVP, XXXX@XXXXX.COM

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp