SQL 2000 to an AS/400 Linked server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

SQL 2000 to an AS/400 Linked server

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-05-18 : 08:29:43

Guy writes "I need an example of how to configure a linked server. I'm try to connect to an AS/400 from an SQL 2000 server.

I have tried several (many) solutions.

I keep getting the same error:

Server: Msg 7302, Level 16, State 1, Line 1
Could not create an instance of OLE DB provider 'IBMDA400'.

I'm creating a DSN from Client Access.

Listed below is a listing of the code I'm using.

What am I missing?
--------------------------------------------------------------
EXEC sp_dropserver 'DB2MVS_IP', 'droplogins'
GO
EXEC sp_addlinkedserver
@server = 'DB2MVS_IP',
@srvproduct = 'Microsoft OLE DB Provider for DB2',
@provider = 'IBMDA400',
@datasrc = '172.16.1.20',
@catalog = 'QGPL',
@provstr='InitCat=P390D37;NetLib=TCPIP;NetAddr=MVSrUS;
NetPort=446;PkgCol=MSPKG;DefSch=DB2DEMO'
GO
EXEC sp_addlinkedsrvlogin
@rmtsrvname='DB2MVS_IP',
@useself=false,
@locallogin=NULL,
@rmtuser='gallicg',
@rmtpassword='mypassword'
GO
Select *
from Openquery(DB2MVS_IP,'Select * from GJGLIB.PCSUP133')
Go"

rohans
Posting Yak Master

194 Posts

Posted - 2004-05-26 : 13:33:20

I am having similar problem. I have no idea what the initial catalog and package information should be. I will use catalog to be the library as I see you have done but I am not sure about anything you have in the providerstr. have you made any head way with the connection?

Where did u get the connection string from? I would love an explanation of it.

All help appreciated.

shihonage
Starting Member

1 Post

Posted - 2004-06-09 : 15:59:58

Here's an example that I use.

Regards,
Jim

SP_ADDLINKEDSERVER
@SERVER = 'AnyNameYouWant', --This is how you reference which linked server
@srvproduct = 'Microsoft OLE DB Provider for ODBC',
@PROVIDER = 'MSDASQL', --This is the type of ODBC connection, I use MS drivers to link to IBM driver specified in @DataSrc
@DATASRC = 'VUSPD', --The name of the DSN you want to use, in my case this is a iSeries for ODBC
@provstr = 'DRIVER={Microsoft OLE DB Provider for ODBC};SERVER=YourServerIP;UID=YourUserID;PWD=YourUserPassword;'

-- For some reason, the UserID and Password in the @provstr doesn't stick, so you add them here.
-- You may not need anything beyond the @provstr
sp_addlinkedsrvlogin
@rmtsrvname = 'AnyNameYouWant', --This is the same name as @Server
@rmtuser = 'YourUserID',
@rmtpassword = 'YourUserPassword'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources