E-mail trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

E-mail trigger

Author

Topic

ictstage
Starting Member

9 Posts

Posted - 2008-12-12 : 05:11:54

Oke guys I realy need your help

I myself have no real knowledge of SQL I know how to get data from certain tables but thats it.

Oke here is the problem. I wanna put a trigger on a table in a database. This trigger needs to send a warning E-mail when it is empty. This shows us that it wen't wrong.

We first wanted to put a E-mail warning at the end of the Job.
But we cannot do this because it's another programma who uses the data base so we aren't realy running a Job.

I Realy Hope you guys can help me. Tnx in advance

Ps: Don't mind my grammer please got dyslexia and english isn't my native langue

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-12 : 05:19:14

this link describes the steps.

http://blog.netnerds.net/2008/02/create-a-basic-sql-server-2005-trigger-to-send-e-mail-alerts/

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-12-12 : 05:38:33

You can set up a trigger on DELETEs and UPDATEs which will check table size and send an email (see below).
But triggers will not fire if a table is TRUNCATED. If this is possible let us know.

FOR Tigger:
First you need to set up an Email Profile in SQL Server "Management\Database Mail"
Then:
CREATE TRIGGER checkTableEmpty ON dbo.TableA AFTER DELETE AS

DECLARE @Size varchar(50)

SET @Size = (SELECT count(*) from dbo.TableA)

IF @Size = 0
BEGIN
DECLARE @msg varchar(500)
SET @msg = 'Table A has "' + @Size + '" rows'
--// CHANGE THE VALUE FOR @recipients
EXEC msdb.dbo.sp_send_dbmail @recipients=N'person@hotmail.com',
@body= @msg, @subject = 'SQL Server Trigger Mail',
@profile_name = 'sqlmailProfileName'
END
GO

ictstage
Starting Member

9 Posts

Posted - 2008-12-12 : 07:27:02

OKe guys Im going to try.

ill let you guys know If i run in to any other problems

ictstage
Starting Member

9 Posts

Posted - 2008-12-15 : 03:19:11

So we tried it. We used your information darkdusky now the problemm we have the moment we put the trigger on the table we. we aren't able to finish the inport export of data.

The error where getting is msdb.dbo.sp_send_dbmail Function doesn't excist

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-15 : 03:43:14

quote:
Originally posted by ictstage

So we tried it. We used your information darkdusky now the problemm we have the moment we put the trigger on the table we. we aren't able to finish the inport export of data.

The error where getting is msdb.dbo.sp_send_dbmail Function doesn't excist

are you using sql 2005? if not, you need to use xp_sendmail instead of sp_send_dbmail as latter is only available on sql 2005 & above
just try below query & see what's version you're having

SELECT @@VERSION

if 8. then its sql 2000 and if 9. then 2005.
even if its 2005 you need to make sure compatibility level used is 90. use below for that

EXEC sp_dbcmptlevel 'yourdatabase'

ictstage
Starting Member

9 Posts

Posted - 2008-12-15 : 07:34:04

Where using 2000 at the moment so we have changed the sp_send_dbmail to xp_sendmail and we are still getting errors.

These errros Could not find stored procedure xp_sendmail.
So I realy don't even know what to do now.

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-12-15 : 08:26:51

try
master..xp_SendMail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-15 : 08:59:40

http://doc.ddart.net/mssql/sql70/xp_aa-sz_11.htm

ictstage
Starting Member

9 Posts

Posted - 2008-12-19 : 02:25:41

Oke guys after Alot of trying we still get these errors.

Number: -2147217900
Description: xp_sendmail: Invalid parameter '@body'
Org. Source: Microsoft OLE DB Provider for SQL Server

Number: -2147217900
Description: xp_sendmail: Invalid parameter '@profile_name'
Org. Source: Microsoft OLE DB Provider for SQL Server

The trigger we made is

CREATE TRIGGER checkTableEmpty ON dbo.Art_Spec AFTER DELETE AS

DECLARE @Size varchar(50)

SET @Size = (SELECT count(*) from dbo.Art_Spec)

IF @Size = 0
BEGIN
DECLARE @msg varchar(500)
SET @msg = 'ERROR "' + @Size + '" rows'
EXEC master.dbo.xp_sendmail @recipients=N'fake@mail.com',
@subject = 'SQL Server error',
@profile_name = 'Administrator'
END

Sorry to bother you guys again

ictstage
Starting Member

9 Posts

Posted - 2008-12-22 : 02:05:48

Is no one able to help me ?

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-12-22 : 04:26:19

Check if:

1) SQLMail service shut down
2) There is a MAPI profile defined / a default MAPI profile set.

ictstage
Starting Member

9 Posts

Posted - 2008-12-22 : 05:32:30

Checked both are working a intended

ictstage
Starting Member

9 Posts

Posted - 2008-12-22 : 05:49:01

Oke now at the moment we only get 1 error

ERROR:
Program version: DataProcessor 4.6.5
Number: -2147217900
Description: xp_sendmail: Invalid parameter '@profile_name'
Org. Source: Microsoft OLE DB Provider for SQL Server

The strange thing about this is. It says the profile isn't right.
but we tested it and the profile name is corect ( we tested it by going to MS MMC And the to SQL mail and there testing the profile name and it says it is correct )

We even looked at HKEY_CURRENT_USER\Software\Microsoft\Windows Messaging Subsystem\Profiles

This matches the profile name we give. So we realy do not understadn wat is going wrong.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-22 : 06:29:05

xp_SendMail is deprecated. Use send_dbmail instead.
Also, send_dbmail is asynchronous.

E 12°55'05.63"
N 56°04'39.26"

NeilG
Aged Yak Warrior

530 Posts

Posted - 2008-12-22 : 06:38:38

they can't there using sql server 2000

ictstage
Starting Member

9 Posts

Posted - 2008-12-22 : 07:55:17