Hello,

Just purchased SQL Server 2014.  I am trying to get a linked server setup to our AS400 using the MS OLE DB Provider for DB2 v5 x64.

I've got the linked server setup, connected and working but it is not translating the CCSID 65535 fields.  I have set the option "Process binary as character=true" and "Binary Codepage=37".  From what I have read, these to options should solve my problem but they are not doing the trick.  The issue is anything that is set with a CCSID 65535 on the 400 is being returned something like this "0x40404040404040404040".  Fields with a CCSID of 37 are being returned normal and readable.  

I should mention, this same setup is working on our SQL 2005 server as we speak.  It is an older version of the OLE DB Provider for DB2 and does not contain the "Binary Codepage=37" but I have tried it with that, without that and with Binary Codepage=0 on the new server to no avail.  I have also tried version 4 of the Provider, Translate binary as character=true, BinasChar=true, and none of it works.

Anyone have any suggestions?  I worked on this for a full day and couldn't get it to go.

We use an ODBC connection for the iSeries instead. The ODBC driver is installed with IBM i Access for Windows. I think you'll have an easier time setting it up.

Set up the ODBC connection first. Then setup your linked server in SQL, and reference the ODBC connection.

If you look at the properties of the ODBC connection it has an option to "Convert binary data (CCSID 65535) to text."

We use this everyday to interact with our iSeries and it works great.

Greg

Hey Greg,

Thanks for the suggestion.  I actually did attempt this.  I got as far as it listing my tables and when I attempted so do a quick select * from one of the tables, it said there were no columns that I had access to.

I know the user id I was using has access to these tables as we use it everyday for other ODBC connections.  I have created a system DSN called AS400, I attempted to use the Microsoft OLE DB Provider for ODBC drivers on the SQL side, I am not sure what provider string I should use to pass the username and password to the ODBC driver. I attempted using uid=username;pwd=password. I filled in the data source to match the DSN (AS400) and I couldn't get any fields in any tables to show up, just the tables themselves.  Any pointers on getting that setup?

Make sure Data Access is enabled in Server Options for the Linked Server.

Where are you setting a provider string?

There are some security options on the SQL Linked Server where you can specify the username and password to use when SQL Server uses the connection. I use the last option, and specify a username and password that's valid on the iSeries.

 

Just found where you can set the username. Again this is a username that uses this table all the time through other ODBC connections so I must still have something off here. Data access is enabled in server options and I still receive this:

contains no columns that can be selected or the current user does not have permissions on that object.

Hey Greg,

Just wanted to let you know I did get it working through ODBC. I am still curious why Process Binary as Character=1 doesn't work on the OLE provider. 

I do still get the error message above if I just right click on a table and select Script Table as > Select to > New Query Editor Window. If I write an actual select * from link.catalog.library.table it works. I would also note that I did have to uncheck "Enable pre-fetch of data for queries" on the ODBC under the performance tab.

• Marked as answer by Eric__ZhangMicrosoft contingent staff, Moderator Friday, March 06, 2015 6:09 AM

Glad you got it working.

I get the same message when I ask query analyzer to script a select statement. That message is query analyzer saying it doesn't have schema information from the other server. Nothing to do with actual permissions you have on the tables.

See the following:

PRB: 'Script table as' reports an error for linked servers created using MSDASQL provider in SQL Server 2005 and SQL Server 2008

http://support.microsoft.com/kb/971261

Also finding that the v5.0 DB2 OLEDB provider does not translate binary to text on SQL Server 2014.

We also have this working perfectly on SQL Server 2012 with the v4.0 provider.

We're even running the "hotfix" version supposedly built exactly to solve this problem.

Any news on this?