DTS to Regularly Update Stored Procedures

[Paul888]

Hi,

I have two databases with the same structure, but different data. A "live" db and a "test" db, with which each night I would like to update the live db with all the sp's on the test machine using a dts.

Using a dts copy objects task, I can specify which sp's to update which is fine. But I want it to copy new sp's created when I run the dts again without having to go and check each new sp's checkbox each time.

Is there a way that I can do this automatically without explicitly checking the new sp on the dts task?

Many Thanks in Advance
Paul

 

[Catadmin]

The only thing I can think of is more of a work around which may or may not work.

If you could track all your SPs in a table on the Test database with possibly an Approved column (bit datatype) and a Sent column (also bit) next to the SP name, you might be able to write a T-SQL Script that calls the approved SPs that have the Sent bit set to False/0 and pushes them into the DTS job.

It's just a random thought. I've recently done a similar thing with Transaction Log restores, tracking which ones have been restored and which ones have not been restored. But that is different than pumping the info into DTS, so I'm not sure how well it'll work.



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!

 

[mrdenny]

I've never tried this, but in theory it should work.

Using an ActiveX script you should be able to modify the DTS object that is transfering the procedures over.

Within the ActiveX script you should be able to pull up a list of the stored procs from the systems table, and use it to populate the DTS object. Then run the DTS Object that copies the procedures over.

Don't ask me how to write an ActiveX script. I've never learned how.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)