Help with SQL 2005 and Sourcesafe 6

Hi,
My office are currently upgrading to SQL 2005, and as part of the upgrade
process we would like to take advantage of the source control functionality.
We already use Microsoft VIsual SOurcesafe 6 to store our VB programs, but
we would also like to use it to store our stored procedures and views. I
have been doing some reading into how this is possible, basically by
scripting them and then adding to sourcesafe, but I was wondering how SQL
would then reference these? I gather once all of the stored procs and view
have been added, they can be removed from the database, just not sure how
SQL will know where to look for the scripted stored procs and views once
they have been deleted from the database.
I would really appreciate some help and assistance in this matter
Thanks in advance
Damon
Nomad (nonsense@.nononsense.com) writes:
> My office are currently upgrading to SQL 2005, and as part of the
> upgrade process we would like to take advantage of the source control
> functionality. We already use Microsoft VIsual SOurcesafe 6 to store our
> VB programs, but we would also like to use it to store our stored
> procedures and views. I have been doing some reading into how this is
> possible, basically by scripting them and then adding to sourcesafe, but
> I was wondering how SQL would then reference these? I gather once all
> of the stored procs and view have been added, they can be removed from
> the database, just not sure how SQL will know where to look for the
> scripted stored procs and views once they have been deleted from the
> database.
Obviously the stored procedures have to be in the database for SQL Server
to run them. But the point is that SQL Server is just a place where you
deploy the stored procedures. It is SourceSafe you have the truth of
what you have shipped. But during run-time there is no reference from
SQL Server to the database.
If you want to prevent that developers forget to use SourceSafe and edit
the procedure directly in the database, you could add the WITH ENCRYPTION
clause to the procedure code. The procedure would still be in the database,
but the source code will not be equally easiliy accessible. (WITH ENCRYPTION
only buys you obfustication, so anyone who wants can still crack the code.)
There is a drawback with this though: Profiler and other tools will give
you less inforamtion.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Thanks very much for the reply. If the stored procedures are in the
database and also SourceSafe, when checking the stored proc back in, would
that update the stored proc in the database, or do you just simply update it
manually, when deploying it?
Thanks for your time.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns98C0EE3707B4EYazorman@.127.0.0.1...
> Nomad (nonsense@.nononsense.com) writes:
> Obviously the stored procedures have to be in the database for SQL Server
> to run them. But the point is that SQL Server is just a place where you
> deploy the stored procedures. It is SourceSafe you have the truth of
> what you have shipped. But during run-time there is no reference from
> SQL Server to the database.
> If you want to prevent that developers forget to use SourceSafe and edit
> the procedure directly in the database, you could add the WITH ENCRYPTION
> clause to the procedure code. The procedure would still be in the
> database,
> but the source code will not be equally easiliy accessible. (WITH
> ENCRYPTION
> only buys you obfustication, so anyone who wants can still crack the
> code.)
> There is a drawback with this though: Profiler and other tools will give
> you less inforamtion.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Nomad (nonsense@.nononsense.com) writes:
> Thanks very much for the reply. If the stored procedures are in the
> database and also SourceSafe, when checking the stored proc back in,
> would that update the stored proc in the database, or do you just simply
> update it manually, when deploying it?
If you check in a piece of VB codes does that also compile the VB
code?
Checking-in and updating the database are two different things. SourceSafe
does not even know about the database. Nor should it. From a version-
control perspective there is no *the* database. Shipping is something
you typically to do many places.
I don't think I've mentioned it, but you can set up integration with
SourceSafe in Mgmt Studio. I know I played a little with it during the
beta, but I've forgotten the details. Personally I have never liked
the various integration features, be it VB or whatever, but I prefer to
work directly in VSS Explorer.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Pardon my jumping in here in the middle of the conversation, but I have used
the VSS integration from SSMS quite extensively. As Erland has correctly
noted, VSS is a code repository. You have to manage the deployment process,
just as with any development environment. Typically, I script ALTER object
commands and store them in VSS. As for using the integration, simply load
the VSS client on your workstation and you will get the VSS options added to
Management Studio. It is no more colex that with any other Visual Studio
component.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns98C1F402F4B7EYazorman@.127.0.0.1...
> Nomad (nonsense@.nononsense.com) writes:
> If you check in a piece of VB codes does that also compile the VB
> code?
> Checking-in and updating the database are two different things. SourceSafe
> does not even know about the database. Nor should it. From a version-
> control perspective there is no *the* database. Shipping is something
> you typically to do many places.
> I don't think I've mentioned it, but you can set up integration with
> SourceSafe in Mgmt Studio. I know I played a little with it during the
> beta, but I've forgotten the details. Personally I have never liked
> the various integration features, be it VB or whatever, but I prefer to
> work directly in VSS Explorer.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Thanks for your reply. One thing with VB code is that once you have checked
it in, the code is not compiled, but it is the latest version. That doesn't
seem to be the case with stored procedures. My question is if you have your
scripted stored procs in VSS, do you then have to, say, run an alter script
@. the end of the day which updates the databases stored procs with the
modified procs from VSS?
Appreciate your time.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns98C1F402F4B7EYazorman@.127.0.0.1...
> Nomad (nonsense@.nononsense.com) writes:
> If you check in a piece of VB codes does that also compile the VB
> code?
> Checking-in and updating the database are two different things. SourceSafe
> does not even know about the database. Nor should it. From a version-
> control perspective there is no *the* database. Shipping is something
> you typically to do many places.
> I don't think I've mentioned it, but you can set up integration with
> SourceSafe in Mgmt Studio. I know I played a little with it during the
> beta, but I've forgotten the details. Personally I have never liked
> the various integration features, be it VB or whatever, but I prefer to
> work directly in VSS Explorer.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Nomad (nonsense@.nononsense.com) writes:
> Thanks for your reply. One thing with VB code is that once you have
> checked it in, the code is not compiled, but it is the latest version.
> That doesn't seem to be the case with stored procedures.
Not sure what you mean here. The latest version is simply what was
checked in most recently. If it isn't checked in, it does not exist
from a CM point of view.

