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

Help with Normalizing table

I am upgrading an Access DB to SQL Server. I have a table in the database
that looks like this,
tblContacts
RegNo - Primary key
Name
Relationship
Phone
WorkPhone
Name2
Relationship2
Phone2
WorkPhone2
Name3
Relationship3
Phone3
WorkPhone3
Name4
Relationship4
Phone4
WorkPhone4
Name5
Relationship5
Phone5
WorkPhone5
Name6
Relationship6
Phone6
WorkPhone6
I have made a new table, Contacts that looks like this,
ContactID - autoincrementing INT
RegNo
Name
Relationship
Phone
WorkPhone
How can I go about getting the data from tblContacts to Contacts?
Thanks,
DrewNow that I think about it... my subject is off somewhat... I have already
normalized the table, I just need to get the data in there now.
Thanks,
Drew
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:%23FttVXj2FHA.400@.TK2MSFTNGP09.phx.gbl...
>I am upgrading an Access DB to SQL Server. I have a table in the database
>that looks like this,
> tblContacts
> RegNo - Primary key
> Name
> Relationship
> Phone
> WorkPhone
> Name2
> Relationship2
> Phone2
> WorkPhone2
> Name3
> Relationship3
> Phone3
> WorkPhone3
> Name4
> Relationship4
> Phone4
> WorkPhone4
> Name5
> Relationship5
> Phone5
> WorkPhone5
> Name6
> Relationship6
> Phone6
> WorkPhone6
> I have made a new table, Contacts that looks like this,
> ContactID - autoincrementing INT
> RegNo
> Name
> Relationship
> Phone
> WorkPhone
> How can I go about getting the data from tblContacts to Contacts?
> Thanks,
> Drew
>|||Use six "select" statements (one for each group) connected by "union all".
insert into Contacts (RegNo, Name, Relationship, Phone, WorkPhone)
select RegNo, Name, Relationship, Phone, WorkPhone
from tblContacts
union all
select RegNo, Name2, Relationship2, Phone2, WorkPhone2
from tblContacts
where
Name2 is not null
and Relationship2 is not null
from tblContacts
union all
select RegNo, Name3, Relationship3, Phone3, WorkPhone3
from tblContacts
where
Name3 is not null
and Relationship3 is not null
from tblContacts
...
AMB
"Drew" wrote:

