Difference between MSDE 7 and SQL 7

Is there a registry key that will tell me if am running MSDE 7 or MS
SQL 7?
Thanks
I guess MSDE 7 is just a typo of yours, but there does not have to be
only the one OR the other, there can also be the two of them installed
on the server, so it would be evenbetter for you to enumerate the
instances on the machine using DMO or SMO.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||This is the situation, I have a wise installation that needs to check
if the target pc is running either MSDE or full SQL. In our case the
target pc will only be running one or the other. Thats why I was
wondering if there is a registry key that will tell me which one is
running.
|||Mapping MSDE versions to their corresponding versions of SQL Server:
MSDE 1.0 used SQL Server 7.0 technology.
MSDE 2000 (also known as SQL Server 2000 Desktop Engine) used SQL Server
2000 technology.
SQL Server 2005 Express Edition is the MSDE replacement for SQL Server 2005.
You could only run one instance of either MSDE 1.0 or SQL Server 7.0 on a
computer, support for multiple instances of the Database Engine on one
computer was not introduced until SQL Server 2000.
I don't know of a registry key, but if you issue a SELECT @.@.VERSION
statement it will report the version of SQL Server 7.0 (MSDE or an edition
such as Standard or Enterprise).
Alan Brewer [MSFT]
SQL Server Documentation Team
Download the latest Books Online update:
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Alan is right, but there are also .NET Framework factory classes that can
report all instances (2000 or 2005) on a network and on any specific
instance, the version etc. There is an example of this on my book's DVD.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Alan Brewer [MSFT]" <alanbr@.microsoft.com> wrote in message
news:OCdydtkRHHA.4060@.TK2MSFTNGP03.phx.gbl...
> Mapping MSDE versions to their corresponding versions of SQL Server:
> MSDE 1.0 used SQL Server 7.0 technology.
> MSDE 2000 (also known as SQL Server 2000 Desktop Engine) used SQL Server
> 2000 technology.
> SQL Server 2005 Express Edition is the MSDE replacement for SQL Server
> 2005.
> You could only run one instance of either MSDE 1.0 or SQL Server 7.0 on a
> computer, support for multiple instances of the Database Engine on one
> computer was not introduced until SQL Server 2000.
> I don't know of a registry key, but if you issue a SELECT @.@.VERSION
> statement it will report the version of SQL Server 7.0 (MSDE or an edition
> such as Standard or Enterprise).
> --
> Alan Brewer [MSFT]
> SQL Server Documentation Team
> Download the latest Books Online update:
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>

Diferrential Backup's

MS 2000 Server MSSQL 7. For an experiment I set up a differential backup
with Enterprise Manage on a training database. I cannot figure out how to
reconfigure the backup; turn it off, rename it, set new time, etc. Is
there anyway to at least turn this differential backup off? ThanksHi
That backup should be run from a scheduled job that appears in the
Management/SQL Server Agent/Jobs branch in Enterprise Manager, you can then
right click and disable or delete the given job.
John
"JD Henderson" <jdhend@.hotmail.com> wrote in message
news:MPG.1a173dac5a7c2c1a989680@.msnews.microsoft.com...
> MS 2000 Server MSSQL 7. For an experiment I set up a differential backup
> with Enterprise Manage on a training database. I cannot figure out how to
> reconfigure the backup; turn it off, rename it, set new time, etc. Is
> there anyway to at least turn this differential backup off? Thanks|||Thanks John
In article <bol5ef$4j6$1@.sparta.btinternet.com>,
jbellnewsposts@.hotmail.com says...
> Hi
> That backup should be run from a scheduled job that appears in the
> Management/SQL Server Agent/Jobs branch in Enterprise Manager, you can then
> right click and disable or delete the given job.
> John
> "JD Henderson" <jdhend@.hotmail.com> wrote in message
> news:MPG.1a173dac5a7c2c1a989680@.msnews.microsoft.com...
> > MS 2000 Server MSSQL 7. For an experiment I set up a differential backup
> > with Enterprise Manage on a training database. I cannot figure out how to
> > reconfigure the backup; turn it off, rename it, set new time, etc. Is
> > there anyway to at least turn this differential backup off? Thanks
>
>

Diagram Editor

Hello,

