Help needed to implement a "big" application

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help needed to implement a "big" application

Author

Topic

dogli
Starting Member

32 Posts

Posted - 2002-09-03 : 12:57:43

Hi guys,

I am new to SQL server, but I need to develop a "big" application with it. Here's my application requirements:

1)Create a table, say TableA, in SQL server 7 or 2000 database,say DB1, based on another Oracle database,say DB2, on a remote server. Update TableA based on DB2 every day in some specific time.
2) If new records are inserted into the TableA,some other tables needed to update in DB1. At the same time, an e-mail will send to three receipts.

I have no idea of which function or method I can use to fulfill the requirements. Can any experts here give me some advice, detail explanation would be grateful.

I appreciate any help you provide.

Dogli

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-03 : 13:21:35

Big?

Depends how much access you have to the Oracle server. Are you allowed read access to the tables? Do you have to call some interface?

What is the structure of the table? How do you identify new records?

Create a linked server to the Oracle server and map the user to something that has read permission on the table.
Best to find the structure of the table and create the copy on your server.
You will probably need some increasing value on the remote table like an ID or timestamp - or maybe it is just all records with previous days time.

The sp to insert would then be

declare @lastID int
select @LastID = max(ID) from mytbl

insert mytbl
select *
from orserver.db2..tbl
where id > @ID

for the further processing you could have a trigger on the table but probably better to put it in the SP after the retrieve.

Schedule this SP using the agent to run at the specific time and you are there - all complete in a couple of hours work.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dogli
Starting Member

32 Posts

Posted - 2002-09-03 : 14:14:40

Thanks for your so prompt and detail reply. The Oracle database table actually has many many fileds and I just need to grab some of the fields. Also, for updating the table, I don't need update all records from the table, only those records meeting some requirements.

I am thinking to use DTS to implement this task, is it a good idea?

Again, thanks for your help.

dogli

rharmon
Starting Member

41 Posts

Posted - 2002-09-03 : 14:21:01

NOOOOO!

DTS has some amazing features for warehousing: generation of metadata, integrated ftp, olap processing etc. But for moving data from one server to another for transactional databases, I would not suggest it especially for those with a database background rather than and application background.

I would suggest setting up the SQL environment (database, table structure etc.) Then add both external databases as linked servers. From there, with a little SQL programming, it shouldn't take long to develop. The bonuses are that it will run much faster than DTS ever could imagine and you don't need background in client side scripting to make it happen.

Hope this helps,

Rob

dogli
Starting Member

32 Posts

Posted - 2002-09-03 : 15:25:36

I appreciate your advice, Rob. But with the way you advised, can I set up to update the new table from the original Oracle database at some specific time every day? I know it is very easy to do so with DTS.

Thanks.

dogli

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-03 : 15:30:48

With linked servers, your Oracle tables behave exactly as if they were SQL Server tables, so you could just run a regular UPDATE statement against them. For what you want to do, linked servers are absolutely the way to go. DTS might work but will require a lot more effort.

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-09-03 : 15:54:12

just write the update code into a procedure and then schedule a job to run every so often to execute that procedure...

rharmon
Starting Member

41 Posts

Posted - 2002-09-03 : 15:58:14

from sbo:

EXEC sp_addlinkedserver 'OracleSvr',
'Oracle 7.3',
'MSDAORA',
'ORCLDB'
GO
SELECT *
FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')
GO

this could be easily modified to:

insert into dbo.desttable
SELECT *
FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')
GO

You can add where clauses etc, to interogate for existing records (an exists clause)

once you have the query written, put it in a proc and schedule using SQL Server Agent

dogli
Starting Member

32 Posts

Posted - 2002-09-03 : 17:33:05

Thanks for all of your help. This is the first time I post questions here, but I am really impressed with all your warm-hearted and knowledeable help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources