Circular relationship

[Lee Newson]

Hi,

We have an issue with one of our schedules in project server. On of our
users came over yesterday and said that they were having difficulty opening
one of their schedules.

It would appear that somehow they have managed to create a circular
relationship, and when trying to open the schedule project tells you this,
and that to fix it select the two tasks involved and unlink them. the only
problem is that although this is a 300+ task schedule, whne you click on ok
the schedule apparently opens but there is no data in it...

has anyone had this problem? we know that the data is there as we have a
program that queries the database via an sql connection and the data gets
returned to us in xml.

does anyone know a way around this??

I have also tried using the PDS using the PDSTest.Net visual studio project
that ships with the pj11PDSref.chm. this returns an error of 9578 in the
returned status value.

if you need anymore information please ask as this is an important schedule
and we need to get it back.

cheers for any help

Lee

 

[AndrewZA]

I am having this exact problem does anyone have a solution. The Project is
viewable via PWA but not through MS Project.

 

[Lee Newson]

This problem has spread to 2 of our schedules now and i have raised a case
with Microsoft... hopefully they will be able to solve it.

 

[Earl Lewis]

Lee,

There is a table in the SQL db called MSP_VIEW_PROJ_PRED that holds all the task predecessors. If you can get a look inside this table you should be able to find the offending tasks because TaskUniqueID will be equal to PredecessorTaskUniqueID.

There are two project ID fields in this table - one is WPROJ_ID which is used for all the web views and ProjectUniqueID which is used to relate back to the MSP_PROJECTS and MSP_TASKS (although their project ID field is called PROJ_ID for whatever reason).

The reason this is important is that if you delete based on the wrong ID you won't fix the project you think your fixing . In fact, if there are no circular references in the targeted project you won't fix anything at all.

Anyway, once you know which project your going after you can execute this query:

DELETE
FROM MSP_VIEW_PROJ_PRED
WHERE WPROJ_ID = [Put your project ID here]
AND TaskUniqueID = PredecessorTaskUniqueID

and this will remove the offending records from the database.

If you or your DBA have access to SQL Enterprise Manager you can create the following stored procedure on your database:

/* Delete circular predecessor task references */
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE dbo.CUSTOM_DeleteCircularTaskRefs
@p0 INT
AS

DELETE
FROM MSP_VIEW_PROJ_PRED
WHERE WPROJ_ID = @p0
AND TaskUniqueID = PredecessorTaskUniqueID

RETURN(0)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/* end stored procedure */

and then call it like this:

EXEC CUSTOM_DeleteCirculatTaskRefs 123

This means: execute the procedure called CUSTOM_DeleteCirculatTaskRefs and give it project ID 123 to work with.

Either of these methods should remove the offending records and you should be able to open that project.

The bigger issue is how are these getting in there to begin with. Someone somewhere is doing something funky. Project Pro issues a warning and does not accept a circular reference for a predecessor. This would imply that these things are happening via direct edits on the database backend - which is a no-no unless you know exactly what the results are going to be.

See if you can give this a try and let us know how you fare. Good luck.

Earl

This problem has spread to 2 of our schedules now and i have raised a case
with Microsoft... hopefully they will be able to solve it.

 

[Lee Newson]

Hi Earl,

Cheers for your help, however this has not solved the problem. before i
started deleteing anything i did a select for the "problem" projects but
nothing was returned, then i tried doing a straight forward query like this:

SELECT * FROM MSP_VIEW_PROJ_PRED
WHERE TaskUniqueID = PredecessorTaskUniqueID

and just incase i also tried this in the MSP_VIEW_PROJ_SUCC.

both queries returned nothing, suggesting that there are no circular
relationships.

I also tried the same in the MSP_LINKS table (which i assume is the linking
table for project views) and again got nothing.

Apart from that, the second schedule that we are having this problem with
states that the circular relationship is in a seperate schedule for a task
that does not even exist in that schedule - e.g. when opening schedule A the
error message says that there is a circular relationship in schedule B for
task 46, when schedule B only has 40 tasks.

You are correct in saying that it is very wierd for this to be happening,
and i can guarantee that only a couple of members in our IT department have
access to this database besides myself, so the every day users are not doing
anything stupid - which has to be a first. Also you are correct in saying
that proj2003 wont let you enter a circular relationship... i know i have
tried many times, in many different ways to try and recreate the problem...

due to this i have raised a support case with Microsoft as i have had just
about enough trying to sort it out myself now. i only hope that they can
find the problem and solve it!!

Thanks again for your help - i will keep a note of this solution/fix just in
case it proves useful in the future.

Cheers

Lee

Lee,

There is a table in the SQL db called MSP_VIEW_PROJ_PRED that holds all

the task predecessors. If you can get a look inside this table you should be
able to find the offending tasks because TaskUniqueID will be equal to
PredecessorTaskUniqueID.

There are two project ID fields in this table - one is WPROJ_ID which is

used for all the web views and ProjectUniqueID which is used to relate back
to the MSP_PROJECTS and MSP_TASKS (although their project ID field is called
PROJ_ID for whatever reason).

The reason this is important is that if you delete based on the wrong ID

you won't fix the project you think your fixing . In fact, if there are no
circular references in the targeted project you won't fix anything at all.

Anyway, once you know which project your going after you can execute this query:

DELETE
FROM MSP_VIEW_PROJ_PRED
WHERE WPROJ_ID = [Put your project ID here]
AND TaskUniqueID = PredecessorTaskUniqueID

and this will remove the offending records from the database.

If you or your DBA have access to SQL Enterprise Manager you can create

the following stored procedure on your database:

/* Delete circular predecessor task references */
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE dbo.CUSTOM_DeleteCircularTaskRefs
@p0 INT
AS

DELETE
FROM MSP_VIEW_PROJ_PRED
WHERE WPROJ_ID = @p0
AND TaskUniqueID = PredecessorTaskUniqueID

RETURN(0)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/* end stored procedure */

and then call it like this:

EXEC CUSTOM_DeleteCirculatTaskRefs 123

This means: execute the procedure called CUSTOM_DeleteCirculatTaskRefs and

give it project ID 123 to work with.

Either of these methods should remove the offending records and you should be able to open that project.

The bigger issue is how are these getting in there to begin with. Someone

somewhere is doing something funky. Project Pro issues a warning and does
not accept a circular reference for a predecessor. This would imply that
these things are happening via direct edits on the database backend - which
is a no-no unless you know exactly what the results are going to be.

 

[Sandy]

Lee - Was Microsoft or you able to resolve this? I started receiving this on
a plan last week and then it disappeared (no tasks visible in Project Pro) so
I restored it from a database backup. The error is back and the project
tasks disappeared again. I'm looking for what I need to do next to resolve
this. Any suggestions would be appreciated. Sandy

Hi Earl,

Cheers for your help, however this has not solved the problem. before i
started deleteing anything i did a select for the "problem" projects but
nothing was returned, then i tried doing a straight forward query like this:

SELECT * FROM MSP_VIEW_PROJ_PRED
WHERE TaskUniqueID = PredecessorTaskUniqueID

and just incase i also tried this in the MSP_VIEW_PROJ_SUCC.

both queries returned nothing, suggesting that there are no circular
relationships.

I also tried the same in the MSP_LINKS table (which i assume is the linking
table for project views) and again got nothing.

Apart from that, the second schedule that we are having this problem with
states that the circular relationship is in a seperate schedule for a task
that does not even exist in that schedule - e.g. when opening schedule A the
error message says that there is a circular relationship in schedule B for
task 46, when schedule B only has 40 tasks.

You are correct in saying that it is very wierd for this to be happening,
and i can guarantee that only a couple of members in our IT department have
access to this database besides myself, so the every day users are not doing
anything stupid - which has to be a first. Also you are correct in saying
that proj2003 wont let you enter a circular relationship... i know i have
tried many times, in many different ways to try and recreate the problem...

due to this i have raised a support case with Microsoft as i have had just
about enough trying to sort it out myself now. i only hope that they can
find the problem and solve it!!

Thanks again for your help - i will keep a note of this solution/fix just in
case it proves useful in the future.

Cheers

Lee

Lee,

There is a table in the SQL db called MSP_VIEW_PROJ_PRED that holds all

the task predecessors. If you can get a look inside this table you should be
able to find the offending tasks because TaskUniqueID will be equal to
PredecessorTaskUniqueID.

There are two project ID fields in this table - one is WPROJ_ID which is

used for all the web views and ProjectUniqueID which is used to relate back
to the MSP_PROJECTS and MSP_TASKS (although their project ID field is called
PROJ_ID for whatever reason).

The reason this is important is that if you delete based on the wrong ID

you won't fix the project you think your fixing . In fact, if there are no
circular references in the targeted project you won't fix anything at all.

Anyway, once you know which project your going after you can execute this query:

DELETE
FROM MSP_VIEW_PROJ_PRED
WHERE WPROJ_ID = [Put your project ID here]
AND TaskUniqueID = PredecessorTaskUniqueID

and this will remove the offending records from the database.

If you or your DBA have access to SQL Enterprise Manager you can create

the following stored procedure on your database:

/* Delete circular predecessor task references */
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE dbo.CUSTOM_DeleteCircularTaskRefs
@p0 INT
AS

DELETE
FROM MSP_VIEW_PROJ_PRED
WHERE WPROJ_ID = @p0
AND TaskUniqueID = PredecessorTaskUniqueID

RETURN(0)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/* end stored procedure */

and then call it like this:

EXEC CUSTOM_DeleteCirculatTaskRefs 123

This means: execute the procedure called CUSTOM_DeleteCirculatTaskRefs and

give it project ID 123 to work with.

Either of these methods should remove the offending records and you should be able to open that project.

The bigger issue is how are these getting in there to begin with. Someone

somewhere is doing something funky. Project Pro issues a warning and does
not accept a circular reference for a predecessor. This would imply that
these things are happening via direct edits on the database backend - which
is a no-no unless you know exactly what the results are going to be.

See if you can give this a try and let us know how you fare. Good luck.

Earl


This problem has spread to 2 of our schedules now and i have raised a case
with Microsoft... hopefully they will be able to solve it.


on have

 

[Mark Carde]

What was inferred earlied is not actually correct this can happen by dragging
and dropping a task (especially a summary task) to another place in the plan
or sometimes by copying a whole row and pasting it somewhere else in the plan.

Here are a few methods that I found a while back on Microsoft's web site
that may help.

Method 1: Turn off Calculations

In some cases, a "bad" calculation may cause the file to stop responding. To
prevent Microsoft Project from performing calculations when it opens a file,
follow these steps:
1. Start Microsoft Project as usual, to a blank project (Project1).
2. On the Tools menu, click Options.
3. On the Calculation tab, under Calculation options for Microsoft Project,
click Manual, and then click OK.
4. On the File menu, click Open. Locate and select the project that you want
to open, and then click Open.
Method 2: Move the File

If your file is stored on a network server or floppy disk, use Windows
Explorer to copy the file to the desktop on your local computer. If the file
is already on your local computer, copy it to another computer.

If you are able to open the file successfully from the new location, you can
still repair any remaining corruption by using the steps in the "How to
Repair a Corrupted File" section of this article.

Method 3: Insert the Project into a New Project

In some cases, the default view or the view that was active during the last
save operation may be corrupted, which will prevent Microsoft Project from
opening the file. To open the project in a new view, follow these steps:
1. Start Microsoft Project as usual, to a blank project (Project1).
2. On the Insert menu, click Project.
3. In the Insert Project dialog box, locate and select the damaged project,
and then click to clear the Link to project check box.
4. Click Insert.

Microsoft Project inserts the file as new data in a new project.

1. On the File menu, click Save As.
2. In the Save as type list, click XML Format (*.xml).
3. In the Save in list, select the location in which you want to save the
project. In the File name box, type the name of the project.
4. Click Save. On the File menu, click Close.
5. On the File menu, click Open. Locate and select the .xml file that you
saved in step 4, and then click Open.

Lee - Was Microsoft or you able to resolve this? I started receiving this on
a plan last week and then it disappeared (no tasks visible in Project Pro) so
I restored it from a database backup. The error is back and the project
tasks disappeared again. I'm looking for what I need to do next to resolve
this. Any suggestions would be appreciated. Sandy

Hi Earl,

Cheers for your help, however this has not solved the problem. before i
started deleteing anything i did a select for the "problem" projects but
nothing was returned, then i tried doing a straight forward query like this:

SELECT * FROM MSP_VIEW_PROJ_PRED
WHERE TaskUniqueID = PredecessorTaskUniqueID

and just incase i also tried this in the MSP_VIEW_PROJ_SUCC.

both queries returned nothing, suggesting that there are no circular
relationships.

I also tried the same in the MSP_LINKS table (which i assume is the linking
table for project views) and again got nothing.

Apart from that, the second schedule that we are having this problem with
states that the circular relationship is in a seperate schedule for a task
that does not even exist in that schedule - e.g. when opening schedule A the
error message says that there is a circular relationship in schedule B for
task 46, when schedule B only has 40 tasks.

You are correct in saying that it is very wierd for this to be happening,
and i can guarantee that only a couple of members in our IT department have
access to this database besides myself, so the every day users are not doing
anything stupid - which has to be a first. Also you are correct in saying
that proj2003 wont let you enter a circular relationship... i know i have
tried many times, in many different ways to try and recreate the problem...

due to this i have raised a support case with Microsoft as i have had just
about enough trying to sort it out myself now. i only hope that they can
find the problem and solve it!!

Thanks again for your help - i will keep a note of this solution/fix just in
case it proves useful in the future.

Cheers

Lee

Lee,

There is a table in the SQL db called MSP_VIEW_PROJ_PRED that holds all

the task predecessors. If you can get a look inside this table you should be
able to find the offending tasks because TaskUniqueID will be equal to
PredecessorTaskUniqueID.

There are two project ID fields in this table - one is WPROJ_ID which is

used for all the web views and ProjectUniqueID which is used to relate back
to the MSP_PROJECTS and MSP_TASKS (although their project ID field is called
PROJ_ID for whatever reason).

The reason this is important is that if you delete based on the wrong ID

you won't fix the project you think your fixing . In fact, if there are no
circular references in the targeted project you won't fix anything at all.

Anyway, once you know which project your going after you can execute this query:

DELETE
FROM MSP_VIEW_PROJ_PRED
WHERE WPROJ_ID = [Put your project ID here]
AND TaskUniqueID = PredecessorTaskUniqueID

and this will remove the offending records from the database.

If you or your DBA have access to SQL Enterprise Manager you can create

the following stored procedure on your database:

/* Delete circular predecessor task references */
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE dbo.CUSTOM_DeleteCircularTaskRefs
@p0 INT
AS

DELETE
FROM MSP_VIEW_PROJ_PRED
WHERE WPROJ_ID = @p0
AND TaskUniqueID = PredecessorTaskUniqueID

RETURN(0)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/* end stored procedure */

and then call it like this:

EXEC CUSTOM_DeleteCirculatTaskRefs 123

This means: execute the procedure called CUSTOM_DeleteCirculatTaskRefs and

give it project ID 123 to work with.

Either of these methods should remove the offending records and you should be able to open that project.

The bigger issue is how are these getting in there to begin with. Someone

somewhere is doing something funky. Project Pro issues a warning and does
not accept a circular reference for a predecessor. This would imply that
these things are happening via direct edits on the database backend - which
is a no-no unless you know exactly what the results are going to be.

See if you can give this a try and let us know how you fare. Good luck.

Earl


Lee Newson<[email protected]> 1/25/2005 7:39:00 AM >>>
This problem has spread to 2 of our schedules now and i have raised a case
with Microsoft... hopefully they will be able to solve it.


I am having this exact problem does anyone have a solution. The Project is
viewable via PWA but not through MS Project.

:

Hi,

We have an issue with one of our schedules in project server. On of our
users came over yesterday and said that they were having difficulty
opening
one of their schedules.

It would appear that somehow they have managed to create a circular
relationship, and when trying to open the schedule project tells you
this,
and that to fix it select the two tasks involved and unlink them. the
only
problem is that although this is a 300+ task schedule, whne you click on
ok
the schedule apparently opens but there is no data in it...

has anyone had this problem? we know that the data is there as we have
a
program that queries the database via an sql connection and the data
gets
returned to us in xml.

does anyone know a way around this??

I have also tried using the PDS using the PDSTest.Net visual studio
project
that ships with the pj11PDSref.chm. this returns an error of 9578 in
the
returned status value.

if you need anymore information please ask as this is an important
schedule
and we need to get it back.

cheers for any help

Lee