> My question is if you have your scripted stored procs in VSS, do you
> then have to, say, run an alter script @. the end of the day which
> updates the databases stored procs with the modified procs from VSS?
In principle, this is no different than VB code: you will have to
compile that latest version to be table to use it.
But you of course be missing the tools to this in a convenient way. A
simple-minded way is to have scripts that goes:
IF object_id('dbo.this_sp') IS NOT NULL
DROP PROCEDURE dbo.this_sp
go
CREATE PROCEDURE dbo.this_sp ...
The file would then also have all permissions needed, since would be
dropped to.
Another alternative is to have a dummy in the beginning of the
file:
IF object_id('dbo.this_sp') IS NULL
EXEC('CREATE PROCEDURE dbo.this_sp AS SELECT 12')
go
ALTER PROCEDURE dbo.this_sp
In our shop we have our own toolset, which is quite advanced by now -
we've had it for over ten years. It's available at
http://www.abaris.se/abaperls/ as freeware, but it may be doing too
much for you.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||I understand where you're coming from but if you open up the .sql script
file, which is based on a stored procedure in the database, in a query
window and then compile it, you have only compiled the .sql file, not the
stored procedure in the database that it belongs to. How does the stored
procedure in the database get updated?
Appreciate the help.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns98CA37AC6B3DYazorman@.127.0.0.1...
> Nomad (nonsense@.nononsense.com) writes:
> Not sure what you mean here. The latest version is simply what was
> checked in most recently. If it isn't checked in, it does not exist
> from a CM point of view.
>
> In principle, this is no different than VB code: you will have to
> compile that latest version to be table to use it.
> But you of course be missing the tools to this in a convenient way. A
> simple-minded way is to have scripts that goes:
> IF object_id('dbo.this_sp') IS NOT NULL
> DROP PROCEDURE dbo.this_sp
> go
> CREATE PROCEDURE dbo.this_sp ...
> The file would then also have all permissions needed, since would be
> dropped to.
> Another alternative is to have a dummy in the beginning of the
> file:
> IF object_id('dbo.this_sp') IS NULL
> EXEC('CREATE PROCEDURE dbo.this_sp AS SELECT 12')
> go
> ALTER PROCEDURE dbo.this_sp
> In our shop we have our own toolset, which is quite advanced by now -
> we've had it for over ten years. It's available at
> http://www.abaris.se/abaperls/ as freeware, but it may be doing too
> much for you.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Nomad wrote:
[vbcol=seagreen]
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns98CA37AC6B3DYazorman@.127.0.0.1...

> I understand where you're coming from but if you open up the .sql script
> file, which is based on a stored procedure in the database, in a query
> window and then compile it, you have only compiled the .sql file, not
the
> stored procedure in the database that it belongs to. How does the stored
> procedure in the database get updated?
(Please don't top-post. Fixed.)
Language goof, I think. You don't compile the script, you execute it;
the effect of executing it is to re-create the stored procedure.
|||Nomad (nonsense@.nononsense.com) writes:
> I understand where you're coming from but if you open up the .sql script
> file, which is based on a stored procedure in the database, in a query
> window and then compile it, you have only compiled the .sql file, not the
> stored procedure in the database that it belongs to. How does the stored
> procedure in the database get updated?
The script does not belong to the stored procedure in the database.
It's rather the other way round: the script defines the stored procedure.
Provided, yes, that the script has some extra things around it. And that
you run it the correct database.
Ideally, the file should just have CREATE PROCEDURE, and that's how it
looks in our shop, where we have our own load tool which reads the files
and replaces CREATE with ALTER as needed. (And does a whole of other
transformations as well.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx