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!
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
dim ConnSQL1 ' SQL Server connection
dim RSSQL ' SQL Server recordset
dim strSQL ' SQL String
' 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
Main = rc