Hello,

I'm in a quandry as to what to do?

I have a remote ORACLE production database and a local SQL
reporting and Data entry database. I don't have
permissions to write Store Procedures on the ORACLE server
so I need to copy the data and put it on the SQL server. I
have come up with two main thoughts.

--1. I could write a VB Module to compare the Results
table with the DATA table and then update the field if it
is different.

--2. I could just copy over the existing DATA table with a
daily DTS package.

This is my dilemma.

If I choose the first, then I have to break the DATA table
up into two parts, which destroys some of the trigger.

If I choose the second, then I'm not sure if VB is the
correct way or how long this could take.

If anyone has any ideas, please direct me as to how to
best handle this.

Thank you,

Brandon H. Campbell

You have a number of options

1. Ask for a trigger to be put on the Oracle tables that write out to a
trigger table with an action Type (U,I,D) then use that as the source.
2. DTS the whole table across then use TSQL to do your Updates, Deletes and
Inserts by comparisons of Key values.
3. Have a field added to the Oracle table which indicates change time and
date. You can then import only records that have a value in here between
the last import time and and now.

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"Brandon" <bkc5 (AT) cdc (DOT) gov> wrote

If you have a column in Oracle that says "I was changed/Added on this date"
then in the SQL Server side you could have a master table that has a record
containing your last load date. You would then compare this against the
Oracle records i.e In your Datapump task do this


SELECT <col List> FROM ORACLE
WHERE LastChangedDate > ?

The ? Maps to the result of a SELECT Query aganst the master table
containing our LastRun column


SELECT MAX(RunDate) FROM TableInSQLServer

Here is how we assign parameters in and out of the ? (Page 7)

How to Use Lookups in DTS
(http://www.sqldts.com/default.aspx?277,1)



--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"Brandon Campbell" <bkc5 (AT) cdc (DOT) gov> wrote