Last post Feb 02, 2019 06:08 AM by yrb.yogi
Jan 29, 2019 06:58 PM|RuthlessRoth|LINK
I am considering running this query and send out an email very minute
Trying to get the time a new record was inserted into a table and send out an email.
It will be formatted with a HTML boy in it.
Would this create a lot of
LEFT JOIN POLineItem ON POReceipt.PONumber = POLineItem.PONumber
AND POReceipt.ItemNumber = POLineItem.ItemNumber
LEFT JOIN POHeader ON POLineItem.PONumber = POHeader.PONumber
LEFT JOIN Orderheader ON POLineItem.jobnumber = orderheader.JobNumber
LEFT JOIN Employee ON Orderheader.CSR = Employee.EmployeeName
LEFT JOIN Vendor ON POHeader.VendorID = Vendor.VendorID
WHERE POLineItem.LineItemType = 0
AND email <> ''
AND Convert(DATE, POReceipt.Receivedate) = Convert(DATE, Getdate())
AND Convert(Time, POReceipt.CreateDatim)<Convert(time, Getdate())
AND dateadd(minute, 0,POReceipt.CreateDatim)>dateadd(minute,-1, Getdate())
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQL2012Mail'
,@recipients = 'emailaddress'
,@body = ''
,@subject = 'Got one'
,@body_format = 'HTML'
Jan 30, 2019 02:25 AM|Ackerly Xu|LINK
What problem you have met?
If you don't know how to run a sql job every minute, please refer to the link below
If you don't know how to write your stored procedure, which could format your body as html, you should cust the result of your select query to nvarchar and then set it to @subject.
SET @tableHTML =
CAST ( ( here write your select query
) AS NVARCHAR(MAX) )
For more information , please refer to the link below
sending html body is in the C. Sending an HTML e-mail message section, the document uses a table as html format
Also , refer to https://stackoverflow.com/questions/13300881/how-to-send-email-from-sql-server
Please ensure you have configured email profile
Jan 30, 2019 01:30 PM|RuthlessRoth|LINK
Sorry the bottom of my email was cut off.
May questions is, running a SQL job every minute will it cause a lot of overhead on the system.
I have been testing it and it runs so fast it does not even register a time duration in the history of the job.
I have it running between 7AM and 6PM each day.
Are there any downside to doing this?
Jan 31, 2019 01:25 AM|Ackerly Xu|LINK
Sql server has tool and t-sql to monitor the state of your sql job, about the run_time , run_duration and other metrics.
Please refer to the link below for more details.
Feb 02, 2019 06:08 AM|yrb.yogi|LINK
Share your table structure & data records available in the tables.
There will be no harm if you have created a good data structure, one minute job will run & give you desired result.
You will find the history of the Job in the JOB LOG.
j.name as 'JobName',
msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime'
From msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory h
ON j.job_id = h.job_id
where j.enabled = 1 --Only Enabled Jobs
order by JobName, RunDateTime desc