i just moved over from MS Access to MSSQL.

when trying to save a relation in the Diagram editor
between 2 tables i get this error:

'abbrev' table saved successfully
'companies' table
- Unable to create relationship 'FK_companies_abbrev'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_companies_abbrev'. The conflict occurred in database 'intranet', table 'abbrev', column 'id'.

relation is between companies.id <-> abbrev.company

both are 4 byte INTs.

it works fine with the other 4 tables but not for this last one.

any ideas/clues about what could be wrong?
and is it usefull to use that diagram or should i redo all the relations just in the query editor everytime it's needed?

thanks in advance.

SorexAre they also both "identity" columns?

Make sure that you have set the following properties for these columns in their respective tables...

Identity: YES
Identity Seed: 1
Identity Increment: 1

Also, I would set both of these columns as PRIMARY KEYS in their respective tables, so that they can be relate to each other as FKs.|||>Are they also both "identity" columns?

no, the link is between companies.id & abbrev.company (not abbrev.id)

>Make sure that you have set the following properties for these
> columns in their respective tables...
>Identity: YES
>Identity Seed: 1
>Identity Increment: 1

done this for both ID's of the tables.

i've put a screenshot online to look at what it actually is

should be on http://users.skynet.be/sky81117/sql.gif|||what's the use of the diagram anyway?

when i reopen the things i did yesterday i see the relations but they dissapear when the table content appears.

so it's a waste of time to use this feature or not?|||Originally posted by sorex
what's the use of the diagram anyway?

when i reopen the things i did yesterday i see the relations but they dissapear when the table content appears.

so it's a waste of time to use this feature or not?

The major purpose of the Diagram Editor is providing a visual tool to identify referential integrity within your database. It is the exact same tool as "Relationships" in Access. Yes, you can identify FK constraints by using the "Manage Index" tool for each table, however, I find the Diagram Editor to be quite useful and easy to establish FK constraints and show referential integrity between tables within my database systems.

The major problem with what you have done so far is within your database schema design. When I said that you should have primary key IDs in your tables, I meant that you needed to specify a current column within the tables and make that the PK, not necessary adding another field. Also, your columns are not normalized across the two tables.

Why do you have duplicate Company and contact information columns within each table?

What is the purpose of the abbrev and company tables? How are they supposed to relate to one another?

Developer design Best Practices

I have about twenty three MSSQL servers in use and need to consolidate those
down to as few as possible. What I would like to do is haev two nodes for
production and two nodes for development. The main concern my developers keep
throwing at me is that they need a generic account to use for creating tables
and stored procedures and the like so when they code them for web use they
don't have to worry about who owns the table. In thepast they utilized this
approacha and as soon as i went to tighten down on the logins they started
complaining about needing to have this generic account. Can Someone help me
with a guideline white paper or some resources to identify the best way to
payout both the server end for development and production and then the
security to go along with that design. These servers would only be used in an
intranet environment although the databases themselves may eventually migrate
to a dmz. Thanks in advance
Gkurtas wrote:
> I have about twenty three MSSQL servers in use and need to
> consolidate those down to as few as possible. What I would like to do
> is haev two nodes for production and two nodes for development. The
> main concern my developers keep throwing at me is that they need a
> generic account to use for creating tables and stored procedures and
> the like so when they code them for web use they don't have to worry
> about who owns the table. In thepast they utilized this approacha and
> as soon as i went to tighten down on the logins they started
> complaining about needing to have this generic account. Can Someone
> help me with a guideline white paper or some resources to identify
> the best way to payout both the server end for development and
> production and then the security to go along with that design. These
> servers would only be used in an intranet environment although the
> databases themselves may eventually migrate to a dmz. Thanks in
> advance
Are you saying the developers want an admistrator account for the
production servers? I've often been on projects where the lead developer
on a project would have an admin account on the development server, but
usually other developers do not. I wouldn't see why more than one or two
project managers or senior developers would need admin rights on the dev
server. On a production server, no way.
David G.
|||In addition to David's post which I couldn't agree more.
My understanding is that you are talking about object owner, i.e. special
database user. It is a good practice if you have only one owner for all
objects. This way you don't have a broken ownership chain when a sp or a
view use table or other object. This way SQL Server checks permissions on
the topmost object in the chain only. This is not only performance gain;
administration is easier as well, since you have to grant permissions on the
topmost objects only. Additionally you get better security, because you can
have a layer of sp's and views between application/users and tables. So, if
you decide for one owner only, then I suggest you to use dbo, as this user
can't be dropped and leave orphaned objects.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Gkurtas" <Gkurtas@.discussions.microsoft.com> wrote in message
news:944EBAAD-B188-4D54-84CF-56AE30610D59@.microsoft.com...
> I have about twenty three MSSQL servers in use and need to consolidate
those
> down to as few as possible. What I would like to do is haev two nodes for
> production and two nodes for development. The main concern my developers
keep
> throwing at me is that they need a generic account to use for creating
tables
> and stored procedures and the like so when they code them for web use they
> don't have to worry about who owns the table. In thepast they utilized
this
> approacha and as soon as i went to tighten down on the logins they started
> complaining about needing to have this generic account. Can Someone help
me
> with a guideline white paper or some resources to identify the best way to
> payout both the server end for development and production and then the
> security to go along with that design. These servers would only be used in
an
> intranet environment although the databases themselves may eventually
migrate
> to a dmz. Thanks in advance
|||Hi,
I agree with the two post's . My Opinion: Dev teams
should not connect to production servers, there must be a
DBA Team that checks and runs all DDL statements on
production environments. This way the DBA team can control
all the database structure and you have a central point to
alter production servers. This could lead to a more
effective checking and control. On the other hand you may
choose to have dbo ownership in all objects since the
admin accounts are all from the DBA team or for instance
you could create an different account just for objects
ownership an either protect the account just for DBA team
use.
Hope this helps
Regards

>--Original Message--
>In addition to David's post which I couldn't agree more.
>My understanding is that you are talking about object
owner, i.e. special
>database user. It is a good practice if you have only one
owner for all
>objects. This way you don't have a broken ownership chain
when a sp or a
>view use table or other object. This way SQL Server
checks permissions on
>the topmost object in the chain only. This is not only
performance gain;
>administration is easier as well, since you have to grant
permissions on the
>topmost objects only. Additionally you get better
security, because you can
>have a layer of sp's and views between application/users
and tables. So, if
>you decide for one owner only, then I suggest you to use
dbo, as this user
>can't be dropped and leave orphaned objects.
>--
>Dejan Sarka, SQL Server MVP
>Associate Mentor
>Solid Quality Learning
>More than just Training
>www.SolidQualityLearning.com
>"Gkurtas" <Gkurtas@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:944EBAAD-B188-4D54-84CF-56AE30610D59@.microsoft.com...
to consolidate[vbcol=seagreen]
>those
haev two nodes for[vbcol=seagreen]
concern my developers[vbcol=seagreen]
>keep
use for creating[vbcol=seagreen]
>tables
them for web use they[vbcol=seagreen]
thepast they utilized[vbcol=seagreen]
>this
logins they started[vbcol=seagreen]
Can Someone help[vbcol=seagreen]
>me
identify the best way to[vbcol=seagreen]
production and then the[vbcol=seagreen]
would only be used in[vbcol=seagreen]
>an
may eventually
>migrate
>
>.
>
|||I am interested in how you are going to go about consolidating 23 servers
down to 4.
Are you going to use more beefier server/storage to host multiple database
in 1 server or multiple instances of SQL Server, or utilize VMs to run
non-production SQL Server?
"Gkurtas" <Gkurtas@.discussions.microsoft.com> wrote
>I have about twenty three MSSQL servers in use and need to consolidate
>those
> down to as few as possible. What I would like to do is haev two nodes for
> production and two nodes for development.
|||I have exactly this issue, as well.
We have a lot of contractors come in and devlop. In the past, they had
their NT Login and were put into the dbo role.
However, this creates a LOT of problems once the contractor leaves. We have
to change the owner of all the objects before we can drop the contractor. My
predecessor simply left the logins active. (!!)
So, what you are saying is you give them the dbo password and let them
connect as that user? How then do you know who did what?
This is only for Dev. Developers are not given access to production.
Limited access to test.
|||Just to clarify - currently all our databases are owned by different people,
some of them gone, and objects within the database are owned by different
people, some of them gone. I would like to have one owner for all of them to
simplify things, and what I have done previously is have a user 'dbo' who is
assigned to the dbo role. But then, I haven't had this many developers
accessing things before.
"Amanda" wrote:

> I have exactly this issue, as well.
> We have a lot of contractors come in and devlop. In the past, they had
> their NT Login and were put into the dbo role.
> However, this creates a LOT of problems once the contractor leaves. We have
> to change the owner of all the objects before we can drop the contractor. My
> predecessor simply left the logins active. (!!)
> So, what you are saying is you give them the dbo password and let them
> connect as that user? How then do you know who did what?
> This is only for Dev. Developers are not given access to production.
> Limited access to test.
>
|||Amanda wrote:[vbcol=seagreen]
> Just to clarify - currently all our databases are owned by different
> people, some of them gone, and objects within the database are owned
> by different people, some of them gone. I would like to have one
> owner for all of them to simplify things, and what I have done
> previously is have a user 'dbo' who is assigned to the dbo role. But
> then, I haven't had this many developers accessing things before.
> "Amanda" wrote:
You can let developers create procedures under their own user ids for
testing purposes. When they are ready to have the procedure elevated to
dbo status in the development database (once it's been tested), they
could ask the senior developer or dba assigned to the project. They
should then remove the one they own to avoid execution problems during
testing.
In general, I would say all objects in a database should be dbo owned by
the time you hit production. In fact, it's faster to execute procedures
using the "dbo." prefix rather than leaving it off if you are not the
dbo (as is the case for users in a production environment). This avoids
any potential ownership issues as well.
David G.
|||OK - this is REALLY freaky.
Since there is only me as a dba, I can't go with the option of scripting all
their objects in dev. We do that for production and test - they develop them
and then I move them, and the owner is dbo.
There is NO DBO User in our dev database!!
It simply doesn't show up in the GUI. Of course it exists in the system
table.
I was planning to map a PIMDevDBO login to the "dbo" user. I'm sure this
can be done via SQL, but it is pretty freaky that dbo isn't showing up.
we have moved this database all over - but I've never seen this happen
before. When I run sp_change_users_login it doesn't show up in report, nor
can I fix it.
Do you guys think I should be concerned?
|||Amanda wrote:
> OK - this is REALLY freaky.
> Since there is only me as a dba, I can't go with the option of
> scripting all their objects in dev. We do that for production and
> test - they develop them and then I move them, and the owner is dbo.
> There is NO DBO User in our dev database!!
> It simply doesn't show up in the GUI. Of course it exists in the
> system table.
> I was planning to map a PIMDevDBO login to the "dbo" user. I'm sure
> this can be done via SQL, but it is pretty freaky that dbo isn't
> showing up.
> we have moved this database all over - but I've never seen this happen
> before. When I run sp_change_users_login it doesn't show up in
> report, nor can I fix it.
> Do you guys think I should be concerned?
I adminit it can be a little confusing. You are correct in that "dbo" is
not really a user in the system. When an object is owned by "dbo" it
just means it is owned by the database owner, which is normally a system
administrator.
By logging into a database as "sa" or an administrator account or a
member of the database owner group, you'll end up creating objects as
"dbo" if you do not specify an owner in the create statement. If you
login as a user that can create tables and specify the user name before
the object name, you end up creating a table owned by that user name.
Someone should be monitoring all the database object creation in
development to make sure it is done properly. It's not always an option
to have a busy dba monitoring development servers. In this case, I
suggest you designate a project manager or senior developer to perform
object creation in the development database as "dbo" owned objects.
Developers create procedures using their own IDs and when they are ready
tell someone on the team to compile as dbo. That's one option.
I realize this adds a level of overhead to development for those
developers who do not have proper rights. But with any project there is
an element of overhead designed to prevent accidents like someone
dropping tables or procedures accidentally.
If you want to mitigate this somewhat, you can let all developers create
procedures, but only allow senior developers the right to manage tables
and views.
In any case, this is sometimes a project manager's decision and maybe
you should defer to that person (of course, give him/her your
recommendations).
In any case, no access to production, except possibly the project
manager. Remember that if a developer gets on the production database
and messes something up, you inherit the problem and the responsibility
and all blame... well, need I say more.
David G.

Developer design Best Practices

I have about twenty three MSSQL servers in use and need to consolidate those
down to as few as possible. What I would like to do is haev two nodes for
production and two nodes for development. The main concern my developers keep
throwing at me is that they need a generic account to use for creating tables
and stored procedures and the like so when they code them for web use they
don't have to worry about who owns the table. In thepast they utilized this
approacha and as soon as i went to tighten down on the logins they started
complaining about needing to have this generic account. Can Someone help me
with a guideline white paper or some resources to identify the best way to
payout both the server end for development and production and then the
security to go along with that design. These servers would only be used in an
intranet environment although the databases themselves may eventually migrate
to a dmz. Thanks in advanceGkurtas wrote:
> I have about twenty three MSSQL servers in use and need to
> consolidate those down to as few as possible. What I would like to do
> is haev two nodes for production and two nodes for development. The
> main concern my developers keep throwing at me is that they need a
> generic account to use for creating tables and stored procedures and
> the like so when they code them for web use they don't have to worry
> about who owns the table. In thepast they utilized this approacha and
> as soon as i went to tighten down on the logins they started
> complaining about needing to have this generic account. Can Someone
> help me with a guideline white paper or some resources to identify
> the best way to payout both the server end for development and
> production and then the security to go along with that design. These
> servers would only be used in an intranet environment although the
> databases themselves may eventually migrate to a dmz. Thanks in
> advance
Are you saying the developers want an admistrator account for the
production servers? I've often been on projects where the lead developer
on a project would have an admin account on the development server, but
usually other developers do not. I wouldn't see why more than one or two
project managers or senior developers would need admin rights on the dev
server. On a production server, no way.
--
David G.|||In addition to David's post which I couldn't agree more.
My understanding is that you are talking about object owner, i.e. special
database user. It is a good practice if you have only one owner for all
objects. This way you don't have a broken ownership chain when a sp or a
view use table or other object. This way SQL Server checks permissions on
the topmost object in the chain only. This is not only performance gain;
administration is easier as well, since you have to grant permissions on the
topmost objects only. Additionally you get better security, because you can
have a layer of sp's and views between application/users and tables. So, if
you decide for one owner only, then I suggest you to use dbo, as this user
can't be dropped and leave orphaned objects.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Gkurtas" <Gkurtas@.discussions.microsoft.com> wrote in message
news:944EBAAD-B188-4D54-84CF-56AE30610D59@.microsoft.com...
> I have about twenty three MSSQL servers in use and need to consolidate
those
> down to as few as possible. What I would like to do is haev two nodes for
> production and two nodes for development. The main concern my developers
keep
> throwing at me is that they need a generic account to use for creating
tables
> and stored procedures and the like so when they code them for web use they
> don't have to worry about who owns the table. In thepast they utilized
this
> approacha and as soon as i went to tighten down on the logins they started
> complaining about needing to have this generic account. Can Someone help
me
> with a guideline white paper or some resources to identify the best way to
> payout both the server end for development and production and then the
> security to go along with that design. These servers would only be used in
an
> intranet environment although the databases themselves may eventually
migrate
> to a dmz. Thanks in advance|||Hi,
I agree with the two post's :). My Opinion: Dev teams
should not connect to production servers, there must be a
DBA Team that checks and runs all DDL statements on
production environments. This way the DBA team can control
all the database structure and you have a central point to
alter production servers. This could lead to a more
effective checking and control. On the other hand you may
choose to have dbo ownership in all objects since the
admin accounts are all from the DBA team or for instance
you could create an different account just for objects
ownership an either protect the account just for DBA team
use.
Hope this helps :)
Regards
>--Original Message--
>In addition to David's post which I couldn't agree more.
>My understanding is that you are talking about object
owner, i.e. special
>database user. It is a good practice if you have only one
owner for all
>objects. This way you don't have a broken ownership chain
when a sp or a
>view use table or other object. This way SQL Server
checks permissions on
>the topmost object in the chain only. This is not only
performance gain;
>administration is easier as well, since you have to grant
permissions on the
>topmost objects only. Additionally you get better
security, because you can
>have a layer of sp's and views between application/users
and tables. So, if
>you decide for one owner only, then I suggest you to use
dbo, as this user
>can't be dropped and leave orphaned objects.
>--
>Dejan Sarka, SQL Server MVP
>Associate Mentor
>Solid Quality Learning
>More than just Training
>www.SolidQualityLearning.com
>"Gkurtas" <Gkurtas@.discussions.microsoft.com> wrote in
message
>news:944EBAAD-B188-4D54-84CF-56AE30610D59@.microsoft.com...
>> I have about twenty three MSSQL servers in use and need
to consolidate
>those
>> down to as few as possible. What I would like to do is
haev two nodes for
>> production and two nodes for development. The main
concern my developers
>keep
>> throwing at me is that they need a generic account to
use for creating
>tables
>> and stored procedures and the like so when they code
them for web use they
>> don't have to worry about who owns the table. In
thepast they utilized
>this
>> approacha and as soon as i went to tighten down on the
logins they started
>> complaining about needing to have this generic account.
Can Someone help
>me
>> with a guideline white paper or some resources to
identify the best way to
>> payout both the server end for development and
production and then the
>> security to go along with that design. These servers
would only be used in
>an
>> intranet environment although the databases themselves
may eventually
>migrate
>> to a dmz. Thanks in advance
>
>.
>|||I am interested in how you are going to go about consolidating 23 servers
down to 4.
Are you going to use more beefier server/storage to host multiple database
in 1 server or multiple instances of SQL Server, or utilize VMs to run
non-production SQL Server?
"Gkurtas" <Gkurtas@.discussions.microsoft.com> wrote
>I have about twenty three MSSQL servers in use and need to consolidate
>those
> down to as few as possible. What I would like to do is haev two nodes for
> production and two nodes for development.|||I have exactly this issue, as well.
We have a lot of contractors come in and devlop. In the past, they had
their NT Login and were put into the dbo role.
However, this creates a LOT of problems once the contractor leaves. We have
to change the owner of all the objects before we can drop the contractor. My
predecessor simply left the logins active. (!!)
So, what you are saying is you give them the dbo password and let them
connect as that user? How then do you know who did what?
This is only for Dev. Developers are not given access to production.
Limited access to test.|||Amanda wrote:
> Just to clarify - currently all our databases are owned by different
> people, some of them gone, and objects within the database are owned
> by different people, some of them gone. I would like to have one
> owner for all of them to simplify things, and what I have done
> previously is have a user 'dbo' who is assigned to the dbo role. But
> then, I haven't had this many developers accessing things before.
> "Amanda" wrote:
>> I have exactly this issue, as well.
>> We have a lot of contractors come in and devlop. In the past, they
>> had their NT Login and were put into the dbo role.
>> However, this creates a LOT of problems once the contractor leaves.
>> We have to change the owner of all the objects before we can drop
>> the contractor. My predecessor simply left the logins active. (!!)
>> So, what you are saying is you give them the dbo password and let
>> them connect as that user? How then do you know who did what?
>> This is only for Dev. Developers are not given access to production.
>> Limited access to test.
You can let developers create procedures under their own user ids for
testing purposes. When they are ready to have the procedure elevated to
dbo status in the development database (once it's been tested), they
could ask the senior developer or dba assigned to the project. They
should then remove the one they own to avoid execution problems during
testing.
In general, I would say all objects in a database should be dbo owned by
the time you hit production. In fact, it's faster to execute procedures
using the "dbo." prefix rather than leaving it off if you are not the
dbo (as is the case for users in a production environment). This avoids
any potential ownership issues as well.
--
David G.|||Amanda wrote:
> OK - this is REALLY freaky.
> Since there is only me as a dba, I can't go with the option of
> scripting all their objects in dev. We do that for production and
> test - they develop them and then I move them, and the owner is dbo.
> There is NO DBO User in our dev database!!
> It simply doesn't show up in the GUI. Of course it exists in the
> system table.
> I was planning to map a PIMDevDBO login to the "dbo" user. I'm sure
> this can be done via SQL, but it is pretty freaky that dbo isn't
> showing up.
> we have moved this database all over - but I've never seen this happen
> before. When I run sp_change_users_login it doesn't show up in
> report, nor can I fix it.
> Do you guys think I should be concerned'
I adminit it can be a little confusing. You are correct in that "dbo" is
not really a user in the system. When an object is owned by "dbo" it
just means it is owned by the database owner, which is normally a system
administrator.
By logging into a database as "sa" or an administrator account or a
member of the database owner group, you'll end up creating objects as
"dbo" if you do not specify an owner in the create statement. If you
login as a user that can create tables and specify the user name before
the object name, you end up creating a table owned by that user name.
Someone should be monitoring all the database object creation in
development to make sure it is done properly. It's not always an option
to have a busy dba monitoring development servers. In this case, I
suggest you designate a project manager or senior developer to perform
object creation in the development database as "dbo" owned objects.
Developers create procedures using their own IDs and when they are ready
tell someone on the team to compile as dbo. That's one option.
I realize this adds a level of overhead to development for those
developers who do not have proper rights. But with any project there is
an element of overhead designed to prevent accidents like someone
dropping tables or procedures accidentally.
If you want to mitigate this somewhat, you can let all developers create
procedures, but only allow senior developers the right to manage tables
and views.
In any case, this is sometimes a project manager's decision and maybe
you should defer to that person (of course, give him/her your
recommendations).
In any case, no access to production, except possibly the project
manager. Remember that if a developer gets on the production database
and messes something up, you inherit the problem and the responsibility
and all blame... well, need I say more.
--
David G.|||The database owner login is a special case and is not specified like a
regular user. You can execute sp_changedbowner to specify the owner or
correct the mapping following a restore or attach:
USE MyDatabase
EXEC sp_changedbowner 'PIMDevDBO'
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Amanda" <Amanda@.discussions.microsoft.com> wrote in message
news:C9078D21-D0C3-468E-A328-FC0AB622B95F@.microsoft.com...
> OK - this is REALLY freaky.
> Since there is only me as a dba, I can't go with the option of scripting
> all
> their objects in dev. We do that for production and test - they develop
> them
> and then I move them, and the owner is dbo.
> There is NO DBO User in our dev database!!
> It simply doesn't show up in the GUI. Of course it exists in the system
> table.
> I was planning to map a PIMDevDBO login to the "dbo" user. I'm sure this
> can be done via SQL, but it is pretty freaky that dbo isn't showing up.
> we have moved this database all over - but I've never seen this happen
> before. When I run sp_change_users_login it doesn't show up in report,
> nor
> can I fix it.
> Do you guys think I should be concerned'
>

Dev. Team - Keeping Three Tiers in Sync

This may not be a MSSQL-specific question, but
I wanted to ask it here first, in case there's
a MSSQL and/or SourceSafe solution that will help.

Our dev team is having some difficulty with
keeping the nightly builds in sync with the
stored proc mods. I'm wondering if there are
some good case studies on how to avoid this
"drift". Something like genning a new DB from
checked-in SPs, etc. alongside each regular build,
then always have a paired enterprise app/database
duo that is tagged and added to a history.

FWIW, we have a 3-tier .NET/C# app, and
ADO.NET is throwing exceptions every other
day.

If the suggestion is to whip the DB guys, that
works for me as well. ;-)

Nah, there's much love there.

Thanks in advance,
~swoozWe keep all of our DDL scripts under source control. These are labeled and
built along with the app code as part of an installer custom action. We
promote all stored procedures, views, and functions along with the rest of
the app so the interfaces don't get out of sync as long as the checked-in
code is correct.

Our database installer allows either a new our upgrade installation. Even
if the 'new' install isn't needed for production, it facilitates testing
since it's the 'gold' database schema. We also include upgrade capability
as needed for production releases. The bottom line is that new and upgraded
schema are identical.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"swoozie" <swooziep@.yahoo.com> wrote in message
news:1112826148.798845.123840@.g14g2000cwa.googlegr oups.com...
> This may not be a MSSQL-specific question, but
> I wanted to ask it here first, in case there's
> a MSSQL and/or SourceSafe solution that will help.
> Our dev team is having some difficulty with
> keeping the nightly builds in sync with the
> stored proc mods. I'm wondering if there are
> some good case studies on how to avoid this
> "drift". Something like genning a new DB from
> checked-in SPs, etc. alongside each regular build,
> then always have a paired enterprise app/database
> duo that is tagged and added to a history.
> FWIW, we have a 3-tier .NET/C# app, and
> ADO.NET is throwing exceptions every other
> day.
> If the suggestion is to whip the DB guys, that
> works for me as well. ;-)
> Nah, there's much love there.
> Thanks in advance,
> ~swooz|||We use a product called DB Ghost to build our SQL Server databases
directly from SourceSafe every 10 minutes. A script checks the report
it produces for any errors and automatically emails all of the DBAs and
Development team if any are found. This covers any syntax or
dependency issues with checked in code.

