Sending mail procedure - 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 2008 Forums

Transact-SQL (2008)

Sending mail procedure

Author

Topic

JordyDB
Starting Member

8 Posts

Posted - 2012-03-27 : 05:13:19

Hi all,

I'm making a mailsystem I got it working staticly, but now I want to get the data(port-number, smtp-server, etc.) out of a settings table (tblsetting in my case).

How can I do this? I can put my values in a cursor, but this must be in the begin part and the settings get declared in the top part...

USE [ProSense_Copy]
GO
/****** Object: StoredProcedure [dbo].[sp_send_mail] Script Date: 03/27/2012 11:05:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_send_mail]
@from varchar(500) ,
@to varchar(500) ,
@subject varchar(500),
@body varchar(4000) ,
@bodytype varchar(10),
@output_mesg varchar(10) output,
@output_desc varchar(1000) output
AS
DECLARE @imsg int
DECLARE @hr int
DECLARE @source varchar(255)
DECLARE @description varchar(500)

EXEC @hr = sp_oacreate 'cdo.message', @imsg out

--SendUsing Specifies Whether to send using port (2) or using pickup directory (1)
EXEC @hr = sp_oasetproperty @imsg,
'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").value','2'

--SMTP Server
EXEC @hr = sp_oasetproperty @imsg,
'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").value',
'192.168.0.254'

--UserName
EXEC @hr = sp_oasetproperty @imsg,
'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").value',
''

--Password
EXEC @hr = sp_oasetproperty @imsg,
'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").value',
''

--UseSSL
EXEC @hr = sp_oasetproperty @imsg,
'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpusessl").value',
'false'

--PORT
EXEC @hr = sp_oasetproperty @imsg,
'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport").value',
'25'

--Requires Aunthentication None(0) / Basic(1)
EXEC @hr = sp_oasetproperty @imsg,
'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").value',
'0'

EXEC @hr = sp_oamethod @imsg, 'configuration.fields.update', null
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'.

EXEC @hr = sp_oasetproperty @imsg, @bodytype, @body
EXEC @hr = sp_oamethod @imsg, 'send', null

SET @output_mesg = 'Success'

-- sample error handling.
IF @hr <>0
SELECT @hr
BEGIN
EXEC @hr = sp_oageterrorinfo null, @source out, @description out
IF @hr = 0
BEGIN
--set @output_desc = ' source: ' + @source
set @output_desc = @description
END
ELSE
BEGIN
SET @output_desc = ' sp_oageterrorinfo failed'
END
IF not @output_desc is NULL
SET @output_mesg = 'Error'
END
EXEC @hr = sp_oadestroy @imsg

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-03-27 : 13:55:02

[code]
declare @port-number varchar(100), @smtp-server varchar(100)
select @port-number = port-number,
@smtp-server = smtp-server

FROM dbo.tblsetting
(some sort of validation here that those values are not null and/or some default value)
--PORT
EXEC @hr = sp_oasetproperty @imsg,
'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport").value',
@port-number
[/code]

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources