Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Posts
    10

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

    -TRoche

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Recordset Modification and Copy

    I assume you are using ADO? Disconnect your recordset by setting your ActiveConnection property to nothing.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Sep 2003
    Posts
    10

    Re: Recordset Modification and Copy

    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

  4. #4
    Join Date
    Sep 2003
    Posts
    522
    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.

  5. #5
    Join Date
    Sep 2003
    Posts
    10
    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

  6. #6
    Join Date
    Sep 2003
    Posts
    522
    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

  7. #7
    Join Date
    Sep 2003
    Posts
    10
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •