VBScript to Query AS/400 and Insert Into SQL Server 2008

Hi Experts,
I am looking for a solution to query our AS/400 and insert that query into a SQL table.  I was thinking of using vbscript but if you think another solution would make more sense, let me know.  Ultimately i would like to create a service to handle it but I don't have the time to focus on that at the moment.

LVL 10
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

If you want the end solution to be a service, I would recommend looking into .Net.  Example AS/400 connection strings can be found at http://connectionstrings.com/as-400.  That site also has the different connection strings for SQL 2008.  Having your solution in an environment you can step through and debug while also being able to compile and deploy sounds like it would fit your needs well.  .Net also has an application config file that you can add custom key-value pairs to should your server names change or server paths or connection strings which allows you quite a lot of flexibility.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FamousMortimerAuthor Commented:
That is something i would like to develop when i have the time to dedicate to it (which is not in my foreseeable future).  For now, i would like to be able to do it with vbscript.  Any ideas?
The process is very similar as far as creating an ADO object in VBScript to connect to both the AS/400 and insert into SQL 2008.  Another option could be to create a linked server from SQL2008 to the AS/400 box.  This would allow you to query the AS/400 and insert from the SQL 2008 box.  For more on linked servers check out the MSDN link,  http://msdn.microsoft.com/en-us/library/ms188279.aspx and how to create a linked server here, http://msdn.microsoft.com/en-us/library/aa560998(v=bts.10).aspx.

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

FamousMortimerAuthor Commented:
Thanks Drew,

I was able to add our AS/400 as a linked server and view all of the tables, however i am unable to perform a query.  if i use the sql mgmt studio to script table as select to... i get a message stating "contains no columns that can be selected or the current user does not have permissions on that object."  I have the user/pass built into the connection string.

I tried using open query like so, "SELECT * FROM OPENQUERY(linkedservername, 'SELECT * FROM Table500') AS tDC1" i receive the following message:

OLE DB provider "MSDASQL" for linked server "CCL01" returned message "[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0204 - Table500 in QGPL type *FILE not found.".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT * FROM Table500" for execution against OLE DB provider "MSDASQL" for linked server "linkedservername".

I'm sure i just missed something, any ideas?
It looks like the AS/400 query doesn't know what Table500 is, based on this article: http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=%2Fcom.ibm.db2.udb.msg.doc%2Fdoc%2Fsql0204.htm

What is the select syntax on AS/400?
FamousMortimerAuthor Commented:
The table is definitely in the database and i can see it when i expand the table list.  i give up at this point. i was able to create a vbscript to perform queries and insert them into SQL however i think using the 400 as a linked server is definitely a better option.

Thanks for the help.  I may post a followup however i had to move on for now.

Here is the script if anyone stumbles upon this and needs it...  My problem with the vbscript (at least the way i did it) is that it loops through the records and writes one at a time which leave a window of time while the script is running where the data is incomplete.

Does anyone know if there is a way to dump the entire recordset into sql rather than looping as i did using vbscript?
Option Explicit

On Error Resume Next

Const adOpenKeySet=1
Const adLockReadOnly=1

Const cs_AS400="Provider=MSDASQL.1;Password=password;Persist Security Info=True;User ID=userid;Data Source=mydatasource;Initial Catalog=catalog"
Const cs_SQL="Provider=SQLOLEDB.1;Initial Catalog=Information;Data Source=machine\instance;Integrated Security=SSPI;"

Dim cn_AS400, rs_AS400, cn_SQL
Dim strSQLQuery, strSQLInsert1, strSQLInsert2, strToday
Dim i

strSQLInsert1="INSERT INTO mysqltable VALUES("
strSQLQuery="SELECT * FROM mydb2table"

Set cn_AS400=CreateObject("ADODB.Connection")
Set rs_AS400=CreateObject("ADODB.RecordSet")
Set cn_SQL=CreateObject("ADODB.Connection")

cn_AS400.Open cs_AS400
rs_AS400.Open strSQLQuery, cn_AS400, adOpenKeySet,adLockReadOnly

cn_SQL.Open cs_SQL
cn_SQL.Execute "TRUNCATE TABLE mysqltable"

With rs_AS400
	Do Until rs_AS400.EOF
		strSQLInsert2 = strSQLInsert1 & .Fields("field1") & "," & .Fields("field2") & "," & _
						.Fields("field3") & "," & .Fields("field4") & "," & .Fields("field5") & "," & _
						.Fields("field6") & ",'" & .Fields("field7") & "')"
		cn_SQL.Execute strSQLInsert2
End With

On Error Resume Next
Set rs_AS400=Nothing


Open in new window

What you're doing with VBScript looks correct, however, to speed up processing on the inserts what I've done is to write the insert records to a CSV file.

Then on the SQL use a BULK Insert command to insert all of the records.  You will need a different file for each table you're moving, but I have seen this take a process that took over 20 minutes cut it down to 2.  For more on Bulk Insert check out this MSDN article: http://msdn.microsoft.com/en-us/library/ms188365.aspx

FamousMortimerAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.