Solved

Urgent:: Loading Oracle Order data into Sql Server DB daily

Posted on 2001-08-03
15
636 Views
Last Modified: 2012-06-27
Hi,
 
Our main database is in sql server 2000.
We have to interface with another database (Oracle v ??) to pull orders
I have the sql query to pull orders from Oracle.
I could use ASP / COM to load them into a  ADO recordset.
I want to load them into a temp table on SQL Server and then create a stored procedure or some ASP code to re-insert the data into a different Order tables in sql server.

Scheduled Task at midnight: I need to do this on a nightly basis. So I need to schedule a JOB on the w2k server to do this.
 
I would like to design this so that I have a minimin of changes should the source oracle database or target sql server database change also.

1. Anyone know of a quick way to populate a temp table in SQL Server from an
external Database. ?

 
2. Advice on whether to use SQL stored procedures or  ASP script / COM to access the
data from Oracle and then insert the data into SQL server.


3. Use of SQL Server Utility DTS, I could specify a source as the Oracle database with a sql query to extract the data and copy to the sql server database (work table ). But I want to be able to programmatically trigger this DTS task and trigger a stored procedure to process the loaded sql server data into the real Order entry tables.

Thanks, Paul

W2K Server SQL SERVER 2000, IIS 5.0 ASP / VB / COM+
0
Comment
Question by:chupaul
  • 9
  • 5
15 Comments
 
LVL 3

Expert Comment

by:peteyhiggins
ID: 6349902
I think the easiest way for you to do this is to create a table in SQL Server and set up the DTS to pull in the Oracle data each night.

Then create an Insert trigger on that table to take the data and put it into the real tables.  This will also allow you to validate it all before you put it into your main tables.

Hope that helps
0
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6349910
But I want to be able to programmatically
trigger this DTS task and trigger a stored procedure to process the loaded sql server data into the
real Order entry tables.

This is not a problem. Whatever you do should be executed by a scheduled job (either dts or stored procs). This job can then either be started by a schedule or from a stored procedure call using sp_start_job.

You have several processes here.

extract from Oracle
import to sql server (to staging table)
insert to production table

Certainly the production table insert should be kept separate i.e. this should not be dependant on the Oracle structure but only on the staging table. It should also be done via a stored proc.

It depends how much control you have over the Oracle server as to how you do this. I prfer to export to a flat file (csv?) and then import to the staging table via bulk import. In this way you keep the Oracle server in control of the file creation without any interaction between the servers.
You could as you say probably use dts or a linked server to access it.
As long as you keep this stage separate then you can change the method at will without too much impact.
0
 
LVL 1

Author Comment

by:chupaul
ID: 6349950
Hi,

You have several processes here.

1. extract from Oracle
   using a sql query with a multi-table join
   I have to "Pull" the data from the oracle server myself.
   If I use DTS to connect to the oracle and dump to a flat file then I "lose" the advantage of the meta data and column names to load into the staging table.


2. import to sql server (to staging table)
    basically just a copy of the oracle data
    use DTS to read from oracle and insert into the staging table ( I want to be able to delete the staging table first)
    Suggestion here was to use a table trigger to start loading into production table but it is not that easy to populate all the target order tables.


3. insert to production table
     multiple tables ( order , order details, transactions, receipts )



good points !

1.How to trigger the task nightly at 12:01 am
a. Schedule the DTS task in SQL Server - have not used
b. Use AT scheduler - I had some trouble using this

2.
0
 
LVL 1

Author Comment

by:chupaul
ID: 6349951
Hi,

You have several processes here.

1. extract from Oracle
   using a sql query with a multi-table join
   I have to "Pull" the data from the oracle server myself.
   If I use DTS to connect to the oracle and dump to a flat file then I "lose" the advantage of the meta data and column names to load into the staging table.


2. import to sql server (to staging table)
    basically just a copy of the oracle data
    use DTS to read from oracle and insert into the staging table ( I want to be able to delete the staging table first)
    Suggestion here was to use a table trigger to start loading into production table but it is not that easy to populate all the target order tables.


3. insert to production table
     multiple tables ( order , order details, transactions, receipts )



good points !

1.How to trigger the task nightly at 12:01 am
a. Schedule the DTS task in SQL Server - have not used
b. Use AT scheduler - I had some trouble using this

2.
0
 
LVL 1

Author Comment

by:chupaul
ID: 6349952
Hi,

