SQL Server 2000 and VSS

[JayKusch]

Morning Folk...

I am interested in any info on how to setup/maintain SQL Server 2000 objects/scripts/triggers and such in VSS. I have read the thread, which is about a year old, on the attempts to integrate SQL Server 7, but found no real “beef” dealing w/ SS2K. Has anyone attempted this as of late? Would anyone know of a good place to visit for some info on this subject? Have a great day!

Thanks

J. Kusch

 

[MikeL04]

The way I do it is using the scripting feature in sql2000. You can script everything from Enterprise Manager. You can do this by right clicking the DB and selecting New Task..Generate SQL Scripts. From here check off what you want to script, ie. the "all stored proceedures" box. Now click the options tab and select to script the keys and object level permissions. Also tell it to create one file per object. Then copy the files created into VSS. When you make changes to a proc, check out of VSS, redo this for the specific procs, and check it back in. To deploy all your proc at once, get the latest of all procs in VSS into a temp folder and use the DOS copy command to copy all procs into one text file, load it into Query Analyzer and run it. It will update all procs to the latest version, or you can simply open the scripted .sql file in QA and run it.

Hope that helps.

Mike

 

[JayKusch]

Thanks ... i am fully aware on that part of the fence. what i am looking for is a method to do this for me through an automated task(s). Here is my senerio. I have over 80 DBs that are worked on by all areas of our company. The programmers have a tendency to create scripts, tables, views, triggers ... and not use VSS in some cases, though some do. at a predetermined time ... say sunday night at 12:00am i wanted a script(s) or app(s) to script out all of these items, i need in VSS, to pre-defined directories that i can then sync up w/ VSS. Thanks for the input and if you have a method to accomplish this hurdle, please advise. have a great week! Thanks

J. Kusch

 

[MikeL04]

Hmm.. I have not heard of such a app or script that could accomplish this, however it would be a godsend. Where I am we have 4 servers dedicated to SQL2000 server, all of which have anywhere from 15 to 30 DBs. If you find of some script that can do this let me know!

Mike

 

[JayKusch]

looking at using sql-DMO as a possibility! Thanks

J. Kusch

 

[Sergioff]

I have been Evaluating Embarcadero's Rapid SQL, this work well with SQL2k and VSS 6.0. I can't handle DTS objects.

 

[TimKarmiste]

Have you tried looking at mssqlXpress? It handles Stored Procs, Views, Triggers and Tables.

http://www.xpressapps.com

 

[Sergioff]

I found two solutions:
1 was developed by bill wunder and all the code including doc can be found at

http://www.nyx.net/~bwunder/dbChangeControl/body.htm.

The Second solution is to use Embarcadero's Rapid SQL.