Solved

Email Data from SQL 2005 View - Automatically

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 express 24 38
Move SQL 2005 Express to Server 2012R2 19 126
Analysis of table use 7 48
convert null in sql server 12 34
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

777 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