Microsoft Project Server ECF custom view automatic refresh
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:
1/*===============================================================================
2 File: MSPS_ECF_VIEW.sql
3 Date: 2011.11.01
4 Description: demo code, MSPS custom view generator
5 SQL Server version: 2005 or newer
6 Author: Berke János - IamBerke.com
7---------------------------------------------------------------------------------
8 (cc) 2011, IamBerke.com
9
10You may alter this code for your own *non-commercial* purposes.
11You may republish altered code as long as you give due credit.
12THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND,
13EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES
14OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
15================================================================================*/
16
17CREATE PROCEDURE [dbo].[MSP_OnRefreshCompleted]
18AS
19--dynamic sql statement variables
20DECLARE @stmt_select nvarchar(max);
21DECLARE @stmt_from nvarchar(max);
22DECLARE @stmt nvarchar(max);
23DECLARE @LookupTables AS TABLE (
24 [id] int identity,
25 [CustomFieldName] nvarchar(max),
26 [LookupTableUID] uniqueidentifier,
27 [ColumnPoolColumnName] sysname,
28 [ColumnPoolTableName] sysname
29 );
30
31--cursor variables
32DECLARE @Name nvarchar(255);
33DECLARE @LookupTableUID uniqueidentifier;
34DECLARE @ColumnName nvarchar(max);
35DECLARE @TableName nvarchar(max);
36DECLARE @TableID int;
37
38
39--assign static variables
40INSERT INTO @LookupTables
41SELECT
42 [CustomFieldName],
43 [LookupTableUID],
44 [ColumnPoolColumnName],
45 [ColumnPoolTableName]
46FROM
47 [dbo].[MFN_Epm_GetAllCustomFieldsInformation]()
48WHERE
49 [EntityName] = N'Project'
50AND
51 [ColumnPoolColumnName] IS NOT NULL
52AND
53 [ColumnPoolTableName] IS NOT NULL
54AND
55 [LookupTableUID] IS NOT NULL
56ORDER BY
57 [CustomFieldName];
58
59
60SET @stmt_select = N'
61SELECT P.*,
62 ';
63SET @stmt_from = N'
64FROM
65 [dbo].[MSP_EpmProject] P
66';
67
68--create left outer joins for all tables
69DECLARE TABLES CURSOR FOR
70SELECT DISTINCT
71 [ColumnPoolTableName]
72FROM
73 [dbo].[MFN_Epm_GetAllCustomFieldsInformation]()
74WHERE
75 [EntityName] = N'Project'
76AND
77 [ColumnPoolColumnName] IS NOT NULL
78AND
79 [ColumnPoolTableName] IS NOT NULL
80ORDER BY
81 [ColumnPoolTableName]
82OPEN TABLES;
83FETCH NEXT FROM TABLES INTO @TableName;
84WHILE @@FETCH_STATUS = 0
85 BEGIN
86 SET @stmt_from = @stmt_from +
87N'LEFT OUTER JOIN
88 [dbo].' + QUOTENAME(@TableName) + ' ON P.[ProjectUID] = [dbo].' + QUOTENAME(@TableName) + '.[EntityUID]
89';
90 FETCH NEXT FROM TABLES INTO @TableName;
91 END
92CLOSE TABLES;
93DEALLOCATE TABLES;
94
95--create left outer join for lookup tables
96DECLARE LOOKUPTABLES CURSOR FOR
97SELECT
98 [id],
99 [LookupTableUID],
100 [ColumnPoolColumnName],
101 [ColumnPoolTableName]
102FROM
103 @LookupTables
104OPEN LOOKUPTABLES;
105FETCH NEXT FROM LOOKUPTABLES INTO @TableID, @LookupTableUID, @ColumnName, @TableName;
106WHILE @@FETCH_STATUS = 0
107 BEGIN
108 SET @stmt_from = @stmt_from +
109N'LEFT OUTER JOIN
110 [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)) + '''
111 ';
112
113 FETCH NEXT FROM LOOKUPTABLES INTO @TableID, @LookupTableUID, @ColumnName, @TableName;
114 END;
115CLOSE LOOKUPTABLES;
116DEALLOCATE LOOKUPTABLES;
117
118--create columns
119DECLARE COLUMNS CURSOR FOR
120SELECT
121 [CustomFieldName],
122 [ColumnPoolTableName],
123 [ColumnPoolColumnName]
124FROM
125 [dbo].[MFN_Epm_GetAllCustomFieldsInformation]()
126WHERE
127 [EntityName] = N'Project'
128AND
129 [ColumnPoolColumnName] IS NOT NULL
130AND
131 [ColumnPoolTableName] IS NOT NULL
132ORDER BY
133 [CustomFieldName]
134OPEN COLUMNS;
135FETCH NEXT FROM COLUMNS INTO @Name, @TableName, @ColumnName;
136WHILE @@FETCH_STATUS = 0
137 BEGIN
138 SET @stmt_select = @stmt_select + N'[dbo].' + QUOTENAME(@TableName) + '.' + QUOTENAME(@ColumnName) + ' AS ' + QUOTENAME(@Name)+ ',
139 ';
140
141 --check lookup table values
142 IF EXISTS (SELECT 1 FROM @LookupTables WHERE [CustomFieldName] = @Name)
143 BEGIN
144 SET @TableID = (SELECT [id] FROM @LookupTables WHERE [CustomFieldName] = @Name);
145 SET @stmt_select = @stmt_select + N'LT' + CAST(@TableID AS nvarchar(max)) + '.[MemberValue] AS ' + QUOTENAME(@Name + ' MemberValue') + ',
146 ';
147 SET @stmt_select = @stmt_select + N'LT' + CAST(@TableID AS nvarchar(max)) + '.[MemberFullValue] AS ' + QUOTENAME(@Name + ' MemberFullValue') + ',
148 ';
149 END
150
151 FETCH NEXT FROM COLUMNS INTO @Name, @TableName, @ColumnName;
152 END
153CLOSE COLUMNS;
154DEALLOCATE COLUMNS;
155
156
157SET @stmt = LEFT(@stmt_select, LEN(@stmt_select)-5) + @stmt_from
158
159--create/alter view
160
161IF EXISTS (
162SELECT
163 1
164FROM
165 sys.objects o
166WHERE
167 o.[schema_id] = SCHEMA_ID('dbo')
168AND
169 o.[name] = 'ProjectView'
170)
171 BEGIN
172 EXEC ('ALTER VIEW [dbo].[ProjectView] AS ' + @stmt);
173 END
174ELSE
175 BEGIN
176 EXEC ('CREATE VIEW [dbo].[ProjectView] AS ' + @stmt);
177 END
178
179RETURN 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 :)