Recordset Modification and Copy

Please Help.

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!!!

-TRocheI assume you are using ADO? Disconnect your recordset by setting your ActiveConnection property to nothing.|||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.

Does anyone have any additional insight?

Thanks!

-TRoche|||create a function READ UNCOMMITTED transaction isolation level (you can do it with nolock optimizer hint) and do your DO UNTIL against the rs that points to it.|||Wow...I am just a beginner at this stuff...I will look into it, but do you have a sample code for this by chance?

thanks,

TRoche|||create function dbo.fn_select_star_from_your_table (
@.parm1 <data type>,
@.parm2 <data type>,
@.parm3 <data type> ) returns table
as return (
select * from your_table (nolock)
where field1 = @.parm1
and field2 = @.parm2
and @.field3 = parm3)
go|||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:

dim Dropx
set Dropx = CreateObject("ADODB.Command")

Dropx = "Drop Function [dbo].[FUNSTUFF]"
connection.execute Dropx

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:
dim ADVICE
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)"

then:
Open.recordset ADVICE, Connection, adOpenKeyset

Then write my do loop:
DO UNTIL recordset.EOF
blah, blah, blah
Loop

Unfortunately, I run into all kinds of errors like saying the operation can not be performed if the recordset is closed. But if I write an open command like:

open.recordset.connection

I get the error that the function FUNSTUFF alreadyexists in the database.

Hmmmmmmmm...

Does anyone have any advice? Do I have to write the Function code before every loop or a drop code after every loop...or...hmmm...I am just confused about how the function works.

A million thanks to anyone who has any input!!

-TRoche