Lavsa, Rich
Rich_Lavsa at pghcorning.com
Wed Jul 27 15:03:29 CDT 2005
Eric & Mike, Thanks for your response. I wanted to stay away from DTS as it doesn't seem logical to use DTS if you are trying to keep everything in one place. Every DTS from all databases on the server go in the same place. Even with appropriate naming conventions things can get lost in there so if I can I stay away from DTS, not that I have been doing this for all that long. We have a consultant SQL developer uses them a lot but her processes are very very complex and doing it in a DTS package makes it very easy to follow. For that I can see value, but not something as simple as I am doing. Then again maybe I am not seeing the whole picture of SQL Server yet... I tried the "Master SPROC" it worked beautifully. Thank you Eric for the suggestion. I know my question probably seemed simple to most if not all of you but I came to a point where I needed to stop butting my head against the wall and ask a question.. Thanks Again, Rich -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Eric Barro Sent: Wednesday, July 27, 2005 3:14 PM To: dba-sqlserver at databaseadvisors.com Subject: RE: [dba-SQLServer] SQL Procedures - Need some advice Rich, You can combine both sprocs into one. Create your first sproc and then your second sproc and then create the "master sproc" that has the following lines... Exec sproc1 Exec sproc2 Go Save that sproc and then run a scheduled job based on that master sproc. Anything after GO is ignored so don't put the word GO after each step. Eric -----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 11:45 AM 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 ---------------------------------------------------------------- The information contained in this e-mail message and any file, document, previous e-mail message and/or attachment transmitted herewith is confidential and may be legally privileged. It is intended solely for the private use of the addressee and must not be disclosed to or used by anyone other than the addressee. If you receive this transmission by error, please immediately notify the sender by reply e-mail and destroy the original transmission and its attachments without reading or saving it in any manner. If you are not the intended recipient, or a person responsible for delivering it to the intended recipient, you are hereby notified that any disclosure, copying, distribution or use of any of the information contained in or attached to this transmission is STRICTLY PROHIBITED. E-mail transmission cannot be guaranteed to be secure or error free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of email transmission. Users and employees of the e-mail system are expressly required not to make defamatory statements and not to infringe or authorize any infringement of copyright or any other legal right by email communications. Any such communication is contrary to company policy. The company will not accept any liability in respect of such communication. _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com