September 28, 2021 at 2:34 pm
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
September 28, 2021 at 2:38 pm
I can't see a question in your post. Are you blogging?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 28, 2021 at 3:07 pm
My question is how do I create the automated email based upon request.
Thanks.
September 28, 2021 at 3:58 pm
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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 28, 2021 at 4:42 pm
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.
September 29, 2021 at 10:59 am
any examples of how to make this work?
Thx.
October 4, 2021 at 6:10 pm
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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply