Question:
Can I replace a Spitfire SQL stored procedure with one of my own?
Answer:
Yes, sometimes. When the datalayer starts, it scans for stored procedure owned by the SQL [Custom] Schema that have names and parameters that exactly match the Spitfire-provided equivalent (that are owned by the [dbo] schema). If your procedure qualifies, it will be listed by EXEC dbo.[p_LoadCustomProcedureList]. If your procedure matches by name, but is disqualified by other issues, then
EXECUTE AS User ='Spitfire' EXEC dbo.[p_LoadCustomProcedureList] 1
may provide some insight. The disqualification detection feature is intended to facilitate system updates. If your procedure is not listed, you have not GRANTED execute access to the SQL SpitfireMain role.
Note that even if your procedure is detected and eligible, it is up to the Spitfire Datalayer to support using the alternate procedure at run time. Specifically:
- Procedures that are called from other SQL code will always call the DBO version
- Only stored procedure are supported
- Reports are not supported at this time (rq_, rpq_ prefixes)
- Describe Value (scalar DV queries) are not supported
You can observe that Spitfire has adopted your [custom] procedure by watching SQL Profiler. For example, the following commands are used when loading a project dashboard. Note that the KPI data is obtained from a custom procedure.
exec [dbo].[p_getProjectDocByType] @pProject='GC123000',@p.....
exec [dbo].[p_GetProjectTeamList] @pProject='GC123000',@pShowHidden=0
exec [dbo].[p_GetLinkedProjects] @pProject='GC123000'
exec [custom].[psf_ProjectKPI] @pProject='GC123000'
exec [dbo].[psf_ProjectCAData] @pProject='GC123000',@pTask='%',@pByAcct=1,@pByTask=0
You can create the custom schema and test custom procedure using a script such as
CREATE Schema custom; GO
CREATE Procedure custom.psf_ProjectKPI @pProject VARCHAR(16) AS exec dbo.psf_projectkpi @pProject GO
Additional Comments:
This is an advanced configuration feature and is not covered by standard implementation and support. Use custom replacement procedures at your own risk. Spitfire cannot guarantee compatibility across major upgrades or minor updates, or that the disqualification detection feature will detect all breaking changes and compatibility issues. For example, disqualification detection does not verify that the result set(s) of the custom procedure match the result set(s) of the standard procedure.
The datalayer only loads the custom procedure list during startup (otherwise the overhead would be excessive). If you want to override a new procedure, you *must* recycle the sfPMS application. Once the datalayer has detected your custom procedure, you can continue to revise and refine it without the need to recycle the sfPMS application.
Remember to GRANT EXEC ON custom.your-replacement-proceduure TO SpitfireMain
KBA-01430; Last updated: September 18, 2017 at 11:34 am;
Keywords: my own SQL stored procedure, replacing SQL stored procedures