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.