Send Email using T-SQL procedure
-
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. **
-
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.
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. **
-
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! -
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);
-
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. **
-
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 ;-).
-
Hey lowell, can we really use gmail for this??? Would be really awesome if it worked!
-
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! -
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 ? 😀
This one's going to my briefcase!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply