• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 646
  • Last Modified:

add existing sql scripts into vss

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?  

thanks
0
thewayne73
Asked:
thewayne73
  • 2
1 Solution
 
8080_DiverCommented:
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. ;-)
 
0
 
thewayne73Author Commented:
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.


0
 
8080_DiverCommented:
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.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now