Solved

Email Data from SQL 2005 View - Automatically

Posted on 2013-01-21
4
181 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
What is the best way to calculate hours worked 5 61
Delete from table 6 44
Analysis of table use 7 42
SQL query to summarize items per month 5 52
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

911 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now