Its all about Database

USE [c1]

/****** Object: Table [dbo].[FailedJobLog_Archive] Script Date: 6/27/2025 3:42:58 PM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[FailedJobLog_Archive](

[JobName] [nvarchar](255) NULL,

[LastRunDate] [datetime] NULL,

[Notification] [bit] NULL,

[ErrorMessage] [nvarchar](max) NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

ALTER TABLE [dbo].[FailedJobLog_Archive] ADD DEFAULT ((0)) FOR [Notification]

---------------------------------

USE [c1]

/****** Object: Table [dbo].[FailedJobLog] Script Date: 6/27/2025 3:42:48 PM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[FailedJobLog](

[JobName] [nvarchar](255) NOT NULL,

[LastRunDate] [datetime] NOT NULL,

[Notification] [bit] NULL,

[ErrorMessage] [nvarchar](max) NULL,

PRIMARY KEY CLUSTERED

(

[JobName] ASC,

[LastRunDate] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

ALTER TABLE [dbo].[FailedJobLog] ADD DEFAULT ((0)) FOR [Notification]

---------

---exec usp_CheckFailedJobsAndSendEmail

create PROCEDURE usp_CheckFailedJobsAndSendEmail

BEGIN

SET NOCOUNT ON;

-- Temp table to hold new failed jobs

DECLARE @NewFailedJobs TABLE (

JobName NVARCHAR(255),

LastRunDate DATETIME,

ErrorMessage NVARCHAR(MAX)

);

-- Insert newly failed jobs

INSERT INTO @NewFailedJobs (JobName, LastRunDate, ErrorMessage)

SELECT

j.name AS JobName,

CONVERT(DATETIME,

STUFF(STUFF(CAST(h.run_date AS NVARCHAR), 5, 0, '-'), 8, 0, '-') + ' ' +

STUFF(STUFF(RIGHT('000000' + CAST(h.run_time AS NVARCHAR), 6), 3, 0, ':'), 6, 0, ':'), 120) AS LastRunDate,

h.message AS ErrorMessage

FROM msdb.dbo.sysjobhistory h

JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id

WHERE h.run_status = 0 -- Failed

AND h.step_id = 0 -- Job-level failures

AND h.run_date = CONVERT(INT, FORMAT(GETDATE(), 'yyyyMMdd'))

AND NOT EXISTS (

SELECT 1 FROM FailedJobLog_Archive f

WHERE f.JobName = j.name

AND f.LastRunDate = CONVERT(DATETIME,

STUFF(STUFF(CAST(h.run_date AS NVARCHAR), 5, 0, '-'), 8, 0, '-') + ' ' +

STUFF(STUFF(RIGHT('000000' + CAST(h.run_time AS NVARCHAR), 6), 3, 0, ':'), 6, 0, ':'), 120)

);

-- Insert into FailedJobLog with ErrorMessage

INSERT INTO FailedJobLog (JobName, LastRunDate, Notification, ErrorMessage)

SELECT JobName, LastRunDate, 0, ErrorMessage FROM @NewFailedJobs;

-- Only send email if new failures found

IF EXISTS (SELECT 1 FROM @NewFailedJobs)

BEGIN

DECLARE @EmailBody NVARCHAR(MAX) = N'

<!DOCTYPE html>

<head>

<title>SQL Server Job Failure Alert</title>

<style>

body { font-family: Segoe UI, Arial, sans-serif; background-color: #f9f9f9; margin: 0; padding: 20px; }

.container { max-width: 800px; margin: auto; background: #ffffff; padding: 30px; border-radius: 8px; box-shadow: 0 0 15px rgba(0,0,0,0.1); }

h2 { background-color: #dc3545; color: white; padding: 15px; border-radius: 5px; text-align: center; }

p { font-size: 14px; color: #333; }

table { width: 100%; border-collapse: collapse; margin-top: 20px; }

th, td { border: 1px solid #ddd; padding: 10px; text-align: left; font-size: 14px; vertical-align: top; }

th { background-color: #dc3545; color: white; }

tr:nth-child(even) { background-color: #f2f2f2; }

.alert { margin-top: 25px; color: #dc3545; font-weight: bold; font-size: 16px; text-align: center; }

.footer { margin-top: 30px; font-size: 12px; color: #666; text-align: center; }

.footer a { color: #007bff; text-decoration: none; font-weight: bold; }

</style>

</head>

<body>

<h2>🚨 SQL Server Job Failure Alert for Clota-Lab01 🚨</h2>

<p>The following SQL Server Agent jobs failed today and require your attention:</p>

<table>

<tr>

<th>Error Message</th>

</tr>';

-- Append job data to HTML

SELECT @EmailBody = @EmailBody +

'<tr><td>' + JobName + '</td><td>' +

CONVERT(NVARCHAR, LastRunDate, 120) + '</td><td>' +

REPLACE(ISNULL(ErrorMessage, 'N/A'), CHAR(10), '<br/>') + '</td></tr>'

FROM @NewFailedJobs;

-- Finalize HTML

SET @EmailBody = @EmailBody + N'

</table>

<p class="alert">⚠ Immediate investigation is recommended.</p>

<p>Generated on ' + FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss') + '.</p>

<p>Powered by <a href="https://www.clotatech.com/">Clota Technology</a></p>

</div>

</body>

</html>';

-- Send the email

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'support',

@recipients = 'dba@clotatechnology.com;forebrain@clotatech.com',

@subject = N'SQL Server Job Failure Alert',

@body = @EmailBody,

@body_format = 'HTML';

-- Update Notification flag

UPDATE FailedJobLog

SET Notification = 1

WHERE Notification = 0;

-- Archive and cleanup

INSERT INTO FailedJobLog_Archive (JobName, LastRunDate, Notification, ErrorMessage)

SELECT JobName, LastRunDate, Notification, ErrorMessage

FROM FailedJobLog

WHERE Notification = 1;

DELETE FROM FailedJobLog WHERE Notification = 1;

END

END;

---------------------------------------------------------------------

SELECT

e.execution_id,

ex.package_name,

es.executable_id,

ex.executable_name,

es.execution_path,

es.execution_result,

es.start_time,

es.end_time,

DATEDIFF(SECOND, es.start_time, es.end_time) AS duration_seconds,

CASE

WHEN executable_name LIKE '%IS_LOAN_DELINQUENT%' THEN 'ARCHOWN.Fincl10_GAM, Archown.Fincl10_GAC, Archown.Fincl10_AAS'

WHEN executable_name LIKE '%IS_CC_DELINQUENT%' THEN 'Archown.PRIME17_CACCOUNTS, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'

WHEN executable_name LIKE '%IS_ACTIVE_OFFER%' THEN 'Archown.SAMIR_OFFER'

WHEN executable_name LIKE '%RIB_ENABLED%' THEN 'Archown.RIB11_CUSR, Archown.Fincl10_Accounts'

WHEN executable_name LIKE '%ACTIVE%' AND executable_name LIKE '%OFFER%' THEN 'Archown.SAMIR_OFFER'

WHEN executable_name like '%Historical cc%' THEN 'Archown.PRIME17_APPLOG, Archown.PRIME17_CACCOUNTS, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'

WHEN executable_name like '%CIF%' then 'ARCHOWN.Fincl10_GAM, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'

WHEN executable_name LIKE '%CIB_ENABLED%' THEN 'Archown.CIB11_CUSR, Archown.Fincl10_Accounts'

WHEN executable_name LIKE '%IS_DO%' THEN 'Archown.PRIME17_CACCOUNTS, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'

WHEN executable_name LIKE '%SAVINGS%' THEN 'ARCHOWN.Fincl10_GAM, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'

WHEN executable_name LIKE '%DUPLI%' THEN 'Archown.SAMIR_OFFER'

WHEN executable_name LIKE '%INFLOW%' THEN 'Archown.Fincl10_GAM, Archown.SAMIR_OFFER, Archown.Fincl10_ATO'

WHEN executable_name LIKE '%ACTIVE%' AND executable_name LIKE '%LOAN%' THEN 'Archown.Fincl10_GAM, Archown'

WHEN executable_name like '%Historical loan%' THEN 'ARCHOWN.Fincl10_GAM, Archown.Fincl10_GAC, Archown.Fincl10_LDT, Archown.Fincl10_LDTH'

WHEN executable_name like '%woff%' THEN 'Archown.PRIME17_CACCOUNTS, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'

WHEN executable_name like '%BLBD%' THEN 'Archown.PRIME17_CACCOUNTS, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'

WHEN executable_name like '%trading%' THEN 'Archown.Fincl10_GAM, Archown.SAMIR_OFFER, Archown.Fincl10_CMG'

WHEN executable_name LIKE '%EXISTING%' AND executable_name LIKE '%LOAN%' THEN 'Archown.Fincl10_GAM, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'

WHEN executable_name LIKE '%WATCH%' THEN 'Archown.Fincl10_ACCOUNTS, Archown.Fincl10_RCT'

WHEN executable_name LIKE '%AML%' then 'Archown.C360_CustomerAML'

WHEN executable_name LIKE '%Defaulted%' THEN 'Archown.Fincl10_GAM, Archown.Fincl10_GAC, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'

WHEN executable_name LIKE 'INSERTING UPDATING HAS DIGITAL LOAN' THEN 'Archown.Fincl10_GAM, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'

WHEN executable_name LIKE 'INSERTING PAYPLUS LOAN' THEN 'Archown.Fincl10_GAM, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'

WHEN executable_name LIKE 'INSERTNG MICRO LOAN FLAG' THEN 'Archown.Fincl10_GAM, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'

WHEN executable_name LIKE 'INSERT INTO WRITE OFF' THEN 'ARCHOWN.Fincl10_GAM, Archown.Fincl10_GAC'

WHEN executable_name LIKE '%IS_D015%' THEN 'Archown.PRIME17_CACCOUNTS, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'

WHEN executable_name LIKE '%ACTIVE PAYADVANCE%' THEN 'Archown.PRIME17_CACCOUNTS, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'

WHEN executable_name LIKE 'INSERTING STG EIM OBLIGATIONS' THEN 'Archown.Fincl10_LRS'

WHEN executable_name LIKE 'INSERTING ST MONTHLY INT AMT' THEN 'Archown.FINCL10_LDT'

WHEN executable_name LIKE 'INSERTING ST PRDEM' THEN 'Archown.FINCL10_LDT'

WHEN executable_name LIKE '%INSERTING SOLE TRADER FLAG%' then 'Archown.C360_GOLDENCUSTOMER'

WHEN executable_name like '%HAS_HISTORICAL_LOAN%' then 'ARCHOWN.Fincl10_GAM, Archown.Fincl10_GAC, Archown.Fincl10_LDT, Archown.Fincl10_LDTH'

WHEN executable_name like '%HIGH%' THEN 'ARCHOWN.Fincl10_GAM, Archown.Fincl10_ATO'

WHEN executable_name like '%HISTORICAL CREDIT CARD%' THEN 'Archown.PRIME17_APPLOG, Archown.PRIME17_CACCOUNTS, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'

WHEN executable_name like '%DECLINE%' THEN 'ARCHOWN.BPM_NCB_AP_CARDS_DETAILS_DECISION, ARCHOWN.BPM_NCB_CARDS_ROUTE_TABLE, ARCHOWN.LENDX_CL_CARDS_EXTTABLE, ARCHOWN.LENDX_LD_CREDITCARD_DECISION'

WHEN executable_name like '%CREDIT CARD DEL%' THEN 'Archown.PRIME17_CACCOUNTS, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'

WHEN executable_name like '%LOAN DELINQUENT%' THEN 'ARCHOWN.Fincl10_GAM, Archown.Fincl10_GAC, Archown.Fincl10_AAS'

WHEN executable_name like '%CLASSIFIED%' THEN 'ARCHOWN.Fincl10_GAM, Archown.Fincl10_GAC'

WHEN executable_name like '%existing cc%' THEN 'Archown.PRIME17_CACCOUNTS, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'

WHEN executable_name = 'INSERT INTO ACCT_CLS_FLG' THEN 'ARCHOWN.Fincl10_GAM, Archown.Fincl10_CMG'

WHEN executable_name LIKE '%BOB ENABLED%' THEN 'Archown.CIB11_CUSR, Archown.Fincl10_Accounts'

WHEN executable_name = 'INSERTING UNSECURED RISK SCORE VALUE' THEN 'Archown_Custom_Grmd.GRMD_FINAL_PRE_UN_SCORES'

WHEN executable_name = 'INSERTING CREDIT CARD RISK SCORE VALUE' THEN 'Archown_Custom_Grmd.GRMD_FINAL_PRE_CC_SCORES'

WHEN executable_name = 'INSERT INTO RWR TMP RTO,GAM' THEN 'ARCHOWN.Fincl10_GAM'

WHEN executable_name like '%UNEMPLOYED%' THEN 'Archown.C360_GOLDENCUSTOMER, ARCHOWN.BPM_NCB_CONSUMERLOAN_EXTTABLE, ARCHOWN.CIB11_SALARY_PAYMENT_REQUEST, ARCHOWN.CIB11_SALARY_TRANSACTION_REQUEST, ARCHOWN.C360_GOLDENCUSTOMERACCOUNT, ARCHOWN.C360_GOLDENCUSTOMER'

WHEN executable_name like '%INSERTING IS_MOBILE_APP%' THEN 'Archown.C360_GOLDENCUSTOMER, Archown.C360_CUSTOMERACTIVE, Archown.C360_GOLDENCUSTOMERKYC, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'

WHEN executable_name = 'INSERTING_ IS_MISSED PAYMENT OCMP INSTALOAN' THEN 'ARCHOWN.Fincl10_GAM, Archown.Fincl10_GAC, Archown.Fincl10_LDT, Archown.Fincl10_LDTH'

WHEN executable_name = 'INSERT INTO HAS STAFF ACCOUNT' THEN 'Archown.Fincl10_GAM, ARCHOWN.C360_GOLDENCUSTOMERSYSTEMMAPPING'

WHEN executable_name like '%LOAN DELIQUENT%' THEN 'ARCHOWN.Fincl10_GAM, Archown.Fincl10_GAC, Archown.Fincl10_AAS'

WHEN executable_name LIKE '%OWNER TYPE%' THEN 'ARCHOWN.Fincl10_GAM, Archown.Fincl10_AAS'

WHEN executable_name = 'INSERTING STG CUSTOMR BALANCES 1' THEN 'ARCHOWN.C360_GOLDENCUSTOMERACCOUNT'

WHEN executable_name = 'INSERTING CUSTOMER DISP AMOUNT,OUTSTANDING BALANCE AND COUNTS' THEN 'ARCHOWN.Fincl10_GAM, ARCHOWN.C360_GOLDENCUSTOMERSYSTEMMAPPING, ARCHOWN.FINCL10_LAM, Archown.Fincl10_RTH'

WHEN executable_name = 'INSERTING UNSECURED LOAN FLAG' THEN 'ARCHOWN.Fincl10_GAM, ARCHOWN.C360_GOLDENCUSTOMERSYSTEMMAPPING'

WHEN executable_name = 'INSERTING EXISTING FASTCASH' THEN 'ARCHOWN.Fincl10_GAM, ARCHOWN.C360_GOLDENCUSTOMERSYSTEMMAPPING'

WHEN executable_name = 'INSERTING UPDATING HAS DIGITAL LOAN 1' THEN 'ARCHOWN.FINCL10_GAM'

WHEN executable_name = 'INSERTING UPDATING HAS DIGITAL LOAN' THEN 'ARCHOWN.FINCL10_GAM'

WHEN executable_name = 'INSERTING ACTIVE CCBIZPREAPPROVED OCMP FLAG' THEN 'ARCHOWN.SAMIR_OFFER'

WHEN executable_name = 'INSERTTING BLOCK LISTED ACCOUNTS' THEN 'ARCHOWN.FINCL10_GAM, ARCHOWN.FINCL10_CMG'

WHEN executable_name = 'INSERTING CC PREQUAL CREDIT CARD EXISTING FLAG OCMP' THEN 'Archown.PRIME17_CACCOUNTS, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'

WHEN executable_name = 'INSERTING INTO STG OBLIGATIONS' THEN 'ARCHOWN.Fincl10_GAM, ARCHOWN.C360_GOLDENCUSTOMERSYSTEMMAPPING, ARCHOWN.FINCL10_LAM, Archown.Fincl10_GAC'

WHEN executable_name = 'INSERT INTO RWR TEMP OFFERS' THEN 'Archown.SAMIR_OFFER'

WHEN executable_name like '%internet%' then 'Archown.Fincl10_Accounts, Archown.RIB11_CUSR'

when executable_name = 'INSERTING PAYADVANCE OFFER FLAG' then 'ARCHOWN.SAMIR_OFFER'

WHEN executable_name = 'INSERTING CREDIT CARD EXISTING FLAG' THEN 'ARCHOWN.C360_GOLDENCUSTOMER, ARCHOWN.C360_GOLDENCUSTOMERSYSTEMMAPPING, ARCHOWN.PRIME17_PEOPLE, ARCHOWN.PRIME17_CARDX'

WHEN executable_name = 'INSERTINGIS_INSTALOAN_FLAG' THEN 'Archown.C360_CUSTOMERAML'

WHEN executable_name = 'INSERTING_CREDIT_LIMIT_CC' then 'ARCHOWN_CUSTOM_GRMD.GRMD_FINAL_PRE_CC_SCORES'

WHEN executable_name = 'INSERT INTO EMPLOYMENT_TENURE' THEN 'ARCHOWN.BPM_NCB_CONSUMERLOAN_EXTTABLE, [ARCHOWN].[C360_GOLDENCUSTOMER], ARCHOWN.CIB11_SALARY_PAYMENT_REQUEST, [ARCHOWN].C360_GOLDENCUSTOMERACCOUNT, ARCHOWN.CIB11_SALARY_TRANSACTION_REQUEST'

when executable_name = 'INSERT INTO IS_PRE_APPROVAL_PRE_QUALIFICATION' then 'ARCHOWN_CUSTOM_GRMD.GRMD_FINAL_PRE_CC_SCORES'

when executable_name = 'INSERTING CREDIT_LIMIT_INSTALOAN' THEN '[ARCHOWN_CUSTOM_GRMD].[GRMD_FINAL_PRE_UN_SCORES]'

WHEN executable_name = 'INSERT INTO CLD CLFD' THEN 'ARCHOWN.Fincl10_GAM, ARCHOWN.C360_GOLDENCUSTOMERSYSTEMMAPPING, ARCHOWN.Fincl10_GAC, ARCHOWN.Fincl10_LTDH'

WHEN executable_name = 'INSERT INTO REPAY COMP' THEN 'ARCHOWN.Fincl10_GAM, ARCHOWN.C360_GOLDENCUSTOMERSYSTEMMAPPING, ARCHOWN.Fincl10_GAC, ARCHOWN.Fincl10_LTDH'

WHEN executable_name = 'INSERT EMPLOYMENT_CHECK_FASTCASH' THEN 'ARCHOWN.BPM_NCB_CONSUMERLOAN_EXTTABLE, [ARCHOWN].[C360_GOLDENCUSTOMERSYSTEMMAPPING], ARCHOWN.CIB11_SALARY_PAYMENT_REQUEST, [ARCHOWN].C360_GOLDENCUSTOMERACCOUNT, ARCHOWN.CIB11_SALARY_TRANSACTION_REQUEST'

ELSE 'TBD' END Tables_Involved

FROM

catalog.executions AS e WITH (NOLOCK)

JOIN

catalog.executable_statistics AS es WITH (NOLOCK) ON e.execution_id = es.execution_id

JOIN

catalog.executables AS ex WITH (NOLOCK) ON es.execution_id = ex.execution_id AND es.executable_id = ex.executable_id

WHERE

e.package_name = 'ETL_UNIFIED_CUSTOMER_BASE.dtsx' and

e.status = 7 and

executable_name LIKE 'INSERT%'

ORDER BY