Solved

Email Data from SQL 2005 View - Automatically

Posted on 2013-01-21
4
188 Views
Last Modified: 2013-09-18
I have created a custom View that populates with todays new customers. I would like an email sent everytime a new customer is added. I am looking for a solution or method to email as new customers are added into system. My SQL 2005 View name is NewCustomers. I need something that maybe watches this view and as new data populates it sends the email with the data in the view in the body of the text. (or in an attachment if need be)
0
Comment
Question by:allenkent
  • 2
4 Comments
 
LVL 8

Expert Comment

by:virtuadept
ID: 38803308
First you can configure MS SQL Server to be able to send emails with Database Mail feature.

http://www.databasejournal.com/features/mssql/article.php/3626056/Database-Mail-in-SQL-Server-2005.htm

Then you can set up a SQL Server Agent job to run your view periodically throughout the day and email the results.
0
 
LVL 22

Accepted Solution

by:
Steve Wales earned 500 total points
ID: 38803718
Once you have Database Mail configured, profiles added and correct permissions granted, you could create a trigger on the table that sends an email

Something like this (untested, going from top of my head):

create trigger email_on_new_trig on tblCust
after insert
as
  Declare @newcust char(10), @newcustname char(50), @msg char(100)

  select @newcust = CustID, @newcustname = CustName
  from Cust c join Inserted i on c.CustID = i.CustID;

  set @msg = 'New Customer Created: '+@newcust+'  Name: '+@newcustname

  exec msdb.dbo.sp_send_dbmail
       @profile_name = 'Your DBMail Profile'
      ,@recipients = 'you@yourdomain.com'
      ,@subject = 'New Customer created'
      ,@body = @msg
go

Open in new window


(You may need to tweak it for exact syntax, as I said, it's off the top of my head)
0
 
LVL 8

Expert Comment

by:virtuadept
ID: 38864027
Just checking if those previous answers were helpful or not?
0
 

Author Closing Comment

by:allenkent
ID: 39504355
This got me moving in the correct direction. Used different system to accomplish system.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question