Many To Many

hi im implementing a database in ms access to migrate it later to SQL, its a project tracking/ employee tracking and im having trouble with some of the tables... the relationships are as follow

Employee : M
Employee_ID
Name
Phone
Supevirsor_Name
Supervisor_Email

Project : M
Project_ID
Project_ Name
Description
Project_Added

EmployeeProject
Employee_ID
Project_ID
AssignedBy

i made this third table called EmployeeProjects for the relationship, but when i go to collect the data everything is ballistic, when i go to capture employees in the employee table everything is fine, i go to the projects and everything is fine there is a "+" in the projects its lists every single employee that i captured in employees in the same project i go to the next record and the same deal, there is a possibility that this could be that many employees can be in a project and also working in another project, what is wrong with it? can anybody help me?I'd say nothing...

Why don't you post the DDL for the tables (CREATE TABLE myTable99(Col1 int, ect)

Some sample Data (INSERT INTO myTable99(Collist) SELECT Data UNION ALL SELECT ect)

The DML You've attempted (SELECT Col1, Col2 FROM myT INNER JOIN myt2, ect)

And the results you'd expect...

I'd say you'd get an answer in 15 minutes of that post...|||The designn looks OK. Id' change your Employ3ee table a little though:

Employee_ID
LastName
FirstName
MiddleName
FullName (calculated...not sure if access has that)
PhoneArea
PhonePrefix
PhoneSuffix
Phone (calculated...same as FullName)
EMail
Supervisor_ID (NULL or same if it's a supervisor)

The reason you may have the situation you describe is only because of the contents of EmployeeProject. Do this:

select Project_ID, count(*) from EmployeeProject group by Project_ID
union all
select 0, count(*) from Employee

This will get you started on finding out how many employees are assigned to each project and what's the total number of employees assigned vs. the total number of employees in the organization.|||I don't have any SQL procedures that can parse that last sentence of yours, so I'm not exactly sure what the problem is.

But I have to ask why you are developing this in MS Access if you are planning to upsize it to SQL Server anyway. Have you considered creating it in SQL Server and using an Access Data Project (.adp file) front-end? You'd get all the benefits of Access forms, reports, and modules for the interface, and you wouldn't have to upsize it later. Plus SQL Server's security is much better and easier to implement than MS Access security.|||the reason for me doing it in access first is my boss maily.... hes a manufacturing engeneer and he knows nothing about servers and all that... he asked me to do it first kinda like a prototype for collecting the data... i said prior to him that i could build it in SQL save us a hole lot of time.. but he wouldnt budge... (putz)... any way i explained that particular situation ( me using access as a front end) but he was like no no Y complicate it so much... just doit in access and then we will see what to leave or what not... any ways thanx for your help...|||i actually did that... in fact i did get the results that i wanted... but also... i dropped the tables and created them again... exactly and got them as i wanted... your solution was indeed good just got to it now... if only i'd gotten to it sooner... thanx for your help u really did help me|||Exactly what i was doing... but also i don't know what in the heck was wrong with the tables... so i'd dropped'm and built them again got what i wanted... what u posted... was what i did and it worked... thanx|||Your boss is a putz. Oh wait, you already said that. Well tell him I said so too.

Asking him why he bothers hiring competent people if he isn't going to trust their expert judgement. Duh.

Many databases on one or multiple SQL2000 servers

Hi there. We currently have an application which requires separate database
s
for each "project" we work on. We are in the process of replacing our 2 SQL
2000 servers and are unsure whether to purchase 1 or 2 servers. Previously,
we implemented with 2 servers due to performance reasons. That was back on
SQL 7 though.
We have approximately 400 databases which range in size from .5GB to about
3GB. These cannot be collapsed into fewer databases. SQL2005 is not an
option at this time (gotta love software companies that don't keep up with
the times!
So, do I get 2 servers or a single (larger in processor and memory) server?
--John J. Berlo
Lend Lease Corportation
Collaboration TechnologiesHi John,
I have found that there are more variables involved in a decision such as
this. How many users, the number of concurrent queries, the design of the
queries, etc., all play a big factor in how much processing power will be
required.
The number of databases is higher than normal, but not unheard of. Our
accounting system currently requires us to operate a server with over 900
databases. I am running this on a single server without issue. The
databases, though numerous, are well designed and support our 200 users with
an acceptable level of performance. The hardware is a 4-way server with 6GB
of RAM and SQL 2000 Enterprise Edition. The disk array is an IBM SAN via 2G
B
fiber.
My guess would be that disk and RAM could be your biggest true enemy in such
an implementation. If you can buy a decent server with 6GB or so of RAM and
a good disk array with quite a few spindles, say 20, you should be able to
support up to 200 - 300 users fairly well.
Hope this helps... also remember, there are a lot of variables and this is
just my "professional" opinion based on the limited information you gave us.
Bryan
"John J. Berlo" wrote:

> Hi there. We currently have an application which requires separate databa
ses
> for each "project" we work on. We are in the process of replacing our 2 S
QL
> 2000 servers and are unsure whether to purchase 1 or 2 servers. Previousl
y,
> we implemented with 2 servers due to performance reasons. That was back o
n
> SQL 7 though.
> We have approximately 400 databases which range in size from .5GB to about
> 3GB. These cannot be collapsed into fewer databases. SQL2005 is not an
> option at this time (gotta love software companies that don't keep up with
> the times!
> So, do I get 2 servers or a single (larger in processor and memory) server
?
> --John J. Berlo
> Lend Lease Corportation
> Collaboration Technologies|||Brian,
Thanks for the information. On our current servers, we are seeing
approximately 650 concurrent users. Not an enourmous amount, but one that
concerns us whether a single server is the best way to go.
If money was no object, I would do a 2-server active-active cluster, but SQL
Enterprise is a large jump in price than standard. So, the situation as I
see it leaves us with 2 choices:
1) Move to a single 2003 Server Enterprise server with 6-8GB RAM and 2-dual
core processors
-or-
2) Stay on 2 2003 Server standard servers with 4GB RAM and a single
dual-core processor
(both with SQL standard licenses and hooked to a very robust SAN).
Thoughts?
--John J. Berlo
Lend Lease Corportation
Collaboration Technologies
"Bryan Ivie" wrote:
[vbcol=seagreen]
> Hi John,
> I have found that there are more variables involved in a decision such as
> this. How many users, the number of concurrent queries, the design of the
> queries, etc., all play a big factor in how much processing power will be
> required.
> The number of databases is higher than normal, but not unheard of. Our
> accounting system currently requires us to operate a server with over 900
> databases. I am running this on a single server without issue. The
> databases, though numerous, are well designed and support our 200 users wi
th
> an acceptable level of performance. The hardware is a 4-way server with 6
GB
> of RAM and SQL 2000 Enterprise Edition. The disk array is an IBM SAN via
2GB
> fiber.
> My guess would be that disk and RAM could be your biggest true enemy in su
ch
> an implementation. If you can buy a decent server with 6GB or so of RAM a
nd
> a good disk array with quite a few spindles, say 20, you should be able to
> support up to 200 - 300 users fairly well.
> Hope this helps... also remember, there are a lot of variables and this i
s
> just my "professional" opinion based on the limited information you gave u
s.
> Bryan
>
> "John J. Berlo" wrote:
>

Many databases on one or multiple SQL2000 servers

Hi there. We currently have an application which requires separate databases
for each "project" we work on. We are in the process of replacing our 2 SQL
2000 servers and are unsure whether to purchase 1 or 2 servers. Previously,
we implemented with 2 servers due to performance reasons. That was back on
SQL 7 though.
We have approximately 400 databases which range in size from .5GB to about
3GB. These cannot be collapsed into fewer databases. SQL2005 is not an
option at this time (gotta love software companies that don't keep up with
the times! :)
So, do I get 2 servers or a single (larger in processor and memory) server?
--John J. Berlo
Lend Lease Corportation
Collaboration TechnologiesHi John,
I have found that there are more variables involved in a decision such as
this. How many users, the number of concurrent queries, the design of the
queries, etc., all play a big factor in how much processing power will be
required.
The number of databases is higher than normal, but not unheard of. Our
accounting system currently requires us to operate a server with over 900
databases. I am running this on a single server without issue. The
databases, though numerous, are well designed and support our 200 users with
an acceptable level of performance. The hardware is a 4-way server with 6GB
of RAM and SQL 2000 Enterprise Edition. The disk array is an IBM SAN via 2GB
fiber.
My guess would be that disk and RAM could be your biggest true enemy in such
an implementation. If you can buy a decent server with 6GB or so of RAM and
a good disk array with quite a few spindles, say 20, you should be able to
support up to 200 - 300 users fairly well.
Hope this helps... also remember, there are a lot of variables and this is
just my "professional" opinion based on the limited information you gave us.
Bryan
"John J. Berlo" wrote:
> Hi there. We currently have an application which requires separate databases
> for each "project" we work on. We are in the process of replacing our 2 SQL
> 2000 servers and are unsure whether to purchase 1 or 2 servers. Previously,
> we implemented with 2 servers due to performance reasons. That was back on
> SQL 7 though.
> We have approximately 400 databases which range in size from .5GB to about
> 3GB. These cannot be collapsed into fewer databases. SQL2005 is not an
> option at this time (gotta love software companies that don't keep up with
> the times! :)
> So, do I get 2 servers or a single (larger in processor and memory) server?
> --John J. Berlo
> Lend Lease Corportation
> Collaboration Technologies|||Brian,
Thanks for the information. On our current servers, we are seeing
approximately 650 concurrent users. Not an enourmous amount, but one that
concerns us whether a single server is the best way to go.
If money was no object, I would do a 2-server active-active cluster, but SQL
Enterprise is a large jump in price than standard. So, the situation as I
see it leaves us with 2 choices:
1) Move to a single 2003 Server Enterprise server with 6-8GB RAM and 2-dual
core processors
-or-
2) Stay on 2 2003 Server standard servers with 4GB RAM and a single
dual-core processor
(both with SQL standard licenses and hooked to a very robust SAN).
Thoughts?
--John J. Berlo
Lend Lease Corportation
Collaboration Technologies
"Bryan Ivie" wrote:
> Hi John,
> I have found that there are more variables involved in a decision such as
> this. How many users, the number of concurrent queries, the design of the
> queries, etc., all play a big factor in how much processing power will be
> required.
> The number of databases is higher than normal, but not unheard of. Our
> accounting system currently requires us to operate a server with over 900
> databases. I am running this on a single server without issue. The
> databases, though numerous, are well designed and support our 200 users with
> an acceptable level of performance. The hardware is a 4-way server with 6GB
> of RAM and SQL 2000 Enterprise Edition. The disk array is an IBM SAN via 2GB
> fiber.
> My guess would be that disk and RAM could be your biggest true enemy in such
> an implementation. If you can buy a decent server with 6GB or so of RAM and
> a good disk array with quite a few spindles, say 20, you should be able to
> support up to 200 - 300 users fairly well.
> Hope this helps... also remember, there are a lot of variables and this is
> just my "professional" opinion based on the limited information you gave us.
> Bryan
>
> "John J. Berlo" wrote:
> > Hi there. We currently have an application which requires separate databases
> > for each "project" we work on. We are in the process of replacing our 2 SQL
> > 2000 servers and are unsure whether to purchase 1 or 2 servers. Previously,
> > we implemented with 2 servers due to performance reasons. That was back on
> > SQL 7 though.
> >
> > We have approximately 400 databases which range in size from .5GB to about
> > 3GB. These cannot be collapsed into fewer databases. SQL2005 is not an
> > option at this time (gotta love software companies that don't keep up with
> > the times! :)
> >
> > So, do I get 2 servers or a single (larger in processor and memory) server?
> >
> > --John J. Berlo
> > Lend Lease Corportation
> > Collaboration Technologies

Management Studio: Project Hierarchy

I created a project type "SQL Server Scripts" in "Microsoft SQL Server
Management Studio", and the default structure created was:
+ Project Name
+ Connections (Type 2)
+ Queries (Type 0)
+ Miscellaneous (Type 3)
How can I create new folders? I'd like to organize my queries by subject
(Finance, Human Resources, Marketing, etc)
I tried to edit the project file (*.ssmssqlproj) to add new Logical Folders,
but it seems those folders are based on "Type" and you cannot have multiple
folders of the same type.
<LogicalFolder Name="Queries" Type="0">
After frustrating attempts, I decided to use the existing hierarchy. I added
my existing stored procedures to the "Queries" folder and realized the files
were not in alphabetical order. It's impossible to find anything when list
contains more than a thousand queries.
Am I using the SQL MANAGEMENT STUDIO correctly? If so, what's the purpose of
creating a SQL SCRIPT PROJECT if you cannot organize the files the way you
want? Please help!!!
I posted the same question a few days ago, and I also played around with the
project file to no avial. Someone replied then that this is an outstanding
issue with Microsoft. Please go to the site, vote on the issue, and add your
voice. If we don't complain, MS isn't going to do anything about it:
http://lab.msdn.microsoft.com/produc...7-be25db5080df
My feeling is that the whole VS 2005 suite, including Management Studio and
Team System, was rushed out and shipped half cooked.
Having replaced the graceful Query Analyzer and Enterprise Manager with a
lumberous piece of bloatware (VS 2005 engine), you'd expect them to
compensate the developers/DBA's by allowing them to integrate SSMS with VS
2005 solutions or at least give them the ability to organize their projects
in a meaningful way.
"YYZRic" wrote:

