[dba-SQLServer] SQL Procedures - Need some advice

Lavsa, Rich Rich_Lavsa at pghcorning.com
Fri Jul 29 10:00:00 CDT 2005


Just wanted to post an alternative solution.

When I went to schedule the procedure, I realized I could schedule each
procedure on its own, and then on success of the first, I could start
the second procedure.  Making a 2 step schedule, on success of step 1,
start step 2.  Doing it this way gives a little more control in my mind
anyway of what gets fired and when it starts even though the MASTER
procedure approach worked just fine.

Thanks again,
Rich


-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Mike &
Doris Manning
Sent: Wednesday, July 27, 2005 3:21 PM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer] SQL Procedures - Need some advice


I would create a DTS package to take care of the execution.  You set up
the first procedure to run and then you create a workflow that runs the
second on completion of the first.  Once you have your package built,
you schedule it to run at the time desired and you can also have it
notify you of success/failure.

Doris Manning
Database Administrator
Hargrove Inc.

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Lavsa,
Rich
Sent: Wednesday, July 27, 2005 2:45 PM
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer] SQL Procedures - Need some advice

Background:
SQL 2000, have 2 procedures.  Both run fine on their own.  
Next step is to execute them on a scheduled basis, however one needs to
run and finish before the other starts.

What I want to happen:
First procedure simply deletes all records in a DataMart table.  Second
procedure runs an insert query that pulls data out of many tables in our
ERP and denormalizes it into a single table structure for quick
processing (37 minute query)

Can I combine both procedures into one?  If so how, when I tried this
the second procedure disappeared.  I had the Delete statement followed
by GO, then the Insert statement followed by GO.  It seemed to save it,
but when I opened it back up it was gone (happened more then once).  
Would it be easier to look for changes and only apply the changes and
new records, if so how would I go about doing this?  I didn't think this
would matter in my case as the table is strictly a reference table and
the 37 minute query will take 37 minutes to run no matter what.  

Looking for some guidance as I am teaching myself all I can about MS SQL
SERVER.. I have done some simple procedures that only do Selects, no
cursors.  Did some dynamic sql stuff but that's about as deep as I have
gotten into Procedures so if anyone could help and excuse my ignorance I
would greatly appreciate it.

Thanks in advance,

Rich 
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com



_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com




More information about the dba-SQLServer mailing list