Error 7302 on SQL2005 creating Linked server to AS400 DB2 using db2oledb

Question

Hi all,
I have a puzzeling problem. I can't get a successful connection to a AS400 DB2 database.
Here's the setup:
SQL2005 SP4 in VMWARE W2K3 sp3. Connection setup, tested and ok.
SQL2005 SP4 Instance3 in Active Cluster W2K3 SP3. Connection setup, tested and ok.
SQL2005 SP4 Instance2 in Active Cluster W2K3 SP3. Connection setup, tested and ok.

SQL2005 SP4 Instance1 in Active Cluster W2K3 SP3. Connection setup, tested and failed.

On each of the sql servers I installed db2oledb from the same source successfully. On each server I can connect using the Data Access Tool.

Only on Instance1 I get a connection error. I have unset and set the AllowInProcess option to no avail.

Any ideas to get this working? 
Since the Linked Server is working on other (identical) servers, I can get to the data, but not from Instance1. Don't think I can do a [link-server].[linked-server] connection?


Here's the connection error:

===================================

"The test connection to the linked server failed."

===================================

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

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
   at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(String cmd)
   at Microsoft.SqlServer.Management.Smo.LinkedServer.TestConnection()
   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.LinkedServerConnectionTest.Invoke()

===================================

Cannot create an instance of OLE DB provider "DB2OLEDB" for linked server "ENIACDB2". (.Net SqlClient Data Provider)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.5000&EvtSrc=MSSQLServer&EvtID=7302&LinkId=20476

------------------------------
Server Name: TECHCYB1-SQL01\TECH01
Error Number: 7302
Severity: 16
State: 1
Procedure: sp_testlinkedserver
Line Number: 1


------------------------------
Program Location:

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)

---

Cees Cappelle

Answer 1

Best way for server to server data transfer is the SSIS Import/Export Wizard (or SSIS in general).

Wizard blog post: http://www.sqlusa.com/bestpractices/ssis-wizard/

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 2

Could you check to see if the DB2OLEDB provider has the "AllowInProcess" option selected?   I believe that this is needed for some linked servers to IBM equipment.

RLF

Answer 3

Kalman, That might be true, but my knowledge of SSIS is about next to nothing ;)

I'm not trying to import data, but to connect two datasources to see where the information differs. Besides, I have more people around me that know about T-SQL than about SSIS (0).

But then again, SSIS is on my knowledge wishlist.

Cees Cappelle

---

Cees Cappelle

Answer 4

Russel, 

I did check the AllowInProcess flag. Unset and set it repeatedly. I've uninstalled and installed the db2oledb set also repeatedly. Checked the registry for the AllowInProcess settings on the Provider. no luck yet.

Any other tips?

Cees Cappelle

---

Cees Cappelle

Answer 5

Can you run SQL Profiler / SQL Trace and trace the call to the linked server? 

If so, see if any errors appear in the Profiler trace.

(And, of course, triple check every setting between the servers.)

RLF

Answer 6

Russel,

I've two servers with a working connection and one without. Which one do you want me to trace? There are no related errors in the events log on the not working box.

Cees Cappelle

Answer 7

I was only interested in the connection that did not work.  If you do trace it, please filter the trace to exclude other connections so as to keep it readable.

Of course, if something shows up in trace, you may well discern the problem on your own.

All the best,
RLF