If there is an easier way, please advise.

Here's what I want to do.

Say I have 30 stored procedures that I want to be sure are up to date and expect
changes to in the development system. I then want to update in the live system.

I'm happy going thru the scripts one at a time as needed, and I'm confident that
no changes have been made in the dev system, but not in production where there
are many hands turning the handles.

So, I would like to create a folder called "Finals" that is sub to the stored
proc's folder.

I'd like to read each proc found in the folder and run them to update all 30
procs in one step via DTS.

What components do I need?

For example I'm thinking that I need FSO, but I'm stummed at the next steps,
starting with how to read an SQL file and execute it.

TIA

JeffP...

OK so from what you mention you want to keep SPs on one system in sync with
the SPs on another. You can easily do this using SQLDMO. You would script
them out to text file, change the CREATE to ALTER and then replay the file
using OSQL (Look in BOL)



--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"JDP@Work" <JPGMTNoSpam (AT) sbcglobal (DOT) net> wrote

Thanks, and I'm still a little in the dark...

I wrote a litte qry file

use aus_central
go
create proc test_osql
as
declare @accno varchar(20)
set @accno = (select top 1 accountno from contact1 where company like 'AUS%')
update contact1
set company = 'AUS JeffP'
where accountno = @accno
return

I ran it and added the sp and tested it and ran a query select to see the
results, all went as expected.

So back to my questions: I [would, would not] be using DTS?

If so, what component? Perhaps SQLTask using sp_cmdShell?

TIA

JeffP......


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

No you do not need to use DTS.

I would write a bit of VB Script to do the scripting out of your procs to a
text file and then use OSQL to apply it to the production server.


--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"JDP@Work" <JPGMTNoSpam (AT) sbcglobal (DOT) net> wrote

If my procs are already in a script file what is VBS doing?

Is VBS running a loop thru the list of found files in a folder and then calling
OSQL to execute?

Do you have a good clue for a Google, perhaps I can see an example?

TIA

JeffP....


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

I use VBScript and DMO to create the file containing the proc statements

All procs can be written to the same file

http://msdn.microsoft.com/library/de...f_m_s_0n1g.asp

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"JDP@Work" <JPGMTNoSpam (AT) sbcglobal (DOT) net> wrote