Hi,
Does anyone have any information on comparison between different databases
for datawarehousing? I am working on a study to develop a datawarehousing
solution for my client, so I would like to get some information on
competitive aspects of SQL Server 2005 over DB2 and Oracle.
Thanks all~
SQL 2005 offer a better package then the others. (at a lower price then the
2 others)
In 1 product you have a database engine, OLAP engine, data mining engine,
report engine and ETL engine.
Analysis Services (7.0, 2000... and 2005...) is the most OLAP server used.
(http://www.olapreport.com/market.htm)
From a performance point of view the 3 RDBMS servers are near identical
(www.tpc.org / TCP-H benchmark)
From a 3rd party support Microsoft is better, you'll found a lot of tools &
product compatible with AS 2000/2005 & SQL 2000/2005.
But you'll found a lot of articles which push one of these vendors...
You have to setup a complete list of needs & requirements of what your
client want.
But from my experience, this doesn't really helps you in your process.
Today the internal expertise & knowledge of a particular database/technology
is most important, if you know and allready use the product you are able to
provide better & faster results.
Also... get the demo versions of the tools or ask the vendors for a proof
of concept.
if you are really neutral, let the sales rep. to do their jobs.
"Stella" <stellajst@.hotmail.com> wrote in message
news:ulhZuoP5FHA.884@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Does anyone have any information on comparison between different databases
> for datawarehousing? I am working on a study to develop a datawarehousing
> solution for my client, so I would like to get some information on
> competitive aspects of SQL Server 2005 over DB2 and Oracle.
> Thanks all~
>
|||There is a technical paper with detailed comparison between Oracle and
SQL Server from wisdomforce site:
http://wisdomforce.com/dweb/resource...0g_compare.pdf
As far as I remember it contains a lot of OLAP info
J=E9j=E9 wrote:
> SQL 2005 offer a better package then the others. (at a lower price then t=
he
> 2 others)
> In 1 product you have a database engine, OLAP engine, data mining engine,
> report engine and ETL engine.
> Analysis Services (7.0, 2000... and 2005...) is the most OLAP server used.
> (http://www.olapreport.com/market.htm)
> From a performance point of view the 3 RDBMS servers are near identical
> (www.tpc.org / TCP-H benchmark)
> From a 3rd party support Microsoft is better, you'll found a lot of tools=
&
> product compatible with AS 2000/2005 & SQL 2000/2005.
> But you'll found a lot of articles which push one of these vendors...
> You have to setup a complete list of needs & requirements of what your
> client want.
> But from my experience, this doesn't really helps you in your process.
> Today the internal expertise & knowledge of a particular database/technol=
ogy
> is most important, if you know and allready use the product you are able =
to[vbcol=seagreen]
> provide better & faster results.
> Also... get the demo versions of the tools or ask the vendors for a proof
> of concept.
> if you are really neutral, let the sales rep. to do their jobs.
>
> "Stella" <stellajst@.hotmail.com> wrote in message
> news:ulhZuoP5FHA.884@.TK2MSFTNGP14.phx.gbl...
ses[vbcol=seagreen]
ing[vbcol=seagreen]
|||Jeje,
"SQL 2005 offer a better package then the others. (at a lower price
then the 2 others) In 1 product you have a database engine, OLAP
engine, data mining engine, report engine and ETL engine."
In your opinion... ;-)
Yes, SQL Server is pretty good, and I've read up on 2005 and it's
pretty good too....I've used the beta and it seems to do what the book
says...
But I recently did a project where the design points were 10,000 users,
180M inserts per day and 15TB of disk.....and there is no question SQL
Server has no references at that level and therefore would not make the
cut under the 'proven technology' selection criteria.
So, which database is 'better' is best prefixed by asking the question,
what are you thinking of doing with it, and Stella has not provided
such information apart from 'data warehousing'...';-)
Nowadays, under 1TB of disk (100GB of data for SQL Server) you can
hardly go wrong with any of these three...
Best Regards
Peter
|||Thanks all for your help!
To be more specific, I'm actually looking for a comparison between databases
of 5TB to 8TB in size, running on Linux or Windows. Would anyone have ever
come across any cases of data-warehousing with these criteria?
Thanks~
"Peter Nolan" <peter@.peternolan.com> wrote in message
news:1131641995.382364.174340@.z14g2000cwz.googlegr oups.com...
> Jeje,
> "SQL 2005 offer a better package then the others. (at a lower price
> then the 2 others) In 1 product you have a database engine, OLAP
> engine, data mining engine, report engine and ETL engine."
> In your opinion... ;-)
> Yes, SQL Server is pretty good, and I've read up on 2005 and it's
> pretty good too....I've used the beta and it seems to do what the book
> says...
> But I recently did a project where the design points were 10,000 users,
> 180M inserts per day and 15TB of disk.....and there is no question SQL
> Server has no references at that level and therefore would not make the
> cut under the 'proven technology' selection criteria.
> So, which database is 'better' is best prefixed by asking the question,
> what are you thinking of doing with it, and Stella has not provided
> such information apart from 'data warehousing'...';-)
> Nowadays, under 1TB of disk (100GB of data for SQL Server) you can
> hardly go wrong with any of these three...
> Best Regards
> Peter
>
|||Hi Stella,
the major metrics to provide are...
1. Volume of source data because each of the databases expands data by
a specific amount and most of them now also support varying levels of
compression. 5 to 8 TB means different sizes on different databases.
2. Number of rows in large tables.
3. Number of users.
As the size, complexity and number of users goes up
Oracle/DB2/Teradata/Sybase IQ come more into their own over MySQL and
SQL Server. I think you will find few examples of 5-8TB databases
running on SQL Server and not that many on windows/oracle. For
DB2/oracle they are commonplace.
For IQ there are almost none because you get compression in IQ.....so
something that is 8TB disk in Oracle is about 800GB disk in IQ. IQ now
holds more of the top 10 records than any other database...amazingly it
is a very well kept secret of out friends at Sybase.
I think it is unrealistic to think that as a consulting provider to a
client you would be given the benefit of years of experience in using
these databases across many projects in short appends on a newsgroup.
Long term experience across a range of databases provides a set of hard
won skills which are of great value to clients should they want someone
to assist in the database decision.
You can ask the vendors. Each vendor produces detailed papers for their
database.
Each of MySQL, SQL Server, Oracle, DB2, Teradata, Sybase IQ have their
strenghts and weaknesses. No vendor is going to tell you anything about
their weaknesses and vendors (and biased consultants) spread plenty of
mis-information and out of date information about competitive products.
For SQL Server (we are on a MSFT newsgroup) MSFT has done an excellent
job of documenting project REAL.
http://www.microsoft.com/sql/solutio...ojectreal.mspx
This is one of the most open presentations of a BI effort any of the
vendors has produced. MSFT are to be congratulated on their open-ness
on this one!!
My FAQs page discusses some of the considerations.
http://www.peternolan.com/FAQs/tabid/136/Default.aspx but it is only
intended to provide advice for smaller clients. Larger clients are
expected to have staff to advise on these decisions or pay for advice
on these decisions. I have been paid by clients to prove that the
database they wanted to use would work for what they wanted to do with
it.
Having said all that...
Many people consider what database to use as setp 1 of a BI effort. It
is one of the most ineffective places to start. It is far better to
start with what business the business is in and to design and build a
working prototype before deciding on what database to use.
It is now possible to build large scale prototypes and to have the the
ETL completely transportable from one database to the other with only
the cost of converting the table definitions.
Of course, none of the database vendors will tell anyone that it is
possible to migrate a DW between databases with trivial effort!! It is
not in their interests. And MSFT/Oracle have tied their ETL tools
tightly to the database so if you write your ETL in one of these two
ETL products you will live with the database forever.
So...It is now possible to postpone the 'which database' decision
until very late in the process, which also means it is possible to
postpone the database license payment until late in the process.
Indeed, in some clients we have built the large scale prototype on 2
competing databases so that we could really compare apples with apples.
No amount of 'competitive information' is equal to actually running two
databases side by side, latest release, with the vendors competing for
the business. The mix of HW/OS/RDBMS/DISK and versions of all these
makes it pretty much impossible to compare two solutions on
paper...though many try.
I recommend to my clients that if they are undecided about which
database then they are best advised to remain undecided until they
complete the development of the protoype and do a side by side
comparison to prove to themselves which one they feel is best for them.
It is not lost on my clients that the database vendors are far more
likely to offer incentives when the decision process is clear and
imminent and the weaknesses of their product have been exposed in large
scale prototype... ;-)
Peter
www.peternolan.com
Showing posts with label working. Show all posts
Showing posts with label working. Show all posts
Sunday, March 25, 2012
Database for datawarehouse
Hi,
Does anyone have any information on comparison between different databases
for datawarehousing? I am working on a study to develop a datawarehousing
solution for my client, so I would like to get some information on
competitive aspects of SQL Server 2005 over DB2 and Oracle.
Thanks all~SQL 2005 offer a better package then the others. (at a lower price then the
2 others)
In 1 product you have a database engine, OLAP engine, data mining engine,
report engine and ETL engine.
Analysis Services (7.0, 2000... and 2005...) is the most OLAP server used.
(http://www.olapreport.com/market.htm)
From a performance point of view the 3 RDBMS servers are near identical
(www.tpc.org / TCP-H benchmark)
From a 3rd party support Microsoft is better, you'll found a lot of tools &
product compatible with AS 2000/2005 & SQL 2000/2005.
But you'll found a lot of articles which push one of these vendors...
You have to setup a complete list of needs & requirements of what your
client want.
But from my experience, this doesn't really helps you in your process.
Today the internal expertise & knowledge of a particular database/technology
is most important, if you know and allready use the product you are able to
provide better & faster results.
Also... get the demo versions of the tools or ask the vendors for a proof
of concept.
if you are really neutral, let the sales rep. to do their jobs.
"Stella" <stellajst@.hotmail.com> wrote in message
news:ulhZuoP5FHA.884@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Does anyone have any information on comparison between different databases
> for datawarehousing? I am working on a study to develop a datawarehousing
> solution for my client, so I would like to get some information on
> competitive aspects of SQL Server 2005 over DB2 and Oracle.
> Thanks all~
>|||There is a technical paper with detailed comparison between Oracle and
SQL Server from wisdomforce site:
http://wisdomforce.com/dweb/resourc...10g_compare.pdf
As far as I remember it contains a lot of OLAP info
J=E9j=E9 wrote:
> SQL 2005 offer a better package then the others. (at a lower price then t=
he
> 2 others)
> In 1 product you have a database engine, OLAP engine, data mining engine,
> report engine and ETL engine.
> Analysis Services (7.0, 2000... and 2005...) is the most OLAP server used.
> (http://www.olapreport.com/market.htm)
> From a performance point of view the 3 RDBMS servers are near identical
> (www.tpc.org / TCP-H benchmark)
> From a 3rd party support Microsoft is better, you'll found a lot of tools=
&
> product compatible with AS 2000/2005 & SQL 2000/2005.
> But you'll found a lot of articles which push one of these vendors...
> You have to setup a complete list of needs & requirements of what your
> client want.
> But from my experience, this doesn't really helps you in your process.
> Today the internal expertise & knowledge of a particular database/technol=
ogy
> is most important, if you know and allready use the product you are able =
to[vbcol=seagreen]
> provide better & faster results.
> Also... get the demo versions of the tools or ask the vendors for a proof
> of concept.
> if you are really neutral, let the sales rep. to do their jobs.
>
> "Stella" <stellajst@.hotmail.com> wrote in message
> news:ulhZuoP5FHA.884@.TK2MSFTNGP14.phx.gbl...
ses[vbcol=seagreen]
ing[vbcol=seagreen]|||Jeje,
"SQL 2005 offer a better package then the others. (at a lower price
then the 2 others) In 1 product you have a database engine, OLAP
engine, data mining engine, report engine and ETL engine."
In your opinion... ;-)
Yes, SQL Server is pretty good, and I've read up on 2005 and it's
pretty good too....I've used the beta and it seems to do what the book
says...
But I recently did a project where the design points were 10,000 users,
180M inserts per day and 15TB of disk.....and there is no question SQL
Server has no references at that level and therefore would not make the
cut under the 'proven technology' selection criteria.
So, which database is 'better' is best prefixed by asking the question,
what are you thinking of doing with it, and Stella has not provided
such information apart from 'data warehousing'...';-)
Nowadays, under 1TB of disk (100GB of data for SQL Server) you can
hardly go wrong with any of these three...
Best Regards
Peter|||Thanks all for your help!
To be more specific, I'm actually looking for a comparison between databases
of 5TB to 8TB in size, running on Linux or Windows. Would anyone have ever
come across any cases of data-warehousing with these criteria?
Thanks~
"Peter Nolan" <peter@.peternolan.com> wrote in message
news:1131641995.382364.174340@.z14g2000cwz.googlegroups.com...
> Jeje,
> "SQL 2005 offer a better package then the others. (at a lower price
> then the 2 others) In 1 product you have a database engine, OLAP
> engine, data mining engine, report engine and ETL engine."
> In your opinion... ;-)
> Yes, SQL Server is pretty good, and I've read up on 2005 and it's
> pretty good too....I've used the beta and it seems to do what the book
> says...
> But I recently did a project where the design points were 10,000 users,
> 180M inserts per day and 15TB of disk.....and there is no question SQL
> Server has no references at that level and therefore would not make the
> cut under the 'proven technology' selection criteria.
> So, which database is 'better' is best prefixed by asking the question,
> what are you thinking of doing with it, and Stella has not provided
> such information apart from 'data warehousing'...';-)
> Nowadays, under 1TB of disk (100GB of data for SQL Server) you can
> hardly go wrong with any of these three...
> Best Regards
> Peter
>|||Hi Stella,
the major metrics to provide are...
1. Volume of source data because each of the databases expands data by
a specific amount and most of them now also support varying levels of
compression. 5 to 8 TB means different sizes on different databases.
2. Number of rows in large tables.
3. Number of users.
As the size, complexity and number of users goes up
Oracle/DB2/Teradata/Sybase IQ come more into their own over mysql and
SQL Server. I think you will find few examples of 5-8TB databases
running on SQL Server and not that many on windows/oracle. For
DB2/oracle they are commonplace.
For IQ there are almost none because you get compression in IQ.....so
something that is 8TB disk in Oracle is about 800GB disk in IQ. IQ now
holds more of the top 10 records than any other database...amazingly it
is a very well kept secret of out friends at Sybase.
I think it is unrealistic to think that as a consulting provider to a
client you would be given the benefit of years of experience in using
these databases across many projects in short appends on a newsgroup.
Long term experience across a range of databases provides a set of hard
won skills which are of great value to clients should they want someone
to assist in the database decision.
You can ask the vendors. Each vendor produces detailed papers for their
database.
Each of MySQL, SQL Server, Oracle, DB2, Teradata, Sybase IQ have their
strenghts and weaknesses. No vendor is going to tell you anything about
their weaknesses and vendors (and biased consultants) spread plenty of
mis-information and out of date information about competitive products.
For SQL Server (we are on a MSFT newsgroup) MSFT has done an excellent
job of documenting project REAL.
http://www.microsoft.com/sql/soluti...rojectreal.mspx
This is one of the most open presentations of a BI effort any of the
vendors has produced. MSFT are to be congratulated on their open-ness
on this one!!
My FAQs page discusses some of the considerations.
http://www.peternolan.com/FAQs/tabid/136/Default.aspx but it is only
intended to provide advice for smaller clients. Larger clients are
expected to have staff to advise on these decisions or pay for advice
on these decisions. I have been paid by clients to prove that the
database they wanted to use would work for what they wanted to do with
it.
Having said all that...
Many people consider what database to use as setp 1 of a BI effort. It
is one of the most ineffective places to start. It is far better to
start with what business the business is in and to design and build a
working prototype before deciding on what database to use.
It is now possible to build large scale prototypes and to have the the
ETL completely transportable from one database to the other with only
the cost of converting the table definitions.
Of course, none of the database vendors will tell anyone that it is
possible to migrate a DW between databases with trivial effort!! It is
not in their interests. And MSFT/Oracle have tied their ETL tools
tightly to the database so if you write your ETL in one of these two
ETL products you will live with the database forever.
So...It is now possible to postpone the 'which database' decision
until very late in the process, which also means it is possible to
postpone the database license payment until late in the process.
Indeed, in some clients we have built the large scale prototype on 2
competing databases so that we could really compare apples with apples.
No amount of 'competitive information' is equal to actually running two
databases side by side, latest release, with the vendors competing for
the business. The mix of HW/OS/RDBMS/DISK and versions of all these
makes it pretty much impossible to compare two solutions on
paper...though many try.
I recommend to my clients that if they are undecided about which
database then they are best advised to remain undecided until they
complete the development of the protoype and do a side by side
comparison to prove to themselves which one they feel is best for them.
It is not lost on my clients that the database vendors are far more
likely to offer incentives when the decision process is clear and
imminent and the weaknesses of their product have been exposed in large
scale prototype... ;-)
Peter
www.peternolan.com
Does anyone have any information on comparison between different databases
for datawarehousing? I am working on a study to develop a datawarehousing
solution for my client, so I would like to get some information on
competitive aspects of SQL Server 2005 over DB2 and Oracle.
Thanks all~SQL 2005 offer a better package then the others. (at a lower price then the
2 others)
In 1 product you have a database engine, OLAP engine, data mining engine,
report engine and ETL engine.
Analysis Services (7.0, 2000... and 2005...) is the most OLAP server used.
(http://www.olapreport.com/market.htm)
From a performance point of view the 3 RDBMS servers are near identical
(www.tpc.org / TCP-H benchmark)
From a 3rd party support Microsoft is better, you'll found a lot of tools &
product compatible with AS 2000/2005 & SQL 2000/2005.
But you'll found a lot of articles which push one of these vendors...
You have to setup a complete list of needs & requirements of what your
client want.
But from my experience, this doesn't really helps you in your process.
Today the internal expertise & knowledge of a particular database/technology
is most important, if you know and allready use the product you are able to
provide better & faster results.
Also... get the demo versions of the tools or ask the vendors for a proof
of concept.
if you are really neutral, let the sales rep. to do their jobs.
"Stella" <stellajst@.hotmail.com> wrote in message
news:ulhZuoP5FHA.884@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Does anyone have any information on comparison between different databases
> for datawarehousing? I am working on a study to develop a datawarehousing
> solution for my client, so I would like to get some information on
> competitive aspects of SQL Server 2005 over DB2 and Oracle.
> Thanks all~
>|||There is a technical paper with detailed comparison between Oracle and
SQL Server from wisdomforce site:
http://wisdomforce.com/dweb/resourc...10g_compare.pdf
As far as I remember it contains a lot of OLAP info
J=E9j=E9 wrote:
> SQL 2005 offer a better package then the others. (at a lower price then t=
he
> 2 others)
> In 1 product you have a database engine, OLAP engine, data mining engine,
> report engine and ETL engine.
> Analysis Services (7.0, 2000... and 2005...) is the most OLAP server used.
> (http://www.olapreport.com/market.htm)
> From a performance point of view the 3 RDBMS servers are near identical
> (www.tpc.org / TCP-H benchmark)
> From a 3rd party support Microsoft is better, you'll found a lot of tools=
&
> product compatible with AS 2000/2005 & SQL 2000/2005.
> But you'll found a lot of articles which push one of these vendors...
> You have to setup a complete list of needs & requirements of what your
> client want.
> But from my experience, this doesn't really helps you in your process.
> Today the internal expertise & knowledge of a particular database/technol=
ogy
> is most important, if you know and allready use the product you are able =
to[vbcol=seagreen]
> provide better & faster results.
> Also... get the demo versions of the tools or ask the vendors for a proof
> of concept.
> if you are really neutral, let the sales rep. to do their jobs.
>
> "Stella" <stellajst@.hotmail.com> wrote in message
> news:ulhZuoP5FHA.884@.TK2MSFTNGP14.phx.gbl...
ses[vbcol=seagreen]
ing[vbcol=seagreen]|||Jeje,
"SQL 2005 offer a better package then the others. (at a lower price
then the 2 others) In 1 product you have a database engine, OLAP
engine, data mining engine, report engine and ETL engine."
In your opinion... ;-)
Yes, SQL Server is pretty good, and I've read up on 2005 and it's
pretty good too....I've used the beta and it seems to do what the book
says...
But I recently did a project where the design points were 10,000 users,
180M inserts per day and 15TB of disk.....and there is no question SQL
Server has no references at that level and therefore would not make the
cut under the 'proven technology' selection criteria.
So, which database is 'better' is best prefixed by asking the question,
what are you thinking of doing with it, and Stella has not provided
such information apart from 'data warehousing'...';-)
Nowadays, under 1TB of disk (100GB of data for SQL Server) you can
hardly go wrong with any of these three...
Best Regards
Peter|||Thanks all for your help!
To be more specific, I'm actually looking for a comparison between databases
of 5TB to 8TB in size, running on Linux or Windows. Would anyone have ever
come across any cases of data-warehousing with these criteria?
Thanks~
"Peter Nolan" <peter@.peternolan.com> wrote in message
news:1131641995.382364.174340@.z14g2000cwz.googlegroups.com...
> Jeje,
> "SQL 2005 offer a better package then the others. (at a lower price
> then the 2 others) In 1 product you have a database engine, OLAP
> engine, data mining engine, report engine and ETL engine."
> In your opinion... ;-)
> Yes, SQL Server is pretty good, and I've read up on 2005 and it's
> pretty good too....I've used the beta and it seems to do what the book
> says...
> But I recently did a project where the design points were 10,000 users,
> 180M inserts per day and 15TB of disk.....and there is no question SQL
> Server has no references at that level and therefore would not make the
> cut under the 'proven technology' selection criteria.
> So, which database is 'better' is best prefixed by asking the question,
> what are you thinking of doing with it, and Stella has not provided
> such information apart from 'data warehousing'...';-)
> Nowadays, under 1TB of disk (100GB of data for SQL Server) you can
> hardly go wrong with any of these three...
> Best Regards
> Peter
>|||Hi Stella,
the major metrics to provide are...
1. Volume of source data because each of the databases expands data by
a specific amount and most of them now also support varying levels of
compression. 5 to 8 TB means different sizes on different databases.
2. Number of rows in large tables.
3. Number of users.
As the size, complexity and number of users goes up
Oracle/DB2/Teradata/Sybase IQ come more into their own over mysql and
SQL Server. I think you will find few examples of 5-8TB databases
running on SQL Server and not that many on windows/oracle. For
DB2/oracle they are commonplace.
For IQ there are almost none because you get compression in IQ.....so
something that is 8TB disk in Oracle is about 800GB disk in IQ. IQ now
holds more of the top 10 records than any other database...amazingly it
is a very well kept secret of out friends at Sybase.
I think it is unrealistic to think that as a consulting provider to a
client you would be given the benefit of years of experience in using
these databases across many projects in short appends on a newsgroup.
Long term experience across a range of databases provides a set of hard
won skills which are of great value to clients should they want someone
to assist in the database decision.
You can ask the vendors. Each vendor produces detailed papers for their
database.
Each of MySQL, SQL Server, Oracle, DB2, Teradata, Sybase IQ have their
strenghts and weaknesses. No vendor is going to tell you anything about
their weaknesses and vendors (and biased consultants) spread plenty of
mis-information and out of date information about competitive products.
For SQL Server (we are on a MSFT newsgroup) MSFT has done an excellent
job of documenting project REAL.
http://www.microsoft.com/sql/soluti...rojectreal.mspx
This is one of the most open presentations of a BI effort any of the
vendors has produced. MSFT are to be congratulated on their open-ness
on this one!!
My FAQs page discusses some of the considerations.
http://www.peternolan.com/FAQs/tabid/136/Default.aspx but it is only
intended to provide advice for smaller clients. Larger clients are
expected to have staff to advise on these decisions or pay for advice
on these decisions. I have been paid by clients to prove that the
database they wanted to use would work for what they wanted to do with
it.
Having said all that...
Many people consider what database to use as setp 1 of a BI effort. It
is one of the most ineffective places to start. It is far better to
start with what business the business is in and to design and build a
working prototype before deciding on what database to use.
It is now possible to build large scale prototypes and to have the the
ETL completely transportable from one database to the other with only
the cost of converting the table definitions.
Of course, none of the database vendors will tell anyone that it is
possible to migrate a DW between databases with trivial effort!! It is
not in their interests. And MSFT/Oracle have tied their ETL tools
tightly to the database so if you write your ETL in one of these two
ETL products you will live with the database forever.
So...It is now possible to postpone the 'which database' decision
until very late in the process, which also means it is possible to
postpone the database license payment until late in the process.
Indeed, in some clients we have built the large scale prototype on 2
competing databases so that we could really compare apples with apples.
No amount of 'competitive information' is equal to actually running two
databases side by side, latest release, with the vendors competing for
the business. The mix of HW/OS/RDBMS/DISK and versions of all these
makes it pretty much impossible to compare two solutions on
paper...though many try.
I recommend to my clients that if they are undecided about which
database then they are best advised to remain undecided until they
complete the development of the protoype and do a side by side
comparison to prove to themselves which one they feel is best for them.
It is not lost on my clients that the database vendors are far more
likely to offer incentives when the decision process is clear and
imminent and the weaknesses of their product have been exposed in large
scale prototype... ;-)
Peter
www.peternolan.com
Wednesday, March 21, 2012
Database error help
I am working on a website that is currently being hosted. The website was configured for admin,guest and members. Everything was working fine.
I made no changes to code. When I log in as admin I am able to navigate to most of the pages however I keep getting this error.
From the error it seems to say that some of the required fields are null but they are not.
Some of the required attributes of Person are NULL in the Persons table
Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.Exception Details:System.InvalidOperationException: Some of the required attributes of Person are NULL in the Persons table
Source Error:
Line 141: While r.Read()
Line 142: If TypeOf r("id") Is DBNull Or TypeOf r("visible") Is DBNull Or TypeOf r("firstName") Is DBNull Or TypeOf r("lastName") Is DBNull Then
Line 143: Throw New InvalidOperationException(Messages.PersonRequiredAttributesMissing)
Line 144: End If
Line 145:
Source File:E:\web\cabletrades\htdocs\App_Code\People\SqlPeopleProvider.vb Line:143
Stack Trace:
[InvalidOperationException: Some of the required attributes of Person are NULL in the Persons table]
SqlPeopleProvider.GetPersonsForApproval(String transactionTypeId) in E:\web\cabletrades\htdocs\App_Code\People\SqlPeopleProvider.vb:143
AdminAccountApproval.Page_Load(Object sender, EventArgs e) in E:\web\cabletrades\htdocs\AdminAccountApproval.aspx.vb:42
System.Web.UI.Control.OnLoad(EventArgs e) +99
System.Web.UI.Control.LoadRecursive() +47
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061
Instead of If TypeOf r("id") is dbNull or ....
A person could try this
If r("id") is nothing or .....
hope this helps
DK
Sunday, March 11, 2012
Database Diagrams - SQL Server 2005 on Vista
Hi all
I am very new to SQL Server, I am working through my SQL Server 2005 for
beginners book.
I have SQL server 2005 installed on a Win 2003 Server. I have workstation
components installed on my Vista Box.
I am trying to create a database diagram. The DB is basic with just 4
tables. When I click new database diagram I get the message
The specified module could not be found.
(MS Visual Database Tools).
When I go to Advanced information for the error under program location I get
this message
at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32
errorCode, IntPtr errorInfo)
at
Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VirtualProject.Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn
origUrn, DocumentType editorType, DocumentOptions aeOptions,
IManagedConnection con)
at
Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn
origUrn, DocumentType editorType, DocumentOptions aeOptions,
IManagedConnection con)
at
Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.CreateDesignerWindow(IManagedConnection
mc, DocumentOptions options)
When I connect to the same DB and create a database diagram from a Windows
XP machine all works OK
Any ideas on the problem please.
Thanks in advance
--
David GriffithsWild guess:
Try running Management Studio as a "real" administrator. (I've turned off UAC, and SSMS work just
fine for me. Perhaps you can start an app as a real admin with UAC on somehow...)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"David Griffiths" <dayvg69@.yahoo.nospam.co.uk> wrote in message
news:1MSdnQgixIgX2UXb4p2dnAA@.telenor.com...
> Hi all
> I am very new to SQL Server, I am working through my SQL Server 2005 for beginners book.
> I have SQL server 2005 installed on a Win 2003 Server. I have workstation components installed on
> my Vista Box.
> I am trying to create a database diagram. The DB is basic with just 4 tables. When I click new
> database diagram I get the message
> The specified module could not be found.
> (MS Visual Database Tools).
> When I go to Advanced information for the error under program location I get this message
> at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr
> errorInfo)
> at
> Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VirtualProject.Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn
> origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con)
> at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn
> origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con)
> at
> Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.CreateDesignerWindow(IManagedConnection
> mc, DocumentOptions options)
> When I connect to the same DB and create a database diagram from a Windows XP machine all works OK
> Any ideas on the problem please.
> Thanks in advance
> --
> David Griffiths
>|||Hi
I went the Uninstall SQL 2005 and VS 2005, cleaned up restarted
re-installed.... now it works, that was about 4 hours of work.
Thanks for your reply.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ADFDF17F-E751-4956-9C9B-EBECF7701BD5@.microsoft.com...
> Wild guess:
> Try running Management Studio as a "real" administrator. (I've turned off
> UAC, and SSMS work just fine for me. Perhaps you can start an app as a
> real admin with UAC on somehow...)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "David Griffiths" <dayvg69@.yahoo.nospam.co.uk> wrote in message
> news:1MSdnQgixIgX2UXb4p2dnAA@.telenor.com...
>> Hi all
>> I am very new to SQL Server, I am working through my SQL Server 2005 for
>> beginners book.
>> I have SQL server 2005 installed on a Win 2003 Server. I have workstation
>> components installed on my Vista Box.
>> I am trying to create a database diagram. The DB is basic with just 4
>> tables. When I click new database diagram I get the message
>> The specified module could not be found.
>> (MS Visual Database Tools).
>> When I go to Advanced information for the error under program location I
>> get this message
>> at
>> System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32
>> errorCode, IntPtr errorInfo)
>> at
>> Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VirtualProject.Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn
>> origUrn, DocumentType editorType, DocumentOptions aeOptions,
>> IManagedConnection con)
>> at
>> Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn
>> origUrn, DocumentType editorType, DocumentOptions aeOptions,
>> IManagedConnection con)
>> at
>> Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.CreateDesignerWindow(IManagedConnection
>> mc, DocumentOptions options)
>> When I connect to the same DB and create a database diagram from a
>> Windows XP machine all works OK
>> Any ideas on the problem please.
>> Thanks in advance
>> --
>> David Griffiths
>>
>
I am very new to SQL Server, I am working through my SQL Server 2005 for
beginners book.
I have SQL server 2005 installed on a Win 2003 Server. I have workstation
components installed on my Vista Box.
I am trying to create a database diagram. The DB is basic with just 4
tables. When I click new database diagram I get the message
The specified module could not be found.
(MS Visual Database Tools).
When I go to Advanced information for the error under program location I get
this message
at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32
errorCode, IntPtr errorInfo)
at
Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VirtualProject.Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn
origUrn, DocumentType editorType, DocumentOptions aeOptions,
IManagedConnection con)
at
Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn
origUrn, DocumentType editorType, DocumentOptions aeOptions,
IManagedConnection con)
at
Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.CreateDesignerWindow(IManagedConnection
mc, DocumentOptions options)
When I connect to the same DB and create a database diagram from a Windows
XP machine all works OK
Any ideas on the problem please.
Thanks in advance
--
David GriffithsWild guess:
Try running Management Studio as a "real" administrator. (I've turned off UAC, and SSMS work just
fine for me. Perhaps you can start an app as a real admin with UAC on somehow...)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"David Griffiths" <dayvg69@.yahoo.nospam.co.uk> wrote in message
news:1MSdnQgixIgX2UXb4p2dnAA@.telenor.com...
> Hi all
> I am very new to SQL Server, I am working through my SQL Server 2005 for beginners book.
> I have SQL server 2005 installed on a Win 2003 Server. I have workstation components installed on
> my Vista Box.
> I am trying to create a database diagram. The DB is basic with just 4 tables. When I click new
> database diagram I get the message
> The specified module could not be found.
> (MS Visual Database Tools).
> When I go to Advanced information for the error under program location I get this message
> at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr
> errorInfo)
> at
> Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VirtualProject.Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn
> origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con)
> at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn
> origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con)
> at
> Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.CreateDesignerWindow(IManagedConnection
> mc, DocumentOptions options)
> When I connect to the same DB and create a database diagram from a Windows XP machine all works OK
> Any ideas on the problem please.
> Thanks in advance
> --
> David Griffiths
>|||Hi
I went the Uninstall SQL 2005 and VS 2005, cleaned up restarted
re-installed.... now it works, that was about 4 hours of work.
Thanks for your reply.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ADFDF17F-E751-4956-9C9B-EBECF7701BD5@.microsoft.com...
> Wild guess:
> Try running Management Studio as a "real" administrator. (I've turned off
> UAC, and SSMS work just fine for me. Perhaps you can start an app as a
> real admin with UAC on somehow...)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "David Griffiths" <dayvg69@.yahoo.nospam.co.uk> wrote in message
> news:1MSdnQgixIgX2UXb4p2dnAA@.telenor.com...
>> Hi all
>> I am very new to SQL Server, I am working through my SQL Server 2005 for
>> beginners book.
>> I have SQL server 2005 installed on a Win 2003 Server. I have workstation
>> components installed on my Vista Box.
>> I am trying to create a database diagram. The DB is basic with just 4
>> tables. When I click new database diagram I get the message
>> The specified module could not be found.
>> (MS Visual Database Tools).
>> When I go to Advanced information for the error under program location I
>> get this message
>> at
>> System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32
>> errorCode, IntPtr errorInfo)
>> at
>> Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VirtualProject.Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn
>> origUrn, DocumentType editorType, DocumentOptions aeOptions,
>> IManagedConnection con)
>> at
>> Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn
>> origUrn, DocumentType editorType, DocumentOptions aeOptions,
>> IManagedConnection con)
>> at
>> Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.CreateDesignerWindow(IManagedConnection
>> mc, DocumentOptions options)
>> When I connect to the same DB and create a database diagram from a
>> Windows XP machine all works OK
>> Any ideas on the problem please.
>> Thanks in advance
>> --
>> David Griffiths
>>
>
Thursday, March 8, 2012
database diagram
Hi,
I restored my database from the backup file, which is working fine but it
is not showing the database diagram in SQL server management studio. What do
I need to do for that?
Thanks.
Manj.
Hi Manj,
I understand that after you restored your database, you found that the
database diagram disappeared in SQL Server Management Studio.
If I have misunderstood, please let me know.
Did you restore a SQL Server 2000 or earlier database to SQL Server 2005?
If so, it is by design that the database diagram will not be displayed in
SQL Server 2005 due to structure incompatibility. If your database is SQL
Server 2005, the database diagram should be there in your restored
database.
Anyway for this issue, after you restore your database, you can manually
create a diagram and add all of your tables to it.
Please feel free to let me know if you have any other questions or
concerns. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Hi Charles,
Thanks for the reply. It is SQL Server 2005 database. When I click on
database diagram I get the following message:
TITLE: Microsoft SQL Server Management Studio
Database diagram support objects cannot be installed because this database
does not have a valid owner. To continue, first use the Files page of the
Database Properties dialog box or the ALTER AUTHORIZATION statement to set
the database owner to a valid login, then add the database diagram support
objects.
BUTTONS:
OK
I added the owner from the Files page but still getting the same message.
When I connect to the database it does show the owner in the 'owner box'.
Cheers.
Manj.
"Charles Wang[MSFT]" wrote:
> Hi Manj,
> I understand that after you restored your database, you found that the
> database diagram disappeared in SQL Server Management Studio.
> If I have misunderstood, please let me know.
> Did you restore a SQL Server 2000 or earlier database to SQL Server 2005?
> If so, it is by design that the database diagram will not be displayed in
> SQL Server 2005 due to structure incompatibility. If your database is SQL
> Server 2005, the database diagram should be there in your restored
> database.
> Anyway for this issue, after you restore your database, you can manually
> create a diagram and add all of your tables to it.
> Please feel free to let me know if you have any other questions or
> concerns. Have a nice day!
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== ===
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== ====
>
>
>
|||Hi Manj,
This is a known issue that was fixed in SP1 or SP2. When you upgrade a
database from SQL Server 2000 to 2005, the database remains in 80
compatibility mode. To use the database diagram tool in SQL Server 2005,
the database must be set to 90 mode. See this Books Online topic:
http://msdn2.microsoft.com/en-us/library/ms186345.aspx
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Manjree Garg" <garg@.newsgroup.nospam> wrote in message
news:7CAFD23C-FA12-4766-A26D-10E18863090A@.microsoft.com...[vbcol=seagreen]
> Hi Charles,
> Thanks for the reply. It is SQL Server 2005 database. When I click on
> database diagram I get the following message:
> TITLE: Microsoft SQL Server Management Studio
> --
> Database diagram support objects cannot be installed because this database
> does not have a valid owner. To continue, first use the Files page of the
> Database Properties dialog box or the ALTER AUTHORIZATION statement to set
> the database owner to a valid login, then add the database diagram support
> objects.
> --
> BUTTONS:
> OK
> --
> I added the owner from the Files page but still getting the same message.
> When I connect to the database it does show the owner in the 'owner box'.
> Cheers.
> Manj.
> "Charles Wang[MSFT]" wrote:
|||Hi Gail,
Thanks for the suggestion. Resolved the issue.
Manj.
"Gail Erickson [MS]" wrote:
> Hi Manj,
> This is a known issue that was fixed in SP1 or SP2. When you upgrade a
> database from SQL Server 2000 to 2005, the database remains in 80
> compatibility mode. To use the database diagram tool in SQL Server 2005,
> the database must be set to 90 mode. See this Books Online topic:
> http://msdn2.microsoft.com/en-us/library/ms186345.aspx
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
> "Manjree Garg" <garg@.newsgroup.nospam> wrote in message
> news:7CAFD23C-FA12-4766-A26D-10E18863090A@.microsoft.com...
>
>
I restored my database from the backup file, which is working fine but it
is not showing the database diagram in SQL server management studio. What do
I need to do for that?
Thanks.
Manj.
Hi Manj,
I understand that after you restored your database, you found that the
database diagram disappeared in SQL Server Management Studio.
If I have misunderstood, please let me know.
Did you restore a SQL Server 2000 or earlier database to SQL Server 2005?
If so, it is by design that the database diagram will not be displayed in
SQL Server 2005 due to structure incompatibility. If your database is SQL
Server 2005, the database diagram should be there in your restored
database.
Anyway for this issue, after you restore your database, you can manually
create a diagram and add all of your tables to it.
Please feel free to let me know if you have any other questions or
concerns. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Hi Charles,
Thanks for the reply. It is SQL Server 2005 database. When I click on
database diagram I get the following message:
TITLE: Microsoft SQL Server Management Studio
Database diagram support objects cannot be installed because this database
does not have a valid owner. To continue, first use the Files page of the
Database Properties dialog box or the ALTER AUTHORIZATION statement to set
the database owner to a valid login, then add the database diagram support
objects.
BUTTONS:
OK
I added the owner from the Files page but still getting the same message.
When I connect to the database it does show the owner in the 'owner box'.
Cheers.
Manj.
"Charles Wang[MSFT]" wrote:
> Hi Manj,
> I understand that after you restored your database, you found that the
> database diagram disappeared in SQL Server Management Studio.
> If I have misunderstood, please let me know.
> Did you restore a SQL Server 2000 or earlier database to SQL Server 2005?
> If so, it is by design that the database diagram will not be displayed in
> SQL Server 2005 due to structure incompatibility. If your database is SQL
> Server 2005, the database diagram should be there in your restored
> database.
> Anyway for this issue, after you restore your database, you can manually
> create a diagram and add all of your tables to it.
> Please feel free to let me know if you have any other questions or
> concerns. Have a nice day!
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== ===
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== ====
>
>
>
|||Hi Manj,
This is a known issue that was fixed in SP1 or SP2. When you upgrade a
database from SQL Server 2000 to 2005, the database remains in 80
compatibility mode. To use the database diagram tool in SQL Server 2005,
the database must be set to 90 mode. See this Books Online topic:
http://msdn2.microsoft.com/en-us/library/ms186345.aspx
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Manjree Garg" <garg@.newsgroup.nospam> wrote in message
news:7CAFD23C-FA12-4766-A26D-10E18863090A@.microsoft.com...[vbcol=seagreen]
> Hi Charles,
> Thanks for the reply. It is SQL Server 2005 database. When I click on
> database diagram I get the following message:
> TITLE: Microsoft SQL Server Management Studio
> --
> Database diagram support objects cannot be installed because this database
> does not have a valid owner. To continue, first use the Files page of the
> Database Properties dialog box or the ALTER AUTHORIZATION statement to set
> the database owner to a valid login, then add the database diagram support
> objects.
> --
> BUTTONS:
> OK
> --
> I added the owner from the Files page but still getting the same message.
> When I connect to the database it does show the owner in the 'owner box'.
> Cheers.
> Manj.
> "Charles Wang[MSFT]" wrote:
|||Hi Gail,
Thanks for the suggestion. Resolved the issue.
Manj.
"Gail Erickson [MS]" wrote:
> Hi Manj,
> This is a known issue that was fixed in SP1 or SP2. When you upgrade a
> database from SQL Server 2000 to 2005, the database remains in 80
> compatibility mode. To use the database diagram tool in SQL Server 2005,
> the database must be set to 90 mode. See this Books Online topic:
> http://msdn2.microsoft.com/en-us/library/ms186345.aspx
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
> "Manjree Garg" <garg@.newsgroup.nospam> wrote in message
> news:7CAFD23C-FA12-4766-A26D-10E18863090A@.microsoft.com...
>
>
Wednesday, March 7, 2012
Database Diagram
I am working through a book about setting up a database, though i'm doing it slightly differently, using a sql server database on a remote server rather than the local sql server express database shipped with visual studio.
everything has been going fine until i've reached a part where they want to add a database diagram to the existing database. they talk about right clicking the database diagram node, and selecting add, but for some reason there is no database diagram node, so i can't do it this way. the book also says using the data>add new>diagram from the menu, but when i do this i either have no diagram option (when i have no table opened) or else the diagram option is greyed out and unselectable (when i have a table opened).
any ideas on how i can add a database diagram?
dc
You need to install the Express Advanced locally and import your database to create the diagram because you cannot create a database diagram without System Admin permissions and you are not a System Admin in your hosting company's server. Hope this helps.
http://msdn.microsoft.com/vstudio/express/sql/download/
|||thanksdatabase diagram
Hi,
I restored my database from the backup file, which is working fine but it
is not showing the database diagram in SQL server management studio. What do
I need to do for that?
Thanks.
Manj.Hi Manj,
I understand that after you restored your database, you found that the
database diagram disappeared in SQL Server Management Studio.
If I have misunderstood, please let me know.
Did you restore a SQL Server 2000 or earlier database to SQL Server 2005?
If so, it is by design that the database diagram will not be displayed in
SQL Server 2005 due to structure incompatibility. If your database is SQL
Server 2005, the database diagram should be there in your restored
database.
Anyway for this issue, after you restore your database, you can manually
create a diagram and add all of your tables to it.
Please feel free to let me know if you have any other questions or
concerns. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi Charles,
Thanks for the reply. It is SQL Server 2005 database. When I click on
database diagram I get the following message:
TITLE: Microsoft SQL Server Management Studio
--
Database diagram support objects cannot be installed because this database
does not have a valid owner. To continue, first use the Files page of the
Database Properties dialog box or the ALTER AUTHORIZATION statement to set
the database owner to a valid login, then add the database diagram support
objects.
--
BUTTONS:
OK
--
I added the owner from the Files page but still getting the same message.
When I connect to the database it does show the owner in the 'owner box'.
Cheers.
Manj.
"Charles Wang[MSFT]" wrote:
> Hi Manj,
> I understand that after you restored your database, you found that the
> database diagram disappeared in SQL Server Management Studio.
> If I have misunderstood, please let me know.
> Did you restore a SQL Server 2000 or earlier database to SQL Server 2005?
> If so, it is by design that the database diagram will not be displayed in
> SQL Server 2005 due to structure incompatibility. If your database is SQL
> Server 2005, the database diagram should be there in your restored
> database.
> Anyway for this issue, after you restore your database, you can manually
> create a diagram and add all of your tables to it.
> Please feel free to let me know if you have any other questions or
> concerns. Have a nice day!
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =====================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================>
>
>|||Hi Manj,
This is a known issue that was fixed in SP1 or SP2. When you upgrade a
database from SQL Server 2000 to 2005, the database remains in 80
compatibility mode. To use the database diagram tool in SQL Server 2005,
the database must be set to 90 mode. See this Books Online topic:
http://msdn2.microsoft.com/en-us/library/ms186345.aspx
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Manjree Garg" <garg@.newsgroup.nospam> wrote in message
news:7CAFD23C-FA12-4766-A26D-10E18863090A@.microsoft.com...
> Hi Charles,
> Thanks for the reply. It is SQL Server 2005 database. When I click on
> database diagram I get the following message:
> TITLE: Microsoft SQL Server Management Studio
> --
> Database diagram support objects cannot be installed because this database
> does not have a valid owner. To continue, first use the Files page of the
> Database Properties dialog box or the ALTER AUTHORIZATION statement to set
> the database owner to a valid login, then add the database diagram support
> objects.
> --
> BUTTONS:
> OK
> --
> I added the owner from the Files page but still getting the same message.
> When I connect to the database it does show the owner in the 'owner box'.
> Cheers.
> Manj.
> "Charles Wang[MSFT]" wrote:
>> Hi Manj,
>> I understand that after you restored your database, you found that the
>> database diagram disappeared in SQL Server Management Studio.
>> If I have misunderstood, please let me know.
>> Did you restore a SQL Server 2000 or earlier database to SQL Server 2005?
>> If so, it is by design that the database diagram will not be displayed in
>> SQL Server 2005 due to structure incompatibility. If your database is SQL
>> Server 2005, the database diagram should be there in your restored
>> database.
>> Anyway for this issue, after you restore your database, you can manually
>> create a diagram and add all of your tables to it.
>> Please feel free to let me know if you have any other questions or
>> concerns. Have a nice day!
>> Best regards,
>> Charles Wang
>> Microsoft Online Community Support
>> =====================================================>> When responding to posts, please "Reply to Group" via
>> your newsreader so that others may learn and benefit
>> from this issue.
>> ======================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> ======================================================>>
>>
>>|||Hi Gail,
Thanks for the suggestion. Resolved the issue.
Manj.
"Gail Erickson [MS]" wrote:
> Hi Manj,
> This is a known issue that was fixed in SP1 or SP2. When you upgrade a
> database from SQL Server 2000 to 2005, the database remains in 80
> compatibility mode. To use the database diagram tool in SQL Server 2005,
> the database must be set to 90 mode. See this Books Online topic:
> http://msdn2.microsoft.com/en-us/library/ms186345.aspx
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
> "Manjree Garg" <garg@.newsgroup.nospam> wrote in message
> news:7CAFD23C-FA12-4766-A26D-10E18863090A@.microsoft.com...
> > Hi Charles,
> >
> > Thanks for the reply. It is SQL Server 2005 database. When I click on
> > database diagram I get the following message:
> >
> > TITLE: Microsoft SQL Server Management Studio
> > --
> >
> > Database diagram support objects cannot be installed because this database
> > does not have a valid owner. To continue, first use the Files page of the
> > Database Properties dialog box or the ALTER AUTHORIZATION statement to set
> > the database owner to a valid login, then add the database diagram support
> > objects.
> >
> > --
> > BUTTONS:
> >
> > OK
> > --
> >
> > I added the owner from the Files page but still getting the same message.
> > When I connect to the database it does show the owner in the 'owner box'.
> >
> > Cheers.
> >
> > Manj.
> > "Charles Wang[MSFT]" wrote:
> >
> >> Hi Manj,
> >> I understand that after you restored your database, you found that the
> >> database diagram disappeared in SQL Server Management Studio.
> >> If I have misunderstood, please let me know.
> >>
> >> Did you restore a SQL Server 2000 or earlier database to SQL Server 2005?
> >> If so, it is by design that the database diagram will not be displayed in
> >> SQL Server 2005 due to structure incompatibility. If your database is SQL
> >> Server 2005, the database diagram should be there in your restored
> >> database.
> >>
> >> Anyway for this issue, after you restore your database, you can manually
> >> create a diagram and add all of your tables to it.
> >>
> >> Please feel free to let me know if you have any other questions or
> >> concerns. Have a nice day!
> >>
> >> Best regards,
> >> Charles Wang
> >> Microsoft Online Community Support
> >> =====================================================> >> When responding to posts, please "Reply to Group" via
> >> your newsreader so that others may learn and benefit
> >> from this issue.
> >> ======================================================> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >> ======================================================> >>
> >>
> >>
> >>
> >>
> >>
>
>|||Hi all,
I have the same problem (SS2005):
TITLE: Microsoft SQL Server Management Studio
--
Database diagram support objects cannot be installed because this database
does not have a valid owner. To continue, first use the Files page of the
Database Properties dialog box or the ALTER AUTHORIZATION statement to set
the database owner to a valid login, then add the database diagram support
objects.
With:
SELECT USER
it returns "dbo"
Where the problem?
Thanks a lot.
Luigi|||The problem seems to be that the SID for your dbo user inside the database doesn't exist as a login
in the master database. Use either ALTER AUTHORIZATION (as suggested by the error message) or
sp_changedbowner to make sure that the database has an owner that really exist in the master
database (as a login).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Luigi" <ciupazNoSpamGrazie@.inwind.it> wrote in message
news:D544D243-9D98-4432-B91B-9D68BE0C189E@.microsoft.com...
> Hi all,
> I have the same problem (SS2005):
> TITLE: Microsoft SQL Server Management Studio
> --
> Database diagram support objects cannot be installed because this database
> does not have a valid owner. To continue, first use the Files page of the
> Database Properties dialog box or the ALTER AUTHORIZATION statement to set
> the database owner to a valid login, then add the database diagram support
> objects.
> With:
> SELECT USER
> it returns "dbo"
> Where the problem?
> Thanks a lot.
> Luigi
>
>|||"Tibor Karaszi" wrote:
> The problem seems to be that the SID for your dbo user inside the database doesn't exist as a login
> in the master database. Use either ALTER AUTHORIZATION (as suggested by the error message) or
> sp_changedbowner to make sure that the database has an owner that really exist in the master
> database (as a login).
Thank you Tibor, I'll make in this way.
Luigi
I restored my database from the backup file, which is working fine but it
is not showing the database diagram in SQL server management studio. What do
I need to do for that?
Thanks.
Manj.Hi Manj,
I understand that after you restored your database, you found that the
database diagram disappeared in SQL Server Management Studio.
If I have misunderstood, please let me know.
Did you restore a SQL Server 2000 or earlier database to SQL Server 2005?
If so, it is by design that the database diagram will not be displayed in
SQL Server 2005 due to structure incompatibility. If your database is SQL
Server 2005, the database diagram should be there in your restored
database.
Anyway for this issue, after you restore your database, you can manually
create a diagram and add all of your tables to it.
Please feel free to let me know if you have any other questions or
concerns. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi Charles,
Thanks for the reply. It is SQL Server 2005 database. When I click on
database diagram I get the following message:
TITLE: Microsoft SQL Server Management Studio
--
Database diagram support objects cannot be installed because this database
does not have a valid owner. To continue, first use the Files page of the
Database Properties dialog box or the ALTER AUTHORIZATION statement to set
the database owner to a valid login, then add the database diagram support
objects.
--
BUTTONS:
OK
--
I added the owner from the Files page but still getting the same message.
When I connect to the database it does show the owner in the 'owner box'.
Cheers.
Manj.
"Charles Wang[MSFT]" wrote:
> Hi Manj,
> I understand that after you restored your database, you found that the
> database diagram disappeared in SQL Server Management Studio.
> If I have misunderstood, please let me know.
> Did you restore a SQL Server 2000 or earlier database to SQL Server 2005?
> If so, it is by design that the database diagram will not be displayed in
> SQL Server 2005 due to structure incompatibility. If your database is SQL
> Server 2005, the database diagram should be there in your restored
> database.
> Anyway for this issue, after you restore your database, you can manually
> create a diagram and add all of your tables to it.
> Please feel free to let me know if you have any other questions or
> concerns. Have a nice day!
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =====================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================>
>
>|||Hi Manj,
This is a known issue that was fixed in SP1 or SP2. When you upgrade a
database from SQL Server 2000 to 2005, the database remains in 80
compatibility mode. To use the database diagram tool in SQL Server 2005,
the database must be set to 90 mode. See this Books Online topic:
http://msdn2.microsoft.com/en-us/library/ms186345.aspx
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Manjree Garg" <garg@.newsgroup.nospam> wrote in message
news:7CAFD23C-FA12-4766-A26D-10E18863090A@.microsoft.com...
> Hi Charles,
> Thanks for the reply. It is SQL Server 2005 database. When I click on
> database diagram I get the following message:
> TITLE: Microsoft SQL Server Management Studio
> --
> Database diagram support objects cannot be installed because this database
> does not have a valid owner. To continue, first use the Files page of the
> Database Properties dialog box or the ALTER AUTHORIZATION statement to set
> the database owner to a valid login, then add the database diagram support
> objects.
> --
> BUTTONS:
> OK
> --
> I added the owner from the Files page but still getting the same message.
> When I connect to the database it does show the owner in the 'owner box'.
> Cheers.
> Manj.
> "Charles Wang[MSFT]" wrote:
>> Hi Manj,
>> I understand that after you restored your database, you found that the
>> database diagram disappeared in SQL Server Management Studio.
>> If I have misunderstood, please let me know.
>> Did you restore a SQL Server 2000 or earlier database to SQL Server 2005?
>> If so, it is by design that the database diagram will not be displayed in
>> SQL Server 2005 due to structure incompatibility. If your database is SQL
>> Server 2005, the database diagram should be there in your restored
>> database.
>> Anyway for this issue, after you restore your database, you can manually
>> create a diagram and add all of your tables to it.
>> Please feel free to let me know if you have any other questions or
>> concerns. Have a nice day!
>> Best regards,
>> Charles Wang
>> Microsoft Online Community Support
>> =====================================================>> When responding to posts, please "Reply to Group" via
>> your newsreader so that others may learn and benefit
>> from this issue.
>> ======================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> ======================================================>>
>>
>>|||Hi Gail,
Thanks for the suggestion. Resolved the issue.
Manj.
"Gail Erickson [MS]" wrote:
> Hi Manj,
> This is a known issue that was fixed in SP1 or SP2. When you upgrade a
> database from SQL Server 2000 to 2005, the database remains in 80
> compatibility mode. To use the database diagram tool in SQL Server 2005,
> the database must be set to 90 mode. See this Books Online topic:
> http://msdn2.microsoft.com/en-us/library/ms186345.aspx
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
> "Manjree Garg" <garg@.newsgroup.nospam> wrote in message
> news:7CAFD23C-FA12-4766-A26D-10E18863090A@.microsoft.com...
> > Hi Charles,
> >
> > Thanks for the reply. It is SQL Server 2005 database. When I click on
> > database diagram I get the following message:
> >
> > TITLE: Microsoft SQL Server Management Studio
> > --
> >
> > Database diagram support objects cannot be installed because this database
> > does not have a valid owner. To continue, first use the Files page of the
> > Database Properties dialog box or the ALTER AUTHORIZATION statement to set
> > the database owner to a valid login, then add the database diagram support
> > objects.
> >
> > --
> > BUTTONS:
> >
> > OK
> > --
> >
> > I added the owner from the Files page but still getting the same message.
> > When I connect to the database it does show the owner in the 'owner box'.
> >
> > Cheers.
> >
> > Manj.
> > "Charles Wang[MSFT]" wrote:
> >
> >> Hi Manj,
> >> I understand that after you restored your database, you found that the
> >> database diagram disappeared in SQL Server Management Studio.
> >> If I have misunderstood, please let me know.
> >>
> >> Did you restore a SQL Server 2000 or earlier database to SQL Server 2005?
> >> If so, it is by design that the database diagram will not be displayed in
> >> SQL Server 2005 due to structure incompatibility. If your database is SQL
> >> Server 2005, the database diagram should be there in your restored
> >> database.
> >>
> >> Anyway for this issue, after you restore your database, you can manually
> >> create a diagram and add all of your tables to it.
> >>
> >> Please feel free to let me know if you have any other questions or
> >> concerns. Have a nice day!
> >>
> >> Best regards,
> >> Charles Wang
> >> Microsoft Online Community Support
> >> =====================================================> >> When responding to posts, please "Reply to Group" via
> >> your newsreader so that others may learn and benefit
> >> from this issue.
> >> ======================================================> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >> ======================================================> >>
> >>
> >>
> >>
> >>
> >>
>
>|||Hi all,
I have the same problem (SS2005):
TITLE: Microsoft SQL Server Management Studio
--
Database diagram support objects cannot be installed because this database
does not have a valid owner. To continue, first use the Files page of the
Database Properties dialog box or the ALTER AUTHORIZATION statement to set
the database owner to a valid login, then add the database diagram support
objects.
With:
SELECT USER
it returns "dbo"
Where the problem?
Thanks a lot.
Luigi|||The problem seems to be that the SID for your dbo user inside the database doesn't exist as a login
in the master database. Use either ALTER AUTHORIZATION (as suggested by the error message) or
sp_changedbowner to make sure that the database has an owner that really exist in the master
database (as a login).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Luigi" <ciupazNoSpamGrazie@.inwind.it> wrote in message
news:D544D243-9D98-4432-B91B-9D68BE0C189E@.microsoft.com...
> Hi all,
> I have the same problem (SS2005):
> TITLE: Microsoft SQL Server Management Studio
> --
> Database diagram support objects cannot be installed because this database
> does not have a valid owner. To continue, first use the Files page of the
> Database Properties dialog box or the ALTER AUTHORIZATION statement to set
> the database owner to a valid login, then add the database diagram support
> objects.
> With:
> SELECT USER
> it returns "dbo"
> Where the problem?
> Thanks a lot.
> Luigi
>
>|||"Tibor Karaszi" wrote:
> The problem seems to be that the SID for your dbo user inside the database doesn't exist as a login
> in the master database. Use either ALTER AUTHORIZATION (as suggested by the error message) or
> sp_changedbowner to make sure that the database has an owner that really exist in the master
> database (as a login).
Thank you Tibor, I'll make in this way.
Luigi
Database Developer Job
Here is a job we are currently looking for. If you have or are working on your MCDBA please apply.
https://www2.mdanderson.org/sapp/webhire/index.cfm?pagename=details&JobID=04-0015432
Treyo-yea...if you have any question please ask
Trey
https://www2.mdanderson.org/sapp/webhire/index.cfm?pagename=details&JobID=04-0015432
Treyo-yea...if you have any question please ask
Trey
Database Designing...
Friends,
Who is responsible for the Design of Database? System Analyst, DBA, Databse Designer, Project Leader? Coz I am working as a System Analyst, but now desgining the Databse for the ERP package which I feel is another man's work. Confussed. Plz help me.
AnilI'll wait for the follow-up poll: Who gets blamed for a poorly designed database?
A: DBA
B: DBA
C: DBA
D: All of the above.|||you're kidding, right?
you have four people, and one of them is a Database Designer, and you're asking whose job it is to design the database?
answer: not the DBA
the DBA's job is to create the physical database from the finished database design
answer: not the project leader
the project leader's job is to talk to management
answer: not the systems analyst
the system analyst's job is to figure out what the users really want, not what they're asking for
let's see, have i missed anybody?|||Yeah r937 you missed someone. You missed the poor suckers that have to perform all of those roles. I'm stuck in that position right now. I know the most about the business processes, db design, dba work, java coding, object modeling, reporting progress to management, and managing interaction with the end users. It sux but it acts as great leverage when negotiating salary.|||I don't see any reason or advantage to splitting the DBA and Database Designer roles. It sounds like a recipe for turf wars to me.
Anybody?|||Originally posted by blindman
I'll wait for the follow-up poll: Who gets blamed for a poorly designed database?
A: DBA
B: DBA
C: DBA
D: All of the above.
You took the words right out of my keyboard :)|||you can't see any advantage to splitting the DBA and Database Designer roles?
whoa, you must be trollin
where to start?
how about: if the Database Designer designs the database, sufficient time and effort will be devoted to the understanding and identification of candidate keys based on business logic, whereas if the DBA does it, you get surrogate autonumber primary keys on every table and $deity knows what other similar crap
how's that?
;)|||Do you mean to say a DBA has no knowledge of Database design ...
Buzz ... You are wrong ... what we are talking about is an Integration of DBA and DD roles ... and that would be absolutely great if the person posseses enough DD and DBA knowledge .|||Well, in large shops it doesn't seem to fly. I'D LOVE TO:
- do the analysis
- do the design
- and do what I do on a daily basis (DBA stuff)
Turf wars is what we have here.
But the sad part is, that after the design is handed over to me to implement (mind you I am not invited to any of the meatings held by the project development team), I find all kinds of design issues in about 70% of the projects. At that point it's too late to take it back to the designer, or rather to the systems analyst, because success is declared and propagated all the way to the top, and I still love my job ;) So what am I left with? MAKE IT WORK!!! Recently the "designers" started listening to me a little more (I AM SHOCKED!) and started implementing any data access through a stored procedure. So, having a break like this, I have a luxury to re-write the procedure if it needs to be, and even alter table structures and relations, because the data is not directly accessed. It's a workaround that I found, but it keeps me occupied (not right now, it's boring here, everything works...)|||no, i do not mean to say the DBA has no knowledge of database design
that'd be silly, and i wouldn't say that
and who said we were talking about an integration of roles?
i thought we were talking about a0060162742357's situation where there is already two people, one of them a database designer and the other a DBA, and the question is, whose job is it to design the database?
duh...|||Database Designer
DBA
Project Leader
Programmer
hmmm .. these guys are there and still the system analyst is designing the db ... wierd|||Unfortunately it really doesnt matter who does it because the pooch usually gets screwed right from the beginning and then you have to spend the rest of your days working around a flawed design.
those magnificent bastards|||Originally posted by Ruprect
those magnificent bastards Do they come any other way ?!?!
-PatP|||How about all the "little" projects where the project manager says something like: "We only have two tables to hold our data, why don't we just dump them in the products database?"
Hmm. Would the fact that it is not product data have anything to do with that decision (or lack thereof)?
Sadly, we have no database design group here. All the developers are free to come up with their own designs, which I find out about later. And let me tell you, we have a creative bunch around here...
How are all of your shops made up? We only have project managers, programmers, and DBA.|||All very interesting comments.
So, in a split DBA/Designer environment, should the DBA be responsble only for pure "admin" functions, such as backups, index optimization, replication, etc?
I think it is absolutely essential that a database designer have good knowledge of database administration, though I don't think the reverse is necessarilly required.
I've only worked in small-to-midsize shops where I was the one-man show, so I'm curious about how duties are split in larger environments and how people keep from stepping on eachother's toes.|||The word is "micromanagement"...Or is it 2 words?|||or the client who says i want to bring these 900 various access 97 2000 and excel files into a sql server database
bottom line the only one who cares that the model is done right, is you.
it's like paul newman said
"only cream and bastards rise"|||Originally posted by blindman
All very interesting comments.
So, in a split DBA/Designer environment, should the DBA be responsble only for pure "admin" functions, such as backups, index optimization, replication, etc?
I think it is absolutely essential that a database designer have good knowledge of database administration, though I don't think the reverse is necessarilly required.
I've only worked in small-to-midsize shops where I was the one-man show, so I'm curious about how duties are split in larger environments and how people keep from stepping on eachother's toes.
I kind of disagree about the database administration piece. It would be impossible for me to be a good DBA if I didn't know about database design. I can only do so much with backups, recoveries, replication, etc.
How would I optimize the indexes and do performance tuning well though if I didn't understand database design. Half of performance tuning is knowing how proper design works and seeing where you can improve performance by applying indexes properly in the design, restructuring entities to be more efficient, and identifying weak code through analysis of the underlying structure.|||I don't disagree with you. I just feel that tweaking indexes falls within the realm of the DBA (though a designer has to plan for indexes as well). Indexes can be changed without modifying the structure or functionality of the schema, so I don't think of them as being the exclusive realm of the designer.|||Friends,
Thanx for ur suggestions n explanations. So Can I conclude that:
A system analyst : feeds the DD with user reqs and other high level flow diagrams.
A DD : models and designs the DB
A DBA (with the help of DD) : creates the Physical Database and Admins it, gives it PL
A PL : uses the info from SA and DBA+DD to develope the System(software)
A programmer : An OX
Am I Right?
Regards,
Anil,
the inexperienced|||This is a typical distribution of tasks, but I would argue that it has several flaws.
I think the Database Designer should create the physical database, though in a development environment. When it is done (or during development) the DBA should review it, and the DBA should be responsible for rolling it out to the production environment.
Also, the Database Designer should be involved with the application design from the start, including requirements gathering and functionality. I think a lot of problems occur when a system analyst and other parties gather requirements and design interfaces and then just hand the package off the the designer with the instructions to "build this". An allegory would be many of Frank Lloyd Wright's architectural designs, which may have been innovative and attractive, but were structurally unsound. The Database Designer should be a critical part of the development team.|||any competent database designer who finds himself or herself not included in the overall systems design and user requirements analysis will not stay in that job for long
Who is responsible for the Design of Database? System Analyst, DBA, Databse Designer, Project Leader? Coz I am working as a System Analyst, but now desgining the Databse for the ERP package which I feel is another man's work. Confussed. Plz help me.
AnilI'll wait for the follow-up poll: Who gets blamed for a poorly designed database?
A: DBA
B: DBA
C: DBA
D: All of the above.|||you're kidding, right?
you have four people, and one of them is a Database Designer, and you're asking whose job it is to design the database?
answer: not the DBA
the DBA's job is to create the physical database from the finished database design
answer: not the project leader
the project leader's job is to talk to management
answer: not the systems analyst
the system analyst's job is to figure out what the users really want, not what they're asking for
let's see, have i missed anybody?|||Yeah r937 you missed someone. You missed the poor suckers that have to perform all of those roles. I'm stuck in that position right now. I know the most about the business processes, db design, dba work, java coding, object modeling, reporting progress to management, and managing interaction with the end users. It sux but it acts as great leverage when negotiating salary.|||I don't see any reason or advantage to splitting the DBA and Database Designer roles. It sounds like a recipe for turf wars to me.
Anybody?|||Originally posted by blindman
I'll wait for the follow-up poll: Who gets blamed for a poorly designed database?
A: DBA
B: DBA
C: DBA
D: All of the above.
You took the words right out of my keyboard :)|||you can't see any advantage to splitting the DBA and Database Designer roles?
whoa, you must be trollin
where to start?
how about: if the Database Designer designs the database, sufficient time and effort will be devoted to the understanding and identification of candidate keys based on business logic, whereas if the DBA does it, you get surrogate autonumber primary keys on every table and $deity knows what other similar crap
how's that?
;)|||Do you mean to say a DBA has no knowledge of Database design ...
Buzz ... You are wrong ... what we are talking about is an Integration of DBA and DD roles ... and that would be absolutely great if the person posseses enough DD and DBA knowledge .|||Well, in large shops it doesn't seem to fly. I'D LOVE TO:
- do the analysis
- do the design
- and do what I do on a daily basis (DBA stuff)
Turf wars is what we have here.
But the sad part is, that after the design is handed over to me to implement (mind you I am not invited to any of the meatings held by the project development team), I find all kinds of design issues in about 70% of the projects. At that point it's too late to take it back to the designer, or rather to the systems analyst, because success is declared and propagated all the way to the top, and I still love my job ;) So what am I left with? MAKE IT WORK!!! Recently the "designers" started listening to me a little more (I AM SHOCKED!) and started implementing any data access through a stored procedure. So, having a break like this, I have a luxury to re-write the procedure if it needs to be, and even alter table structures and relations, because the data is not directly accessed. It's a workaround that I found, but it keeps me occupied (not right now, it's boring here, everything works...)|||no, i do not mean to say the DBA has no knowledge of database design
that'd be silly, and i wouldn't say that
and who said we were talking about an integration of roles?
i thought we were talking about a0060162742357's situation where there is already two people, one of them a database designer and the other a DBA, and the question is, whose job is it to design the database?
duh...|||Database Designer
DBA
Project Leader
Programmer
hmmm .. these guys are there and still the system analyst is designing the db ... wierd|||Unfortunately it really doesnt matter who does it because the pooch usually gets screwed right from the beginning and then you have to spend the rest of your days working around a flawed design.
those magnificent bastards|||Originally posted by Ruprect
those magnificent bastards Do they come any other way ?!?!
-PatP|||How about all the "little" projects where the project manager says something like: "We only have two tables to hold our data, why don't we just dump them in the products database?"
Hmm. Would the fact that it is not product data have anything to do with that decision (or lack thereof)?
Sadly, we have no database design group here. All the developers are free to come up with their own designs, which I find out about later. And let me tell you, we have a creative bunch around here...
How are all of your shops made up? We only have project managers, programmers, and DBA.|||All very interesting comments.
So, in a split DBA/Designer environment, should the DBA be responsble only for pure "admin" functions, such as backups, index optimization, replication, etc?
I think it is absolutely essential that a database designer have good knowledge of database administration, though I don't think the reverse is necessarilly required.
I've only worked in small-to-midsize shops where I was the one-man show, so I'm curious about how duties are split in larger environments and how people keep from stepping on eachother's toes.|||The word is "micromanagement"...Or is it 2 words?|||or the client who says i want to bring these 900 various access 97 2000 and excel files into a sql server database
bottom line the only one who cares that the model is done right, is you.
it's like paul newman said
"only cream and bastards rise"|||Originally posted by blindman
All very interesting comments.
So, in a split DBA/Designer environment, should the DBA be responsble only for pure "admin" functions, such as backups, index optimization, replication, etc?
I think it is absolutely essential that a database designer have good knowledge of database administration, though I don't think the reverse is necessarilly required.
I've only worked in small-to-midsize shops where I was the one-man show, so I'm curious about how duties are split in larger environments and how people keep from stepping on eachother's toes.
I kind of disagree about the database administration piece. It would be impossible for me to be a good DBA if I didn't know about database design. I can only do so much with backups, recoveries, replication, etc.
How would I optimize the indexes and do performance tuning well though if I didn't understand database design. Half of performance tuning is knowing how proper design works and seeing where you can improve performance by applying indexes properly in the design, restructuring entities to be more efficient, and identifying weak code through analysis of the underlying structure.|||I don't disagree with you. I just feel that tweaking indexes falls within the realm of the DBA (though a designer has to plan for indexes as well). Indexes can be changed without modifying the structure or functionality of the schema, so I don't think of them as being the exclusive realm of the designer.|||Friends,
Thanx for ur suggestions n explanations. So Can I conclude that:
A system analyst : feeds the DD with user reqs and other high level flow diagrams.
A DD : models and designs the DB
A DBA (with the help of DD) : creates the Physical Database and Admins it, gives it PL
A PL : uses the info from SA and DBA+DD to develope the System(software)
A programmer : An OX
Am I Right?
Regards,
Anil,
the inexperienced|||This is a typical distribution of tasks, but I would argue that it has several flaws.
I think the Database Designer should create the physical database, though in a development environment. When it is done (or during development) the DBA should review it, and the DBA should be responsible for rolling it out to the production environment.
Also, the Database Designer should be involved with the application design from the start, including requirements gathering and functionality. I think a lot of problems occur when a system analyst and other parties gather requirements and design interfaces and then just hand the package off the the designer with the instructions to "build this". An allegory would be many of Frank Lloyd Wright's architectural designs, which may have been innovative and attractive, but were structurally unsound. The Database Designer should be a critical part of the development team.|||any competent database designer who finds himself or herself not included in the overall systems design and user requirements analysis will not stay in that job for long
Database Design Question..
Hi Everyone,
I have been working closely with major telecom providers in the pastand I am about to redesign their major billing system... Anyonehave any links which can give me head start in designing a database andsystem for a telecom provider. ??
Cheers!
http://database.ittoolbox.com/nav/t.asp?t=349&p=349&h1=349
I have been working closely with major telecom providers in the pastand I am about to redesign their major billing system... Anyonehave any links which can give me head start in designing a database andsystem for a telecom provider. ??
Cheers!
http://database.ittoolbox.com/nav/t.asp?t=349&p=349&h1=349
Saturday, February 25, 2012
Database design question
Hi,
I am working on a Budgeting web based application using ASP.NET 2 and SQL
Server 2000. The budgeting data will build up over a course of period and
these historical data will be used for decision making in future budget.
My questions:
1. what design approach should I use to store the historical data? Data
Mining or Datawarehouse? or other options?
2. Is there any built in tool in sql 2000 to keep track of the audit trail?
If yes, where they are saved?
Thanks for you help!
Hi
The choice of datawarehouse or datamining really need deciding by analysing
and producing the requirements for the future needs so can't really be
answered with the level of information you have given.
Although SQL Server 2000 has no automatic method of auditing, it is possible
to implement something using triggers and there is a simple example in the
CREATE TRIGGER topic in Books Online. I would recommend that you do the
mimimum amount of work require in the trigger and do any
aggregation/formatting... as a ofline process. This will reduce the impact of
the trigger on any oltp activity. You can also get third party applications
that implement auditing for you such as Lumigent's auditdb
http://www.lumigent.com/products/auditdb.html
HTH
John
"Mindy" wrote:
> Hi,
> I am working on a Budgeting web based application using ASP.NET 2 and SQL
> Server 2000. The budgeting data will build up over a course of period and
> these historical data will be used for decision making in future budget.
> My questions:
> 1. what design approach should I use to store the historical data? Data
> Mining or Datawarehouse? or other options?
> 2. Is there any built in tool in sql 2000 to keep track of the audit trail?
> If yes, where they are saved?
> Thanks for you help!
>
|||Thanks for the quick response. Can you send me the link to the online book on
Create Triggers topic?
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> The choice of datawarehouse or datamining really need deciding by analysing
> and producing the requirements for the future needs so can't really be
> answered with the level of information you have given.
> Although SQL Server 2000 has no automatic method of auditing, it is possible
> to implement something using triggers and there is a simple example in the
> CREATE TRIGGER topic in Books Online. I would recommend that you do the
> mimimum amount of work require in the trigger and do any
> aggregation/formatting... as a ofline process. This will reduce the impact of
> the trigger on any oltp activity. You can also get third party applications
> that implement auditing for you such as Lumigent's auditdb
> http://www.lumigent.com/products/auditdb.html
> HTH
> John
>
> "Mindy" wrote:
|||You can download Books Online from here:
SQL Server Books Online
2005 -
http://www.microsoft.com/technet/pro...ads/books.mspx
2000 -
http://www.microsoft.com/downloads/d...displaylang=en
Then search for CREATE TRIGGER...
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Mindy" <Mindy@.discussions.microsoft.com> wrote in message
news:49F456EE-FBC0-46AD-8257-089E6887AAFD@.microsoft.com...[vbcol=seagreen]
> Thanks for the quick response. Can you send me the link to the online book
> on
> Create Triggers topic?
>
> "John Bell" wrote:
|||Hi
If you don't want to download books online check out
http://msdn.microsoft.com/library/de...asp?frame=true
John
"Mindy" wrote:
[vbcol=seagreen]
> Thanks for the quick response. Can you send me the link to the online book on
> Create Triggers topic?
>
> "John Bell" wrote:
I am working on a Budgeting web based application using ASP.NET 2 and SQL
Server 2000. The budgeting data will build up over a course of period and
these historical data will be used for decision making in future budget.
My questions:
1. what design approach should I use to store the historical data? Data
Mining or Datawarehouse? or other options?
2. Is there any built in tool in sql 2000 to keep track of the audit trail?
If yes, where they are saved?
Thanks for you help!
Hi
The choice of datawarehouse or datamining really need deciding by analysing
and producing the requirements for the future needs so can't really be
answered with the level of information you have given.
Although SQL Server 2000 has no automatic method of auditing, it is possible
to implement something using triggers and there is a simple example in the
CREATE TRIGGER topic in Books Online. I would recommend that you do the
mimimum amount of work require in the trigger and do any
aggregation/formatting... as a ofline process. This will reduce the impact of
the trigger on any oltp activity. You can also get third party applications
that implement auditing for you such as Lumigent's auditdb
http://www.lumigent.com/products/auditdb.html
HTH
John
"Mindy" wrote:
> Hi,
> I am working on a Budgeting web based application using ASP.NET 2 and SQL
> Server 2000. The budgeting data will build up over a course of period and
> these historical data will be used for decision making in future budget.
> My questions:
> 1. what design approach should I use to store the historical data? Data
> Mining or Datawarehouse? or other options?
> 2. Is there any built in tool in sql 2000 to keep track of the audit trail?
> If yes, where they are saved?
> Thanks for you help!
>
|||Thanks for the quick response. Can you send me the link to the online book on
Create Triggers topic?
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> The choice of datawarehouse or datamining really need deciding by analysing
> and producing the requirements for the future needs so can't really be
> answered with the level of information you have given.
> Although SQL Server 2000 has no automatic method of auditing, it is possible
> to implement something using triggers and there is a simple example in the
> CREATE TRIGGER topic in Books Online. I would recommend that you do the
> mimimum amount of work require in the trigger and do any
> aggregation/formatting... as a ofline process. This will reduce the impact of
> the trigger on any oltp activity. You can also get third party applications
> that implement auditing for you such as Lumigent's auditdb
> http://www.lumigent.com/products/auditdb.html
> HTH
> John
>
> "Mindy" wrote:
|||You can download Books Online from here:
SQL Server Books Online
2005 -
http://www.microsoft.com/technet/pro...ads/books.mspx
2000 -
http://www.microsoft.com/downloads/d...displaylang=en
Then search for CREATE TRIGGER...
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Mindy" <Mindy@.discussions.microsoft.com> wrote in message
news:49F456EE-FBC0-46AD-8257-089E6887AAFD@.microsoft.com...[vbcol=seagreen]
> Thanks for the quick response. Can you send me the link to the online book
> on
> Create Triggers topic?
>
> "John Bell" wrote:
|||Hi
If you don't want to download books online check out
http://msdn.microsoft.com/library/de...asp?frame=true
John
"Mindy" wrote:
[vbcol=seagreen]
> Thanks for the quick response. Can you send me the link to the online book on
> Create Triggers topic?
>
> "John Bell" wrote:
Database design question
Hi,
I am working on a Budgeting web based application using ASP.NET 2 and SQL
Server 2000. The budgeting data will build up over a course of period and
these historical data will be used for decision making in future budget.
My questions:
1. what design approach should I use to store the historical data? Data
Mining or Datawarehouse? or other options?
2. Is there any built in tool in sql 2000 to keep track of the audit trail?
If yes, where they are saved?
Thanks for you help!Hi
The choice of datawarehouse or datamining really need deciding by analysing
and producing the requirements for the future needs so can't really be
answered with the level of information you have given.
Although SQL Server 2000 has no automatic method of auditing, it is possible
to implement something using triggers and there is a simple example in the
CREATE TRIGGER topic in Books Online. I would recommend that you do the
mimimum amount of work require in the trigger and do any
aggregation/formatting... as a ofline process. This will reduce the impact o
f
the trigger on any oltp activity. You can also get third party applications
that implement auditing for you such as Lumigent's auditdb
http://www.lumigent.com/products/auditdb.html
HTH
John
"Mindy" wrote:
> Hi,
> I am working on a Budgeting web based application using ASP.NET 2 and SQL
> Server 2000. The budgeting data will build up over a course of period and
> these historical data will be used for decision making in future budget.
> My questions:
> 1. what design approach should I use to store the historical data? Data
> Mining or Datawarehouse? or other options?
> 2. Is there any built in tool in sql 2000 to keep track of the audit trail
?
> If yes, where they are saved?
> Thanks for you help!
>|||Thanks for the quick response. Can you send me the link to the online book o
n
Create Triggers topic?
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> The choice of datawarehouse or datamining really need deciding by analysin
g
> and producing the requirements for the future needs so can't really be
> answered with the level of information you have given.
> Although SQL Server 2000 has no automatic method of auditing, it is possib
le
> to implement something using triggers and there is a simple example in the
> CREATE TRIGGER topic in Books Online. I would recommend that you do the
> mimimum amount of work require in the trigger and do any
> aggregation/formatting... as a ofline process. This will reduce the impact
of
> the trigger on any oltp activity. You can also get third party application
s
> that implement auditing for you such as Lumigent's auditdb
> http://www.lumigent.com/products/auditdb.html
> HTH
> John
>
> "Mindy" wrote:
>|||You can download Books Online from here:
SQL Server Books Online
2005 -
http://www.microsoft.com/technet/pr...oads/books.mspx
2000 -
http://www.microsoft.com/downloads/...&displaylang=en
Then search for CREATE TRIGGER...
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Mindy" <Mindy@.discussions.microsoft.com> wrote in message
news:49F456EE-FBC0-46AD-8257-089E6887AAFD@.microsoft.com...[vbcol=seagreen]
> Thanks for the quick response. Can you send me the link to the online book
> on
> Create Triggers topic?
>
> "John Bell" wrote:
>|||Hi
If you don't want to download books online check out
http://msdn.microsoft.com/library/d...asp?frame=true
John
"Mindy" wrote:
[vbcol=seagreen]
> Thanks for the quick response. Can you send me the link to the online book
on
> Create Triggers topic?
>
> "John Bell" wrote:
>
I am working on a Budgeting web based application using ASP.NET 2 and SQL
Server 2000. The budgeting data will build up over a course of period and
these historical data will be used for decision making in future budget.
My questions:
1. what design approach should I use to store the historical data? Data
Mining or Datawarehouse? or other options?
2. Is there any built in tool in sql 2000 to keep track of the audit trail?
If yes, where they are saved?
Thanks for you help!Hi
The choice of datawarehouse or datamining really need deciding by analysing
and producing the requirements for the future needs so can't really be
answered with the level of information you have given.
Although SQL Server 2000 has no automatic method of auditing, it is possible
to implement something using triggers and there is a simple example in the
CREATE TRIGGER topic in Books Online. I would recommend that you do the
mimimum amount of work require in the trigger and do any
aggregation/formatting... as a ofline process. This will reduce the impact o
f
the trigger on any oltp activity. You can also get third party applications
that implement auditing for you such as Lumigent's auditdb
http://www.lumigent.com/products/auditdb.html
HTH
John
"Mindy" wrote:
> Hi,
> I am working on a Budgeting web based application using ASP.NET 2 and SQL
> Server 2000. The budgeting data will build up over a course of period and
> these historical data will be used for decision making in future budget.
> My questions:
> 1. what design approach should I use to store the historical data? Data
> Mining or Datawarehouse? or other options?
> 2. Is there any built in tool in sql 2000 to keep track of the audit trail
?
> If yes, where they are saved?
> Thanks for you help!
>|||Thanks for the quick response. Can you send me the link to the online book o
n
Create Triggers topic?
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> The choice of datawarehouse or datamining really need deciding by analysin
g
> and producing the requirements for the future needs so can't really be
> answered with the level of information you have given.
> Although SQL Server 2000 has no automatic method of auditing, it is possib
le
> to implement something using triggers and there is a simple example in the
> CREATE TRIGGER topic in Books Online. I would recommend that you do the
> mimimum amount of work require in the trigger and do any
> aggregation/formatting... as a ofline process. This will reduce the impact
of
> the trigger on any oltp activity. You can also get third party application
s
> that implement auditing for you such as Lumigent's auditdb
> http://www.lumigent.com/products/auditdb.html
> HTH
> John
>
> "Mindy" wrote:
>|||You can download Books Online from here:
SQL Server Books Online
2005 -
http://www.microsoft.com/technet/pr...oads/books.mspx
2000 -
http://www.microsoft.com/downloads/...&displaylang=en
Then search for CREATE TRIGGER...
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Mindy" <Mindy@.discussions.microsoft.com> wrote in message
news:49F456EE-FBC0-46AD-8257-089E6887AAFD@.microsoft.com...[vbcol=seagreen]
> Thanks for the quick response. Can you send me the link to the online book
> on
> Create Triggers topic?
>
> "John Bell" wrote:
>|||Hi
If you don't want to download books online check out
http://msdn.microsoft.com/library/d...asp?frame=true
John
"Mindy" wrote:
[vbcol=seagreen]
> Thanks for the quick response. Can you send me the link to the online book
on
> Create Triggers topic?
>
> "John Bell" wrote:
>
Database design question
Hello,
Your description is very abstract, and I might change my mind if knew
more about what's in this table. But from the description you have
given, this latter solution is what I would prefer.
We have a problem we're not quite how to solve best. We're working on a
Web-application where some values that are used, are pre-defined (default
values), and other values should be user-defined (users can add additional
values)
Currently, the 2 different things have been separated into 2 different
tables. The problem we're having now, is when the values from the 2 tables,
should be referenced in another table, i.e when the items are saved as a
part of a form-submission. Should we use 2 different columns to represent
the ID for the 2 different tables?
Here is an exmple of that design:
DropDownValues_SystemDefined
------------
ID
Name
Value
DropDownValues_CustomerDefined
------------
ID
CustomerID
Name
Value
DropDownValues_SavedItems
-----------
ID
DropDownID_System
DropDownID_Customer
The other solution , as far as we can see, is to put all values into 1
table, and use CustomerID=-1 or null when the item is a System-defined
value, and not a customer-defined value.
DropDownValues_CustomerAndSystemDefined
------------
ID
CustomerID
Name
Value
Regards Christian H.Christian H (no@.ni.na) writes:
Quote:
Originally Posted by
The other solution , as far as we can see, is to put all values into 1
table, and use CustomerID=-1 or null when the item is a System-defined
value, and not a customer-defined value.
>
DropDownValues_CustomerAndSystemDefined
------------
ID
CustomerID
Name
Value
Your description is very abstract, and I might change my mind if knew
more about what's in this table. But from the description you have
given, this latter solution is what I would prefer.
The presumption here is that the values really describe the same
entity.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Assuming these are actually the same thing, and that you simply want to
track that some are Customer defined, and that you'll want to enforce
relationships to either system or customer records, here's what I'd do:
DropDownValues
------------
DropDownValuesID int identity
Name
Value
DropDownValues_SystemDefined
------------
DropDownValuesID int
DropDownValues_CustomerDefined
------------
DropDownValuesID int
CustomerID
DropDownValues would serve as the base of your two classes, and would
be used to generate keys and hold common information. Your customer
data could extend that table, and the system ones would simply need a
placeholder record to identify themselves.
Of course, if you won't need to enforce relationships to System or
Customer records (for instance, another record that can only hook up to
a System value, but not a Customer one), then you could simply add a
nullable CustomerID to the DropDownValues table and be done with it.
Good luck!
Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/
--
Get your own Travel Blog, with itinerary maps and photos!
http://www.blogabond.com/
Christian H wrote:
Quote:
Originally Posted by
Hello,
>
We have a problem we're not quite how to solve best. We're working on a
Web-application where some values that are used, are pre-defined (default
values), and other values should be user-defined (users can add additional
values)
Currently, the 2 different things have been separated into 2 different
tables. The problem we're having now, is when the values from the 2 tables,
should be referenced in another table, i.e when the items are saved as a
part of a form-submission. Should we use 2 different columns to represent
the ID for the 2 different tables?
>
Here is an exmple of that design:
>
DropDownValues_SystemDefined
------------
ID
Name
Value
>
DropDownValues_CustomerDefined
------------
ID
CustomerID
Name
Value
>
DropDownValues_SavedItems
-----------
ID
DropDownID_System
DropDownID_Customer
>
>
>
The other solution , as far as we can see, is to put all values into 1
table, and use CustomerID=-1 or null when the item is a System-defined
value, and not a customer-defined value.
>
DropDownValues_CustomerAndSystemDefined
------------
ID
CustomerID
Name
Value
>
>
Regards Christian H.
Database design question
I working on a project that will let users essentially create design and requirement documents online and keep the information stored in a database.
I was thinking that the model would be similar to that of a book online.
I was thinking the basic schema would be something like
Table - Project
Table - Author.
Table - Topic
Table - Sub Topic
A project can have many topics, A project can have many authors etc.
A topic can have many sub topics etc.
So I was wondering if anyone knew of some sample schemas that may support that functionality.
The examples in the link you provided are good but I was looking for something more specific. Such as if I was going to build an online
store, I would want to see a sample database from another online store to see how/what data is stored.
Thanks,
|||
I was thinking that the model would be similar to that of a book online.
I was thinking the basic schema would be something like
Table - Project
Table - Author.
Table - Topic
Table - Sub Topic
A project can have many topics, A project can have many authors etc.
A topic can have many sub topics etc.
So I was wondering if anyone knew of some sample schemas that may support that functionality.
Try this link and download the PPT slide to get started. You may not need four tables because it is files and association. Hope this helps.
http://wings.buffalo.edu/mgmt/courses/mgtsand/data.html
The examples in the link you provided are good but I was looking for something more specific. Such as if I was going to build an online
store, I would want to see a sample database from another online store to see how/what data is stored.
Thanks,
|||
SQL Server 2000 beginner's Guide by Dusan Petkovic has a project database and it is one of the better SQL Server books for Developers. But I would look at Pubs database to get started it is only nine tables so it will be easy to modify the create table statement. Hope this helps.
Database design question
Hi,
I am working on a Budgeting web based application using ASP.NET 2 and SQL
Server 2000. The budgeting data will build up over a course of period and
these historical data will be used for decision making in future budget.
My questions:
1. what design approach should I use to store the historical data? Data
Mining or Datawarehouse? or other options?
2. Is there any built in tool in sql 2000 to keep track of the audit trail?
If yes, where they are saved?
Thanks for you help!Hi
The choice of datawarehouse or datamining really need deciding by analysing
and producing the requirements for the future needs so can't really be
answered with the level of information you have given.
Although SQL Server 2000 has no automatic method of auditing, it is possible
to implement something using triggers and there is a simple example in the
CREATE TRIGGER topic in Books Online. I would recommend that you do the
mimimum amount of work require in the trigger and do any
aggregation/formatting... as a ofline process. This will reduce the impact of
the trigger on any oltp activity. You can also get third party applications
that implement auditing for you such as Lumigent's auditdb
http://www.lumigent.com/products/auditdb.html
HTH
John
"Mindy" wrote:
> Hi,
> I am working on a Budgeting web based application using ASP.NET 2 and SQL
> Server 2000. The budgeting data will build up over a course of period and
> these historical data will be used for decision making in future budget.
> My questions:
> 1. what design approach should I use to store the historical data? Data
> Mining or Datawarehouse? or other options?
> 2. Is there any built in tool in sql 2000 to keep track of the audit trail?
> If yes, where they are saved?
> Thanks for you help!
>|||Thanks for the quick response. Can you send me the link to the online book on
Create Triggers topic?
"John Bell" wrote:
> Hi
> The choice of datawarehouse or datamining really need deciding by analysing
> and producing the requirements for the future needs so can't really be
> answered with the level of information you have given.
> Although SQL Server 2000 has no automatic method of auditing, it is possible
> to implement something using triggers and there is a simple example in the
> CREATE TRIGGER topic in Books Online. I would recommend that you do the
> mimimum amount of work require in the trigger and do any
> aggregation/formatting... as a ofline process. This will reduce the impact of
> the trigger on any oltp activity. You can also get third party applications
> that implement auditing for you such as Lumigent's auditdb
> http://www.lumigent.com/products/auditdb.html
> HTH
> John
>
> "Mindy" wrote:
> > Hi,
> >
> > I am working on a Budgeting web based application using ASP.NET 2 and SQL
> > Server 2000. The budgeting data will build up over a course of period and
> > these historical data will be used for decision making in future budget.
> >
> > My questions:
> > 1. what design approach should I use to store the historical data? Data
> > Mining or Datawarehouse? or other options?
> >
> > 2. Is there any built in tool in sql 2000 to keep track of the audit trail?
> > If yes, where they are saved?
> >
> > Thanks for you help!
> >|||You can download Books Online from here:
SQL Server Books Online
2005 -
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
2000 -
http://www.microsoft.com/downloads/details.aspx?familyid=a6f79cb1-a420-445f-8a4b-bd77a7da194b&displaylang=en
Then search for CREATE TRIGGER...
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Mindy" <Mindy@.discussions.microsoft.com> wrote in message
news:49F456EE-FBC0-46AD-8257-089E6887AAFD@.microsoft.com...
> Thanks for the quick response. Can you send me the link to the online book
> on
> Create Triggers topic?
>
> "John Bell" wrote:
>> Hi
>> The choice of datawarehouse or datamining really need deciding by
>> analysing
>> and producing the requirements for the future needs so can't really be
>> answered with the level of information you have given.
>> Although SQL Server 2000 has no automatic method of auditing, it is
>> possible
>> to implement something using triggers and there is a simple example in
>> the
>> CREATE TRIGGER topic in Books Online. I would recommend that you do the
>> mimimum amount of work require in the trigger and do any
>> aggregation/formatting... as a ofline process. This will reduce the
>> impact of
>> the trigger on any oltp activity. You can also get third party
>> applications
>> that implement auditing for you such as Lumigent's auditdb
>> http://www.lumigent.com/products/auditdb.html
>> HTH
>> John
>>
>> "Mindy" wrote:
>> > Hi,
>> >
>> > I am working on a Budgeting web based application using ASP.NET 2 and
>> > SQL
>> > Server 2000. The budgeting data will build up over a course of period
>> > and
>> > these historical data will be used for decision making in future
>> > budget.
>> >
>> > My questions:
>> > 1. what design approach should I use to store the historical data?
>> > Data
>> > Mining or Datawarehouse? or other options?
>> >
>> > 2. Is there any built in tool in sql 2000 to keep track of the audit
>> > trail?
>> > If yes, where they are saved?
>> >
>> > Thanks for you help!
>> >|||Hi
If you don't want to download books online check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnanchor/html/sqlserver.asp?frame=true
John
"Mindy" wrote:
> Thanks for the quick response. Can you send me the link to the online book on
> Create Triggers topic?
>
> "John Bell" wrote:
> > Hi
> >
> > The choice of datawarehouse or datamining really need deciding by analysing
> > and producing the requirements for the future needs so can't really be
> > answered with the level of information you have given.
> >
> > Although SQL Server 2000 has no automatic method of auditing, it is possible
> > to implement something using triggers and there is a simple example in the
> > CREATE TRIGGER topic in Books Online. I would recommend that you do the
> > mimimum amount of work require in the trigger and do any
> > aggregation/formatting... as a ofline process. This will reduce the impact of
> > the trigger on any oltp activity. You can also get third party applications
> > that implement auditing for you such as Lumigent's auditdb
> > http://www.lumigent.com/products/auditdb.html
> >
> > HTH
> >
> > John
> >
> >
> > "Mindy" wrote:
> >
> > > Hi,
> > >
> > > I am working on a Budgeting web based application using ASP.NET 2 and SQL
> > > Server 2000. The budgeting data will build up over a course of period and
> > > these historical data will be used for decision making in future budget.
> > >
> > > My questions:
> > > 1. what design approach should I use to store the historical data? Data
> > > Mining or Datawarehouse? or other options?
> > >
> > > 2. Is there any built in tool in sql 2000 to keep track of the audit trail?
> > > If yes, where they are saved?
> > >
> > > Thanks for you help!
> > >
I am working on a Budgeting web based application using ASP.NET 2 and SQL
Server 2000. The budgeting data will build up over a course of period and
these historical data will be used for decision making in future budget.
My questions:
1. what design approach should I use to store the historical data? Data
Mining or Datawarehouse? or other options?
2. Is there any built in tool in sql 2000 to keep track of the audit trail?
If yes, where they are saved?
Thanks for you help!Hi
The choice of datawarehouse or datamining really need deciding by analysing
and producing the requirements for the future needs so can't really be
answered with the level of information you have given.
Although SQL Server 2000 has no automatic method of auditing, it is possible
to implement something using triggers and there is a simple example in the
CREATE TRIGGER topic in Books Online. I would recommend that you do the
mimimum amount of work require in the trigger and do any
aggregation/formatting... as a ofline process. This will reduce the impact of
the trigger on any oltp activity. You can also get third party applications
that implement auditing for you such as Lumigent's auditdb
http://www.lumigent.com/products/auditdb.html
HTH
John
"Mindy" wrote:
> Hi,
> I am working on a Budgeting web based application using ASP.NET 2 and SQL
> Server 2000. The budgeting data will build up over a course of period and
> these historical data will be used for decision making in future budget.
> My questions:
> 1. what design approach should I use to store the historical data? Data
> Mining or Datawarehouse? or other options?
> 2. Is there any built in tool in sql 2000 to keep track of the audit trail?
> If yes, where they are saved?
> Thanks for you help!
>|||Thanks for the quick response. Can you send me the link to the online book on
Create Triggers topic?
"John Bell" wrote:
> Hi
> The choice of datawarehouse or datamining really need deciding by analysing
> and producing the requirements for the future needs so can't really be
> answered with the level of information you have given.
> Although SQL Server 2000 has no automatic method of auditing, it is possible
> to implement something using triggers and there is a simple example in the
> CREATE TRIGGER topic in Books Online. I would recommend that you do the
> mimimum amount of work require in the trigger and do any
> aggregation/formatting... as a ofline process. This will reduce the impact of
> the trigger on any oltp activity. You can also get third party applications
> that implement auditing for you such as Lumigent's auditdb
> http://www.lumigent.com/products/auditdb.html
> HTH
> John
>
> "Mindy" wrote:
> > Hi,
> >
> > I am working on a Budgeting web based application using ASP.NET 2 and SQL
> > Server 2000. The budgeting data will build up over a course of period and
> > these historical data will be used for decision making in future budget.
> >
> > My questions:
> > 1. what design approach should I use to store the historical data? Data
> > Mining or Datawarehouse? or other options?
> >
> > 2. Is there any built in tool in sql 2000 to keep track of the audit trail?
> > If yes, where they are saved?
> >
> > Thanks for you help!
> >|||You can download Books Online from here:
SQL Server Books Online
2005 -
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
2000 -
http://www.microsoft.com/downloads/details.aspx?familyid=a6f79cb1-a420-445f-8a4b-bd77a7da194b&displaylang=en
Then search for CREATE TRIGGER...
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Mindy" <Mindy@.discussions.microsoft.com> wrote in message
news:49F456EE-FBC0-46AD-8257-089E6887AAFD@.microsoft.com...
> Thanks for the quick response. Can you send me the link to the online book
> on
> Create Triggers topic?
>
> "John Bell" wrote:
>> Hi
>> The choice of datawarehouse or datamining really need deciding by
>> analysing
>> and producing the requirements for the future needs so can't really be
>> answered with the level of information you have given.
>> Although SQL Server 2000 has no automatic method of auditing, it is
>> possible
>> to implement something using triggers and there is a simple example in
>> the
>> CREATE TRIGGER topic in Books Online. I would recommend that you do the
>> mimimum amount of work require in the trigger and do any
>> aggregation/formatting... as a ofline process. This will reduce the
>> impact of
>> the trigger on any oltp activity. You can also get third party
>> applications
>> that implement auditing for you such as Lumigent's auditdb
>> http://www.lumigent.com/products/auditdb.html
>> HTH
>> John
>>
>> "Mindy" wrote:
>> > Hi,
>> >
>> > I am working on a Budgeting web based application using ASP.NET 2 and
>> > SQL
>> > Server 2000. The budgeting data will build up over a course of period
>> > and
>> > these historical data will be used for decision making in future
>> > budget.
>> >
>> > My questions:
>> > 1. what design approach should I use to store the historical data?
>> > Data
>> > Mining or Datawarehouse? or other options?
>> >
>> > 2. Is there any built in tool in sql 2000 to keep track of the audit
>> > trail?
>> > If yes, where they are saved?
>> >
>> > Thanks for you help!
>> >|||Hi
If you don't want to download books online check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnanchor/html/sqlserver.asp?frame=true
John
"Mindy" wrote:
> Thanks for the quick response. Can you send me the link to the online book on
> Create Triggers topic?
>
> "John Bell" wrote:
> > Hi
> >
> > The choice of datawarehouse or datamining really need deciding by analysing
> > and producing the requirements for the future needs so can't really be
> > answered with the level of information you have given.
> >
> > Although SQL Server 2000 has no automatic method of auditing, it is possible
> > to implement something using triggers and there is a simple example in the
> > CREATE TRIGGER topic in Books Online. I would recommend that you do the
> > mimimum amount of work require in the trigger and do any
> > aggregation/formatting... as a ofline process. This will reduce the impact of
> > the trigger on any oltp activity. You can also get third party applications
> > that implement auditing for you such as Lumigent's auditdb
> > http://www.lumigent.com/products/auditdb.html
> >
> > HTH
> >
> > John
> >
> >
> > "Mindy" wrote:
> >
> > > Hi,
> > >
> > > I am working on a Budgeting web based application using ASP.NET 2 and SQL
> > > Server 2000. The budgeting data will build up over a course of period and
> > > these historical data will be used for decision making in future budget.
> > >
> > > My questions:
> > > 1. what design approach should I use to store the historical data? Data
> > > Mining or Datawarehouse? or other options?
> > >
> > > 2. Is there any built in tool in sql 2000 to keep track of the audit trail?
> > > If yes, where they are saved?
> > >
> > > Thanks for you help!
> > >
Friday, February 24, 2012
Database Design Help Required
Hi
I am working on a community site that pretty much works like anyother community site like orkut or myspace..I have few doubts for which i badly need your help.. if you can point me to some usefully links, articles, pdf or your suggestions..i will surely be obiliged.
THE application i am talking about willl be invite only.. and will let the users grow there network of friends... there will be other data associated with each userid like the profile,bookmarks etc etc.. , also there will be aurthorisation based on who are the members friends are who are not...
My problem.. database design
though i am planning ot user MS SQLSERVER 2005 ,, i have not finalised yet.. I want to make up my mind on how to structure the database..also,,if you have seen Orkut.com when you visit a cirten persons profile it shows (trhu a breadcrum like view) how you are connected.. ie.. thru what friend of yours you are connected...
I want to know ,,what kind of mapping is used here... how can i achive that without sacrifising performance,, coz surely thease kind of applications are to be build for VERY LARGE USER BASE...
Please suggest ...I am fighting my war alone..but i am determind.. you can help though. :)
If you go with Sql 2005, you can retreive breadcrumbs in a single query very easy and efficiently.
Say I have a Catalog with the following Categories:
If I use the following query against my Categories Table:
WITH Navigation (CategoryID, ParentID, Name, Sequence) AS ( SELECT CategoryID, ParentID, Name, 0 AS Sequence FROM Categories WHERE CategoryID = @.CategoryID AND Disabled = 0 UNION ALL SELECT c.CategoryID, c.ParentID, c.Name, n.Sequence + 1 AS Sequence FROM Categories c INNER JOIN Navigation n ON c.CategoryID = n.ParentID) SELECT CategoryID, Name, ParentID FROM Navigation
WHERE @.CategoryID = 20 and Navigation is a derived table, the results come back like this
This makes it very easy to build breadcrumbs (you can tack on an ORDER BY CategoryID to flip the results).
So if you have a table of related data, replacing the above data:
And you are looking at John's profile, you can run the above query and trace this back to you "Bob." It's probably a little more in depth than that because not everyone in the table will end up back to a single "Home" friend, but you can probably modify the query statement to know exactly where to stop. I hope this gives you a start.|||
thanks, surely thats a great start.. will get back to this post, once I get my coding done, to post what i did .
thanks again.
Database design for versioned data
We're working on an app that needs to keep versioned data (i.e., the
current values plus all previous values). The versioning is integral
to the app so it's more than just an audit trail or history (some
versioned data needs to link to specific versions of other data).
Can anyone share experiences with the database structure for this type
of requirement or point me to helpful resources?
Thanks,
Sam
----
We're hiring! B-Line Medical is seeking .NET
Developers for exciting positions in medical product
development in MD/DC. Work with a variety of technologies
in a relaxed team environment. See ads on Dice.com.Check out the free book from Richard Snodgrass at:
http://www.cs.arizona.edu/people/rts/publications.html
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Samuel R. Neff" <samuelneff@.nomail.com> wrote in message
news:uh0j73hhdv0po8bbq7cd15nv42inl63v4c@.4ax.com...
> We're working on an app that needs to keep versioned data (i.e., the
> current values plus all previous values). The versioning is integral
> to the app so it's more than just an audit trail or history (some
> versioned data needs to link to specific versions of other data).
> Can anyone share experiences with the database structure for this type
> of requirement or point me to helpful resources?
> Thanks,
> Sam
> ----
> We're hiring! B-Line Medical is seeking .NET
> Developers for exciting positions in medical product
> development in MD/DC. Work with a variety of technologies
> in a relaxed team environment. See ads on Dice.com.|||I have an article on one way to do this, here:
http://simple-talk.com/sql/t-sql-programming/a-primer-on-managing-data-bitemporally/
Note that one of my main references was the book Tibor posted the link to,
so you might want to read that instead of or in addition to this.
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Samuel R. Neff" <samuelneff@.nomail.com> wrote in message
news:uh0j73hhdv0po8bbq7cd15nv42inl63v4c@.4ax.com...
> We're working on an app that needs to keep versioned data (i.e., the
> current values plus all previous values). The versioning is integral
> to the app so it's more than just an audit trail or history (some
> versioned data needs to link to specific versions of other data).
> Can anyone share experiences with the database structure for this type
> of requirement or point me to helpful resources?
> Thanks,
> Sam
> ----
> We're hiring! B-Line Medical is seeking .NET
> Developers for exciting positions in medical product
> development in MD/DC. Work with a variety of technologies
> in a relaxed team environment. See ads on Dice.com.|||Perhaps an app like ApexSQL's Audit can help. Not sure based on your spec
whether you need the data to exist in the exact same tables or not.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Samuel R. Neff" <samuelneff@.nomail.com> wrote in message
news:uh0j73hhdv0po8bbq7cd15nv42inl63v4c@.4ax.com...
> We're working on an app that needs to keep versioned data (i.e., the
> current values plus all previous values). The versioning is integral
> to the app so it's more than just an audit trail or history (some
> versioned data needs to link to specific versions of other data).
> Can anyone share experiences with the database structure for this type
> of requirement or point me to helpful resources?
> Thanks,
> Sam
> ----
> We're hiring! B-Line Medical is seeking .NET
> Developers for exciting positions in medical product
> development in MD/DC. Work with a variety of technologies
> in a relaxed team environment. See ads on Dice.com.
current values plus all previous values). The versioning is integral
to the app so it's more than just an audit trail or history (some
versioned data needs to link to specific versions of other data).
Can anyone share experiences with the database structure for this type
of requirement or point me to helpful resources?
Thanks,
Sam
----
We're hiring! B-Line Medical is seeking .NET
Developers for exciting positions in medical product
development in MD/DC. Work with a variety of technologies
in a relaxed team environment. See ads on Dice.com.Check out the free book from Richard Snodgrass at:
http://www.cs.arizona.edu/people/rts/publications.html
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Samuel R. Neff" <samuelneff@.nomail.com> wrote in message
news:uh0j73hhdv0po8bbq7cd15nv42inl63v4c@.4ax.com...
> We're working on an app that needs to keep versioned data (i.e., the
> current values plus all previous values). The versioning is integral
> to the app so it's more than just an audit trail or history (some
> versioned data needs to link to specific versions of other data).
> Can anyone share experiences with the database structure for this type
> of requirement or point me to helpful resources?
> Thanks,
> Sam
> ----
> We're hiring! B-Line Medical is seeking .NET
> Developers for exciting positions in medical product
> development in MD/DC. Work with a variety of technologies
> in a relaxed team environment. See ads on Dice.com.|||I have an article on one way to do this, here:
http://simple-talk.com/sql/t-sql-programming/a-primer-on-managing-data-bitemporally/
Note that one of my main references was the book Tibor posted the link to,
so you might want to read that instead of or in addition to this.
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Samuel R. Neff" <samuelneff@.nomail.com> wrote in message
news:uh0j73hhdv0po8bbq7cd15nv42inl63v4c@.4ax.com...
> We're working on an app that needs to keep versioned data (i.e., the
> current values plus all previous values). The versioning is integral
> to the app so it's more than just an audit trail or history (some
> versioned data needs to link to specific versions of other data).
> Can anyone share experiences with the database structure for this type
> of requirement or point me to helpful resources?
> Thanks,
> Sam
> ----
> We're hiring! B-Line Medical is seeking .NET
> Developers for exciting positions in medical product
> development in MD/DC. Work with a variety of technologies
> in a relaxed team environment. See ads on Dice.com.|||Perhaps an app like ApexSQL's Audit can help. Not sure based on your spec
whether you need the data to exist in the exact same tables or not.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Samuel R. Neff" <samuelneff@.nomail.com> wrote in message
news:uh0j73hhdv0po8bbq7cd15nv42inl63v4c@.4ax.com...
> We're working on an app that needs to keep versioned data (i.e., the
> current values plus all previous values). The versioning is integral
> to the app so it's more than just an audit trail or history (some
> versioned data needs to link to specific versions of other data).
> Can anyone share experiences with the database structure for this type
> of requirement or point me to helpful resources?
> Thanks,
> Sam
> ----
> We're hiring! B-Line Medical is seeking .NET
> Developers for exciting positions in medical product
> development in MD/DC. Work with a variety of technologies
> in a relaxed team environment. See ads on Dice.com.
Database design for versioned data
We're working on an app that needs to keep versioned data (i.e., the
current values plus all previous values). The versioning is integral
to the app so it's more than just an audit trail or history (some
versioned data needs to link to specific versions of other data).
Can anyone share experiences with the database structure for this type
of requirement or point me to helpful resources?
Thanks,
Sam
We're hiring! B-Line Medical is seeking .NET
Developers for exciting positions in medical product
development in MD/DC. Work with a variety of technologies
in a relaxed team environment. See ads on Dice.com.
Check out the free book from Richard Snodgrass at:
http://www.cs.arizona.edu/people/rts/publications.html
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Samuel R. Neff" <samuelneff@.nomail.com> wrote in message
news:uh0j73hhdv0po8bbq7cd15nv42inl63v4c@.4ax.com...
> We're working on an app that needs to keep versioned data (i.e., the
> current values plus all previous values). The versioning is integral
> to the app so it's more than just an audit trail or history (some
> versioned data needs to link to specific versions of other data).
> Can anyone share experiences with the database structure for this type
> of requirement or point me to helpful resources?
> Thanks,
> Sam
> ----
> We're hiring! B-Line Medical is seeking .NET
> Developers for exciting positions in medical product
> development in MD/DC. Work with a variety of technologies
> in a relaxed team environment. See ads on Dice.com.
|||I have an article on one way to do this, here:
http://simple-talk.com/sql/t-sql-programming/a-primer-on-managing-data-bitemporally/
Note that one of my main references was the book Tibor posted the link to,
so you might want to read that instead of or in addition to this.
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Samuel R. Neff" <samuelneff@.nomail.com> wrote in message
news:uh0j73hhdv0po8bbq7cd15nv42inl63v4c@.4ax.com...
> We're working on an app that needs to keep versioned data (i.e., the
> current values plus all previous values). The versioning is integral
> to the app so it's more than just an audit trail or history (some
> versioned data needs to link to specific versions of other data).
> Can anyone share experiences with the database structure for this type
> of requirement or point me to helpful resources?
> Thanks,
> Sam
> ----
> We're hiring! B-Line Medical is seeking .NET
> Developers for exciting positions in medical product
> development in MD/DC. Work with a variety of technologies
> in a relaxed team environment. See ads on Dice.com.
|||Perhaps an app like ApexSQL's Audit can help. Not sure based on your spec
whether you need the data to exist in the exact same tables or not.
TheSQLGuru
President
Indicium Resources, Inc.
"Samuel R. Neff" <samuelneff@.nomail.com> wrote in message
news:uh0j73hhdv0po8bbq7cd15nv42inl63v4c@.4ax.com...
> We're working on an app that needs to keep versioned data (i.e., the
> current values plus all previous values). The versioning is integral
> to the app so it's more than just an audit trail or history (some
> versioned data needs to link to specific versions of other data).
> Can anyone share experiences with the database structure for this type
> of requirement or point me to helpful resources?
> Thanks,
> Sam
> ----
> We're hiring! B-Line Medical is seeking .NET
> Developers for exciting positions in medical product
> development in MD/DC. Work with a variety of technologies
> in a relaxed team environment. See ads on Dice.com.
current values plus all previous values). The versioning is integral
to the app so it's more than just an audit trail or history (some
versioned data needs to link to specific versions of other data).
Can anyone share experiences with the database structure for this type
of requirement or point me to helpful resources?
Thanks,
Sam
We're hiring! B-Line Medical is seeking .NET
Developers for exciting positions in medical product
development in MD/DC. Work with a variety of technologies
in a relaxed team environment. See ads on Dice.com.
Check out the free book from Richard Snodgrass at:
http://www.cs.arizona.edu/people/rts/publications.html
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Samuel R. Neff" <samuelneff@.nomail.com> wrote in message
news:uh0j73hhdv0po8bbq7cd15nv42inl63v4c@.4ax.com...
> We're working on an app that needs to keep versioned data (i.e., the
> current values plus all previous values). The versioning is integral
> to the app so it's more than just an audit trail or history (some
> versioned data needs to link to specific versions of other data).
> Can anyone share experiences with the database structure for this type
> of requirement or point me to helpful resources?
> Thanks,
> Sam
> ----
> We're hiring! B-Line Medical is seeking .NET
> Developers for exciting positions in medical product
> development in MD/DC. Work with a variety of technologies
> in a relaxed team environment. See ads on Dice.com.
|||I have an article on one way to do this, here:
http://simple-talk.com/sql/t-sql-programming/a-primer-on-managing-data-bitemporally/
Note that one of my main references was the book Tibor posted the link to,
so you might want to read that instead of or in addition to this.
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Samuel R. Neff" <samuelneff@.nomail.com> wrote in message
news:uh0j73hhdv0po8bbq7cd15nv42inl63v4c@.4ax.com...
> We're working on an app that needs to keep versioned data (i.e., the
> current values plus all previous values). The versioning is integral
> to the app so it's more than just an audit trail or history (some
> versioned data needs to link to specific versions of other data).
> Can anyone share experiences with the database structure for this type
> of requirement or point me to helpful resources?
> Thanks,
> Sam
> ----
> We're hiring! B-Line Medical is seeking .NET
> Developers for exciting positions in medical product
> development in MD/DC. Work with a variety of technologies
> in a relaxed team environment. See ads on Dice.com.
|||Perhaps an app like ApexSQL's Audit can help. Not sure based on your spec
whether you need the data to exist in the exact same tables or not.
TheSQLGuru
President
Indicium Resources, Inc.
"Samuel R. Neff" <samuelneff@.nomail.com> wrote in message
news:uh0j73hhdv0po8bbq7cd15nv42inl63v4c@.4ax.com...
> We're working on an app that needs to keep versioned data (i.e., the
> current values plus all previous values). The versioning is integral
> to the app so it's more than just an audit trail or history (some
> versioned data needs to link to specific versions of other data).
> Can anyone share experiences with the database structure for this type
> of requirement or point me to helpful resources?
> Thanks,
> Sam
> ----
> We're hiring! B-Line Medical is seeking .NET
> Developers for exciting positions in medical product
> development in MD/DC. Work with a variety of technologies
> in a relaxed team environment. See ads on Dice.com.
Database design for versioned data
We're working on an app that needs to keep versioned data (i.e., the
current values plus all previous values). The versioning is integral
to the app so it's more than just an audit trail or history (some
versioned data needs to link to specific versions of other data).
Can anyone share experiences with the database structure for this type
of requirement or point me to helpful resources?
Thanks,
Sam
----
We're hiring! B-Line Medical is seeking .NET
Developers for exciting positions in medical product
development in MD/DC. Work with a variety of technologies
in a relaxed team environment. See ads on Dice.com.Check out the free book from Richard Snodgrass at:
http://www.cs.arizona.edu/people/rts/publications.html
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Samuel R. Neff" <samuelneff@.nomail.com> wrote in message
news:uh0j73hhdv0po8bbq7cd15nv42inl63v4c@.
4ax.com...
> We're working on an app that needs to keep versioned data (i.e., the
> current values plus all previous values). The versioning is integral
> to the app so it's more than just an audit trail or history (some
> versioned data needs to link to specific versions of other data).
> Can anyone share experiences with the database structure for this type
> of requirement or point me to helpful resources?
> Thanks,
> Sam
> ----
> We're hiring! B-Line Medical is seeking .NET
> Developers for exciting positions in medical product
> development in MD/DC. Work with a variety of technologies
> in a relaxed team environment. See ads on Dice.com.|||I have an article on one way to do this, here:
http://simple-talk.com/sql/t-sql-pr...orall
y/
Note that one of my main references was the book Tibor posted the link to,
so you might want to read that instead of or in addition to this.
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Samuel R. Neff" <samuelneff@.nomail.com> wrote in message
news:uh0j73hhdv0po8bbq7cd15nv42inl63v4c@.
4ax.com...
> We're working on an app that needs to keep versioned data (i.e., the
> current values plus all previous values). The versioning is integral
> to the app so it's more than just an audit trail or history (some
> versioned data needs to link to specific versions of other data).
> Can anyone share experiences with the database structure for this type
> of requirement or point me to helpful resources?
> Thanks,
> Sam
> ----
> We're hiring! B-Line Medical is seeking .NET
> Developers for exciting positions in medical product
> development in MD/DC. Work with a variety of technologies
> in a relaxed team environment. See ads on Dice.com.|||Perhaps an app like ApexSQL's Audit can help. Not sure based on your spec
whether you need the data to exist in the exact same tables or not.
TheSQLGuru
President
Indicium Resources, Inc.
"Samuel R. Neff" <samuelneff@.nomail.com> wrote in message
news:uh0j73hhdv0po8bbq7cd15nv42inl63v4c@.
4ax.com...
> We're working on an app that needs to keep versioned data (i.e., the
> current values plus all previous values). The versioning is integral
> to the app so it's more than just an audit trail or history (some
> versioned data needs to link to specific versions of other data).
> Can anyone share experiences with the database structure for this type
> of requirement or point me to helpful resources?
> Thanks,
> Sam
> ----
> We're hiring! B-Line Medical is seeking .NET
> Developers for exciting positions in medical product
> development in MD/DC. Work with a variety of technologies
> in a relaxed team environment. See ads on Dice.com.
current values plus all previous values). The versioning is integral
to the app so it's more than just an audit trail or history (some
versioned data needs to link to specific versions of other data).
Can anyone share experiences with the database structure for this type
of requirement or point me to helpful resources?
Thanks,
Sam
----
We're hiring! B-Line Medical is seeking .NET
Developers for exciting positions in medical product
development in MD/DC. Work with a variety of technologies
in a relaxed team environment. See ads on Dice.com.Check out the free book from Richard Snodgrass at:
http://www.cs.arizona.edu/people/rts/publications.html
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Samuel R. Neff" <samuelneff@.nomail.com> wrote in message
news:uh0j73hhdv0po8bbq7cd15nv42inl63v4c@.
4ax.com...
> We're working on an app that needs to keep versioned data (i.e., the
> current values plus all previous values). The versioning is integral
> to the app so it's more than just an audit trail or history (some
> versioned data needs to link to specific versions of other data).
> Can anyone share experiences with the database structure for this type
> of requirement or point me to helpful resources?
> Thanks,
> Sam
> ----
> We're hiring! B-Line Medical is seeking .NET
> Developers for exciting positions in medical product
> development in MD/DC. Work with a variety of technologies
> in a relaxed team environment. See ads on Dice.com.|||I have an article on one way to do this, here:
http://simple-talk.com/sql/t-sql-pr...orall
y/
Note that one of my main references was the book Tibor posted the link to,
so you might want to read that instead of or in addition to this.
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Samuel R. Neff" <samuelneff@.nomail.com> wrote in message
news:uh0j73hhdv0po8bbq7cd15nv42inl63v4c@.
4ax.com...
> We're working on an app that needs to keep versioned data (i.e., the
> current values plus all previous values). The versioning is integral
> to the app so it's more than just an audit trail or history (some
> versioned data needs to link to specific versions of other data).
> Can anyone share experiences with the database structure for this type
> of requirement or point me to helpful resources?
> Thanks,
> Sam
> ----
> We're hiring! B-Line Medical is seeking .NET
> Developers for exciting positions in medical product
> development in MD/DC. Work with a variety of technologies
> in a relaxed team environment. See ads on Dice.com.|||Perhaps an app like ApexSQL's Audit can help. Not sure based on your spec
whether you need the data to exist in the exact same tables or not.
TheSQLGuru
President
Indicium Resources, Inc.
"Samuel R. Neff" <samuelneff@.nomail.com> wrote in message
news:uh0j73hhdv0po8bbq7cd15nv42inl63v4c@.
4ax.com...
> We're working on an app that needs to keep versioned data (i.e., the
> current values plus all previous values). The versioning is integral
> to the app so it's more than just an audit trail or history (some
> versioned data needs to link to specific versions of other data).
> Can anyone share experiences with the database structure for this type
> of requirement or point me to helpful resources?
> Thanks,
> Sam
> ----
> We're hiring! B-Line Medical is seeking .NET
> Developers for exciting positions in medical product
> development in MD/DC. Work with a variety of technologies
> in a relaxed team environment. See ads on Dice.com.
Sunday, February 19, 2012
Database Description Report
Hi All,
I am working on generating a manual explaining databases description. some
of the databases are SQL Server. is there a tool i can use to automatically
generate this report? it must show table name, description and all fields
with the attribute and description of each
Regards
Shawki
Hi
Usually you can get this from your modelling tool such as Visio. You can
also get your own information from the INFORMATION_SCHEMA catalogues and
extended properties can be used to add additional information for example:
http://tinyurl.com/ak43h
Third part tools to do this include:
http://www.ag-software.com/ags_scribe_index.asp you may want to search
google for more.
John
"Shawki" <Shawki@.discussions.microsoft.com> wrote in message
news:E9B548D1-4CEB-405A-81E8-DB681BB056DB@.microsoft.com...
> Hi All,
> I am working on generating a manual explaining databases description. some
> of the databases are SQL Server. is there a tool i can use to
> automatically
> generate this report? it must show table name, description and all fields
> with the attribute and description of each
> Regards
> Shawki
|||Hi,
To get the description you need to add all the column desription using
extended properties and the description will be stored in "sysproperties"
system table in each database.
Eg:
CREATE table test (id int , name char (20))
go
EXEC sp_addextendedproperty 'caption', 'Employee ID', 'user', dbo,
'table', 'test', 'column', id
go
EXEC sp_addextendedproperty 'caption', 'Employee Name', 'user', dbo,
'table', 'test', 'column', name
go
select * from sysproperties where object_name(id)='test'
Instead of querying the system tables use the below code using functions to
get the extended properties,
SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table',
'test', 'column', default)
APEXSQL had got a very good tool for documentation.
http://www.apexsql.com/sql_tools_doc.asp
Thanks
Hari
SQL Server MVP
"Shawki" <Shawki@.discussions.microsoft.com> wrote in message
news:E9B548D1-4CEB-405A-81E8-DB681BB056DB@.microsoft.com...
> Hi All,
> I am working on generating a manual explaining databases description. some
> of the databases are SQL Server. is there a tool i can use to
> automatically
> generate this report? it must show table name, description and all fields
> with the attribute and description of each
> Regards
> Shawki
I am working on generating a manual explaining databases description. some
of the databases are SQL Server. is there a tool i can use to automatically
generate this report? it must show table name, description and all fields
with the attribute and description of each
Regards
Shawki
Hi
Usually you can get this from your modelling tool such as Visio. You can
also get your own information from the INFORMATION_SCHEMA catalogues and
extended properties can be used to add additional information for example:
http://tinyurl.com/ak43h
Third part tools to do this include:
http://www.ag-software.com/ags_scribe_index.asp you may want to search
google for more.
John
"Shawki" <Shawki@.discussions.microsoft.com> wrote in message
news:E9B548D1-4CEB-405A-81E8-DB681BB056DB@.microsoft.com...
> Hi All,
> I am working on generating a manual explaining databases description. some
> of the databases are SQL Server. is there a tool i can use to
> automatically
> generate this report? it must show table name, description and all fields
> with the attribute and description of each
> Regards
> Shawki
|||Hi,
To get the description you need to add all the column desription using
extended properties and the description will be stored in "sysproperties"
system table in each database.
Eg:
CREATE table test (id int , name char (20))
go
EXEC sp_addextendedproperty 'caption', 'Employee ID', 'user', dbo,
'table', 'test', 'column', id
go
EXEC sp_addextendedproperty 'caption', 'Employee Name', 'user', dbo,
'table', 'test', 'column', name
go
select * from sysproperties where object_name(id)='test'
Instead of querying the system tables use the below code using functions to
get the extended properties,
SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table',
'test', 'column', default)
APEXSQL had got a very good tool for documentation.
http://www.apexsql.com/sql_tools_doc.asp
Thanks
Hari
SQL Server MVP
"Shawki" <Shawki@.discussions.microsoft.com> wrote in message
news:E9B548D1-4CEB-405A-81E8-DB681BB056DB@.microsoft.com...
> Hi All,
> I am working on generating a manual explaining databases description. some
> of the databases are SQL Server. is there a tool i can use to
> automatically
> generate this report? it must show table name, description and all fields
> with the attribute and description of each
> Regards
> Shawki
Subscribe to:
Posts (Atom)