> I created a project type "SQL Server Scripts" in "Microsoft SQL Server
> Management Studio", and the default structure created was:
> + Project Name
> + Connections (Type 2)
> + Queries (Type 0)
> + Miscellaneous (Type 3)
> How can I create new folders? I'd like to organize my queries by subject
> (Finance, Human Resources, Marketing, etc)
> I tried to edit the project file (*.ssmssqlproj) to add new Logical Folders,
> but it seems those folders are based on "Type" and you cannot have multiple
> folders of the same type.
> <LogicalFolder Name="Queries" Type="0">
> After frustrating attempts, I decided to use the existing hierarchy. I added
> my existing stored procedures to the "Queries" folder and realized the files
> were not in alphabetical order. It's impossible to find anything when list
> contains more than a thousand queries.
> Am I using the SQL MANAGEMENT STUDIO correctly? If so, what's the purpose of
> creating a SQL SCRIPT PROJECT if you cannot organize the files the way you
> want? Please help!!!
>
>
>
|||Thanks Buddie, I cast my vote!
"Homam" wrote:
[vbcol=seagreen]
> I posted the same question a few days ago, and I also played around with the
> project file to no avial. Someone replied then that this is an outstanding
> issue with Microsoft. Please go to the site, vote on the issue, and add your
> voice. If we don't complain, MS isn't going to do anything about it:
> http://lab.msdn.microsoft.com/produc...7-be25db5080df
> My feeling is that the whole VS 2005 suite, including Management Studio and
> Team System, was rushed out and shipped half cooked.
> Having replaced the graceful Query Analyzer and Enterprise Manager with a
> lumberous piece of bloatware (VS 2005 engine), you'd expect them to
> compensate the developers/DBA's by allowing them to integrate SSMS with VS
> 2005 solutions or at least give them the ability to organize their projects
> in a meaningful way.
>
>
> "YYZRic" wrote:

Management Studio Solution user-defined project folders?

When you create a Solution/Project in Management Studio, it creates 3 folders for you - Connections, Queries, Miscellaneous.

It does not appear that there exists the ability to create your own set of folders, either under the project or any of the 3 provided folders. Does anyone know of a way to do this?

If you are working on a project that has hundreds, perhaps thousands of stored procedures, views, etc., there currently seems to be no way to organize them. If this is true, this is an incredible MS oversight!

Thanks!

You have found the growing pain problems with the tool...maybe future versions will correct it.|||I would hope so! Since Mgmt Studio is based upon the Visual Studio shell, it doesn't seem like it would be that hard. In fact, I would think they would have had to specifically disable that functionality in this version, it seems so basic and fundamental to an IDE.|||

This is one of our most requested features. You can track the progress of this at http://connect.microsoft.com.

Here is the direct link to the issue: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124787

Vote on it and let us know that it is important to you.

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server
http://blogs.msdn.com/sqlrem/

|||

I already did. I also started my own post to this issue a long time ago.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127202

|||

Ahh, nice... even though your solution is not solved, can you mark the post as answered?

Management Studio Solution user-defined project folders?

When you create a Solution/Project in Management Studio, it creates 3 folders for you - Connections, Queries, Miscellaneous.

It does not appear that there exists the ability to create your own set of folders, either under the project or any of the 3 provided folders. Does anyone know of a way to do this?

If you are working on a project that has hundreds, perhaps thousands of stored procedures, views, etc., there currently seems to be no way to organize them. If this is true, this is an incredible MS oversight!

Thanks!

You have found the growing pain problems with the tool...maybe future versions will correct it.|||I would hope so! Since Mgmt Studio is based upon the Visual Studio shell, it doesn't seem like it would be that hard. In fact, I would think they would have had to specifically disable that functionality in this version, it seems so basic and fundamental to an IDE.|||

This is one of our most requested features. You can track the progress of this at http://connect.microsoft.com.

Here is the direct link to the issue: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124787

Vote on it and let us know that it is important to you.

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server
http://blogs.msdn.com/sqlrem/

|||

I already did. I also started my own post to this issue a long time ago.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127202

|||

Ahh, nice... even though your solution is not solved, can you mark the post as answered?

|||I agree it is a huge MS oversight... fortunately theres a 3rd party solution available... Here's the link SQL Server Management Studio 2005 Project Plugin|||I agree it is a huge MS oversight... fortunately theres a 3rd party solution available... Here's the link SQL Server Management Studio 2005 Project Plugin