question

Venugopal-1631 avatar image
0 Votes"
Venugopal-1631 asked LiHongMSFT-3908 answered

How to send email last updated(Inserted) records from AuditLog table using Stored Procedure in SQL SERVER

Hi Folks,

i have AuditLog table in my DB. This table is captureing mutiple ETL run information. i want to send email current records on every run. Below is my sample table. Can you please help me how to write store procedure to achive this. if any sample code would be more helpful for me. my client said No Triggers.


205121-audit-log.png






sql-server-generalsql-server-transact-sql
audit-log.png (63.4 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Below is my sample table.

I don't see a table (definition), only a screenshot and I can't query screenshot.
Please post table design as DDL, some sample data as DML statement and the expected result.
And some more details about that should work? Polling frequently the data or ... ?
0 Votes 0 ·

Hi Olaf. Thanks for you reply.

This is The log table. There is data in screen shot. im unable to paste the data here. in example now ETL ran then what ever the information on the specific run records i need to send as email.

CREATE TABLE [PACEMETA].[Audit_log](
[OBJECT_ID] [nvarchar](10) NOT NULL,
[PIPELINE_NAME] [nvarchar](50) NOT NULL,
[SCHEMA_NAME] [nvarchar](100) NULL,
[TBL_NAME] [nvarchar](50) NULL,
[PIPELINE_RUNID] [nvarchar](128) NOT NULL,
[ROWS_READ] [bigint] NULL,
[ROWS_COPIED] [bigint] NULL,
[ROWS_SKIPPED] [bigint] NULL,
[COPY_DURATION_SECS] [bigint] NULL,
[THROUGHPUT] [decimal](13, 2) NULL,
[DIU_USED] [int] NULL,
[PARALLELCOPIES_USED] [int] NULL,
[MIGRATION_STATUS] [nvarchar](50) NULL,
[INSERTED_DATE] [datetime] NULL,
[TIME_TO_FIRST_BYTE] [int] NULL
) ON [PRIMARY]
GO

0 Votes 0 ·

1 Answer

LiHongMSFT-3908 avatar image
0 Votes"
LiHongMSFT-3908 answered

Hi @Venugopal-1631
First, you need to configure database mail in SQL Server. Refer to this article: Configure Database Mail

i want to send email current records on every run.

It seams using a DML trigger with a reminder e-mail message will be a good solution. Refer to this thread for more details: Send e-mail from a trigger

However, considering that your clients don't want to use triggers, you can try setup a SQL job, with a stored procedure to check the table periodically and send out an email as needed.
Use the SQL Server Change Data Capture or Change Tracking features(CHANGETABLE function) to get the changed records for a table and information about these changes. It queries the records stored in the internal change tracking tables.
Then, a stored procedure that reads the records should be used, and a job that will run once in a while.

A drawback of these features is that they do not track the time of the change nor old and new values of the changed record. Since the operation is synchronous, it prolongs the transaction time. Another drawback is that you have to query the tables periodically and then send the notification email.

Best regards,
LiHong


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.