SQL Cheat Sheet

⚠ Dev database names (CLAUDE.md §6 is stale). The Dev 1st-Touch DB is Versaa_PrtyDev on lhp-sql02 — there is no 1stTouch_PrtyDev (and aareon-sql01 holds the Live DBs, 1stTouch_PrtyLive). The Dev QL HMS DB is a_qlfdat on lhp-sql02 (shared with/like live — test on a known test property). Verified 11/06/2026 while diagnosing the Void Survey QL contact.

---

View Form XML for a Task

-- Dev: Versaa_PrtyDev.Task.Task on lhp-sql02. Cols: TaskId, UserName, FormTypeId,
-- Status, LastMessageReceived, OwnerId, CreatedAt, TaskXml, SourceServiceName.
SELECT TOP 10 CreatedAt, UserName, Status, LastMessageReceived, convert(XML, TaskXml) AS TaskXml
FROM [Versaa_PrtyDev].[Task].[Task]
WHERE LastMessageReceived LIKE 'Void Survey%'
ORDER BY CreatedAt DESC

Always use convert(XML, ...) — without it SSMS truncates the text. Click the hyperlink in the TaskXml column to open the full XML viewer. Extract a single item's value with XQuery (DataItem value = element text):

convert(XML, TaskXml).value('(//DataItem[@name="QLMV_Requested"]/text())[1]','nvarchar(50)')

⚠ Task.Task only holds ACTIVE/dispatched tasks (Status=Acknowledged, LastMessageReceived like …TaskDelivered/…Started) — completed tasks are cleared after processing, so you cannot read a completed run's answers here. To inspect a completed run, use Management Studio → Workflows → Search Instances → Instance Details (shows the full completed Task XML, the failing step + error, and Failed Creation Requests). See sessions/gotchas-and-tips.md.

Filter by form via LastMessageReceived (older Dev/Test/Live [1stTouch_*] example):

SELECT cast(TaskXML as XML) AS TaskXML
FROM [Task].[Task]
WHERE Username LIKE '%Steelp%'
  AND LastMessageReceived = 'Component Replacement Request.TaskDelivered'

Replace Component Replacement Request.TaskDelivered with [YourFormName].TaskDelivered for any form. Omit Username filter to see all completions of that form across all users.

---

View Workflow Errors

SELECT * FROM [1stTouch_PrtyLive].[LHP].[vw_Workflow_Errors]

---

QL tenancy contacts — where ImportContactCreationRequest writes

The QL HMS database is a_qlfdat on server lhp-sql02 ($ENV_QL_DB_ConnectionString$ = Server=lhp-sql02;Database=a_qlfdat). When a form raises a contact (the QLcrm_*/QLMV_* suffix-matched items → ImportContactCreationRequest workflow step), it flows:

form task → hpm1stcr (1st Touch contact-request staging) → QL processes → hgmcntct (live tenancy contact)

Table	Role	Key columns
a_qlfdat.dbo.hpm1stcr	Staging — where the request lands first	brief_desc, full_desc, class, class_level1..5, contact_type, contact_source, prty_id, tency_seq_no, comp_date, processed_yn, error_flag, error_code, new_contact_no
a_qlfdat.dbo.hgmcntct	Live QL tenancy contact	contact_no (PK), prty_id, client_no, tency_seq_no, contact_class, class_level1..5, contact_type, contact_source, brief_desc, full_desc, taken_by, taken_by_dt, completed_yn, due_date, order_no
hgmctaud	contact audit/history	(mirror of hgmcntct)
hgmcntcl	classification lookup	level_desc

-- Did the contact request land + get processed cleanly? (error_flag/error_code blank = OK)
SELECT TOP 20 * FROM a_qlfdat.dbo.hpm1stcr ORDER BY comp_date DESC;

-- The live contact for a property (newest first). class_level1/2/3 = the classification path.
SELECT TOP 20 contact_no, prty_id, contact_class, class_level1, class_level2, class_level3,
       contact_type, contact_source, brief_desc, full_desc, taken_by, taken_by_dt
FROM a_qlfdat.dbo.hgmcntct
WHERE prty_id = 'PROPERTY_ID'   -- or: brief_desc LIKE 'your brief%'
ORDER BY contact_no DESC;

Void property contacts (CORRECTED 18/06/2026): a void has no tenant, so to raise a contact set the contact provider's <prefix>_Use Property Void Client No = Yes (e.g. QLMV_Use Property Void Client No for Void Survey, QLcrm_… for Asbestos). QL then resolves the property's void client number and raises the contact against it — hgmcntct.client_no is that real void client (e.g. 3000384), NOT 0 (earlier note saying 0 was wrong). The provider step must also receive the propertyId (else it can't find the void client → "no client no provided"). Verified: Void Survey form v34 raised contact_no=1701884 for prty_id=10804, client_no=3000384. The contact's taken_by is the submitting operative.

⚠ Column caveat: hpm1stcr has no prty_id column (the staging-table column list above is partly inferred — query SELECT TOP 20 * FROM a_qlfdat.dbo.hpm1stcr ORDER BY comp_date DESC to see real columns). The hgmcntct (live) query is the reliable check. ⚠ a_qlfdat on lhp-sql02 appears shared with live — test contact-raising forms on a known test property.

---

Workflow Message Trigger Lookup

Find which workflows fire when a particular form completes:

SELECT WMT.TriggerMessage, W.Name AS WorkflowName, W.WorkflowID
FROM [1stTouch_PrtyLive].[workflow].[WorkflowMessageTrigger] WMT
LEFT JOIN [1stTouch_PrtyLive].[workflow].[Workflow] W
    ON W.WorkflowID = WMT.WorkflowID
WHERE WMT.TriggerMessage LIKE '%.PCACompletion%'
ORDER BY WMT.TriggerMessage

• Wildcard *.PCACompletion matches any form's completion → fires the Generic Task Completion Workflow.
• Exact match AsbestosSurvey.PCACompletion fires only for that form.
• A form completion typically fires two workflows: one form-specific, one generic.

Find all triggers for a specific workflow:

SELECT W.Name AS WorkflowName, WMT.TriggerMessage
FROM [1stTouch_PrtyLive].[workflow].[WorkflowMessageTrigger] WMT
LEFT JOIN [1stTouch_PrtyLive].[workflow].[Workflow] W
    ON W.WorkflowID = WMT.WorkflowID
WHERE W.Name LIKE '%Generic Task Completion%'

---

HPM First Touch Staging Tables

Versaa writes to these before QL picks up the data (~every 2–5 minutes):

CRM contact creation (HGMCNTCT)

SELECT TOP 1000 * FROM HGMCNTCT WHERE compIT = 'LHP' ORDER BY contact_now DESC

Key columns: contact_now (timestamp), brief_desc, class1–class5, contact_src, client_no (negative = pending)

Client update (HGMCLIENT)

SELECT TOP 1000 * FROM HGMCLIENT ORDER BY 1 DESC

---

Key Tables

Table	Purpose
[Task].[Task]	Form submissions — TaskXml column holds form data
[workflow].[WorkflowMessageTrigger]	Maps message events to workflow IDs
HGMCNTCT	CRM contact creation staging
HGMCLIENT	Client update staging
hgm1stup	Property data
hpm1stoa	Order tracking (appointments)
hpm1stnt	Notes
[LHP].[vw_Workflow_Errors]	Workflow error view

---

XPath / XML Tips

• Use convert(XML, ...) not cast() when viewing TaskXml in SSMS — it enables the clickable hyperlink.
• Use the ▶ collapse button next to repeating node groups (property asbestos records, warnings) to fold and navigate the XML.
• Key fields in TaskXml: WorkflowSuffixID, PropertyID, AddressLine1–4, TenancySequenceNumber, RentAccountNumber, ClientNumber, Insights_*, Alert_*.

---

Self Serve Repairs Testing

-- Find tenant with a future appointment (best for SS active repairs testing)
SELECT TOP 10 
  o.order_no, o.client_no, o.prty_id, o.targ_comp_dt, a.start_time
FROM hpmordhd o
INNER JOIN hpmaptmt a ON a.order_no = o.order_no
INNER JOIN hratency t ON t.prty_ref = o.prty_id AND t.comp_id = o.comp_id
WHERE o.ord_status = 'ISS'
  AND a.start_time > GETDATE()
  AND o.targ_comp_dt != CAST(a.start_time AS DATE)
  AND (t.tency_end_dt IS NULL OR t.tency_end_dt > GETDATE())
ORDER BY a.start_time ASC

-- Find order details by order number
SELECT o.order_no, o.client_no, o.prty_id, o.targ_comp_dt, o.ord_status, a.start_time
FROM hpmordhd o
LEFT JOIN hpmaptmt a ON a.order_no = o.order_no
WHERE o.order_no = ''

-- Find current tenant with active repair + appointment (for SS testing)
SELECT TOP 20 
  o.order_no, o.client_no, o.prty_id, o.targ_comp_dt, a.start_time, t.tency_seq_no
FROM hpmordhd o
INNER JOIN hpmaptmt a ON a.order_no = o.order_no
INNER JOIN hratency t ON t.prty_ref = o.prty_id AND t.comp_id = o.comp_id
WHERE o.ord_status = 'ISS'
  AND (t.tency_end_dt IS NULL OR t.tency_end_dt > GETDATE())
ORDER BY a.start_time DESC

QL Database names by environment:

Environment	Database
SS Test	t_qlfdat
Dev / Acceptance	a_qlfdat
Live	QLFDAT

---

QL Work Tray

If the QL Work Tray is not closing, check for actions with no outcomes: Actions_with_no_Outcomes report

Related

• workflows/fix-errors.md
• forms/testing-configuration.md
• platform/versaa-overview.md