| Author |
Topic |
|
rosrosros
Starting Member
3 Posts |
 Posted - 2004-02-04 : 08:05:04
|
| In our development team we have a problem on how to version control our stored procedures with VSS and Visual Studio.NET 2003:The development setup is like this (simplified to 2 developers in the team for greater overview):- Each developer has a workstation with VS.NET 2003 and MSSQL 2000, where he can develop and debug.- One VSS where all source code is placed (through VS.NET 2003)So in fact the two developers each have a copy of the source code on their local machine. When changing code it’s of course checked out. This approach has proved useful with many developers on the project. Today stored procedures are handled like text files in VSS – so when the developer wants to edit a SP he must:- checkout the text file containing the SP- create the SP in the local database (from the text file) – to ensure he has the latest version- make the change to the SP and save it to the local database (both using VS.NET 2003 )- use Query Analyzer to script the SP into a text file- update the from VSS checked out text file – and check it in againThis procedure is very tedious and not very safe – so it requires a large amount of discipline on behalf of the developers.We’ve tried to use the built-in functionality in VS.NET 2003 to use stored procedures with VSS. But it seems to us that the functionality is minded on setups with only one MSSQL that all the developers share. When creating a new SP in the local MSSQL and adding it to VSS it appears that VSS will store the SP as belonging to that local MSSQL. But the functionality we want is a shared VSS location where all the developers easily can checkout SP’s to their local MSSQL, edit them and check them in again.We would like to avoid 3. party software if possibleAny help is appreciated! |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-02-04 : 08:13:04
|
| You can alleviate a great deal of the tedium by NOT using Visual Studio. Check the procedures out from VSS, but modify and run them from Query Analyzer instead. The current sequence of events:- checkout the text file containing the SP- create the SP in the local database (from the text file) – to ensure he has the latest version- make the change to the SP and save it to the local database (both using VS.NET 2003 )- use Query Analyzer to script the SP into a text file- update the from VSS checked out text file – and check it in again...will become:- checkout the text file containing the SP- open the file in Query Analyzer and (re)create the SP in the local database- make the change to the SP and save it to the local database and the text file- check it in againWe do this at my place of work, and it takes at most 10 seconds to check something in and out from VSS. All of the sproc's are edited in Query Analyzer. Checking in and out are done using the SourceSafe app, never Visual Studio. And deploying individual procedures to multiple databases is very easy...just run the script file to create the procedure. |
 |
|
|
rosrosros
Starting Member
3 Posts |
Posted - 2004-02-04 : 08:17:58
|
| Thanks for the fast answer. Yes that could be a way to work, but you would loose the advangede of the CTRL+S save in VS.NET and worse – you would not be able to debug you SP when they are called from C# code…Or am I wrong? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-02-04 : 08:27:45
|
| Ummmm, Ctrl-S works in Query Analyzer, and version 2000 has a debugger built in. And debugging from a C# call is not a good practice IMHO. The stored procedure should be tested, debugged, and should run independently of any client code that might call it. A stored procedure that is interwoven with application code is on a shaky foundation and is more likely to fail when something gets changed. |
|
|
|
rosrosros
Starting Member
3 Posts |
Posted - 2004-02-04 : 08:34:03
|
| Yes CTRL-S works in QA, but it dons't save the SP to the SQL Server - whitch VS.NET does. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-02-04 : 08:35:05
|
| When you execute the code in QA it creates the procedure. There is no "saving" a procedure in a database, you create it. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-02-04 : 09:07:20
|
| seehttp://www.nigelrivett.net/sqlserverdevelopment.htmlAs Rob says keep the SP and database development separate from the application.You should be able to change the SPs and database structure without running the application.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2004-02-05 : 01:24:56
|
| Actually you can execute sql script files (like stored proc scripts) from VS.NET if you add a Database project to your solution. See http://tinyurl.com/yuf5yDamian |
|
|
|
yanky
Starting Member
1 Post |
Posted - 2004-03-01 : 10:11:06
|
| Hello rosrosros,I'm just getting started with VS.NET.Could you tell me, how to add a stored procedure to Visual SourceSafe?That would be to nice.Thanks, Dennis. |
|
|
|
|
stored procedures with VSS and Visual Studio.NET 2