Sending Locking Alert

  • I have 2 tables the first one holds the dbwait information(locks), and the second table holds the db sessions related to the locks. I'm in build block stage where I have the files loading to SQL, and will make updates to the Table Defs(PK,Index) after some testing.  Just trying to work thru the logic of an automated email based upon the data. I first need to read the dbwait table to see if I have any locked records (lockmethod) will be 'Active'. Then I need to check who it's blocking (lockmethod) will be 'Begin', and Lockrcd will be the same. After I find who is locking who I need to read the dbsession table and relate the txnnbr field of the 2 tables. Then I would like to send an EMAIL to the head of the lock chain(dbwait), and tell them which users they have locked.

    Email sample:

    From: lockalert@xxx.com

    To:  --> qaduser  from the session table@xxx.com

    Subject: Locking records

    Body:

    (qaduser  from the session table) you are currently holding locks in pgm(field qadmenu) and are blocking the following users:

    -- list on users blocked --

    (qaduser  from the session table)     --->  locked records from dbwait table (lockmethod) will be 'Begin'

     

    Thanks

    CREATE TABLE [dbo].[db_wait](
    [dbname] [varchar](255) NULL,
    [txnnbr] [varchar](255) NULL,
    [osuser] [varchar](255) NULL,
    [locaktype] [varchar](255) NULL,
    [locktbl] [varchar](255) NULL,
    [lockrcd] [varchar](255) NULL,
    [lockmethod] [varchar](255) NULL
    ) ON [PRIMARY]
    GO

    Insert Into db_wait
    Values('dbprod','79','npr_con','rec','541','4354','X L Active')
    go
    Insert Into db_wait
    Values('dbprod','51','npr_con','rec','541','4354','X Q H Begin')
    go



    /****** Object: Table [dbo].[db_session] Script Date: 9/28/2021 9:56:00 AM ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[db_session](
    [dbsession] [varchar](255) NULL,
    [qaduser] [varchar](255) NULL,
    [osuser] [varchar](255) NULL,
    [dbtask] [varchar](255) NULL,
    [ptsnbr] [varchar](255) NULL,
    [qadpgm] [varchar](255) NULL,
    [qadmenu] [varchar](255) NULL,
    [tnxnbr] [varchar](255) NULL,
    [ostask] [varchar](255) NULL,
    [locktime] [varchar](255) NULL
    ) ON [PRIMARY]
    GO

    Insert Into db_session
    Values('WEB',Usera','npr_con','3227','pts/6','pgma','4.4.4','51','27480','10:53:27')
    go
    Insert Into db_session
    Values('WEB',Userb','npr_con','3228','pts/8','pgmb','4.4.4','79','27481','10:55:27')
    go

     

  • I can't see a question in your post. Are you blogging?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • My question is how do I create the automated email based upon request.

    Thanks.

  • Automated e-mails can be generated from SQL Agent jobs, SSIS packages and using various other technologies.

    I assume that's not the answer you're after, but it does answer your question, bearing in mind that you have not defined what 'request' is.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Looking for the SP logic that joins by txnnbr the 2 tables and produces the logic to determine which user is doing the locking and email with criteria provided in early posts.

    Thanks.

  • any examples of how to make this work?

    Thx.

  • How can I send the alert (email) to the user(qad_user from session table) where db_wait table has LockMethod = 'Active' . I want to send the screen that they have active (Qadmenu).

    Thx.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply