Stored Procedures - recommended handling of these?

[mwehlou]

Hi,

Trying to find a method to work with stored procedures, and I'm not getting there.

I usually edit my stored procedures in VS.NET 2003 since it has a really nifty editor. It picks up the stored procedure code directly from the SQL Server and puts it back there when you save. There's no "save as..." to a file or something that I can find.

Through "File | Source Control | Open from source control..." you can call up the integrated Vault client, so saving an SP to source control must be provided in VS.NET at least. But once in the Vault Client, you must enter a "working folder" which makes no sense. The stored procedure isn't in a file in any folder on the machine I'm working on; it's only in the database.

I tried to humour the Vault client by giving it a new empty folder, but then it says "The source control server folder you selected contains no Visual Studio .NET solution or project files. Please select a different server folder.". IOW, it's terminally confused by the idea of stored procedures, I think. Presumably, SQL user defined functions won't fare any better.

A while back somebody else asked about this on the list, but it sort of petered out without any earth-shattering revelations. Can we pick this up again, please? Currently, I'm simply logging in the entire database script as one big file every time, but there must be better ways.

Thanks!

[jclausius]

this is a personal posting. the views expressed here are not necessarily those of sourcegear or other sourcegear personnel.

i actually like to keep the number of files to about 3 or 4. 1 - database and all table def'n, 2 - all indices, 3 - all stored procs, triggers, ufns, etc. 4 - any data

my experience has shown the greater number of files, the more likely it is some other developer / end-user will miss the running for some critical ddl required by your schema.


in any case, here's what you can do w/ in vs.net (2002 or 2003).

i) add a data project to your solution.
ii) if you already have ddl files, add them to the data project's sub folders
iii) if you do not have the ddl, create a data connection from within your server explorer. you can then drag any database objects from the sql server in the server explorer to your data project. assuming the project is already under vault's scc control, vs.net will script out the ddl, and then add it directly to the vault repository.

there's a big gotcha here in a multi-user environment. i believe vs.net expects all developers to all work from the same database server. and as you add items to the data project, it also adds database connections to the project as well.

this caused problems when opening a data project, and user a's sql server could not be accessed by user b. in order to get around this, i modified the database project file by hand, removing all of the sql server connections from the project.

hth

[Guest]

Hi,

Trying to find a method to work with stored procedures, and I'm not getting there.

I usually edit my stored procedures in VS.NET 2003 since it has a really nifty editor. It picks up the stored procedure code directly from the SQL Server and puts it back there when you save. There's no "save as..." to a file or something that I can find.

Through "File | Source Control | Open from source control..." you can call up the integrated Vault client, so saving an SP to source control must be provided in VS.NET at least. But once in the Vault Client, you must enter a "working folder" which makes no sense. The stored procedure isn't in a file in any folder on the machine I'm working on; it's only in the database.

I tried to humour the Vault client by giving it a new empty folder, but then it says "<B style="color:black;background-color:#ffff66">The source control server folder you selected contains no</B> Visual Studio .NET solution or project files. Please select a different server folder.". IOW, it's terminally confused by the idea of stored procedures, I think. Presumably, SQL user defined functions won't fare any better.

A while back somebody else asked about this on the list, but it sort of petered out without any earth-shattering revelations. Can we pick this up again, please? Currently, I'm simply logging in the entire database script as one big file every time, but there must be better ways.

Thanks!

[dan]

Vault is very much a file based SCC system. Breaking up SP files into separately managed stored procs (without them each being different files) simply isn't handled by either VS or by Vault.

[lneville]

If you were on Visual Source Safe I would immediately recommend a tool called SQLSourceSafe from BestSoftTool (http://www.bestsofttool.com/SQLSourceSa ... ction.aspx)

It is essentially a development environment for SQL objects - you can check tables, stored procedures, views, and udfs in and out of VSS individually and work on them directly against your database - there is no manual scripting to files.

It isn't perfect - it doesn't have all the features I would like to see, and I don't know if it can be used with Vault, but I would look into it.

[OrtizConsulting]

The way my company is using it currently for one of our projects is as such: We have a SQL Script Project with a group of individual SQL script files. Each file is essentially an ALTER TABLE or ALTER PROCEDURE or ALTER VIEW with the details of whatever database object they update; this project is stored in version control through the Vault IDE plugin in SQL Management Studio. As each script is changed and updated, they are executed and update their database versions, where it is the versions in the database that are run and tested. Once all the work is finished, a new project is created and all the SQL scripts are copied and imported into the new project, where they are changed from ALTER to CREATE; this is our initial setup set, which is then tested on a new, blank database and checked in to version control when finished as a historical set. The ALTER versions are used for ongoing development and bug fixes. As we release new major versions, we copy over the old CREATE scripts with the new ALTER scripts and update them so we always have a current install set.

The difference between a regular VS project and a SQL project is, in this setup, there's actually sort of like two working directories: the working folder where all the SQL script and project files are stored, and the test database that gets updated whenever anyone makes a change to a checked out script file. Using SQL Management Studio in this manner, tracking only the individual scripts and not the actual live database objects, the Vault plugin works just like VS2005 and we haven't had any problems with it (so long as nobody ever edits the database directly).