ActiveX v Stored procedure - SQL Server Forums

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)

ActiveX v Stored procedure

Author

Topic

Tigger
Yak Posting Veteran

85 Posts

Posted - 2002-02-27 : 22:08:39

We have to download some customer data from Oracle to SQL 2000.
We then have to do some processing eg get the customers intials.

I've just been asked which approach is better

1. Do the processing during the DTS download using ActiveX script during the Transform Data Task

2. After downloading the data have a stored procedure to do the processing using user-defined functions.

And I don't know the answer !

Edited by - tigger on 02/27/2002 22:09:25

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-02-27 : 22:31:59

Tigger,

It Depends!

For ease of administration the DTS (Everything is there!)
For Speed the SP

Better yet.. Instead of running an ActiveX script why not add a SQL Task that runs the SP after the "Success" of the download.
This gives you the benefit of centralised admin and speed!

DavidM

Tomorrow is the same day as Today was the day before.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-02-27 : 22:32:39

Either the download/stored procedure is better...or my name isn't William Jefferson Clinton!

As David says - I do that with my oracle DTS package - on success I run a stored procedure which transforms all the data.

...but I suspect it's going to depend on what you'll be doing in the user defined functions...

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Edited by - rrb on 02/27/2002 22:34:21

Tigger
Yak Posting Veteran

85 Posts

Posted - 2002-02-27 : 22:44:50

Thanks guys.

We are getting a new system to replace our Oracle database and so a team has been set up to download the Oracle data into SQL Server and transform it prior to loading it into the new system.

The team are all mainframe programmers who haven't used SQL Server before and so I am having to teach them DTS and the best way to do things.

My gut instinct was to have a DTS package which would do the download and then call a stored procedure to do the processing. However, they want to do everything in the DTS download task. I'm having a hard time convincing them that it is likely to be a nightmare to try and do that !!

Good to know that my way of thinking is not too far off the mark.

I thought the SP would be quicker but couldn't back up my argument!

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-02-27 : 22:53:53

Go with what David said. Then as far as anyone is concerned it IS all happening in the DTS task.

Damian

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-02-27 : 22:56:19

I'm majorly on your side. I mean #$%&^ one step at a time!!! How're they going to even debug the transformation? - Especially if the transformation is complex, on a big table etc

Uggh. Sure you can't get away with - do an sp to do the transformations and just make it part of your DTS task- and tell them that SQL Task step is always required for DTS

Go on Tigger - let 'em have some of that big-cat inside...

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Tigger
Yak Posting Veteran

85 Posts

Posted - 2002-02-27 : 23:03:21

Only snag is I'm not actually on the project. They are building the DTS scripts and I can just see that they are going to tie themselves in knots trying to do everything in one go !!

Can't get them to see that sometimes 2 steps are better than 1.

I may end up on the project - not sure if that is a good or a bad thing !!

At least if I end up on it I might be able to convince them that I know what I'm talking about.

I'd better sharpen my claws tonight and practice growling and snarling

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-02-27 : 23:05:40

quote:

I'd better sharpen my claws tonight and practice growling and snarling

Works for me

Damian

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-28 : 07:42:38

If you'd like an analogy to illustrate the differences, imagine mailing out 5,000 letters. If you sit at a desk, take a letter, fold it, insert it into the envelope, seal the envelope, and stamp it, you have to do this for each letter. That's DTS.

If you fold all the letters, then stuff all the envelopes, then seal all the envelopes, then stamp all the letters, that's how the SP would work.

I'll let your co-workers guess which one will work faster.

Tigger
Yak Posting Veteran

85 Posts

Posted - 2002-02-28 : 14:19:04

quote:
imagine mailing out 5,000 letters. If you sit at a desk, take a letter, fold it, insert it into the envelope, seal the envelope, and stamp it, you have to do this for each letter. That's DTS.

If you fold all the letters, then stuff all the envelopes, then seal all the envelopes, then stamp all the letters, that's how the SP would work.

That's just what I needed. Thanks.

Tigger
Yak Posting Veteran

85 Posts

Posted - 2002-02-28 : 18:57:36

Guess who's just been seconded onto the project 3 days a week !!!

Wonder if Tiggers turn grey....

Guess I will find out soon enough