Duplicate FK Relationships - Help pl

I came across a situation in couple of the enterprise application projects, where I found a strange fact in the MS SQL Server Database they used.

I found multiple foreign-key(FK) relationships existing between tables. When examining the table design, I found that same relationship existed thrice with 3 different FK constraint names.

There are tables with single primary-key(PK) and few others with composite-PKs. As there are tables linked through relationships, will it matter that tables with composite-PKs will involve redundant FK relations with other tables?

I am puzzled as how redundant FK relationships could have got created. Is this purely human error or anythign with respect to MS SQL Server?

Please explain the probable cause for this scenario.

Thanks,

Ganesh

MS SQL does not create anything automatically. If FKs are there, it is because some human or a code generator created it.

You didn't say what version you were using, but there were weird problems, which were fixed early in 2000, with the "Diagrams" feature which might have done stuff like that, but I don't know anyone who uses the diagramming in MS SQL so I would be surprised if that was the cause.

|||

Hi Tom,

Thanks for the reply.

I use MS SQL Server 2000 version.

The other project used previous version of MS SQL Server.

In both these projects, the data base design was migrated from another source. I doubt this migration of design is the main cause. But still, the same question remains for the 1st database from where the design was migrated: "Does the initial database source had this duplication because of humar error or something else"?

NOTE: The diagram tool in MS SQL Server is perfect in this aspect, as I checked the design of tables from Query Analyzer - where there were duplicate FK replationships (different constraint-names) for the same column mapping. No database programmer intentionally creates multiple FK constraints on same column mapping. Then, what behind this?

Please check the database of your projects and come back if you too find the same. Also think of the cause for this.

|||There is nothing restricting the use of multiple FKs on the same fields to the same tables, with different constraint names. Yes, it is redundant and will hurt performance and should not be done.

Still, either some human did this, accidentally or for some unknown purpose, or a code generator did it. Most likely a code generator created them.

In any case, they should be removed.
|||Thanks Phillips.

dual processor benefit

Currently running SQL 2K on a W2K sp3 box with one P3 1.2 processor.
Current db size is 62 gigabytes.
Our situation: We use Meditech hospital software which has it's own db
structure (mumps). Nightly, most of the data that is entered into the
"live" system is replicated to the SQL database. We recently upgraded the
Meditech software and now their SQL consultant is telling us that we should
be installing a second processor because their initial load program (in
which the program compares data in the Meditech system and the SQL to verify
it's all there) is taxing the CPU at >40%.
My question: Does SQL 2000 effectively utilize a second CPU or is it, as we
believe, something in the newer Meditech software that's causing the
problem?
Hi,
To directly answer your question: Yes SQL Server can utilize the processors
effectively.
But, CPU usage aprx. 40% is nothing, unless it is causing other issues on
the server. And if there were no other changes made to the server, its the
application that is causing the extra load.
hth
DeeJay Puar
"pdwight" wrote:

> Currently running SQL 2K on a W2K sp3 box with one P3 1.2 processor.
> Current db size is 62 gigabytes.
> Our situation: We use Meditech hospital software which has it's own db
> structure (mumps). Nightly, most of the data that is entered into the
> "live" system is replicated to the SQL database. We recently upgraded the
> Meditech software and now their SQL consultant is telling us that we should
> be installing a second processor because their initial load program (in
> which the program compares data in the Meditech system and the SQL to verify
> it's all there) is taxing the CPU at >40%.
> My question: Does SQL 2000 effectively utilize a second CPU or is it, as we
> believe, something in the newer Meditech software that's causing the
> problem?
>
>
|||40% proc utilization is not high... If the batch + users run it up, you
may wish to get another processor... One of the big reasons to go multi proc
is to allow SQL to service 2 connections concurrently, and therefore no
single long running thing will block the processor queue...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"pdwight" <paulc@.mmcwm.com> wrote in message
news:OE6ogW$KFHA.2952@.TK2MSFTNGP10.phx.gbl...
> Currently running SQL 2K on a W2K sp3 box with one P3 1.2 processor.
> Current db size is 62 gigabytes.
> Our situation: We use Meditech hospital software which has it's own db
> structure (mumps). Nightly, most of the data that is entered into the
> "live" system is replicated to the SQL database. We recently upgraded the
> Meditech software and now their SQL consultant is telling us that we
> should be installing a second processor because their initial load program
> (in which the program compares data in the Meditech system and the SQL to
> verify it's all there) is taxing the CPU at >40%.
> My question: Does SQL 2000 effectively utilize a second CPU or is it, as
> we believe, something in the newer Meditech software that's causing the
> problem?
>

dual processor benefit

Currently running SQL 2K on a W2K sp3 box with one P3 1.2 processor.
Current db size is 62 gigabytes.
Our situation: We use Meditech hospital software which has it's own db
structure (mumps). Nightly, most of the data that is entered into the
"live" system is replicated to the SQL database. We recently upgraded the
Meditech software and now their SQL consultant is telling us that we should
be installing a second processor because their initial load program (in
which the program compares data in the Meditech system and the SQL to verify
it's all there) is taxing the CPU at >40%.
My question: Does SQL 2000 effectively utilize a second CPU or is it, as we
believe, something in the newer Meditech software that's causing the
problem?Hi,
To directly answer your question: Yes SQL Server can utilize the processors
effectively.
But, CPU usage aprx. 40% is nothing, unless it is causing other issues on
the server. And if there were no other changes made to the server, its the
application that is causing the extra load.
hth
DeeJay Puar
"pdwight" wrote:

> Currently running SQL 2K on a W2K sp3 box with one P3 1.2 processor.
> Current db size is 62 gigabytes.
> Our situation: We use Meditech hospital software which has it's own db
> structure (mumps). Nightly, most of the data that is entered into the
> "live" system is replicated to the SQL database. We recently upgraded the
> Meditech software and now their SQL consultant is telling us that we shoul
d
> be installing a second processor because their initial load program (in
> which the program compares data in the Meditech system and the SQL to veri
fy
> it's all there) is taxing the CPU at >40%.
> My question: Does SQL 2000 effectively utilize a second CPU or is it, as
we
> believe, something in the newer Meditech software that's causing the
> problem?
>
>|||40% proc utilization is not high... If the batch + users run it up, you
may wish to get another processor... One of the big reasons to go multi proc
is to allow SQL to service 2 connections concurrently, and therefore no
single long running thing will block the processor queue...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"pdwight" <paulc@.mmcwm.com> wrote in message
news:OE6ogW$KFHA.2952@.TK2MSFTNGP10.phx.gbl...
> Currently running SQL 2K on a W2K sp3 box with one P3 1.2 processor.
> Current db size is 62 gigabytes.
> Our situation: We use Meditech hospital software which has it's own db
> structure (mumps). Nightly, most of the data that is entered into the
> "live" system is replicated to the SQL database. We recently upgraded the
> Meditech software and now their SQL consultant is telling us that we
> should be installing a second processor because their initial load program
> (in which the program compares data in the Meditech system and the SQL to
> verify it's all there) is taxing the CPU at >40%.
> My question: Does SQL 2000 effectively utilize a second CPU or is it, as
> we believe, something in the newer Meditech software that's causing the
> problem?
>

dual processor benefit

Currently running SQL 2K on a W2K sp3 box with one P3 1.2 processor.
Current db size is 62 gigabytes.
Our situation: We use Meditech hospital software which has it's own db
structure (mumps). Nightly, most of the data that is entered into the
"live" system is replicated to the SQL database. We recently upgraded the
Meditech software and now their SQL consultant is telling us that we should
be installing a second processor because their initial load program (in
which the program compares data in the Meditech system and the SQL to verify
it's all there) is taxing the CPU at >40%.
My question: Does SQL 2000 effectively utilize a second CPU or is it, as we
believe, something in the newer Meditech software that's causing the
problem?Hi,
To directly answer your question: Yes SQL Server can utilize the processors
effectively.
But, CPU usage aprx. 40% is nothing, unless it is causing other issues on
the server. And if there were no other changes made to the server, its the
application that is causing the extra load.
hth
DeeJay Puar
"pdwight" wrote:
> Currently running SQL 2K on a W2K sp3 box with one P3 1.2 processor.
> Current db size is 62 gigabytes.
> Our situation: We use Meditech hospital software which has it's own db
> structure (mumps). Nightly, most of the data that is entered into the
> "live" system is replicated to the SQL database. We recently upgraded the
> Meditech software and now their SQL consultant is telling us that we should
> be installing a second processor because their initial load program (in
> which the program compares data in the Meditech system and the SQL to verify
> it's all there) is taxing the CPU at >40%.
> My question: Does SQL 2000 effectively utilize a second CPU or is it, as we
> believe, something in the newer Meditech software that's causing the
> problem?
>
>|||40% proc utilization is not high... If the batch + users run it up, you
may wish to get another processor... One of the big reasons to go multi proc
is to allow SQL to service 2 connections concurrently, and therefore no
single long running thing will block the processor queue...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"pdwight" <paulc@.mmcwm.com> wrote in message
news:OE6ogW$KFHA.2952@.TK2MSFTNGP10.phx.gbl...
> Currently running SQL 2K on a W2K sp3 box with one P3 1.2 processor.
> Current db size is 62 gigabytes.
> Our situation: We use Meditech hospital software which has it's own db
> structure (mumps). Nightly, most of the data that is entered into the
> "live" system is replicated to the SQL database. We recently upgraded the
> Meditech software and now their SQL consultant is telling us that we
> should be installing a second processor because their initial load program
> (in which the program compares data in the Meditech system and the SQL to
> verify it's all there) is taxing the CPU at >40%.
> My question: Does SQL 2000 effectively utilize a second CPU or is it, as
> we believe, something in the newer Meditech software that's causing the
> problem?
>

dtsx with config file?

Hi all

This is the situation: I've a dtsx package wich creates a tab delimited txt file from a sql server 2005 databasetable. Now this all works just fine. But what I want to do is that the user can choose the destination path of that created txt-file. Right now I've declared the path when I created the flat file source.

On the net I found that a config file for a dts package (sql server 2000 - Dynamic Property Task) could do the trick...

Any help plz?

thx!

There are a number of ways of doing this. You could use a configuration file but that would mean editing the configuration file each time and I don't think that's what you want to do.

You could change the value using the /SET option of dtexec.exe. Will that work for you?

-Jamie