Question:
How can I have the system data layer run some script every time the web application starts up?
Answer:
In V2017, the system will check for and run extension procedures in the SQL [custom] schema.
- custom.p_HousekeepingOnStartup runs every time the web application starts, including every farm member instance.
- custom.psf_DBSysMaint is run once per day, typically by the first farm instance
In V2018, the system supports two SQL datalayer housekeeping extensions stored with specific names in the System Admin Workflow Scripts tool (even though they don’t use ATC script commands).
- DAL-HouseKeepingEveryStartup runs every time the web application starts, including every farm member instance.
- DAL-HouseKeepingDailyStartup runs once per day, typically by the first farm instance.
Example:
This snippet will promote a field in a distributed lookup (DocumentListCI) from the “expanded” view to the primary view (both column and filter). It is helpful to have it run daily since any update applied by Spitfire might reset the defaults for the lookup.
-- ATC: * SQL Housekeeping
UPDATE t SET ShowField = 'P', PredicateMode = 'P'
FROM dbo.xsfLookupField t
WHERE LookupKey = 'F2344966-B146-46F8-8C86-5905CC73F173'
AND FieldAlias in ('SourceDocno');
Additional Information:
- Using SQL extensions is a simpler and more accessible option than the Site_Wrapup.SQL file used by ICTool on the IIS server.
- These extension scripts do not support HTML.
- Be sure to use single quotes (‘), not double quotes (“) or fancy open/close quotes.
- Test your script in an appropriate tool such as SQL Server Management Studio.
- Failures during startup are logged only to xsfEventLog.
For assistance, please contact Support. Assistance with SQL extensions is typically billable.
KBA-01711; Last updated: May 2, 2018 at 13:09 pm