Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Send Email using T-SQL procedure


Send Email using T-SQL procedure

Author
Message
David Paskiet
David Paskiet
Old Hand
Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)

Group: General Forum Members
Points: 317 Visits: 202
I am currently in a small bind when trying to send email from a SQL server (SQL 2K5 64 Bit). The procedure that is currenty being used is calling the sp_OAcreate. This is not my most preferred method of doing this. Doing this particular process in the SQL memory space is apparently causing a variety of issues up to and including restarting my SQL Server. I looked at the current process and it appears my predecessor took a t-sql proc from somewhere on line and modified it. Does anyone have any suggestions on how to accomplish this task without loading any third party tool or creating another security hole?Discuss

Kindest Regards,
David

** Obstacles are those frightening things that appear when we take our eyes off the goal. **
David Paskiet
David Paskiet
Old Hand
Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)

Group: General Forum Members
Points: 317 Visits: 202
I am sticking the code being used now here:

CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" ",
@attachment nvarchar(100)= " ",
@bodytype varchar(10)=" "
/*********************************************************************

This stored procedure takes the parameters and sends an e-mail.
All the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
References to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp

***********************************************************************/
AS
Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)
declare @Comment Varchar(1000)

--************* Create the CDO.Message Object ************************
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value',
'mail.to-me.com'

-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
Select @bodytype=
CASE @bodytype
when ' ' then 'TextBody'
else @bodytype
end


print @attachment
EXEC @hr = sp_OASetProperty @iMsg, @bodytype, @Body
if @attachment is not null
begin
EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment',@Comment out, @attachment
end
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

-- Sample error handling.
IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END

-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg

Kindest Regards,
David

** Obstacles are those frightening things that appear when we take our eyes off the goal. **
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14984 Visits: 39002
with SQL 2005 and above, tehre is a built in service you can use by calling msdb.dbo.sp_send_dbmail

you have to create a profile in SSMS (there's a simple wizard for this) so that SQL knows which SMTP server you use to send the mail with. once that's set up, you can use it in your code...the nice part is that it is asynchronous, so your proc doesn't wait for the mail to be delivered the way sp_OACreate does.

example code:


declare @body1 varchar(4000)
set @body1 = '<html><head>
<title> Embedded Logo Example</title>
<meta name="Generator" content="EditPlus">
<meta name="Author" content="">
<meta name="Keywords" content="">
<meta name="Description" content="">
</head>
<body>
<table><tr><td valign="top" align="left">MyHeader</td></tr>
<tr><td valign="top" align="left"><img src="cid:sqlservercentral_logo.gif" width="235" height="70" border="0" alt=""></td></tr>
</table>
</body></html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name='MyEmail as scripts',
@recipients='lowell@someDomain.net',
@subject = 'SQl 2008 email test',
@body = @body1,
@body_format = 'HTML',
@query = 'SELECT top 3 * from sysobjects where xtype=''U''',
@query_result_header = 0,
@exclude_query_output = 1,
@append_query_error = 1,
@attach_query_result_as_file = 1,
@query_attachment_filename = 'results.txt',
@query_result_no_padding = 1,
@file_attachments = 'C:\sqlservercentral_logo.gif'




example setup:




Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20941 Visits: 9671
Here's the preffered way to send mail in sql server 2k5+


      EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'JOB SQL',
@recipients = 'rgregoire@fordia.com;remi@remigregoire.com;sbergeron@fordia.com;dcardinal@fordia.com',
@query = 'SET NOCOUNT ON; SELECT * FROM msdb.dbo.DBA_CheckDB' ,
@subject = 'ERREUR FATALE, CORRUPTION DE LA BD master',
@attach_query_result_as_file = 1,
@body = 'Voici la query qui réaffiche les résultats de CHECKDB : SELECT * FROM msdb.dbo.DBA_CheckDB',
@importance = 'HIGH',
@query_result_separator = '|',
@file_attachments = '\\Fordiavcenter\Informatique\Navision backup\Help, my database is corrupt_ Now what - SQLServerCentral.mht' -- nvarchar(max);


David Paskiet
David Paskiet
Old Hand
Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)

Group: General Forum Members
Points: 317 Visits: 202
DUH! Why is it the simple answers go right past us. You are absolutely correct and it works like a champ. Thank you for the smack in the head!

Kindest Regards,
David

** Obstacles are those frightening things that appear when we take our eyes off the goal. **
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20941 Visits: 9671
David Paskiet (8/1/2011)
DUH! Why is it the simple answers go right past us. You are absolutely correct and it works like a champ. Thank you for the smack in the head!



Happy to smack you anytime you want ;-).
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20941 Visits: 9671
Hey lowell, can we really use gmail for this??? Would be really awesome if it worked!
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14984 Visits: 39002
Ninja's_RGR'us (8/1/2011)
Hey lowell, can we really use gmail for this??? Would be really awesome if it worked!

Yes Ninja absolutely GMail works; the only wierdness is the alternate non-"25" port, which is why i like to use that image for my examples...that's a setup and working profile i have.

that way anyone can go create a gmail account and start testing.

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20941 Visits: 9671
Lowell (8/1/2011)
Ninja's_RGR'us (8/1/2011)
Hey lowell, can we really use gmail for this??? Would be really awesome if it worked!

Yes Ninja absolutely GMail works; the only wierdness is the alternate non-"25" port, which is why i like to use that image for my examples...that's a setup and working profile i have.

that way anyone can go create a gmail account and start testing.



Amazing. So what's the password ? :-D

This one's going to my briefcase!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search