servers to DB2 and AS400

Hi!
I would like to set up linked servers to DB2 and AS400 in SQL server and update the tables in these two databases via stored procedures in SQL servers.

I have read articles on Microsoft site which indicate installing Microsoft Host integration server and use SNA server 4.0 Service pack 4.0 to

configure DB2OLEDB drivers.

Could any one suggest how do go about doing this.

Do I need to buy Host integration server or is there any other way to do it.

your help is much appreciated

NalinaThere are more ways to skin a cat than there are cats. I can think of several ways to do this, from several different vendors.

My suggestion would be to approach your vendor of choice (probably IBM in this case), and ask them to propose a solution. If that solution works for you, and is affordable, I'd run with it. If not, open up the bidding to at least two more vendors (and inform your vendor of choice, of course), then see what happens.

The biggest problem that I see is that when you are doing this kind of integration, there are many factors that come into play. Without considerable knowledge of your circumstances it would be easy to lead you astray and cost you an enormous amount of extra money or create a solution that was only part of what you need.

-PatP

server using SQL Native Client

We have a SQL 2005 SP2 64 bit server that needs to communicate with our 32 bit AS400 server. We have stored procedures in databases that query the AS400 (generally just select statements). I am not understanding the procedure to connect to the AS400 now that the OLE DB for ODBC provider no longer exists.

I know how to create a system DSN (and that it will be located in C:\WINDOWS\SysWOW64\odbcad32.exe). I called the System DSN 'ConnectMe'. The server I am trying to access is 'ServerA'. I have filled out the following to create a linked server:
Linked Server: AS400
Provider: SQL Native Client
Product Name: AS400 (from what I understand, this holds no value and only serves as a description)
Data source: ServerA
Provider String: dsn::=ConnectMe (same as system DSN)
Catalog:<I left it blank>
Under the security tab I have the remote username and password that should be used.

When I try to create the linked server, I get the error:
"OLE DB provider "SQLNCLI" for linked server "AS400" returned message "An error has occured while establishing a connection to the server. ..may be be caused by the fact that under the default settings SQL Server does not allow remote connections"

Remote connections for both TCP/IP and named pipes is enabled and SQL services have restarted to enable that. What am I missing?

64-bit OLE DB provider for ODBC is going to be available with Vista SP, or Longhorn.

SQL Native Client can only be used to connect to a Microsoft SQL server.

You might also want to check the IBM's OLE DB provider for AS400 and see if you can use it. Have a look at http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/rzaik/rzaikoledbprovider.htm

|||

Thanks for the reply. I read from various forums that SQL native client can also access ODBC data (http://technet.microsoft.com/en-us/library/ms131415.aspx) but I haven't been able to get it to work.

I tried that OLE DB provider from AS400 but performance is terrible (using OLE DB for ODBC on a 32 bit machine takes around 2 minutes to query one of our tables and using the IBM driver, it takes over 6 minutes to query the same table).

I guess our SQL 2005 farm will have to go back to 32 bit until Longhorn which is disappointing but I guess that's what happens sometimes.

Thanks anyways for your help.