<div>
That is something i would like to develop when i have the time to dedicate to it (which is not in my foreseeable future). &nbsp;For now, i would like to be able to do it with vbscript. &nbsp;Any ideas?
</div>

<div>
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. &nbsp;Another option could be to create a linked server from SQL2008 to the AS/400 box. &nbsp;This would allow you to query the AS/400 and insert from the SQL 2008 box. &nbsp;For more on linked servers check out the MSDN link, &nbsp;<a href="http://msdn.microsoft.com/en-us/library/ms188279.aspx" rel="ugc">http://msdn.microsoft.com/en-us/library/ms188279.aspx</a> and how to create a linked server here, <a href="http://msdn.microsoft.com/en-us/library/aa560998(v=bts.10).aspx" rel="ugc nofollow">http://msdn.microsoft.com/en-us/library/aa560998(v=bts.10).aspx</a>. 
 
Drew
</div>

<div>
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. &nbsp;if i use the sql mgmt studio to script table as select to... i get a message stating &quot;contains no columns that can be selected or the current user does not have permissions on that object.&quot; &nbsp;I have the user/pass built into the connection string. 
 
I tried using open query like so, &quot;SELECT * FROM OPENQUERY(linkedservername, 'SELECT * FROM Table500') AS tDC1&quot; i receive the following message: 
 
OLE DB provider &quot;MSDASQL&quot; for linked server &quot;CCL01&quot; returned message &quot;[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0204 - Table500 in QGPL type *FILE not found.&quot;. 
Msg 7321, Level 16, State 2, Line 1 
An error occurred while preparing the query &quot;SELECT * FROM Table500&quot; for execution against OLE DB provider &quot;MSDASQL&quot; for linked server &quot;linkedservername&quot;. 
 
I'm sure i just missed something, any ideas?
</div>

<div>
The table is definitely in the database and i can see it when i expand the table list. &nbsp;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. &nbsp;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... &nbsp;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? 

<pre><code class="code-10 nolinks" id="code-8226828">Option Explicit

On Error Resume Next

Const adOpenKeySet=1
Const adLockReadOnly=1

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

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

strSQLInsert1=&quot;INSERT INTO mysqltable VALUES(&quot;
strSQLQuery=&quot;SELECT * FROM mydb2table&quot;

Set cn_AS400=CreateObject(&quot;ADODB.Connection&quot;)
Set rs_AS400=CreateObject(&quot;ADODB.RecordSet&quot;)
Set cn_SQL=CreateObject(&quot;ADODB.Connection&quot;)

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

cn_SQL.Open cs_SQL
cn_SQL.Execute &quot;TRUNCATE TABLE mysqltable&quot;

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

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

WScript.Quit
</code></pre>
</div>

<div>
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. &nbsp;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. &nbsp;For more on Bulk Insert check out this MSDN article: <a href="http://msdn.microsoft.com/en-us/library/ms188365.aspx" rel="ugc">http://msdn.microsoft.com/en-us/library/ms188365.aspx</a> 
 
Drew
</div>

<div>
<div class="content wysiwyg-content">
Hi Experts, 
I am looking for a solution to query our AS/400 and insert that query into a SQL table. &nbsp;I was thinking of using vbscript but if you think another solution would make more sense, let me know. &nbsp;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
</div>
</div>

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

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

Visual Basic is Microsoft’s event-driven programming language and integrated development environment (IDE) for its Component Object Model (COM) programming model. It is relatively easy to learn and use because of its graphical development features and BASIC heritage. It has been replaced with VB.NET, and is very similar to VBA (Visual Basic for Applications), the programming language for the Microsoft Office product line.

Visual Basic Classic

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.