I am migrating databases from SQL Standard 2005 (32 bit) to SQL Standard 2012 (64 bit) and getting errors when I create the needed Linked Servers. I know for Oracle I had to install Oracle Client and ODAC in order for our linked servers to our Oracle servers to work but I am not familiar with what is required in order to do the same thing for the IBM side. I scripted the as400 linked server from the 2005 server and created it on the 2012 server:
EXEC master.dbo.sp_addlinkedserver @server = N'AS400', @srvproduct=N'DB2 UDB for iSeries', @provider=N'MSDASQL', @datasrc=N'AS400'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'AS400',@useself=N'False',@locallogin=NULL,@rmtuser=N'USER_NAME',@rmtpassword='PSSWD' (plus various sp_serveroptions)
I then verified that the MSDASQL Provider "Allow Inprocess" was checked and the Linked Server is listed as using the provider but when I try to view the objects under the "AS400" Linked Server I get an error saying: Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "AS400". (Microsoft SQL Server, Error: 7303)
I got a similar error from the Oracle Linked Server before I loaded the Oracle Client and ODAC so I assume I need to load something similar for the AS400 Linked Server to work but I dont know what that is or where to get it from? I have tried searching but cant figure out what exactly I need to install or where to get it from. It works on the 2005 server but I was not involved with that server. Can someone please help a linked server newbie?