add existing sql scripts into vss

Posted on 2009-04-30
Last Modified: 2013-11-25
Hello Experts,

We have started using VSS, but have a lot of scripts (stored proc, views, functions) that we want to put into VSS that are only in SQL Server 2005.  I tried to just drag n drop each script from Object Explorer into the solution in the Solution Explorer window, but it does not like that.

Is there an easy way to get these scripts into a VSS solution?  

Question by:thewayne73
    LVL 22

    Expert Comment

    Use Management studio to script the various SPs, Views, UDFs, etc., (I would even consider scripting out the tables, indexes , and everything else, while you are at it ;-).  Script them each into a new query window and then create a .SQL file for each (e.g. UDF_MyDateFunction.sql) so that ecah one is separately maintainable.  
    You can add them to the Solution in Visual Studio; however, I would tend to think you would want them added as a separate "project" in VSS so that you don't wind up with 42 variations on, for instance, you UDFMyDateRoutine.sql file.  Once they are added to their own "project", you can link to them from other projects if you feel compelled to.  However, IMHO, it would be better to have a "Database Scripts "project that has sub projects for each type of script.
    As far as the SQL for specific Solutions that are developed, I tend to put my SQL statements in a separate assembly/file that only has SQL statements (defined as named constants), that way, I know where to look for them and can maximize the reuse of fairly common SQL statements.  It also lets you identify those statements that are repeatedly used so that you can consider conervting them to Sotred procs. ;-)

    Author Comment

    Thanks, but I am trying to find out if there is an easy way to all already existing scripts into VSS.  I know that I can just 'Drag and Drop' the scripts if I have them saved in a file, but hoping to be able to do something like that if they are saved in SQL Server.

    LVL 22

    Accepted Solution

    Well, I suppose that you could script out the entire database into one script and put that in VSS.
    Unfortunately, I know of no "easy" solution in the sense that you apparently want (i.e. fully automated).  Other than manually scripting the objects out and creating a file that is then loaded into VSS, I don't think there is anything that will automatically perform that for you.  (However, that does sound like a great niche market if you wanted to develop a tool for that purpose. ;-)
    I believe Idera and RedHat have some tools that may do something similar and there are some other tools (which I have never been allowed to buy ;-) that do a pretty good job of scripting out databases.  However, most of these blow everything out into one massive script and I do not recommend the unified-script approach to documenting oyour database, much less for saving it in VSS.  What happens when you have to change a couple of SP's, add two or three tables and a few indexes, and modify and/or add a couple of UDF's?  You have to script the entire kit out again and, if you are trying to restore on piece, then you have a problem.
    I know it seems like a royal pain in the a** (which it is) but, in the long run, it pays off to have a set of scripts instead of a script.
    Similarly, there are no "easy" solutions for documenting the various tables and columns, i.e. adding the comments/explanations to them.  There are some scripts for extracting the documentation on them in order create a data dictionary but none for creating the documentation.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
    So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
    This video discusses moving either the default database or any database to a new volume.
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now