Ok I am stumped.....

I am currently writing a custom DTS task through the DTS designer and an ActiveX script. The goal of my Script is to access 3 tables which already exist in the DB, extract and numerically manipulate data from each table, and then insert this data into a new table.

This is my first time writing an ActiveX script and my first time using the VB scripting language, so most of my script is based on examples I could find.

To be more exact about my problem here is the description. I created six recordsets from the data I extracted from the 3 tables. When I attempt to insert all the data into my new table some of the data inserts in the wrong order. Three of my columns are time columns, so it is easy to see where the data falls out of its intended sequential order. It is also easy to see that this happens at the exact same time value, every time I run the code.

I am confused because if I write a for loop and insert less data either through fewer columns or fewer rows, my code seems to work perfectly. I have approximately 4800 rows total to insert, but my code seems to mess up if I try and insert in excess of 4100 rows. Someone suggested that I may be exceeding my allowed buffer, but I don't really know what this means or how to correct it.

Becasue my data inserts correctly if I only insert, say 2 columns, but all the rows, I think that the recordsets are being creatred correctly, and that the fault lies in my insert loop.

I have included the script for my insert loop and an example of how the data looks when it is in error.

I am very grateful for any help you might be able to provide and please let me thank you in advance for you time!

-TRoche

do until GPSxRecord.EOF
GPSx = GPSxRecord.Fields ("GPS_x").value
tx = GPSxRecord.Fields ("tx").value

GPSy = GPSyRecord.Fields ("GPS_y").value
ty = GPSyRecord.Fields ("ty").value

GPSz = GPSzRecord.Fields ("GPS_z").value
tz = GPSzRecord.Fields ("tz").value

'Executing the Insert Command
DestCmd = "INSERT INTO GPSIMPORT VALUES ( " & tx & ", " & GPSx & ", " & ty & ", " & GPSy & ", " & tz & ", " & GPSz & ") "

DestCon.Execute DestCmd

GPSxRecord.MoveNext
GPSyRecord.MoveNext
GPSzRecord.MoveNext

Loop

Time(x) GPSx Time(y) GPS(y)
58.9447 383421.96 58.94497 1213470.912 58.94526 488
58.99134 383421.959 58.99162 1213470.912 58.99191 489
59.04329 383421.957 59.04356 1213470.912 59.04385 490
59.0951 383421.956 59.09538 1213470.912 59.09566 490
59.14204 383421.955 59.14231 1213470.912 59.1426 490
221.19337 383447.07 221.19364 1213349.901 221.19393 479
221.24045 383447.069 221.24072 1213349.895 221.241 479
221.29253 383447.068 221.2928 1213349.889 221.29308 479
221.34434 383447.067 221.34461 1213349.883 221.3449 478

'************************************************* **********
' Visual Basic ActiveX Script
'************************************************* **********

Function Main()

dim ConnSQL1 ' SQL Server connection
dim RSSQL ' SQL Server recordset
dim strSQL ' SQL String
dim rc

' SET DATA HANDLING OBJECTS
set ConnSQL1 = CreateObject("ADODB.Connection")
set RSSQL = CreateObject("ADODB.Recordset")

'OPEN DATA CONNECTION
ConnSQL1.Open = "Provider=SQLOLEDB;Data Source=server;Initial Catalog=database;UID=username;Password=asdf"


'create a select statement and put into temp table in the
'format that you are after from the three tables
'I assume you can do that
'Once you have all those records, then just insert them
'from the temp table

strSQL = "SELECT * into #temp from table " & _
"insert into newtable(f1, f2, f3, f4)" & _
"select f1, f2, f3, f4 from #temp"

RSSQL.Open strSQL, ConnSQL1
ConnSQL1.execute strSQL
RSSQL.close
Main = rc
End Function