473,320 Members | 1,858 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Using Visual SourceSafe with Sql Server

I've ready many of the posts on this and other newsgroups in which
people describe working practices for source control of database
scripts. We are looking to implement something similar in my current
workplace.

We have agreed that developers should not modify objects such as views
or stored procedures directly, they should check the script out of VSS
first, modify it, run it, and then check it back in.

The problem we are having is finding a quick, easy way to run all the
scripts in VSS in the right order, to create a new database. If we
don't run them in the right order the sysdepends table will be
incorrect.

If anyone has any suggestions please let me know.
Regards,

Ross Neilson
Software Developer
Interactive Products Ltd.

Jul 23 '05 #1
5 3481
We are also in the process of implementing Source Control on Database
scripts.. We made the decision that VSS is one of the easy ways and we
are in the process of developing a methodolgy so the developers can
follow.

So far, we already have a rule that no objects can be altered except
DTS, Table and the rest of the objects like SP, view, triggers should
be dropped and re created...

I would like to hear more from anyone who has more suggestions...!
Thanks in advance..!

Jul 23 '05 #2

<gi******@talk21.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
I've ready many of the posts on this and other newsgroups in which
people describe working practices for source control of database
scripts. We are looking to implement something similar in my current
workplace.

We have agreed that developers should not modify objects such as views
or stored procedures directly, they should check the script out of VSS
first, modify it, run it, and then check it back in.

The problem we are having is finding a quick, easy way to run all the
scripts in VSS in the right order, to create a new database. If we
don't run them in the right order the sysdepends table will be
incorrect.

If anyone has any suggestions please let me know.
Regards,

Ross Neilson
Software Developer
Interactive Products Ltd.


Have you had a look at Erland's solution? It includes functionality to check
for dependencies during the build process.

http://www.abaris.se/abaperls/index.html

Simon
Jul 23 '05 #3
Simon Hayes (sq*@hayes.ch) writes:
Have you had a look at Erland's solution? It includes functionality to
check for dependencies during the build process.

http://www.abaris.se/abaperls/index.html


Thanks for the plug Simon. :-)

However, it's only does a half-good job of getting sysdepends right.
You do get right dependencies for stored procedures, functons and views
vis-à-vis tables. However, you don't get it between stored procedures.
To at least be saved all the "cannot be added to sysdepends" messages,
DBBUILD loads all stored procedures twice. It's only that when you
ALTER a stored procedures, SQL Server - silly boy! - removes all the
dependencies from the referencing stored procedures. (This has been
addressed for SQL 2005.)

I've considered sorting the stored procedures before loading them, but
it's a kind of dauting task.

For views, by the way, the recommended way is to make use of the $REQUIRE
macros, so that a view that refers to another view (or a UDF) should
perform a $REQUIRE on that view. Maybe a little messy, but we don't use
views very much. ($REQUIRE is a kind of include, that only includes on
first invocation.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4
Erland,

Just to clarify - AbaPerls installs the stored procedures in some
random order, then installs them a second time to get the dependencies
correct?

If that is the case, then it might be what we're after. It would be
undesirable to infer the dependencies from the target database, which
could be out of synch with the scripts in VSS.

Ross

Jul 23 '05 #5
(gi******@talk21.com) writes:
Just to clarify - AbaPerls installs the stored procedures in some
random order,
Actually alphabetic.

then installs them a second time to get the dependencies correct?


It was the intention, but:

CREATE PROCEDURE a_sp AS
EXEC b_sp
go
CREATE PROCEDURE b_sp AS
SELECT 12
go
CREATE PROCEDURE c_sp AS
EXEC b_sp
go
ALTER PROCEDURE a_sp AS
EXEC b_sp
go
ALTER PROCEDURE b_sp AS
SELECT 12
go
ALTER PROCEDURE c_sp AS
EXEC b_sp
go
EXEC sp_depends b_sp
EXEC sp_depends a_sp

The output is:

Cannot add rows to sysdepends for the current stored procedure because it
depends on the missing object 'b_sp'. The stored procedure will still be
created.
In the current database, the specified object is referenced by the
following:

name type
------------ -----------------
dbo.c_sp stored procedure

Object does not reference any object, and no objects reference it.

The reason for this is that SQL Server drops the dependencies when you say
ALTER PROCEDURE. (Fixed in SQL 2005.)

There is still a point with loading procedures twice, though: on the
second time round, you don't get the "add rows to sysdepends" message.
AbaPerls includes a tool LISTERRS which reads a log from DBBUILD, and
LISTERRS skips the first run of procedures. This means that all "add
rows to sysdepends" messages it lists are for real.

In any case, even if SQL Server did it right it would not be sufficient
for us in many cases, since many references are from client code. When
I need to know whether a stored procedure is referenced somewhere, I
run another AbaPerls tool: SSGREP. This tool searches one or more
SourceSafe as defined from a config file for one or more Perl regexps.
By default, SSGREP only searches code and excludes comments. If I like,
I can restrict the change by file type of language. Output can be plain
text or HTML. (The HTML does not have any links, it was just that HTML
was an easy way to bring highlighting into the output.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Adam Sawyer | last post by:
Hi I'm having trouble getting Visual Studio 6 Enterprise (C++) to work on my Win XP Pro (SP 1 - latest .NET framework). Whenever I try to compile a C program, it just hangs, cancel build doesn't...
1
by: Rod | last post by:
I'm having trouble with VS.NET2003 and Visual SourceSafe. I upgraded from VS.NET2002 with Visual SourceSafe already installed, and then upgraded to 2003. My Source Control option has disappeared...
7
by: Monica | last post by:
Hi everybody, I am going to buy 5 VStudio .Net Enterprise Architect licences and I would like to know if I get licenses for Visio and Visual SourceSafe with it. I have been looking for...
3
by: David Lozzi | last post by:
Howdy, My coworked and I are working on a project together and we're trying to figure out the best route to use VS.NET in this environment. We have a central server hosting the app and in the web...
2
by: Mesan | last post by:
Does anyone know if it's possible to use team foundation server as a SCM backend with non-team system editions of visual studio? For example, we're planning on getting a copy of Visual Studio 2005...
2
by: =?Utf-8?B?TmFt?= | last post by:
On my ASP.NET 2.0 website project on VS 2005, I am getting the following error when building the solution: “The type or namespace name 'ADSI' does not exist in the namespace 'myNameSpace' (are...
2
by: plugwalsh | last post by:
Hi I'm having a problem accessing my dotnet projects through Visual Studio 2008 & SourceSafe 2005 (Internet) on XP Pro. Firstly, I've set up the Source Control in VS2008's Tools -Options for...
7
by: Curious | last post by:
Hi, I have the same .NET program developed on two machines, therefore, two different versions, call them A and B. There are following differences between A and B: 1) They have differences...
5
by: =?Utf-8?B?Sm9obiBXYWxrZXI=?= | last post by:
Hi, i just recently installed VB 6.0 and Sourcesafe 6.0 onto Windows XP. Unfortunately, now my .NET project are using VSS 6.0 for source control instead of VSS 2005 which it was which it was...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.