Relationship Problem

Hi
Fairly new to SQL so apologies if this is really dumb.
Can anyone see what is wrong with the relationship here
(http://www.step-online.org.uk/Diagram.png)
I two many-to-many relationships which I have tried to make a
many-to-many-to-many relationship out of!
I have Individuals and Courses.
Courses can be held at Many Locations by Many Tutors - many-to-many
relationship 1 - this works fine.
Many Individuals can attend Many Courses - so I have created another join
table and tried joining it to the courses table (which is part of the
previous many-to-many join).
It works, but I get each result multiplied x 4. So if I enter that one
individual attended 1 course and run it, it gives me the result 4 times!
Making it look like he attended the same course 4 times!
Have I done something wrong here?Forgot to say - there is a 3rd many-to-many relationship involved!
An Individual can be linked to many Businesses also!
"Keith" <@..> wrote in message news:ewdyEuwFEHA.3540@.TK2MSFTNGP09.phx.gbl...
> Hi
> Fairly new to SQL so apologies if this is really dumb.
> Can anyone see what is wrong with the relationship here
> (http://www.step-online.org.uk/Diagram.png)
> I two many-to-many relationships which I have tried to make a
> many-to-many-to-many relationship out of!
> I have Individuals and Courses.
> Courses can be held at Many Locations by Many Tutors - many-to-many
> relationship 1 - this works fine.
> Many Individuals can attend Many Courses - so I have created another join
> table and tried joining it to the courses table (which is part of the
> previous many-to-many join).
> It works, but I get each result multiplied x 4. So if I enter that one
> individual attended 1 course and run it, it gives me the result 4 times!
> Making it look like he attended the same course 4 times!
> Have I done something wrong here?
>|||If a single instance of a course given at a particular time can have
multiple tutors ( say 4), I can see where you might get 4 rows.. It would be
better for you to post your query also..
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"Keith" <@..> wrote in message news:ewdyEuwFEHA.3540@.TK2MSFTNGP09.phx.gbl...
> Hi
> Fairly new to SQL so apologies if this is really dumb.
> Can anyone see what is wrong with the relationship here
> (http://www.step-online.org.uk/Diagram.png)
> I two many-to-many relationships which I have tried to make a
> many-to-many-to-many relationship out of!
> I have Individuals and Courses.
> Courses can be held at Many Locations by Many Tutors - many-to-many
> relationship 1 - this works fine.
> Many Individuals can attend Many Courses - so I have created another join
> table and tried joining it to the courses table (which is part of the
> previous many-to-many join).
> It works, but I get each result multiplied x 4. So if I enter that one
> individual attended 1 course and run it, it gives me the result 4 times!
> Making it look like he attended the same course 4 times!
> Have I done something wrong here?
>|||My tables are just full of dummy info at the moment.
2 records in every table with the exception of individual which has 3
records.
There are 3 individual-business joins, 6 course joins, and 1
individual-course join (the one that gives me 4 copies).
Simple SQL statement below (this will not be the final statement - just
testing that it worked).
Thanks
SELECT dbo.SYS_Individual.IND_First_Name,
dbo.SYS_Individual.IND_Surname, dbo.SYS_Courses.COURSE_Title
FROM dbo.SYS_Courses INNER JOIN
dbo.SYS_Xref_Join_Courses ON dbo.SYS_Courses.COURSE_ID
= dbo.SYS_Xref_Join_Courses.COURSE_ID INNER JOIN
dbo.SYS_Courses_TimeTable ON
dbo.SYS_Xref_Join_Courses.COURSE_TT_ID =
dbo.SYS_Courses_TimeTable.COURSE_TT_ID INNER JOIN
dbo.SYS_Courses_Tutors ON
dbo.SYS_Xref_Join_Courses.COURSE_TUT_ID =
dbo.SYS_Courses_Tutors.COURSE_TUT_ID INNER JOIN
dbo.SYS_Courses_Venues ON
dbo.SYS_Xref_Join_Courses.COURSE_VEN_ID =
dbo.SYS_Courses_Venues.COURSE_VEN_ID INNER JOIN
dbo.SYS_Xref_Join_Ind_Courses ON
dbo.SYS_Courses.COURSE_ID = dbo.SYS_Xref_Join_Ind_Courses.COURSE_ID INNER
JOIN
dbo.SYS_Individual ON
dbo.SYS_Xref_Join_Ind_Courses.IND_ID = dbo.SYS_Individual.IND_ID
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:O6wMiNxFEHA.576@.TK2MSFTNGP11.phx.gbl...
> If a single instance of a course given at a particular time can have
> multiple tutors ( say 4), I can see where you might get 4 rows.. It would
be
> better for you to post your query also..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.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
>
> "Keith" <@..> wrote in message
news:ewdyEuwFEHA.3540@.TK2MSFTNGP09.phx.gbl...
join
>

Rejoining two broken replicating servers

We had a two-site system go badly wrong as a result of something. The net
result of this was that the publisher had to be completely reformatted and
reinstalled. The server has, as a result of this, been given a different
name (went from being a named instance to not being one).
Currently the publisher doesn't know it's a replicating system.
Is there any way to join these two together so that they just start
from where they left off, or will I have to re-do all the replication
from scratch by various SELECT INTOs to get the data across, then
pushing out fresh subscriptions?
Thanks,
Jim
Find me at http://www.ursaMinorBeta.co.uk
JediGeeks http://www.jedigeeks.com
"There's no 'I' in team, but there is a 'me' if you jumble
the letters up a bit." - Dr. House.
Jim,
I'm hoping you have you got a copy of your replication scripts? If so, I'd
alter the server name in the scripts and then set things up from scratch by
initializing the subscribers. There's no need to do select intos - the
odbcbcp will take care of the initial data flow.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||In article <OZP8o$qvFHA.1648@.TK2MSFTNGP10.phx.gbl>, Paul Ibison wrote:
> Jim,
> I'm hoping you have you got a copy of your replication scripts? If so, I'd
> alter the server name in the scripts and then set things up from scratch by
> initializing the subscribers. There's no need to do select intos - the
> odbcbcp will take care of the initial data flow.
> Cheers,
Forgive me, but which scripts are these?
I'm a bit new at this sort of low-level tinkering :-)
Jim
Find me at http://www.ursaMinorBeta.co.uk
JediGeeks http://www.jedigeeks.com
"There's no 'I' in team, but there is a 'me' if you jumble
the letters up a bit." - Dr. House.
|||Jim,
I make regular backups of my replication setup - (see
http://www.replicationanswers.com/Script7.asp). If you don't have these,
you'll might still try running my script to see if you can generate them on
your renamed database - bit of a longshot but worth a try. Next thing to try
is to restore the database to it's original name and run hte script.
Finally, restore this database, msdb and the distribution database then run
the script. I can't test this right now, but please let me know how it goes.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Rejoining replicating servers

We had a two-site system go badly wrong as a result of something. The net
result of this was that the publisher had to be completely reformatted and
reinstalled. The server has, as a result of this, been given a different
name (went from being a named instance to not being one).

Currently the publisher doesn't know it's a replicating system.

Is there any way to join these two together so that they just start
from where they left off, or will I have to re-do all the replication
from scratch by various SELECT INTOs to get the data across, then
pushing out fresh subscriptions?

Thanks,

Jim
--
Find me at http://www.ursaMinorBeta.co.uk
JediGeeks http://www.jedigeeks.com
"There's no 'I' in team, but there is a 'me' if you jumble
the letters up a bit." - Dr. House.You might get a better response in
microsoft.public.sqlserver.replication.

Simon|||In article <1127313805.460090.79830@.g14g2000cwa.googlegroups.c om>, Simon Hayes wrote:
> You might get a better response in
> microsoft.public.sqlserver.replication.

Thanks.

Jim
--
Find me at http://www.ursaMinorBeta.co.uk
JediGeeks http://www.jedigeeks.com
"There's no 'I' in team, but there is a 'me' if you jumble
the letters up a bit." - Dr. House.

reinitialize replication

Hi All,
I select the wrong database to do merge replication. Trying to delete the
replication I delete the suscription and the publication but still
replication folder in my database and all my tables have the extra columns.
How can I delete the replication from my database so I can start all over
this the right database?
Tks in advance, rgds.
Johnny
You will have to drop ROWGUID columns manually.
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"JFB" <jfb@.newSQL.com> wrote in message
news:%23JQqZY62EHA.4072@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I select the wrong database to do merge replication. Trying to delete the
> replication I delete the suscription and the publication but still
> replication folder in my database and all my tables have the extra
columns.
> How can I delete the replication from my database so I can start all over
> this the right database?
> Tks in advance, rgds.
> Johnny
>
|||Ok, what about the Publications folder inside the database, the extra system
tables and the blue hand in the enterprise manager.
Can I delete this?
Tks for you reply
Johnny
"Kestutis Adomavicius" <kicker.lt@.nospaamm_tut.by> wrote in message
news:eT5aEc62EHA.3452@.TK2MSFTNGP14.phx.gbl...
> You will have to drop ROWGUID columns manually.
> --
> Regards,
> Kestutis Adomavicius
> Consultant
> UAB "Baltic Software Solutions"
>
> "JFB" <jfb@.newSQL.com> wrote in message
> news:%23JQqZY62EHA.4072@.TK2MSFTNGP10.phx.gbl...
> columns.
>
|||run this script in the database.
http://groups-beta.google.com/group/...a?dmode=source
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"JFB" <jfb@.newSQL.com> wrote in message
news:%23JQqZY62EHA.4072@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I select the wrong database to do merge replication. Trying to delete the
> replication I delete the suscription and the publication but still
> replication folder in my database and all my tables have the extra
> columns.
> How can I delete the replication from my database so I can start all over
> this the right database?
> Tks in advance, rgds.
> Johnny
>
|||Tks for you reply and help Hilary,
I run the script and I got this result with some errors: Can you help me to
fix this?
Rgds
Johnny
**********************************8
Server: Msg 208, Level 16, State 1, Line 3
Invalid object name 'syspublications'.
(0 row(s) affected)
(0 row(s) affected)
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'syssubscriptions'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sysarticleupdates'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'systranschemas'.
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sysarticles'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sysschemaarticles'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'syspublications'.
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'MSpub_identity_range'.
(0 row(s) affected)
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'MSreplication_subscriptions'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'MSsubscription_agents'.
Server: Msg 259, Level 16, State 2, Line 2
Ad hoc updates to system catalogs are not enabled. The system administrator
must reconfigure SQL Server to allow this.
Server: Msg 259, Level 16, State 2, Line 1
Ad hoc updates to system catalogs are not enabled. The system administrator
must reconfigure SQL Server to allow this.
Server: Msg 3701, Level 11, State 5, Line 1
Cannot drop the view 'sysextendedarticlesview', because it does not exist in
the system catalog.
Server: Msg 259, Level 16, State 2, Line 1
Ad hoc updates to system catalogs are not enabled. The system administrator
must reconfigure SQL Server to allow this.
dropping rowguid constraints MSmerge_delete_conflicts
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OYi$Rg82EHA.2112@.TK2MSFTNGP15.phx.gbl...
> run this script in the database.
> http://groups-beta.google.com/group/...a?dmode=source
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
> "JFB" <jfb@.newSQL.com> wrote in message
> news:%23JQqZY62EHA.4072@.TK2MSFTNGP10.phx.gbl...
>