Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Send Email using T-SQL procedure Expand / Collapse
Author
Message
Posted Monday, August 1, 2011 8:37 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, April 14, 2014 10:31 AM
Points: 315, Visits: 192
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?

Kindest Regards,
David

** Obstacles are those frightening things that appear when we take our eyes off the goal. **

Post #1151857
Posted Monday, August 1, 2011 8:38 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, April 14, 2014 10:31 AM
Points: 315, Visits: 192
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. **

Post #1151861
Posted Monday, August 1, 2011 8:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:07 PM
Points: 12,910, Visits: 32,028
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1151877
Posted Monday, August 1, 2011 8:47 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, August 28, 2014 4:00 AM
Points: 21,397, Visits: 9,612
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);

Post #1151882
Posted Monday, August 1, 2011 9:45 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, April 14, 2014 10:31 AM
Points: 315, Visits: 192
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. **

Post #1151936
Posted Monday, August 1, 2011 9:51 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, August 28, 2014 4:00 AM
Points: 21,397, Visits: 9,612
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 .
Post #1151943
Posted Monday, August 1, 2011 9:51 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, August 28, 2014 4:00 AM
Points: 21,397, Visits: 9,612
Hey lowell, can we really use gmail for this??? Would be really awesome if it worked!
Post #1151944
Posted Monday, August 1, 2011 9:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:07 PM
Points: 12,910, Visits: 32,028
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1151949
Posted Monday, August 1, 2011 10:00 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, August 28, 2014 4:00 AM
Points: 21,397, Visits: 9,612
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!
Post #1151955
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse