I'm writing this article as ECF (Enterprise Custom Fields) handling inside the Microsoft Project Server database backend is not really documented, at least hard to find any information about it :)
I needed to write custom audit and custom code for MSPS 2007 and 2010. Requirements were relying on ECF's and needed to use them in my custom code. I decided to build a view for my project entity with all custom fields. This view can be created in the ProjectServer_Reporting database. It was also a requirement to update the view when a new custom field added or an existing one removed. The solution was easy after I've found the [dbo].[MSP_OnRefreshCompletedInternal] stored procedure. MSPS event handler calls this procedure when an ECF is updated/created/deleted. It is a wrapper procedure only, you need to write your own stored procedure. This stored procedure must be named as follows:
- in MSPS 2007: [dbo].[MSP_OnRefreshCompleted]
- in MSPS 2010: [dbo].[MSPUser_OnRefreshCompleted]
It may be a good idea to check your installation for the procedure name in the wrapper stored procedure. It may be changed since I created this post.
Here is a sample view generator stored procedure:
/*===============================================================================
File: MSPS_ECF_VIEW.sql
Date: 2011.11.01
Description: demo code, MSPS custom view generator
SQL Server version: 2005 or newer
Author: Berke János - IamBerke.com
---------------------------------------------------------------------------------
(cc) 2011, IamBerke.com
You may alter this code for your own *non-commercial* purposes.
You may republish altered code as long as you give due credit.
THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND,
EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES
OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
================================================================================*/
CREATE PROCEDURE [dbo].[MSP_OnRefreshCompleted]
AS
--dynamic sql statement variables
DECLARE @stmt_select nvarchar(max);
DECLARE @stmt_from nvarchar(max);
DECLARE @stmt nvarchar(max);
DECLARE @LookupTables AS TABLE (
[id] int identity,
[CustomFieldName] nvarchar(max),
[LookupTableUID] uniqueidentifier,
[ColumnPoolColumnName] sysname,
[ColumnPoolTableName] sysname
);
--cursor variables
DECLARE @Name nvarchar(255);
DECLARE @LookupTableUID uniqueidentifier;
DECLARE @ColumnName nvarchar(max);
DECLARE @TableName nvarchar(max);
DECLARE @TableID int;
--assign static variables
INSERT INTO @LookupTables
SELECT
[CustomFieldName],
[LookupTableUID],
[ColumnPoolColumnName],
[ColumnPoolTableName]
FROM
[dbo].[MFN_Epm_GetAllCustomFieldsInformation]()
WHERE
[EntityName] = N'Project'
AND
[ColumnPoolColumnName] IS NOT NULL
AND
[ColumnPoolTableName] IS NOT NULL
AND
[LookupTableUID] IS NOT NULL
ORDER BY
[CustomFieldName];
SET @stmt_select = N'
SELECT P.*,
';
SET @stmt_from = N'
FROM
[dbo].[MSP_EpmProject] P
';
--create left outer joins for all tables
DECLARE TABLES CURSOR FOR
SELECT DISTINCT
[ColumnPoolTableName]
FROM
[dbo].[MFN_Epm_GetAllCustomFieldsInformation]()
WHERE
[EntityName] = N'Project'
AND
[ColumnPoolColumnName] IS NOT NULL
AND
[ColumnPoolTableName] IS NOT NULL
ORDER BY
[ColumnPoolTableName]
OPEN TABLES;
FETCH NEXT FROM TABLES INTO @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @stmt_from = @stmt_from +
N'LEFT OUTER JOIN
[dbo].' + QUOTENAME(@TableName) + ' ON P.[ProjectUID] = [dbo].' + QUOTENAME(@TableName) + '.[EntityUID]
';
FETCH NEXT FROM TABLES INTO @TableName;
END
CLOSE TABLES;
DEALLOCATE TABLES;
--create left outer join for lookup tables
DECLARE LOOKUPTABLES CURSOR FOR
SELECT
[id],
[LookupTableUID],
[ColumnPoolColumnName],
[ColumnPoolTableName]
FROM
@LookupTables
OPEN LOOKUPTABLES;
FETCH NEXT FROM LOOKUPTABLES INTO @TableID, @LookupTableUID, @ColumnName, @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @stmt_from = @stmt_from +
N'LEFT OUTER JOIN
[dbo].[MSP_EpmLookupTable] LT' + CAST(@TableID AS nvarchar) + ' ON [dbo].' + QUOTENAME(@TableName) + '.' + QUOTENAME(@ColumnName) + ' = LT' + CAST(@TableID AS nvarchar) + '.[MemberUID] AND LT' + CAST(@TableID AS nvarchar) + '.[LookupTableUID] = ''' + CAST(@LookupTableUID AS nvarchar(max)) + '''
';
FETCH NEXT FROM LOOKUPTABLES INTO @TableID, @LookupTableUID, @ColumnName, @TableName;
END;
CLOSE LOOKUPTABLES;
DEALLOCATE LOOKUPTABLES;
--create columns
DECLARE COLUMNS CURSOR FOR
SELECT
[CustomFieldName],
[ColumnPoolTableName],
[ColumnPoolColumnName]
FROM
[dbo].[MFN_Epm_GetAllCustomFieldsInformation]()
WHERE
[EntityName] = N'Project'
AND
[ColumnPoolColumnName] IS NOT NULL
AND
[ColumnPoolTableName] IS NOT NULL
ORDER BY
[CustomFieldName]
OPEN COLUMNS;
FETCH NEXT FROM COLUMNS INTO @Name, @TableName, @ColumnName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @stmt_select = @stmt_select + N'[dbo].' + QUOTENAME(@TableName) + '.' + QUOTENAME(@ColumnName) + ' AS ' + QUOTENAME(@Name)+ ',
';
--check lookup table values
IF EXISTS (SELECT 1 FROM @LookupTables WHERE [CustomFieldName] = @Name)
BEGIN
SET @TableID = (SELECT [id] FROM @LookupTables WHERE [CustomFieldName] = @Name);
SET @stmt_select = @stmt_select + N'LT' + CAST(@TableID AS nvarchar(max)) + '.[MemberValue] AS ' + QUOTENAME(@Name + ' MemberValue') + ',
';
SET @stmt_select = @stmt_select + N'LT' + CAST(@TableID AS nvarchar(max)) + '.[MemberFullValue] AS ' + QUOTENAME(@Name + ' MemberFullValue') + ',
';
END
FETCH NEXT FROM COLUMNS INTO @Name, @TableName, @ColumnName;
END
CLOSE COLUMNS;
DEALLOCATE COLUMNS;
SET @stmt = LEFT(@stmt_select, LEN(@stmt_select)-5) + @stmt_from
--create/alter view
IF EXISTS (
SELECT
1
FROM
sys.objects o
WHERE
o.[schema_id] = SCHEMA_ID('dbo')
AND
o.[name] = 'ProjectView'
)
BEGIN
EXEC ('ALTER VIEW [dbo].[ProjectView] AS ' + @stmt);
END
ELSE
BEGIN
EXEC ('CREATE VIEW [dbo].[ProjectView] AS ' + @stmt);
END
RETURN 0
Please note the [dbo].[MFN_Epm_GetAllCustomFieldsInformation] table-valued function. It returns lot of information about your custom fields.I hope it helps to some MSPS developers :)