Post reply

SQL 2000 transfer to Oracle 9 based on datestamp

  • March 10, 2008 at 4:01 pm

    #187837

    I hope someone can help, I have been battling this mystery for a while and my customers have become impatient.

    I have a SQL Server 2000 database that needs new and updated records to be transferred to an Oracle database by the current date. There has been a DTS package created (I inherited) by a consultant that transfers the contents of any new or updated record (based on the current date) to another database on the same instance and then is transferred to oracle with the DTS package every night.

    The packaged software has been setup to transfer the updated or new records based and the customer wants more fields transferred to the Oracle database however the vendor that created the package isn't willing to help modify its software package.

    I have tried to create a trigger using a join and the inserted table on each table that will transfer the record to the staging SQL Server database when the record has been created however the software package began to have problems. I imagine because of multiple changes being made to the same record possibly at one time.

    If anyone understands my explanation and can help at all, I am willing to try anything.

    Thanks to all. I have never posted here but have been reading for a long time and learned a lot along the way.

  • March 10, 2008 at 9:11 pm

    #787740

    What kind of problems is the software experiencing? Having multiple updates taking place simultaneously should not cause any problems with the trigger as it should handle that. What do you have for indexes on the staging table into which you are inserting the changed data? What locks are being acquired on this table? Try running a profile to see if there is contention on this table and what kind.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply