Stored Procedure to compare dates

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

General SQL Server Forums

New to SQL Server Programming

Stored Procedure to compare dates

Author

Topic

boblarson
Starting Member

17 Posts

Posted - 2008-03-20 : 17:29:02

Hi:

Well, I am venturing into new territory for me. I'm very illiterate when it comes to SQL Server and so I need assistance. I have the beginnings of my stored procedure, which is supposed to compare two dates/times and If they are not equal I need to kick off a DTS Package.

So, here's what I have so far (it returns two dates like I would expect):


CREATE PROCEDURE usrCompareDataDownload 
AS
BEGIN
SELECT MAX(ASP_ZZ_CHNG_TMST) FROM tbl_MaterialWeeklyData;
SELECT MAX(ZZ_CHNG_TMST) FROM TV_ASP_DPUL_WKLY;

END
GO

Thanks,

Bob Larson

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-03-20 : 17:36:36

Listen carefully, and you may hear warning sirens in the distance.

Why are you kicking off a DTS package in your sproc? What does the package do? What is the sproc supposed to do?

e4 d5 xd5 Nf6

boblarson
Starting Member

17 Posts

Posted - 2008-03-20 : 17:58:23

quote:
Originally posted by blindman

Listen carefully, and you may hear warning sirens in the distance.

Why are you kicking off a DTS package in your sproc? What does the package do? What is the sproc supposed to do?

e4 d5 xd5 Nf6

Sorry for not including more information. I should know better.

Now that you mention it, it has me rethinking what I need to do because before we had linked to Oracle tables in Access and
then we would check the dates (see #1) and import into SQL Server based on that. We want to get out of using Access as a
linked to Oracle as we don't need to access the data but twice a week and the extra links slow down the application.

What I need to accomplish is this:

1. I need to check a table in Oracle (currently linked within my Access Application) for a date/time to compare to
the date/time I have in my imported tables (in SQL Server) to see if the new updates are there.

2. If the new update is there then I need to run a DTS package to transfer the files from Oracle to SQL Server as I
don't have the ability to set up direct links between the two.

So, in reality I guess I need to run a DTS package (unless there's a better way) to connect to Oracle, get the latest update date/time stamp, compare it against the current SQL data, and then import the data if the date in Oracle is greater than the date in SQL.

I'm open to any ideas that will work and that I will be able to implement within the structure of this company. This is a very large company so there are obstacles that may make certain solutions infeasible.

Thanks,

Bob Larson

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-03-20 : 23:53:25

Think a minute...

You are going to check an Oracle table to see if there is new data. If there is, you are going to run a process that imports the new data. If there is no new data, you are not going to run the process.
Right?
So why bother with checking the dates? Just run a process that imports new data. If the data exists, it will be imported. If the data does not exist, the process will run but nothing will be imported.
The end result is the same, but the process has only one step instead of two.
And I'm thinking this DTS package should be kicked off by a scheduled job. Not by a stored procedure.

So...just create a DTS package that identifies new records in your Oracle database and transfers them to your SQL Server database. I recommend transfering the data to staging tables and then run a stored procedure to load the data from the staging tables into your production tables. Separating the logic like this makes debugging easier.

e4 d5 xd5 Nf6

boblarson
Starting Member

17 Posts

Posted - 2008-03-21 : 11:29:49

1. How would I check to see if there are new records in an Oracle database from a SQL Server database?

2. There are hundreds of thousands of rows in that Oracle table, so checking one field for a date difference seemed like the better way to go as we don't want to go through the process each time it checks as the data set is very large.

3. Yes, we are going to have a job schedule everything depending on what needs to occur (we were checking the dates to see if an update has occured so then, if so, transfer the data).

So, based on your response, I am not sure how I would check to see if there are modified records without checking the date/time stamp as the SQL Server and Oracle Db are not linked in any way, nor are they going to be allowed to be due to the nature of the policies in place.

Thanks,

Bob Larson

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-03-21 : 15:38:48

Obviously it must be possible to link the two systems through the DTS package, right? So there is a "link" between them.
How often do you intend to check on whether there is any new data? Does the Oracle table have timestamps on the records?
How do you intend to identify the Oracle data to import?

e4 d5 xd5 Nf6

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources