Solved

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

Posted on 2001-08-03
15
661 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

717 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