Solved

Email Data from SQL 2005 View - Automatically

Posted on 2013-01-21
4
189 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In this article I will describe the Copy Database Wizard 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.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

734 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