04 Oct Problems with Status Reports

<dry again>

Status reports have been around since Project Server 2003 (or even in 2002? – I can’t remember), but I do know that I’ve had various problems with them in 2007, but haven’t been able to specifically reproduce them in order to get a bug raised with Microsoft.

My specific scenario was that when Status Reports where deleted users were still getting reminders to send the reports; in addition other Status Reports were successfully sent but actually never received by the Project Manager!  Both of these issues started after a few false starts with Status Reports, we’d created quite a few for testing purposes and then deleted them. 

As part of the troubleshooting process I started delving into the published database and discovered that deleted status reports are not deleted, but marked as disabled.  In order to clean up the Status Reports I decided to clean up the database using some SQL scripts. 

Here’s the caveat – I’m no SQL guy, so though this worked for me there are no guarantees it’ll work for you.  Also it’s a semi manual task involving two separate steps; someone smarter than me can probably create a single script that does the same job – if you can – I’d like a copy.

The 1st stage finds all of the records that need deleting; the 2nd stage deletes them.

1st stage

The 1st thing to do is to select the published database and then get the UID for the Status Reports that are set to False (i.e., deleted ones) – my DB is called project_live_published

— define the database ** yours will be different**
use project_live_published

— get the SR_UID from SR_Reports that have been deleted
select SR_UID from dbo.MSP_SR_REPORTS where SR_IS_ENABLED = ‘False’ 

Use this output to as part of the next select statement to find all of the Response UIDs (SR_RESP_UID) from the SR_RESPONSES database.

— get the SR_RESP_UID for the “deleted” reports
select SR_UID, SR_RESP_UID from dbo.MSP_SR_RESPONSES where SR_UID in
(‘EEBF89E1-6E74-4B88-9CE6-3B7F93EEAC62’ ,
‘379BA18E-E98E-4FD8-AC83-5D8BF8ABF04A’ ,
’87BF5F35-843E-43FC-9EF6-9BB542E8677A’ ,
‘7332150B-69AD-446A-A946-C08DFC57D3D9’) order by SR_UID, SR_RESP_UID

The output from both select statements is then used to select the rows that need deleting in the database.

2nd stage

The following tables are effected;

SR_DISTRIBUTION; SR_SECTIONS; SR_RESPONSES; SR_REQUESTS; SR_REQUENCIES; SR_REPORTS

My SQL script looked like this.

— define the database
use project_live_published

— delete the records from SR_DISTRIBUTION
delete from dbo.MSP_SR_DISTRIBUTION where SR_RESP_UID in
(‘225E71BD-C639-441B-8FD8-1983DBBD15D7’,
‘086D6E61-EF43-40A8-AF4F-47982373FB88’,
‘A6EC490C-55EA-4145-9508-59D6749CE29F’,
‘D5CEEBF8-17DD-4D58-9E58-5B3587269620’,
‘8D77214C-CA97-4FB1-9251-5BFFDD25B775′,
’17F9C2DA-69B5-45AD-9338-66624E6A79E8’,
‘D2138BA6-3808-434F-A3EA-7BE1BF5AE028’,
‘43185E36-0459-4BC6-93D1-88FF9E2E2D7D’,
’77BD3939-A2D1-4F2D-97DE-9CC99D69C0CA’,
‘87437EA6-6576-4AF7-8EAC-A9009DD8F217’,
‘5082CCDE-4683-4356-BE5C-DE4F87A5DAB1’,
‘25061369-DD4F-4C22-A8F3-DFA4F5FAEA50’,
‘AF777558-241C-488B-B951-E8A3C1D04FC1’,
‘C1186BED-CD1B-4F4E-BD75-EE349BCBF0CE’,
‘1656301F-2463-4900-994D-F3621C051754’,
‘D82E51E9-A89A-41AF-86E1-0C0071A06647’,
‘B625269B-A1D6-4353-99FB-11D499AA80CB’,
‘AAB7359A-92DC-4416-9048-350C48BCFBC2’,
‘3138136B-1427-41BE-A052-3AAD97C273AD’,
’49BB243C-0D97-40F0-AF54-545813FC5CC2′,
‘3CB7EE4D-3A94-4090-A809-74317AC42A29’,
‘3F61FB9E-FAE8-418C-BF22-779ACD71CE1E’,
‘DC628EC6-01D4-4039-A1C1-AAE4737E27FD’,
‘220CF8EF-6D55-4F6F-869E-AFAE68CF067D’,
‘F9A3CDF0-A532-44C1-AA30-B10354890A9F’,
‘39238C2C-F9FA-49F6-BA1C-CB1F8B1EB998′,
’65BD1BC4-B386-4E1E-AE39-DF81FDA61397’,
‘F04C0A57-C166-43B9-BA00-EB7251074764′,
’99B8B405-9153-4C36-8A16-AE35C216A224’
)

— delete the records from SR_SECTIONS
delete from dbo.MSP_SR_SECTIONS where SR_RESP_UID in
(‘225E71BD-C639-441B-8FD8-1983DBBD15D7’,
‘086D6E61-EF43-40A8-AF4F-47982373FB88’,
‘A6EC490C-55EA-4145-9508-59D6749CE29F’,
‘D5CEEBF8-17DD-4D58-9E58-5B3587269620’,
‘8D77214C-CA97-4FB1-9251-5BFFDD25B775′,
’17F9C2DA-69B5-45AD-9338-66624E6A79E8’,
‘D2138BA6-3808-434F-A3EA-7BE1BF5AE028’,
‘43185E36-0459-4BC6-93D1-88FF9E2E2D7D’,
’77BD3939-A2D1-4F2D-97DE-9CC99D69C0CA’,
‘87437EA6-6576-4AF7-8EAC-A9009DD8F217’,
‘5082CCDE-4683-4356-BE5C-DE4F87A5DAB1’,
‘25061369-DD4F-4C22-A8F3-DFA4F5FAEA50’,
‘AF777558-241C-488B-B951-E8A3C1D04FC1’,
‘C1186BED-CD1B-4F4E-BD75-EE349BCBF0CE’,
‘1656301F-2463-4900-994D-F3621C051754’,
‘D82E51E9-A89A-41AF-86E1-0C0071A06647’,
‘B625269B-A1D6-4353-99FB-11D499AA80CB’,
‘AAB7359A-92DC-4416-9048-350C48BCFBC2’,
‘3138136B-1427-41BE-A052-3AAD97C273AD’,
’49BB243C-0D97-40F0-AF54-545813FC5CC2′,
‘3CB7EE4D-3A94-4090-A809-74317AC42A29’,
‘3F61FB9E-FAE8-418C-BF22-779ACD71CE1E’,
‘DC628EC6-01D4-4039-A1C1-AAE4737E27FD’,
‘220CF8EF-6D55-4F6F-869E-AFAE68CF067D’,
‘F9A3CDF0-A532-44C1-AA30-B10354890A9F’,
‘39238C2C-F9FA-49F6-BA1C-CB1F8B1EB998′,
’65BD1BC4-B386-4E1E-AE39-DF81FDA61397’,
‘F04C0A57-C166-43B9-BA00-EB7251074764′,
’99B8B405-9153-4C36-8A16-AE35C216A224’
)

–delete the records from SR_RESPONSES
delete from dbo.MSP_SR_RESPONSES where SR_UID in
(‘EEBF89E1-6E74-4B88-9CE6-3B7F93EEAC62’ ,
‘379BA18E-E98E-4FD8-AC83-5D8BF8ABF04A’ ,
’87BF5F35-843E-43FC-9EF6-9BB542E8677A’ ,
‘7332150B-69AD-446A-A946-C08DFC57D3D9’)

–delete the records from SR_REQUESTS
delete from dbo.MSP_SR_REQUESTS where SR_UID in
(‘EEBF89E1-6E74-4B88-9CE6-3B7F93EEAC62’ ,
‘379BA18E-E98E-4FD8-AC83-5D8BF8ABF04A’ ,
’87BF5F35-843E-43FC-9EF6-9BB542E8677A’ ,
‘7332150B-69AD-446A-A946-C08DFC57D3D9’)

–delete the records from SR_FREQUENCIES
delete from dbo.MSP_SR_FREQUENCIES where SR_UID in
(‘EEBF89E1-6E74-4B88-9CE6-3B7F93EEAC62’ ,
‘379BA18E-E98E-4FD8-AC83-5D8BF8ABF04A’ ,
’87BF5F35-843E-43FC-9EF6-9BB542E8677A’ ,
‘7332150B-69AD-446A-A946-C08DFC57D3D9’)

–delete the records from SR_REPORTS
delete from dbo.MSP_SR_REPORTS where SR_UID in
(‘EEBF89E1-6E74-4B88-9CE6-3B7F93EEAC62’ ,
‘379BA18E-E98E-4FD8-AC83-5D8BF8ABF04A’ ,
’87BF5F35-843E-43FC-9EF6-9BB542E8677A’ ,
‘7332150B-69AD-446A-A946-C08DFC57D3D9’)

That worked nicely for me; however, we’ve just had a change of project managers so we might have to go through the whole process again!

Good luck!