1. To overcome that SQL Server limitation you can modify mySendMail code as the following CREATE FUNCTION mySendMail( @recipient VARCHAR(30), @subject VARCHAR(255), @message VARCHAR(8000)) RETURNS INT AS BEGIN DECLARE @ret INT EXEC @ret = master.dbmail.SendMail @recipient, @subject, @message, 'helpdesk@domain.com' INSERT INTO dbmail.pipe (message_type, recipients, subject, message, reply_to, content_type, priority, attachment_id) VALUES ('EMAIL', @recipient, @subject, @message, 'helpdesk@domain.com', 'text/plain', 1, NULL ) RETURN (@@identity) END This way you can call it from anywhere. 2. Please check out excellent article about calling UDFs and known workarounds http://msdn.microsoft.com/SQL/sqlreldata/TSQL/default.aspx?pull=/library/en-us/dnsqlpro03/html/sp03e9.asp CREATE PROCEDURE dbmail.SendMail( @recipients VARCHAR(8000), @subject VARCHAR(255), @message VARCHAR(8000), @reply_to VARCHAR(255) = NULL, @content_type VARCHAR(50) = 'text/plain', @priority INT = 1, @attachment_id INT = NULL) AS /*****************************************************************/ /* dbmail.SENDMAIL procedure - use this procedure to send email */ /* messages. */ /* Email send method (SMTP, MAPI, Lotus Notes) should */ /* be configured in DB Mail console */ /*****************************************************************/ BEGIN INSERT INTO dbmail.pipe (message_type, recipients, subject, message, reply_to, content_type, priority, attachment_id) VALUES ('EMAIL', @recipients, @subject, @message, @reply_to, @content_type, @priority, @attachment_id ) RETURN @@identity END : The examples under Sending Emails, Microsoft SQL Server, Example 2, shows : that you can create a function and then use it in a select statement. : Unfortunately this won't work. : I keep getting the following error message. : Server: Msg 557, Level 16, State 2, Procedure mySendMail, Line 8 : Only functions and extended stored procedures can be executed from within a : function. : Has anyone found a way around this without creating an extended stored : procedure?
|