If everything builds OK DB Ghost is then used to produce an upgrade
script against a restored copy of our test database. If this fails the
problem is emailed out and fixed there and then. The test database
copy is then restored again and the compare rerun - if it works then we
have a delta script that we can run in Query Analyzer against our real
test database. This same approach is used all the way to production.
The script produced also works first time - I've used other products
before but none of them come close to producing a working delta script
in the first run - I always had to correct object dependency problems
manually in Query Analyzer first.

The real beauty of this is that we only keep the delta script produced
by our overnight build which starts from a labelled baseline of all
code in VSS. This means that our SQL Code is 100% in line with our
VB6/.NET and ASP code every single time we do a release. Sure, it
doesn't get around the problem of developers not checking their changes
in properly but it does give us a great audit trail which means we get
to the root causes of those tricky test database problems real quick.

We are even thinking of losing the delta script altogether and just
letting DB Ghost do the upgrade dynamically, that way our baselined,
labelled scripts in VSS are the real 'source database' - this is
totally cool stuff.

determining who is logged into database server

Hi,
There used to be a command in mssql 6.5 that would tell
return the login names of users who had the database open,
ie, they were still in the app that connects to the db. I
think the command was something like @.@.who. Is there and
equivalent command in SQL 7 and 2k?
Also, is it possible to run sql 6.5 on a win2k server? It
keeps telling me the app is not loaded when I try to run
the service pack.
Thanks,
MikeI think you are looking for the system stored procedures
sp_who and sp_who2.
-Sue
On Mon, 3 Nov 2003 18:29:51 -0800, "Mike Stefani"
<anonymous@.discussions.microsoft.com> wrote:
>Hi,
>There used to be a command in mssql 6.5 that would tell
>return the login names of users who had the database open,
>ie, they were still in the app that connects to the db. I
>think the command was something like @.@.who. Is there and
>equivalent command in SQL 7 and 2k?
>Also, is it possible to run sql 6.5 on a win2k server? It
>keeps telling me the app is not loaded when I try to run
>the service pack.
>Thanks,
>Mike|||Hi,
I think you are referring to SP_WHO system stored procedure. You can use the
same procedure in SQL 7.0 and SQL 2k from Query analyzer to identify
all the current processes (Users) connected to SQL server.
You can defenetely run SQL 6.5 is Win2k server , but as you said there is
lots of problems. Microsoft recommends not to run SQL 6.5 in Win2k.
Thanks
Hari
MCDBA
"Mike Stefani" <anonymous@.discussions.microsoft.com> wrote in message
news:0b1a01c3a27b$862eff90$a101280a@.phx.gbl...
> Hi,
> There used to be a command in mssql 6.5 that would tell
> return the login names of users who had the database open,
> ie, they were still in the app that connects to the db. I
> think the command was something like @.@.who. Is there and
> equivalent command in SQL 7 and 2k?
> Also, is it possible to run sql 6.5 on a win2k server? It
> keeps telling me the app is not loaded when I try to run
> the service pack.
> Thanks,
> Mike|||its sp_who
--
Shaju Thomas
"Mike Stefani" <anonymous@.discussions.microsoft.com> wrote in message
news:0b1a01c3a27b$862eff90$a101280a@.phx.gbl...
> Hi,
> There used to be a command in mssql 6.5 that would tell
> return the login names of users who had the database open,
> ie, they were still in the app that connects to the db. I
> think the command was something like @.@.who. Is there and
> equivalent command in SQL 7 and 2k?
> Also, is it possible to run sql 6.5 on a win2k server? It
> keeps telling me the app is not loaded when I try to run
> the service pack.
> Thanks,
> Mike