10/01: The Biggest Auction Ever:
This web part will be inherited from Microsoft.SharePoint.WebPartPages.DataFormWebPart because i want to use XSLT transformation generated in sharepoint designer.
Now we need to create SQL query which return Global Permissions for all users
SELECT TOP (100) PERCENT r.RES_UID, CASE WHEN (MAX(CAST(cp.WSEC_ALLOW AS int)) = 1 AND MAX(CAST(cp.WSEC_DENY AS int)) = 0)
THEN 1 ELSE 0 END AS access, dbo.MSP_WEB_SECURITY_FEATURES_ACTIONS.WSEC_FEA_ACT_UID
FROM dbo.MSP_RESOURCES AS r WITH (NOLOCK) INNER JOIN
dbo.MSP_WEB_SECURITY_GROUP_MEMBERS AS GM WITH (NOLOCK) ON GM.WRES_GUID = r.RES_SECURITY_GUID INNER JOIN
dbo.MSP_WEB_SECURITY_SP_CAT_RELATIONS AS cr WITH (NOLOCK) ON cr.WSEC_SP_GUID = GM.WSEC_GRP_GUID AND
cr.WSEC_CAT_UID IS NULL INNER JOIN
dbo.MSP_WEB_SECURITY_SP_CAT_PERMISSIONS AS cp WITH (NOLOCK) ON cp.WSEC_REL_UID = cr.WSEC_REL_UID INNER JOIN
dbo.MSP_WEB_SECURITY_FEATURES_ACTIONS WITH (NOLOCK) ON
cp.WSEC_FEA_ACT_UID = dbo.MSP_WEB_SECURITY_FEATURES_ACTIONS.WSEC_FEA_ACT_UID
GROUP BY r.RES_UID, dbo.MSP_WEB_SECURITY_FEATURES_ACTIONS.WSEC_FEA_ACT_NAME_ID,
dbo.MSP_WEB_SECURITY_FEATURES_ACTIONS.WSEC_FEA_ACT_UID
As you can see I’m using (MAX(CAST(cp.WSEC_ALLOW AS int)) = 1 AND MAX(CAST(cp.WSEC_DENY AS int)) = 0) to aggregate permission thouht different security categories, is you need to display details remove this aggregation
Because PSI cannot be used, we have to retrieve users permissions directly from DB. At first we have to find location of Project Server databases.
public class ProjectInfo
{
private string _csPublished;
private string _csWorking;
private string _csVersions;
private string _csReporting;
private string _sspURL;
public string sspURL
{
get { return _sspURL; }
set { }
}
public string ConnectionStringPublished
{
get { return _csPublished; }
set { }
}
public string ConnectionStringWorking
{
get { return _csWorking; }
set { }
}
public string ConnectionStringVersions
{
get { return _csVersions; }
set { }
}
public string ConnectionStringReporting
{
get { return _csReporting; }
set { }
}
public ProjectInfo(SPSite pwaSite)
{
ReadInfo(pwaSite.ID, pwaSite.WebApplication.Id);
}
private ProjectInfo(Guid pwaUID)
{
Guid AppUID;
using (SPSite sitePWA = new SPSite(pwaUID))
{
AppUID = sitePWA.WebApplication.Id;
ReadInfo(pwaUID, AppUID);
}
}
private void ReadInfo(Guid pwaUID, Guid AppUID)
{
ServerContext sc = ServerContext.Default;
object serverFarm = sc.GetType().GetField("m_ServerFarm", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(sc);
//Use reflection to get the ShareResourceProviders collection
object sharedResourceProviders = serverFarm.GetType().GetProperty("SharedResourceProviders").GetValue(serverFarm, null);
//Enumerate all Shared Service providers
foreach (object sharedResourceProvider in sharedResourceProviders as IEnumerable)
{
object WebApps = sharedResourceProvider.GetType().GetProperty("WebApplications").GetValue(sharedResourceProvider, null);
foreach (object webApp in WebApps as IEnumerable)
{
if ((Guid)webApp.GetType().GetProperty("Id").GetValue(webApp, null) == AppUID)
{
Guid sspGuid = (Guid)(sharedResourceProvider.GetType().GetProperty("AdministrationSiteId" /* "ApplicationId"*/).GetValue(sharedResourceProvider, null));
string sspName = sharedResourceProvider.GetType().GetProperty("Name").GetValue(sharedResourceProvider, null).ToString();
bool sspSSL = (bool)sharedResourceProvider.GetType().GetProperty("UseHttpsForWebServices").GetValue(sharedResourceProvider, null);
using (SPSite sspSite = new SPSite(sspGuid))
{
if (sspSSL)
{
_sspURL = string.Format("https://{0}:56738/{1}", sspSite.HostName, sspName);
}
else
{
_sspURL = string.Format("http://{0}:56737/{1}", sspSite.HostName, sspName);
}
}
};
}
object SharedApplications = sharedResourceProvider.GetType().GetProperty("SharedApplications").GetValue(sharedResourceProvider, null);
foreach (object sharedApplication in SharedApplications as IEnumerable)
{
if (sharedApplication.GetType().FullName == "Microsoft.Office.Project.Server.Administration.ProjectPSISharedApplication")
{
object ProvisionSettingsCollection = sharedApplication.GetType().GetProperty("SiteCollection" /*"ProvisionSettingsCollection"*/).GetValue(sharedApplication, null);
foreach (object ProvisionSettings in ProvisionSettingsCollection as IEnumerable)
{
Guid SiteID = (Guid)GetProperty(ProvisionSettings, "SiteId");
if (SiteID == pwaUID)
{
_csPublished = (String)GetProperty(GetProperty(ProvisionSettings, "PublishedDatabase"), "DatabaseConnectionString");
_csReporting = (String)GetProperty(GetProperty(ProvisionSettings, "ReportingDatabase"), "DatabaseConnectionString");
_csVersions = (String)GetProperty(GetProperty(ProvisionSettings, "VersionsDatabase"), "DatabaseConnectionString");
_csWorking = (String)GetProperty(GetProperty(ProvisionSettings, "WorkingDatabase"), "DatabaseConnectionString");
return;
}
}
}
}
}
}
private static object GetProperty(object obj, string property)
{
return obj.GetType().GetProperty(property).GetValue(obj, null);
}
}
BigSisters - Erotic club/ reality show with koscher girls :) and all services are free of charge.
there is a location : 50°3'45.5094" N, 14°24'34.01244" E .
In this article i'll show you how to create simple MPS web part. Which can be customized by SPD
So in one view i can display 2 permissions
in another view all permissions will be displayed.
Same time after SP2 deployment customer told me that users can't create new timesheets
- The timesheet creation failed, because of problems with the project server or with
- data validations such as resources not being valid work resources or users. Check
- your environment and then try again.
In the event log I found following error:
- A runtime exception was detected. Details follow.
- Message: Could not find stored procedure 'MSP_WEB_SP_QRY_ValidateResourceUID'.
When i checked version of project server database (Select * from versions ) and i noticed that database wasn't upgraded,
As workaround i try to run sharepoint configuration and wizzard again, wizzard finished sucessfully but my MPS databases wasn't upgraded, but other MOSS databases were upgraded correctly.
28/05: SP2 cannot be installed
Error message: The detection failed.this can be due to corrupted installation database.
Open Regedit and rename Patches to Pathech_OLD in following paths
- HKCR\Installer\Products\00002109F70100000000000000F01FEC
- HKCR\Installer\Products\00002109F01100000000000000F01FEC
- HKCR\Installer\Products\00002109130100000000000000F01FEC
- HKCR\Installer\Products\00002109230100000000000000F01FEC
- HKCR\Installer\Products\000021090B0190400000000000F01FEC
- HKCR\Installer\Products\00002109080190400000000000F01FEC
and run installer again.
24/03: Israelis fashion T-shirts
09/02: OLAP - Task_UID
Same time ago I created Custom cube, which was combining data from timesheets with custom task properties, but in production many lines from timesheet weren’t linked to tasks.
My first idea was that users doing same strange operations with project plan which changing TASK_UID (for example they deleted tasks, or they created a copy of same task and then the original tasks were removed), so as workaround make a huge SQL script which each night make a copy of all existing task from Reporting database to my custom reporting database.
I expected that that I will be able find custom task information even in case that task or project will be deleted from Project server.
I also expected that there is bug in project professional. So I created PSI application which was detecting such TASK_UID changes in project plan.
Application is here:
1 month later there was still problem with OLAP cube, but when I go deeper into db I discovered that TASK_UID wasn’t changed. Co there isn’t bug in Project Professional, my users aren’t stupid.
So most likely there is ST wrong in project server.
So I make new query which linking data from timesheets with data from project plan.
As I expected same tasks’ cannot be found in MSP_EpmTask_UserView
But there is unexpected, you can see 2 rows for the first one I can find task in MSP_EpmTask_UserView but for second row not but both rows have the same AssignmentUID!?!?:
SELECT DISTINCT
TOP (100) PERCENT dbo.MSP_TimesheetTask.TaskName, dbo.MSP_TimesheetProject.ProjectName, dbo.MSP_TimesheetTask.TaskNameUID,
dbo.MSP_TimesheetLine.AssignmentUID, dbo.MSP_EpmTask_UserView.TaskUID AS TestTASK_UID
FROM dbo.MSP_TimesheetLine INNER JOIN
dbo.MSP_Timesheet ON dbo.MSP_TimesheetLine.TimesheetUID = dbo.MSP_Timesheet.TimesheetUID INNER JOIN
dbo.MSP_TimesheetProject ON dbo.MSP_TimesheetLine.ProjectNameUID = dbo.MSP_TimesheetProject.ProjectNameUID INNER JOIN
dbo.MSP_TimesheetResource ON dbo.MSP_Timesheet.OwnerResourceNameUID = dbo.MSP_TimesheetResource.ResourceNameUID INNER JOIN
dbo.MSP_TimesheetTask ON dbo.MSP_TimesheetLine.TaskNameUID = dbo.MSP_TimesheetTask.TaskNameUID INNER JOIN
dbo.MSP_TimesheetPeriod ON dbo.MSP_Timesheet.PeriodUID = dbo.MSP_TimesheetPeriod.PeriodUID LEFT OUTER JOIN
dbo.MSP_EpmTask_UserView ON dbo.MSP_TimesheetTask.TaskUID = dbo.MSP_EpmTask_UserView.TaskUID
WHERE (dbo.MSP_TimesheetResource.ResourceName LIKE 'Raus%') AND (dbo.MSP_TimesheetPeriod.StartDate > CONVERT(datetime, '1.1.2009', 104)) AND
(dbo.MSP_TimesheetProject.ProjectName = N'Operativní 2009 PMO')
ORDER BY dbo.MSP_TimesheetProject.ProjectName, dbo.MSP_TimesheetTask.TaskName
So there is ST wrong with value TaskUID in MSP_TimesheetTask.
In the first week this value vas stored correct TaskUID but 2nd week there is GUID without relationship to task, but this row contains AssignmentUID which can be used to find a task
So i created 2nd query which using AssignmentUID to return incorrect TaskUID
SELECT DISTINCT dbo.MSP_TimesheetTask.TaskUID, TimesheetTask_WrongID.TaskUID AS TaskUID_Wrong
FROM dbo.MSP_EpmTask_UserView INNER JOIN
dbo.MSP_TimesheetLine INNER JOIN
dbo.MSP_TimesheetTask ON dbo.MSP_TimesheetLine.TaskNameUID = dbo.MSP_TimesheetTask.TaskNameUID ON
dbo.MSP_EpmTask_UserView.TaskUID = dbo.MSP_TimesheetTask.TaskUID INNER JOIN
dbo.MSP_TimesheetLine AS TimesheetLine_WrongID ON
dbo.MSP_TimesheetLine.AssignmentUID = TimesheetLine_WrongID.AssignmentUID INNER JOIN
dbo.MSP_TimesheetTask AS TimesheetTask_WrongID ON TimesheetLine_WrongID.TaskNameUID = TimesheetTask_WrongID.TaskNameUID AND
dbo.MSP_TimesheetTask.TaskUID <> TimesheetTask_WrongID.TaskUID
So at end II created following script which fixing TaskUID and problem disappeared.
USE [GPE_OLAPCUBE_v2]
GO
/****** Object: StoredProcedure [dbo].[CreateMissingLines] Script Date: 01/22/2009 15:03:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: martin Winzig
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[FixWrongTask_UID]
AS
BEGIN
declare @OldTaskUID uniqueidentifier
declare @ProjUID uniqueidentifier
declare @TaskUID uniqueidentifier
declare @TaskUID_WRONG uniqueidentifier
declare @taskName nvarchar(255)
Declare curTasks CURSOR GLOBAL DYNAMIC
for
SELECT DISTINCT dbo.MSP_TimesheetTask.TaskUID, TimesheetTask_WrongID.TaskUID AS TaskUID_Wrong, dbo.MSP_EpmTask_UserView.ProjectUID
FROM dbo.MSP_EpmTask_UserView INNER JOIN
dbo.MSP_TimesheetLine INNER JOIN
dbo.MSP_TimesheetTask ON dbo.MSP_TimesheetLine.TaskNameUID = dbo.MSP_TimesheetTask.TaskNameUID ON
dbo.MSP_EpmTask_UserView.TaskUID = dbo.MSP_TimesheetTask.TaskUID INNER JOIN
dbo.MSP_TimesheetLine AS TimesheetLine_WrongID ON
dbo.MSP_TimesheetLine.AssignmentUID = TimesheetLine_WrongID.AssignmentUID INNER JOIN
dbo.MSP_TimesheetTask AS TimesheetTask_WrongID ON TimesheetLine_WrongID.TaskNameUID = TimesheetTask_WrongID.TaskNameUID AND
dbo.MSP_TimesheetTask.TaskUID <> TimesheetTask_WrongID.TaskUID
open curTasks
FETCH next FROM curTasks INTO @TaskUID, @TaskUID_WRONG, @ProjUID
WHILE (@@FETCH_STATUS = 0)
BEGIN
UPDATE dbo.MSP_TimesheetTask set TaskUID=@TaskUID where TaskUID=@TaskUID_WRONG
FETCH next FROM curTasks INTO @TaskUID, @TaskUID_WRONG, @ProjUID end
close curTasks
DEALLOCATE curTasks
END
