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

Posted on 2011-10-04
Last Modified: 2012-06-27
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.

Question by:FamousMortimer
    LVL 9

    Accepted Solution

    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  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.

    LVL 10

    Author Comment

    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?
    LVL 9

    Expert Comment

    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, and how to create a linked server here,

    LVL 10

    Author Comment

    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?
    LVL 9

    Assisted Solution

    It looks like the AS/400 query doesn't know what Table500 is, based on this article:

    What is the select syntax on AS/400?
    LVL 10

    Author Comment

    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

    LVL 9

    Expert Comment

    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:

    LVL 10

    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    737 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

    21 Experts available now in Live!

    Get 1:1 Help Now