> I am upgrading an Access DB to SQL Server. I have a table in the database
> that looks like this,
> tblContacts
> RegNo - Primary key
> Name
> Relationship
> Phone
> WorkPhone
> Name2
> Relationship2
> Phone2
> WorkPhone2
> Name3
> Relationship3
> Phone3
> WorkPhone3
> Name4
> Relationship4
> Phone4
> WorkPhone4
> Name5
> Relationship5
> Phone5
> WorkPhone5
> Name6
> Relationship6
> Phone6
> WorkPhone6
> I have made a new table, Contacts that looks like this,
> ContactID - autoincrementing INT
> RegNo
> Name
> Relationship
> Phone
> WorkPhone
> How can I go about getting the data from tblContacts to Contacts?
> Thanks,
> Drew
>
>|||Drew
CREATE TABLE People
(
PeopleID NOT NULL PRIMARY KEY,
[Name] VARCHAR(50) NOT NULL,
Address VARCHAR(100) NOT NULL,
Birthdate DATETIME NOT NULL
)
What does relatioship column mean?
CREATE TABLE Contacts
(
People_Cont NOT NULL PRIMARY KEY,
Contact_Name VARCHAR(50) NOT NULL,
Email VARCHAR(20) NULL
)
If the relatioship between these tables are many-to many so create a
"JUNCTION" table which will contain People_ID and People_Cont as Primary
KEY ( since I don't know your business reqirements , I can only guess)
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:%23FttVXj2FHA.400@.TK2MSFTNGP09.phx.gbl...
>I am upgrading an Access DB to SQL Server. I have a table in the database
>that looks like this,
> tblContacts
> RegNo - Primary key
> Name
> Relationship
> Phone
> WorkPhone
> Name2
> Relationship2
> Phone2
> WorkPhone2
> Name3
> Relationship3
> Phone3
> WorkPhone3
> Name4
> Relationship4
> Phone4
> WorkPhone4
> Name5
> Relationship5
> Phone5
> WorkPhone5
> Name6
> Relationship6
> Phone6
> WorkPhone6
> I have made a new table, Contacts that looks like this,
> ContactID - autoincrementing INT
> RegNo
> Name
> Relationship
> Phone
> WorkPhone
> How can I go about getting the data from tblContacts to Contacts?
> Thanks,
> Drew
>|||Uri,
The table is used to hold contact information for our clients. The
relationship column holds the relationship of the client to the contact.
I.E. Mother, Father, Brother, Guardian, etc.
Thanks,
Drew
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:erW5Lhj2FHA.3600@.TK2MSFTNGP12.phx.gbl...
> Drew
> CREATE TABLE People
> (
> PeopleID NOT NULL PRIMARY KEY,
> [Name] VARCHAR(50) NOT NULL,
> Address VARCHAR(100) NOT NULL,
> Birthdate DATETIME NOT NULL
> )
>
> What does relatioship column mean?
>
> CREATE TABLE Contacts
> (
> People_Cont NOT NULL PRIMARY KEY,
> Contact_Name VARCHAR(50) NOT NULL,
> Email VARCHAR(20) NULL
> )
> If the relatioship between these tables are many-to many so create a
> "JUNCTION" table which will contain People_ID and People_Cont as Primary
> KEY ( since I don't know your business reqirements , I can only guess)
>
>
> "Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
> news:%23FttVXj2FHA.400@.TK2MSFTNGP09.phx.gbl...
>|||One popular approach is to do:
SELECT RegNo, Name,
CASE n WHEN 1 THEN Relationship
WHEN 2 THEN Relationship2
..
WHEN 6 THEN Relationship6
END AS "Relationship",
CASE n WHEN 1 THEN Phone
WHEN 2 THEN Phone2
..
WHEN 6 THEN Phone6
END AS "Phone",
CASE n WHEN 1 THEN WorkPhone
WHEN 2 THEN WorkPhone2
..
WHEN 6 THEN WorkPhone6
END AS "WorkPhone"
FROM tbl, ( SELECT 1 UNION
SELECT 2 UNION
..
SELECT 6 ) D ( n ) ;
After thoroughly reviewing your data model, you might want to decide whether
an additional identifier is required.
Anith|||It worked the charm! Thanks a bunch for your input!
I did have to make some changes,
insert into Contacts (RegNo, Name, Relationship, Phone, WorkPhone)
select RegNo, Name, Relationship, Phone, WorkPhone
from tblContacts
union all
select RegNo, Name2, Relationship2, Phone2, WorkPhone2
from tblContacts
where
Name2 is not null
and Relationship2 is not null
union all
select RegNo, Name3, Relationship3, Phone3, WorkPhone3
from tblContacts
where
Name3 is not null
and Relationship3 is not null
...
Thanks,
Drew
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:2C717449-94D9-4A44-9943-F895BB991247@.microsoft.com...
> Use six "select" statements (one for each group) connected by "union all".
> insert into Contacts (RegNo, Name, Relationship, Phone, WorkPhone)
> select RegNo, Name, Relationship, Phone, WorkPhone
> from tblContacts
> union all
> select RegNo, Name2, Relationship2, Phone2, WorkPhone2
> from tblContacts
> where
> Name2 is not null
> and Relationship2 is not null
> from tblContacts
> union all
> select RegNo, Name3, Relationship3, Phone3, WorkPhone3
> from tblContacts
> where
> Name3 is not null
> and Relationship3 is not null
> from tblContacts
> ...
>
> AMB
>
> "Drew" wrote:
>|||"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:%23FttVXj2FHA.400@.TK2MSFTNGP09.phx.gbl...
> I am upgrading an Access DB to SQL Server. I have a table in the
database
> that looks like this,
<snip>

> I have made a new table, Contacts that looks like this,
> ContactID - autoincrementing INT
> RegNo
> Name
> Relationship
> Phone
> WorkPhone
> How can I go about getting the data from tblContacts to Contacts?
>
Drew,
The above table has both Phone and WorkPhone columns. This is not
1NF, as this represents repeating columns for the same type of
information (a phone number).
Contacts
ContactID
RegNo
Name
Relationship
Phones
PhoneID
ContactID
PhoneNumber
PhoneType
Sincerely,
Chris O.