I have written a custom DTS task in an ActiveX Script. My script creates three recordsets from three existing tables in an SQL Server 2k DB by copying all the data in these tables.
My script then modifies the data in these recordsets including dropping several records, creates a new table and inserts the modified data into the new table.
Unfortunately, I need to keep my original data unmodified...and currently my code changes my original data when it modifies the record sets.
Does anyone have a method for modifying a recordset without modifying the source data? I am writing my ActiveX Script through the VB scripting language.
From what I have read on the internet some people suggest creating a new recordset, copying the data from the original recordset to the new recordset, and then modify and insert the new recordset. This is supposed to keep my source data unchanged. Unfortunately, I have not been successful with this method mainly because I am having trouble creating a new recordset and copying my original recordset data into this new object.
Thank you in advance for any help or input you might have!!!
I don't think this will work. You see I have to manipulate the data through several DO Until loops. The constraint on my DO loop is
"DO UNTIL rs.EOF"
Before I set the recordset ActiveConnection to nothing It tells me I have to first close the recordset. Unfortunately, I can not run the syntax of the above loop if the recrordset is closed, and if I open the recordset with a new connection it again changes my source data.
Hmmmmm.....ok to start I am writing a custom DTS Task, using an ActiveX Script in the DTS Designer, coding in VBScript
Well I am having a few problems with the previous comments. First off I can't get the code to drop the function ms_sql_dba advised. Here is my DROP code for a function called FUNSTUFF:
set Dropx = CreateObject("ADODB.Command")
Dropx = "Drop Function [dbo].[FUNSTUFF]"
I am only trying to drop the function because if I run the code multiple times it errors saying the the object "FunStuff" already exists in the database....and I looked....and it does...dang Does anyone see something wrong with the above DROP code?
Also, ms_sql_dba, I am not sure I understand what your function does. Here is how I thought to use your advice.
Write a function called ADVICE as follows:
set ADVICE = CreateObject("ADODB.Command")
ADVICE = "CREATE FUNCTION dbo.FUNSTUFF (@time float, @Position float ) RETURNS Table AS RETURN (select * from GPSy (nolock) where ty = time and GPS_y = Position)"