SQL Report from Server not cleaning up.

[Eric_H]

Ok, got a new issue... We are using delverables to identify tasks that each
of our organizations are responsible for. To do this we created a SQL query
on our published DB and poll it for the deliverables and create a spreadsheet
of this data. We have a number of deliverables that appear on the report
AFTER they have been deleted. In most cases they were deleted from the
schedule and in one case the schedule was deleted and the sharepoint sites
associated with it was deleted as well. They still show upon the report but
ot in the schedules or the work space. Is this a failure of my DB not
cleaning properly? how can I check and what can I do?

P.S. Using MSPS 2007 SP1 and Project 2007 SP1 and OCT hot fix.

Thanks in advance.

 

[Marc Soester [MVP]]

Eric_H, I dont quite know why you have this issue. What I do know is that
querieng the published DB this way is not supported by Microsoft.

Anyway, the first thing I would invertigate is the queue and event logs (
always my first point of call). Often you will see an error that gives you a
clue to why some information is not clean.

Hope this helps

 

[Eric_H]

Where else can I query the delverables? I need to generate a report that
contains all of the deliverables at once, and can't possibly do it from each
individual workspace because I am talking about hundreds of schedules... Any
Reco would be appreciated but I caouldn't find the deliverables anywhere else
except the pub DB.

As for the logs, I haven't noticed anything, but maybe not looking in the
right place... Suggest anything specific to look for???

Thanks!

 

[Marc Soester [MVP]]

Hi Eric,

The way we report against Deliverables via the workspaces. We developed a
"Reporting DB" for SharePoint ( essentially replicating the Project Server DB
with SharePoint Data) which allows us to report any item in your workspace,
which also includes Deliverables.

If you want to report against the Publish DB from Project Server, you will
need to use the PSI ( Project Server Interface - essetnially web services)
which allows you to get information out of the Publish DB. I know that is
compersome and it is a little frustrating that Deliverables do not appear in
the Reporting DB, but at least Microsoft does give you the option to report
against it. You may want to check out the Project Server SDK which can be
found on the Microsoft page. There are some very good examples of the PSI.

I hope this helps

 

[Eric_H]

Could you give me details on creating that DB you speak of???
Also we have installed PSI but my techie has been having problems getting it
to work, I am unsure of all of the details but I beleive there may be some
trust issue somewhere..

 

[Eric_H]

ok, I was getting an error: "User Synchronization for Project Web Access App
Root Site and Project WSS Workspace" fail with the Job State error of "Failed
But Not Blocking Correlation" so I am assuming this was prohibiting the DB
associated with the workspace from completely sync'ing... is this likely? and
if so, now can you recco a way to clean the residual erronous data from my DB?

 

[Eric_H]

One update, I was incorrect in that we were polling the reporting DB. we are
in fact polling the wss_content... I need to clean the old data

 

[Marc Soester [MVP]]

Hi Eric,

The sharepoint reporting database is an internal development peace we did,
which allows us to report against sharepoint data directly from the database.
The report development is much more rapit when we report directly of the
database, hense the reason for the development we did.

Hope this helps