SQL Server Express 2008 R2 Linked Server to IBM i (iSeries): queries return text as 0xD4F3F1F7F9F1F0

1
votes

Just linked SQL Server 2008 R2 Express to iSeries and can't see my files. I can query them though. Text returned as 0xD4F3F1F7F9F1F0 instead of something like M123450 (that's a work order number). Using the IDM DB2 for IBMDA400 OLE DB Provider.

I tried using a connection string that works for other applications. I get errors:

TITLE: Microsoft SQL Server Management Studio

The linked server has been created but failed a connection test. Do you want to keep the linked server?

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Cannot initialize the data source object of OLE DB provider "IBMDA400" for linked server "S10A7F0P". (Microsoft SQL Server, Error: 7303)

Has anyone encountered anything like this? I'm reasonably new to the whole SQL server thing.

Thanks in advance!

M

1
sql-servertranslationibm-midrange

1 Answers

3
votes

The reason your data is returned as 0xD4F3F1F7F9F1F0 is that the text column was defined as CCSID 65535; which stands for binary data and so by default the DB doesn't translate it from EBCDIC to ASCII/Unicode.

You can see the CCSID of the various columns in a table by using the green screen DSPFFD command or the iNav GUI.

The best way to fix this it to ensure your text columns are tagged with the appropriate CCSID for your language (37 if US).

The alternative is to force the DB to translate text columns by using the "Force Translate" property of the OLEDB driver.