May 22, 2014 at 2:20 pm
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?
May 23, 2014 at 1:42 am
You need the OLEDB provider for DB2 AS/400 that you can download here:
http://www.microsoft.com/en-us/download/details.aspx?id=29100
-- Gianluca Sartori
June 3, 2014 at 2:29 am
Alternatively, installing Client Access on the box hosting SQL server will install IBM's providers.
June 4, 2014 at 3:19 pm
Although installing the IBM Client was my solution, I still had to figure out the correct parameters to return the objects I needed like the previous install. With IBM tech supports help we were able to figure out my remaining issue which was to get access to both our "QGPL" and "ABC" library objects via the Linked Server. There is also a IBM public document "Configuring an OLE DB Provider on an SQL Server" that helped:
http://www-01.ibm.com/support/docview.wss?uid=nas8N1014514
This is the create statement I ended up with (the names were changed to protect my job!):
EXEC master.dbo.sp_addlinkedserver @server = N'AS400X', @srvproduct=N'DB2 for i', @provider=N'IBMDASQL', @datasrc=N'AS400', @provstr=N'Default Collection=QGPL; Catalog Library List=ABC;', @catalog=N'AS400'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'AS400X',@useself=N'False',@locallogin=NULL,@rmtuser=N'USER_NAME',@rmtpassword='PSSWD' (plus various defaulted sp_serveroptions)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply