linked server using IBM OLE DB Provider for DB2

[Cismail via SQLMonster.com]

Hello,

I am trying to create a linked server in SQL Server 2005 using the provider
IBM OLE DB Provider for DB2 to connect to an AS400.
IBM DB2 connect version 9 is installed on the server and a database
connection called AS400DB
exists in DB2.
A system DSN called AS400DSN also exists on the server.

Can anyone provide details on how to set up a linked server for this
configuration???

Thank you in advance for any feedback!

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-connectivity/200902/1

[Russell Fields]

The response in the following thread from 2005 offers a detailed set of
steps.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49610

RLF

"Cismail via SQLMonster.com" <u14416@uwe> wrote in message
news:923de8c1cf3aa@uwe...

[Cismail via SQLMonster.com]

Hi Russell,

Thank you so much for the reply.

The link was helpful, unfortunately I do not have Microsoft OLE DB Provider
for ODBC Drivers installed on the server.
I created a test.udl file using IBM OLE DB Provider for DB2(driver installed
by DB2 connect) intsead of the above mentioned driver.
There was no option where I could specify the initial cataog(database) in the
udl configuration window.

Result was that I was able to create the linked server using the provider
string generated by the udl file.

I tested the connection(right clicked on linked server that I created and
selected Test Connection) and that was successful, however, when I click on
tables to see the files on the AS400 I get the following
error message:

The OLE DB provider "SQL Server" for linked server "(null)" reported an error.
Authentication failed.
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "SQL
Server" for linked server "(null)". The provider supports the interface, but
returns a failure code when it is used. (Microsoft SQL Server, Error: 7399)

Any help would really be appreciated....I really need to get this working.

Once again, thanks for any replies!

Russell Fields wrote:
>The response in the following thread from 2005 offers a detailed set of
>steps.
>http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49610
>
>RLF
>

>> Hello,
>>
>[quoted text clipped - 10 lines]

[Russell Fields]

From the IBM Support site:
http://www-01.ibm.com/support/docview.wss?uid=nas1d7cd7e4509cc894c86257280005c65dc
"The OLE DB provider must be configured to allow inprocess. Under server
Objects -> Linked Servers -> Providers right click on the provider being
used (IBMDASQL, IBMDA400, or IBMDARLA) and select properties. Make sure
"Allow Inprocess" is checked.

If the user is getting "Msg 7399, Level 16, State 1, Line 1" type errors
when attempting to run the sample select statement, this is a problem with
the SQL Server service and the rights the user it is running under has.
There are two things that can affect this. Note that this is all Microsoft
code-related and is as-is at best. Assistance with this should be directed
to Microsoft service and support."

If that does not give you a clue, then could you provide a script of your
linked server, linked server logins, and the udl file? (You can get the
linked server and linked server logins by right clicking on the linked
server definition and choosing Script Linked Server As \ Create.) That
might help someone understand a little better.

You mentioned you do not have the Microsoft OLE DB Driver, but it is freely
downloadable here:
http://blogs.msdn.com/dotnetinterop/archive/2006/01/19/oledb-provider-for-db2.aspx

It is part of the Feature Packs for SQL Server, all freely downloadable at
these locations:
http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en
-- SQL Server 2005
http://www.microsoft.com/downloads/details.aspx?familyid=228de03f-3b5a-428a-923f-58a033d316e1&displaylang=en&tm#QuickInfoContainer -
- SQL Server 2008

Another resource still focused on using the Microsoft OLE DB driver:
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1326266,00.html

Then there are some other links that may hold some interest for you:
http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=65&threadid=47240&enterthread=y

Hope that something helps.
RLF

"Cismail via SQLMonster.com" <u14416@uwe> wrote in message

news:9241269635a83@uwe...

[White@discussions.microsoft.com Ken White]

I'm having the same problem except I'm using a Linked Server (microsoft ODBC
connection) to a progress database. When I try to query / expand the catalog
of the linked server on 2005 I get the famous Cannot obtain the schema rowset
dbschema_catalogs for ole db provider sql server for linked server nul blah
blah.. error 7311. Under SQL 2000 I'm able to expand the entire catalog
listing of the linked server no problem.. now that we've moved to 2005, I can
no longer do this.
I've verified that the providers preference for SQLOLEDB is set to allow
inprocess but that did not eliminate the error. Any suggestions?
We're running Windows 2003 Server, SQL 2005 32 bit connecting to a progress
DB using the 10.1b Progress Drivers (ODBC). Works GREAT on SQL 2000. Same
config on 2005 SQL returns the error, yet I can query data using the DB in a
SQL QUERY no problem.