You have several processes here.

1. extract from Oracle
   using a sql query with a multi-table join
   I have to "Pull" the data from the oracle server myself.
   If I use DTS to connect to the oracle and dump to a flat file then I "lose" the advantage of the meta data and column names to load into the staging table.


2. import to sql server (to staging table)
    basically just a copy of the oracle data
    use DTS to read from oracle and insert into the staging table ( I want to be able to delete the staging table first)
    Suggestion here was to use a table trigger to start loading into production table but it is not that easy to populate all the target order tables.


3. insert to production table
     multiple tables ( order , order details, transactions, receipts )



good points !

1.How to trigger the task nightly at 12:01 am
a. Schedule the DTS task in SQL Server - have not used
b. Use AT scheduler - I had some trouble using this

2.
0
 
LVL 1

Author Comment

by:chupaul
ID: 6349990
oops, I somewho submitted without finishing...

3. insert to production table
    multiple tables ( order , order details, transactions, receipts )
    The input staging table will have multiple detail lines for each line in the order. I will need to populate a order record and then order details and also a transaction and receipt table also with the total order amounts. For instance, I have to insert a new record into the order table and use the identify order number to build the order detail rows , trans and receipt tables too.


1.How to trigger the task nightly at 12:01 am
a. Schedule the DTS task in SQL Server - have not used
b. Use AT scheduler - I had some trouble using this

Thanks, Paul
0
 
LVL 1

Author Comment

by:chupaul
ID: 6350060
oops, I somewho submitted without finishing...

3. insert to production table
    multiple tables ( order , order details, transactions, receipts )
    The input staging table will have multiple detail lines for each line in the order. I will need to populate a order record and then order details and also a transaction and receipt table also with the total order amounts. For instance, I have to insert a new record into the order table and use the identify order number to build the order detail rows , trans and receipt tables too.


1.How to trigger the task nightly at 12:01 am
a. Schedule the DTS task in SQL Server - have not used
b. Use AT scheduler - I had some trouble using this

Thanks, Paul
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 18

Expert Comment

by:nigelrivett
ID: 6351298
If you want to schedule the DTS package or an SP I would use the sql server scheduler - it is easy to use and is probably what you are using for backups anyway.
0
 
LVL 1

Author Comment

by:chupaul
ID: 6351832
hi,

I don't have any control over the oracle server regarding scheduling jobs to produce extracts for me daily.

I have to "pull" the data myself.
I think SQL SVR DTS is appropriate then.

Paul
0
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6352527
You could use DTS or a linked server with openquery or an openrowset command probably.
0
 
LVL 1

Author Comment

by:chupaul
ID: 6352644
Hi

Not familiar with this:

a linked server with openquery or an openrowset command probably.

We can create a Linked Server to an Oracle Instance ?

can you give a link or example ?

Thanks, Paul
0
 
LVL 18

Accepted Solution

by:
nigelrivett earned 100 total points
ID: 6353477
Examples are in bol

F. Use the Microsoft OLE DB Provider for Oracle
This example creates a linked server named OrclDB that uses the Microsoft OLE DB Provider for Oracle and replaces the OracleDB string (data_source) with the name of the SQL*Net alias.

USE master
GO
sp_addlinkedserver
    'OrclDB',
    'Oracle',
    'MSDAORA',
    'OracleDB'
GO

 

EXEC sp_addlinkedserver 'OracleSvr',
    'Oracle 7.3?,
    'MSDAORA',
    'ORCLDB'
GO

SELECT *
FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')
GO

 

0
 
LVL 1

Author Comment

by:chupaul
ID: 6353765
hi nigelrivett,

This looks neat!
But I don't need both of these right ?
What is the difference ?

sp_addlinkedserver
   'OrclDB',
   'Oracle',
   'MSDAORA',
   'OracleDB'
GO


EXEC sp_addlinkedserver
   'OracleSvr',
   'Oracle 7.3',
   'MSDAORA',
   'ORCLDB'
GO

Back late tonite.

cheers, Paul
0
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6353920
Nope, you add the linked server once which defines the link to the Oracle database then can use the link at any time.
0
 
LVL 1

Author Comment

by:chupaul
ID: 6354990
nigelrivett,

Thanks for your help. I wasn't aware of a linked server to Oracle ( are there capabilities to all ODBC database ???).
I will try these options when we get the oracle client configured on the SQL 2000 server.

Regards, Paul Chu
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now