Jul 3, 2025

 USE [c1]

GO


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

SET ANSI_NULLS ON

GO


SET QUOTED_IDENTIFIER ON

GO


CREATE TABLE [dbo].[FailedJobLog_Archive](

[JobName] [nvarchar](255) NULL,

[LastRunDate] [datetime] NULL,

[Notification] [bit] NULL,

[ErrorMessage] [nvarchar](max) NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO


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

GO


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


USE [c1]

GO


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

SET ANSI_NULLS ON

GO


SET QUOTED_IDENTIFIER ON

GO


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]

GO


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

GO



---------


---exec usp_CheckFailedJobsAndSendEmail  

create PROCEDURE usp_CheckFailedJobsAndSendEmail      

AS      

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>      

        <html lang="en">      

        <head>      

            <meta charset="UTF-8">      

            <meta name="viewport" content="width=device-width, initial-scale=1.0">      

            <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>      

            <div class="container">      

                <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>Job Name</th>      

                        <th>Last Run Date & Time</th>      

                        <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>      

                <div class="footer">      

                    <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>      

            </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

    es.start_time desc

Posted by

No comments:

Post a Comment

If you have any doubt or question, please contact us.

Subscribe to: Post Comments (Atom)