Solved

Replication from AS400/PRMS to Microsoft SQL2000. HOW TO PLEASE.

Posted on 2008-10-27
6
680 Views
Last Modified: 2013-11-30
I am trying to replicate the data contain within our AS400 running INFOR's "PRMS" to a SQL 2000 server.
I would like to do this via ODBC. I do not see AS400 driver in the ODBC list. At this point I feel some Expert advice or better yet a How To is in order.

If you would please.
0
Comment
Question by:Hypermechanic
  • 2
  • 2
6 Comments
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 250 total points
Comment Utility
The first thing to do is install whatever IBM client tools you have onto the server.
This should install an AS400 ODBC driver.
Then you may need to create an ODBC entry against that driver, which SQL will be able to reference.
 
0
 
LVL 14

Expert Comment

by:daveslater
Comment Utility
Hi
i would use ADODB, this is the same as ODBC but you can create the connections dynamically.
You could create a list of files, libraries etc in Excel the run down the list abd populete the sql table.

in excel VB you get acces to ADODB via F11, vb macro, Tools/References then select any Microsoft DAO 3.x Object library.

Public Sub AS400()
 

Dim con As New ADODB.Connection

Dim rs As New ADODB.Recordset

Dim No400 As Boolean

Dim Userid As String
 

On Error GoTo OhEck
 

con.Open "PROVIDER=IBMDA400;Data Source=999.999.999.999;USER ID= ;PASSWORD= ;"
 

Set rs.ActiveConnection = con
 

rs.Open ("SELECT * FROM MYLIB.MYFILE WHERE Condition")

do while not rs.eof

// process db to table move

enddo ;
 
 

rs.Close
 

End
 

Exit Sub
 

OhEck:
 

MsgBox "Cann not connect to the AS/400 - Downloads are not possible", vbInformation, cHeading

End
 

End Sub

	 

Open in new window

0
 
LVL 30

Expert Comment

by:nmcdermaid
Comment Utility
I don't think he's goin to have the IBMDA400 provider until the client tools get installed.
0
 
LVL 14

Expert Comment

by:daveslater
Comment Utility
Hi
nmcdermaid: gave the correct answer in that the ODBC driver comes on the client access tools CD.
Once the software is installed it appears in the systems DSN.

I simply gave an alternate to ODBC by using ADODB.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now