?
Solved

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

Posted on 2011-10-04
8
Medium Priority
?
534 Views
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.

Thanks,
-Jeremy
0
Comment
Question by:FamousMortimer
  • 4
  • 4
8 Comments
 
LVL 9

Accepted Solution

by:
DrewKjell earned 2000 total points
ID: 36909647
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.

Drew
0
 
LVL 10

Author Comment

by:FamousMortimer
ID: 36909891
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?
0
 
LVL 9

Expert Comment

by:DrewKjell
ID: 36910181
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.

Drew
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Author Comment

by:FamousMortimer
ID: 36924343
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?
0
 
LVL 9

Assisted Solution

by:DrewKjell
DrewKjell earned 2000 total points
ID: 36932550
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?
0
 
LVL 10

Author Comment

by:FamousMortimer
ID: 36967916
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
		.MoveNext
	Loop
End With

On Error Resume Next
cn_AS400.Close
cn_SQL.Close
Set rs_AS400=Nothing

WScript.Quit

Open in new window

0
 
LVL 9

Expert Comment

by:DrewKjell
ID: 36968394
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

Drew
0
 
LVL 10

Author Closing Comment

by:FamousMortimer
ID: 36986415
thanks
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

862 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