I am currently writing a custom DTS package using an ActiveX script. This is my first time writing an ActiveX script and using the VBscripting language.
What I need to do is take data from three existing MS SQL Server 2k tables and import it into a single new table after serveral numerical data manipulations.
The specific problem I am having is that when I insert my recorsets into the new table several of the records are out of their sequential order. I am confused because if I cut down on the amount of data I insert, either in the number of rows and/or number of columns, I have no problem with my insert, but when I insert all the data I need things get out of order and swap places. I am inserting using what I think is called a connection/execute command with TSQL commands, and from what I have read this is the most efficient way to go about it.
I have approximately 4800 rows to insert with 6 columns, but my program seems to error with I try to insert in excess of 4100 rows.
Does anyone have any ideas? I was told that it might have something to do with a buffer, but I have not been able to find any helpful documentation. I have included the code for my insert loop.
Thank you in Advance!
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
Do you have a primary key on GPSIMPORT? If not, then your data structure is known as a "heap" and SQL server makes no guarantees about the order in which data is stored, or even the order in which it is retrieved in consecutive statements.
It is not a good idea to rely on the order in which data is inserted to be the order in which it is kept or retrieved. Define a primary key for your data.