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

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.

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.

>>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 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.

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

Please tell me details steps if possible

Thanks

very good

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'

Select allOpen in new window

Thanks sir