Solved

Email Data from SQL 2005 View - Automatically

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
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…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

695 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