By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,766 Members | 1,252 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,766 IT Pros & Developers. It's quick & easy.

Using Visual SourceSafe with Sql Server

P: n/a
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
Share this Question
Share on Google+
5 Replies


P: n/a
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

P: n/a

<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

P: n/a
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

P: n/a
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

P: n/a
(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 discussion thread is closed

Replies have been disabled for this discussion.