Avatar of venmarces
venmarces
Flag for Canada asked on

SQl Server : Send an email alert when new record is added in a view

Hi

I have a view that is alimented from a program. I need to send an email alert to a group of persons (Active Directory) when new records are added to this view. Therefore, once new recorded are added I need to send by email the list od ID's of those records to this group. I thought about trigger but It is not active in my case so what is the simple manner to do it

Thanks    
Microsoft SQL ServerMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
venmarces

8/22/2022 - Mon
Bodestone

Well, you can't add a trigger to a view in that way, you need to add it to one of the tables that the view references if that is any help.

Alternatively you could have a table that records the max ID from the view.
Then have a job that compared the latest max ID from your email log to the max ID in the view and, if greater sends an email out about any IDs beween the 2 and then adds a new record with the new max ID and status of email sending.
Bodestone

I initially took it that you were OK with the functionality of database mail in sql 2k5 and up. If not this guide is fairly comprehensive:
http://www.databasejournal.com/features/mssql/article.php/3626056/Database-Mail-in-SQL-Server-2005.htm

You can specify any email address to send to but the from should be configured within database mail.
Sending internally should cause no issues. It's only when the recipient is outside your domain that things get complex and you need to organise witht the mail administrator to allow allow relaying from the SQL server box. I mention this as an aside since scope can change over time and it's an issue that hit me when I released a load of new stuff to people who instantly went "Cool, can w send it to.. as well"


Also, I know I may have been a bit hasty about the absolute statement about triggers and views without knowing more about the structures involved.

Anthony Perkins

>>Well, you can't add a trigger to a view in that way,<<
Actually you can add an INSTEAD OF TRIGGER to a VIEW.  But I would never recommend it used solely for the purpose of sending an email.  You are better off adding an entry to a table that can serve as a queue to be checked periodically by some SQL Server Agent job.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Anthony Perkins

>>I know I may have been a bit hasty about the absolute statement about triggers and views <<
Oops, never mind.  I am sorry, I had not read this.
venmarces

ASKER
Can someone tell me how I can send a Result DataSet by email using SQL SSIS

Please tell me details steps if possible

Thanks
ASKER CERTIFIED SOLUTION
Bodestone

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
venmarces

ASKER
very good
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Bodestone

If you were to be really fancy you could send it as an HTML email by pre-preparing the body text before executing sp_send_dbmail

With huge results sets though I am not sure of the overhead of this but I have achieved some nice emails by adding in style statements at the begining as well.

Of course normally that would be done by client end software but we don't have any of that until I have finished bring all our DB schemas in line and start learning one in detail.
SELECT @formattedbody = '<h1>Look at the results below</h1><table><tr><th>col1</th><th>col2</th><th>col3</th></tr>' +
(SELECT '<tr>' + '<td>' + col1 + '</td><td>' + col2 + '</td><td>' + col3 + '</td></tr>' FROM mytable WHERE myid = 5 FOR XML PATH(''),TYPE).value('.','VARCHAR(4000)')
+ '</table>'

EXEC msdb.dbo.sp_send_dbmail
@recipients=N'me@me.com;him@him.com',
@body=@formattedbody, 

@subject ='Message Subject',
@profile_name ='DatabaseMailProfile'

Open in new window

venmarces

ASKER
Thanks sir