Diagnosing missing webapp data through MSSQL

The case of all recently made redundant employee’s had disappeared from our HRM after they received the payout.

Return all the recently run queries:


SELECT deqs.last_execution_time AS [Time], dest.text AS [Query], dest.*
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE dest.dbid = DB_ID('databasename')
ORDER BY deqs.last_execution_time DESC

Searched┬áthe recent results for the table ‘q2employees’ as this references all the employee details, I knew this would need to be included.

One of these large queries pointed to a view called ‘q2vEmployeeDirectory’, bingo. Using ‘Design’ to look at the query that runs this view it looked like the following.


SELECT dbo.q2employees.emp_code,
dbo.q2employees.surname,
dbo.q2employees.given_name,
dbo.q2employees.email_address,
dbo.q2employees.image,
dbo.q2employees.phone_home,
dbo.q2employees.phone_work,
dbo.q2employees.phone_mobile,
dbo.q2department_employees.department,
{ fn LCASE(dbo.q2employees.surname) } AS
lsurname,
{ fn LCASE(dbo.q2employees.given_name) } AS
lgiven_name,
CASE
WHEN dbo.q2employees.preferred_name IS NULL THEN
dbo.q2employees.given_name + ' '
+ dbo.q2employees.surname
ELSE dbo.q2employees.preferred_name + ' '
+ dbo.q2employees.surname
END AS
emp_name,
dbo.q2employees.preferred_name,
dbo.q2employees.work_mobile,
(SELECT payer_name
FROM dbo.q2payer_groups
WHERE ( dbo.q2employees.co_id = co_id )
AND ( dbo.q2employee_pay_details.payer_id = payer_id )) AS
payer_group,
dbo.q2employees.co_id
FROM dbo.q2employee_pay_details
INNER JOIN dbo.q2employees
ON dbo.q2employee_pay_details.emp_code = dbo.q2employees.emp_code
LEFT OUTER JOIN dbo.q2department_employees
ON dbo.q2employees.emp_code =
dbo.q2department_employees.emp_code
WHERE ( dbo.q2employee_pay_details.date_term IS NULL )

Which gave me the hint at the very end:


WHERE ( dbo.q2employee_pay_details.date_term IS NULL )

So, to return all employee’s back into the directory this is easily fixed by setting the date_term field back to NULL.


UPDATE q2employee_pay_details
SET date_term = NULL;

Total Views: (113)

Leave a Reply

Your email address will not be published. Required fields are marked *

Connect with Facebook

*