SP_SEND_DBMAIL - 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

SQL Server 2005 Forums

SQL Server Administration (2005)

SP_SEND_DBMAIL

Author

Topic

Kabelo
Starting Member

1 Post

Posted - 2007-08-08 : 07:52:57

I wrote a trigger as follows:

CREATE TRIGGER TR_ABC
ON TABLE_A
AFTER INSERT
AS
DECLARE @E_MAIL VARCHAR(255),
@MESSAGE VARCHAR(255)
SET @MESSAGE = 'A new call request has been logged'

SELECT @E_MAIL = E_MAIL
FROM TABLE_A AS A
LEFT OUTER JOIN TABLE_B AS B
ON B.SYSADMIN = A.SYSADMIN
WHERE SYS_ADMIN = 1

EXEC MSDB.DBO.SP_SEND_DBMAIL
@profile_name = 'SCINFO',
@recipients = @E_MAIL,
@body = @MESSAGE,
@subject = 'SCI Service Request';

The problem is everytime I insert data into the table I get an error stating that-:
'At least one of the following parameters must be specified. "@recipients, @copy_recipients, @blind_copy_recipients".
Mail queued.'

Please help.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-08 : 08:57:43

SELECT @E_MAIL = E_MAIL
FROM inserted AS A
LEFT OUTER JOIN TABLE_B AS B
ON B.SYSADMIN = A.SYSADMIN
WHERE SYS_ADMIN = 1

also if no row is inserted your @E_MAIL will be null.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources