Creating A Trigger That Sends An Email
Jul 26, 2000
Can anyone assist me?
I am trying to create a trigger within our local database that will allow me submit an email to a local exchange server. Its an internal email that our company has created for responses by candidates when the original stored procedure meets a condition of TRUE.
Is SQL 7.0 capable of sending emails to a specified address through a Trigger? I was told it could but have yet to come across any documentation backing this up.
Would anyone know the generic syntax I can use to create such a trigger?
Any suggestions would be appreciated.
Thanks in advance,
Claude Johnson
cjohnson@staffmentor.net
View 3 Replies
ADVERTISEMENT
Jan 31, 2008
Hi all,
I have several DTSX packages which send out report files by mail. For security purposes, the SMTP server can only be reached from specified IP addresses, from specified processes, with specified sender address. I know all this information, except the process name that sends out the emails in a DTSX package. So my question is, what process can be identified as the one that sends out the email?
Thx!!!
View 3 Replies
View Related
Apr 26, 2007
Hi,
I've setup the option to mail the error to a person. When the option is on I get the error message by mail but the package does not finish (eg. the failing task does not become red and the output windows never says anything about the error) - if I set the option to off the task fails as expected.
Is there something I havn't set up correctly?
Regards
Simon
View 5 Replies
View Related
Dec 18, 2007
create proc emailnew
as
declare @From varchar(8000)
declare @Subject varchar(8000)
declare @Body varchar(4000)
declare @smtp varchar(8000)
declare @counter int, @tbl varchar(8000)
Declare @MailID int
Declare @hr int
Declare @To varchar(8000)
Declare @tblquery varchar(8000)
declare @id int, @deptemail varchar(8000), @tmpmth varchar(8000), @usedb varchar(8000)
set @from = 'name@mail.com'
set @subject = 'testheader'
set @body = 'testing successful'
set @smtp = 'smtp.com'
--=========================================================================
--============================ get database name =======================
IF (LEN(MONTH(GETDATE())) = 1)
BEGIN
Set @TmpMth = '0' + CAST(MONTH(GETDATE()) AS varchar(2)) --01
END
ELSE
BEGIN
Set @TmpMth = CAST(MONTH(GETDATE()) AS varchar(2)) --12
END
SET @UseDB = 'DATA' + CAST(YEAR(GETDATE()) AS varchar(4)) + @TmpMth --aia_DATA200712
--===================================================================
--============================ get table number =======================
set @counter = 1
while @counter >= 59
begin
IF (LEN(@counter) = 1)
BEGIN
Set @Tbl = '0' + @counter --01
END
ELSE
BEGIN
Set @Tbl = @counter --12
END
--=========================check if table being created exists
IF EXISTS (SELECT 1
FROM information_schema.schemata
WHERE catalog_name = 'temptable')
GOTO table_1
--=================================== get all email accounts =====================
set @tblquery = '
select ID, Email
INTO temptable
FROM
(
select distinct d.id, d.email from tt32_aia.dbo.department d right outer join tt32_aia.dbo.extension e ON (e.parentid = d.id) right outer join
' + @usedb + '.dbo.other' + @tbl + ' data ON (e.extn = data.extn) where callclass = '''' or callclass is null
UNION ALL select distinct d.id, d.email from tt32_aia.dbo.department d right outer join tt32_aia.dbo.extension e ON (e.parentid = d.id) right outer join
' + @usedb + '.dbo.inward' + @tbl + ' data ON (e.extn = data.extn) where callclass = '''' or callclass is null
UNION ALL select distinct d.id, d.email from tt32_aia.dbo.department d right outer join tt32_aia.dbo.extension e ON (e.parentid = d.id) right outer join
' + @usedb + '.dbo.local' + @tbl + ' data ON (e.extn = data.extn) where callclass = '''' or callclass is null
UNION ALL select distinct d.id, d.email from tt32_aia.dbo.department d right outer join tt32_aia.dbo.extension e ON (e.parentid = d.id) right outer join
' + @usedb + '.dbo.other' + @tbl + ' data ON (e.extn = data.extn) where callclass = '''' or callclass is null
)data
DECLARE deptemail_cursor CURSOR FOR select id, email
from temptable where id is not null
'
--=============================================================================
--====just above you can see the cursor.. below is sending the emails
--======================================================================
exec(@tblquery)
OPEN deptemail_cursor
FETCH NEXT FROM deptemail_cursor INTO @id, @deptemail
WHILE @@FETCH_STATUS = 0
BEGIN
--If LEN(@deptemail) > 0
--BEGIN
set @to = @deptemail
EXEC @hr = sp_OACreate 'CDo.message', @MailID OUT --CDo.message |CDONTS.NewMail <-- different mail server
EXEC @hr = sp_OASetProperty @MailID ,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
EXEC @hr = sp_OASetProperty @MailID ,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @smtp
EXEC @hr = sp_OASetProperty @MailID , 'From', @From
EXEC @hr = sp_OASetProperty @MailID , 'HTMLBody', @Body
EXEC @hr = sp_OASetProperty @MailID , 'Subject', @Subject
EXEC @hr = sp_OASetProperty @MailID , 'To', @to
EXEC @hr = sp_OAMethod @MailID , 'Send', NULL
EXEC @hr = sp_OADestroy @MailID
--END
FETCH NEXT FROM deptemail_cursor INTO @id, @deptemail
END
CLOSE deptemail_cursor
DEALLOCATE deptemail_cursor
Table_1:
drop table temptable
return
set @counter = @counter + 1
end
this is suppose to send email automatically to every email account that it will get from all the tables (around 250 tables).
the problem is its not sending, but if i try to take my code outside of the "SET @COUNTER = @COUNTER + 1 END" and close the if statement above, i can produce the correct result.. i'm thinking maybe its the positioning? but, there could be some overhauling needed to do with this.. sorry for posting the sp. sorry for the trouble.. please help me
View 2 Replies
View Related
Nov 30, 2004
I receive the following error when trying to invoke xp_sendmail 'xp_sendmail: failed with mail error 0x80070005'. However, for alert notifications the email works fine. SQL is using an alias account to send mail. This account doesn't have any funny characters. Any ideas why the alert system works but xp_sendmail does not? The invokation of xp_sendmail is being done by SA.
Thanks.
View 6 Replies
View Related
Apr 14, 2006
Hello
Can you setup a trigger to mail some one when a record is updated in SQL server 2005?
If you can, can anyone help me?
C
View 1 Replies
View Related
Jun 29, 2004
Hi,
I am looking for a SQL trigger that will send email notification to a mailing list whenever a folders contents have been changed.
Can anyone help on this as I have little to no experience with SQL??
View 1 Replies
View Related
Jan 5, 2006
Hi all,
I have a SQL Server Job that I would like to run when I receive an email saying "Data is now ready to be imported to SQL Server" ?
Is there a way to accomplish this.
- Vivek
View 2 Replies
View Related
Oct 16, 2006
Hi,
I have a trigger for a table which stores email information generated from an ACCESS form. The trigger should send an auto email response to users who submitted an email to request for their password (we have forgetful users!). There is something wrong with my trigger because the auto email is sent out with a blank body... I will appreciate any advice!
Thank you!
My trigger:
CREATE TRIGGER tr_SendPassword ON PIPEmail
FOR INSERT
AS
DECLARE @Password varchar(100)
DECLARE @EmailAddress varchar(100)
DECLARE @message varchar(100)
IF (select count(*) from inserted) = 1
BEGIN
IF exists (SELECT * FROM inserted
WHERE Subject = 'Forgot my password')
BEGIN
select @Password = 'We received an email request from you for your password. Your password for SAR Search is: ' + UserRole.Password,
@EmailAddress = [User].Email
from UserRole
join inserted on UserRole.WindowsUser = inserted.WindowsUser
join [User] on [User].WindowsUser = UserRole.WindowsUser
exec master.dbo.xp_sendmail @recipients=@EmailAddress,
@subject='SAR Search password request',
@message=@Password
END
END
View 4 Replies
View Related
Jun 2, 2006
I am new to developing as will be evident from this post. Your help will be greatly appreciated.
I am developing an intranet for our company using ASP.NET with a SQL backend. I am currently working on a suggestion box form.
I would like to have an email sent to specific persons when a new entry is made in the suggestion table. I have been able to configure the trigger and generate the email (This was easy). Formatting the email has proven more difficult to resolve.
The format I would like is somewhat as follows:
F_NAME L_NAME submitted the following suggestion:
IDEA
BENEFIT
APPROVE | DECLINE
The items in RED are columns in the table and the Blue Underlines are hyperlinks to change the Status column in the table.
How can I generate the email to contain the data from the inserted record and in the above format.
Being new at this I only now how to send a static email advising that the entry has been made.
Any help creating the dynamic email form for this trigger will be greatly appreciated.
Lastly, what books are most helpful for SQL, ASP.NET, and VBScript referencing and examples?
Thanks
View 4 Replies
View Related
Feb 4, 2002
I need to create a "simple" sql query but have it formatted and then emailed
to individuals. I am trying to convert over from Oracle but I am having a
hard time getting the query to be formatted the way I want. Here is the select statement I tried to use with no luck with xp_sendmail:
SELECT ALARM ID, TIMESTAMP as TIME, CLASS, RESOURCE, P, L, F, ALARM_MESSAGE
FROM ALARM_LOG WHERE ([TIMESTAMP] < GETDATE() - 1) ORDER BY [TIMESTAMP];
Here what the output should look like with the column headers underlined and
then the column data (notice the column heading ALARM MESSAGE is on the
next line before any data):
ALARM ID TIME CLASS RESOURCE P L F
-------------------- ----------- ----- ---------------- - - -
ALARM_MESSAGE
-------------------------------------------------------------
B3_TEMP_ALM 02-02@11:21 ALARM BUILDING3_TEMP N G G
BUILDING 3 TEMPERATURE ALARM, CALL SECURITY.
B5_TEMP_ALM 02-02@11:22 ALARM BUILDING5_TEMP N G G
BUILDING 5 TEMPERATURE ALARM, CALL SECURITY.
How can I run the query and have it formatted and emailed out?
What is the best way to accomplish this?
Thank you,
Mark
View 1 Replies
View Related
Jan 28, 2008
Hi,
If I want to automatically insert a record which has default value in a table,
how can I create the trigger?
View 5 Replies
View Related
Aug 18, 2005
I have a basic trigger that populated an audit table, but I want to add logic to that to send an email everytime the trigger is called,Is there a easy way to add code to my basic trigger to send an email to me everytime the data changes.Thanks
View 1 Replies
View Related
May 2, 2008
Hello to a new forum. I am very impressed with the level of ability I have been seeing on this forum and all the helpful posts that are going on.
I am hoping someone can help me solve my problem. I know enough SQL to be very dangerous how to create, delete, insert, update etc... and how to build queries. Here is my dilemma I need to have a piece of scheduling software send out reminder emails based on a DATETIME field. I am running SQL Server 2005, and the database mail is already configured and can successfully send out email from the management studio interface.
Does anyone know of a tutorial or can point me in the right direction to accomplish this task. I am well versed in PHP, and am hoping I might be able to get the server to trigger a PHP script or something to this extent.
View 1 Replies
View Related
Oct 17, 2015
I have created a trigger like this below:
CREATE TRIGGER TriggerName ON Table_Name AFTER INSERT AS
DECLARE @SERVICE varchar(40)
DECLARE @STATUS varchar(3)
SET @SERVICE=(SELECT [SERVICE] FROM inserted)
SET @STATUS=(SELECT [STATUS] FROM inserted)
[Code] ....
When I added some a new record to Table_Name with Status='X' nothing happened. I have tried to exec only script below:
EXEC msdb.dbo.sp_send_dbmail @recipients=N'moj_adres', @body= 'test', @subject = 'Subject!!!!', @profile_name = 'profil'
and I received email notification.
View 7 Replies
View Related
Aug 6, 2013
I need to trigger an email to user when a work order due date is approaching 1 day prior to the due date. Also I need to trigger when a user adds, delete, or modify work order to check on work order due.
Not sure how to query it in SQL.
Due Date = NeedByDate
user = AssignedTo
View 1 Replies
View Related
Oct 9, 2007
Hello,
I'm tryng to set up a CLR Trigger, when executing i get this error:
Msg 6522, Niveau 16, État 1, Procédure Trigger_EnvoiEmail, Ligne 1
Une erreur .NET Framework s'est produite au cours de l'exécution de la routine ou de la fonction d'agrégation définie par l'utilisateur 'Trigger_EnvoiEmail' :
System.Security.SecurityException: Request for the permission of type 'System.Net.Mail.SmtpPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.CodeAccessPermission.Demand()
at System.Net.Mail.SmtpClient.Initialize()
at System.Net.Mail.SmtpClient..ctor(String host)
at AffYEmailQueue.MailHelper.SendMailMessage(String from, String destinataire, String bcc, String cc, String subject, String Htmlbody, String Textbody, Int32 idAFFY)
at AffYEmailQueue.Triggers.EnvoiEmail()
Can anybody help me ? thanks
View 5 Replies
View Related
Jul 20, 2007
Do you guys have any good links or suggestions relative to this?
View 1 Replies
View Related
Oct 18, 2007
hello,
need help with a simple trigger i have been working on. the trigger automatically sends me an email out when a record is inserted, how ever i can't seem to get the row column data into the email. The part i do not understand is that I get the row column data information in the email if I update the row.
This is for 2005 SQL
Any direction would be greatly appreaciated
OneIDesigned
View 5 Replies
View Related
Apr 14, 2004
Does anyone have a stored procedure for sending an email (using SQLMail) when a new record is added to a database table?
I have SQLMail all setup, I just need a good example of a stored procedure or trigger that will sending an email with the details for a new record entry into a table.
thanks!
View 4 Replies
View Related
Nov 10, 2015
Trying to find tsql for TRIGGER which will send me email, if someone rename database in test/dev environment. I found it for CREATE and DROP database trigger but could not find for RENAME database.
View 8 Replies
View Related
Oct 18, 2015
I have a report that gets a Customer_Number parameter and sends a mail with that customer's data.I want my users to be able to get this report's results by sending an email to a certain email address with a customer number in the topic.
View 1 Replies
View Related
Aug 21, 2007
I need to send an email when my 'LastRunDate' field is 30 days old (i.e. It should send an email if the LastRunField = 7/21/2007).
I would need to include the matching fields in the database (i.e. MachineID, Description, etc.) then update that field to todays date.
I have a few values in the 'Frequency' field such as Daily, Monthly, Yearly. Daily would be 24 hrs, monthly 30 days, yearly 365 days from the lastrundate.
I am new to T-SQL & need a good p[lace to start.
Any sugesstions.
Thanks.
View 6 Replies
View Related
Oct 24, 2006
My question is it possible to send a report developed in reporting services via email as either a attachment or imbedded in the email, from an insert trigger placed on a table in sql.
If so could you please help with the string that would be need to achieve this.
The report will need to have one or two parameters passed to it from the table.
The report is built and working through reporting services already. I was impressed with the subscription services that reporting services has in place and would like to utilise the reports further by auto emailing out when a new record is placed in the table. To be sent out either as an attachment or imbedded in the email it self.
Thanks for any assistance in advance
David
View 2 Replies
View Related
Sep 20, 2007
Hi Everybody,
I am trying to setup a stored procedure that runs through a Reminders table and sends an email to users based on DateSent field being smaller than todays date. I have already setup the stored procedure to send the email, just having trouble looping through the recordset.
Code Snippet
CREATE PROCEDURE [dbo].[hrDB_SendEmail]
AS
BEGIN
DECLARE @FirstName nvarchar(256),
@LastName nvarchar(256),
@To nvarchar(256),
@ToMgr nvarchar(256),
@Subject nvarchar(256),
@Msg nvarchar(256),
@DateToSend datetime,
@Sent nvarchar(256),
@ReminderID int,
@RowCount int,
@Today datetime,
@Result nvarchar(256)
-- Get the reminders to send
SELECT
@ReminderID = r.intReminderID,
@DateToSend = r.datDateToSend,
@FirstName = e.txtFirstName,
@LastName = e.txtLastName,
@To = e.txtEmail,
@Subject = t.txtReminderSubject,
@Sent = r.txtSent
FROM
(auto_reminders r INNER JOIN employee e ON r.intEmployeeID = e.intEmployeeID) INNER JOIN ref_reminders t ON r.intReminderType = t.intReminderTempID
WHERE
(((r.datDateToSend)<20/12/09) AND
((r.txtSent)='False'))
-- Send the Emails
WHILE(LEN(@To) > 0)
BEGIN
EXEC @Result = sp_send_cdosysmail @To, @ToMgr, @Subject, @Msg
END
-- Mark the records as sent
IF @Result = 'sp_OAGetErrorInfo'
BEGIN
SELECT @Sent = 'Error'
END
ELSE
BEGIN
SELECT @Sent = 'True'
END
UPDATE auto_reminders
SET
auto_reminders.txtSent = @Sent, auto_reminders.datDateSent = @Today
WHERE
intReminderID = @ReminderID
END
GO
From the code you can probably tell I am new to writing stored procedures, so I apologise for any obvious errors. My major problems are :-
how to loop through each record
how to get todays date
whether the struture of the procedure is correct
Also, if you think there is an easier way or a better method, please suggest it. I am open to any suggestions you may have,
Thanks in advance
Ben
View 1 Replies
View Related
Mar 18, 1999
I began installing SQL Server 7.0 the other day on a standard NT Server. When I got to the installation options, Enterprise Edition was the only server option. It wouldn't install on standard NT. Nothing on the box, in the box, or printed on the cd indicated that it was the Enterprise Version. The error message says something about not being able to install on this server from this CD.
We called MS and they did admit to sending an unknown number of CD's out with the wrong setup. I just thought I would pass that on to those of you who might be questioning your own sanity.
Good luck getting the replacement copy. They are telling us that it will be 4+ weeks before they overnight our copy to us. I won't even try to figure out why it would take that long to get a copy. Let us all know if any of you have run into this. I would like to see how common this is.
View 1 Replies
View Related
Jan 23, 2007
We have SQL server 2005 Reporting Services SP1. In Report Manager I have a shared subscription that runs every hour between 8am and 6pm (I created the shared subscription in Report Manager, then modified it in Sql Server Management Studio to run the hours I wanted). I have a second shared schedule that runs Tuesday and Friday at 7am. For 2 separate report subscriptions - one uses the hourly schedule and the other uses the 2-day schedule, the first time they run during the day it sends 2 email - after that it sends only 1 email, as it should. I created a subscription to email a report when the report content is refreshed from a report snapshot. In Properties...Execution, I selected to render this report from a report execution snapshot, and selected my shared schedule. For the History property I have selected Allow report history to be created manually and Store all report execution snapshots in history; I am not using a schedule to add snapshots to report history in the History property.
When I look at the job in Sql Server Management Studio, it has only ran once each hour. When I look at the snapshot history, there is only one record for each hour. Why is the first scheduled instance sent twice instead of once?
View 8 Replies
View Related
Dec 7, 2005
I want to create a trigger in SQL 2000 Enterprise, but not sure about triggers, how they work, etc. I just know that I was told I could create a trigger when new info is added to one table and check another table to see if this info is already in the other table, if not, copy the data to the other table too.
Would this be what a trigger can do for me?
what would be the best place to learn how triggers work, how to write one and install it, test it, etc?
View 2 Replies
View Related
Oct 22, 2007
I have 3 tables on my db, Projects, ProljectAllocationLog and Users
Project consists of Projectid(PK), ProjectName, UserID
ProjectAllocationLog consists of ProjectAllocationID(PK), Projectid, UserID,Date
Users consists of UserID (PK), Fullname
Over the course of time the user allocated to a project may change. The db has been set up to always show the most current user in the UserID of the Projects table,
I want to create a log that will record everytime that user has changed. (ProjectAllocationLog)
Having read through some examples posted on Forums, I believe that I can do this with a trigger, but I am not sure if I am doing it right, the trigger I have written is
Create Trigger tr_UpdateAllocationLog
ON Projects
AFTER Update
AS
If NOT UPDATE (Userid)
DECLARE @PROJECTID
DECLARE @NEWUSER
DECLARE @PREVIOUSUSER
SET @PROJECTID= (SELECT projected FROM Inserted)
SET @NEWUSER = (SELECT UserID from Inserted)
SET @ PREVIOUSUSER = (SELECT UserID from Deleted)
If @NEWUSER <> @PREVIOUSUSER
INSERT INTO ProjectAllocationLog (ProjectID, UserID, Date) VALUES (@PROJECTID, @NEWUSER, GETDATE())
Go
I would appreciate any comments
View 11 Replies
View Related
Apr 17, 2008
Hello,
I do have one table "ABC" in DB "Master" & other table "XYZ" in DB "Test".
Both tables are having same structures & same data currently. Now i want to create trigger in such a way that after every insertion & updation on table "ABC" in DB "Master" will insert & update records in table "XYZ" in DB "Test" respectively.
Can any one help me out?
Thanks
Prashant Hirani
Where Dreams Never Ends
View 7 Replies
View Related
Jan 11, 2008
I have a package which truncates files, fills them with data andshould send a mail with the files as attachment when it finishesrunning. In between, when the task is still running mail is sent withempty files. When I take a look at the directory (at the end of thetask), where the files are created I find out that the files are notempty.(e.g. in task: doA->doB->doC->SendMail. But instead of waiting tilldoB->doC finishes, it does the following: doA->SendMail ... may bebecause doB takes long.)Can I indicate anywhere that it should delay the mail until at the endof the task?Thanks in advance.Harp
View 1 Replies
View Related
Aug 16, 2004
I need help writing an Update trigger that puts the current date in a changed record. I understand the basic idea but can't seem to get it to work. Any help would be greatly appreciated
View 1 Replies
View Related
Jun 5, 2004
i am trying to create a trigger which when I insert a "y" on a student table in insCheck column, instructor table will create a record of the same person.
The reason is there are two tables are in my DB, student and instructor table. Student can be a instructor so whenever insCheck conlum in studnent table is checked as "y", instructor table should be populated.
Both table studentId and instructorId inserted as manually so that I am not sure how i should populate the instructor's table just fName, mI, and lName and I have go back to the instructor table and populate the rest of the table manually or is there any way to poppulate the insturctorid also while trigger is populate the name onthe instructor table.
My Two tables DDL are like down below
create table student(
studentId char(4) not null primary key,
fName varchar(25) not null,
mI char(1) null,
lName varchar(25) not null,
insCheck char(1) not null,
);
create table instructor(
instructorId char(4) not null primary key,
fName varchar(25) not null,
mI char(1) null,
lName varchar(25) not null,
instructorQual varchar(100) not null,
);
thanks for your help in advance!
gazawaymy
View 6 Replies
View Related
