March 28, 2008 at 8:49 pm
If have successfully used XPSMTP to send mail to multiple recipients the problem is all the recipients email addresses are visible to each other. i don't want that, i tried putting the variable for the emails in BCC, but i am forced to use my email address in the TO field. i want to be able to use the TO field for individual email address.
Or is their another way of sending email to multiple addresses and each recipient sees his or her email in the TO field.
Thanks
Adekunle
March 29, 2008 at 5:40 am
well ... the "old" way ..
in stead of sending one mail to x recepients.... send x maild to one recepient at a time.
seems like a bug in your xpsmtp ...
Is this Gert Drapers's xpsmtp ?
(http://sqldev.net/xp/xpsmtp.htm)
If it is, send him a mail pointing to this flaw. He'll appriciate it.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 30, 2008 at 3:17 pm
You can have a look at my stored procedure.
CREATE PROCEDURE dbo.proc_happyBirthday2
AS
BEGIN
SET NOCOUNT ON
-- if you have more than 80 matches on any
-- possible day, you may want to use a cursor
-- instead - this will only handle 8000 chars.
DECLARE @BCCList VARCHAR(8000)
SET @BCCList = ''
SELECT @BCCList = @BCCList + ';' + COALESCE(email, '')
FROM Birthdays2
-- trim off leading ';' character from concatenation
SET @BCCList = SUBSTRING(@BCCList, 2, LEN(@BCCList))
EXEC master.dbo.xp_smtp_sendmail
@TO = @BCCList,
@BCC = @BCCList,
@from = 'mymail@yahoo.com',
@subject = 'Happy Birthday to you!',
@message = 'You have a great future!',
@server = 'mail.myserver.com'
END
GO
i am pulling the email address from the database,i don't know if anybody can help so that each recipient will see only their email address in the TO field.
April 2, 2008 at 2:19 am
you are usingn the @BCClist for as well the @To as the @BCC parameters !!
Only use one of both parameters to start with.
CREATE PROCEDURE dbo.proc_happyBirthday2
AS
BEGIN
SET NOCOUNT ON
-- if you have more than 80 matches on any
-- possible day, you may want to use a cursor
-- instead - this will only handle 8000 chars.
DECLARE @BCCList VARCHAR(8000)
SET @BCCList = ''
SELECT @BCCList = @BCCList + ';' + COALESCE(email, '')
FROM Birthdays2
where COALESCE(email, '') <> '' -- Added by ALZDBA
-- trim off leading ';' character from concatenation
SET @BCCList = SUBSTRING(@BCCList, 2, LEN(@BCCList))
EXEC master.dbo.xp_smtp_sendmail
@TO='', -- @TO = @BCCList, commented by ALZDBA
@BCC = @BCCList,
@from = 'mymail@yahoo.com',
@subject = 'Happy Birthday to you!',
@message = 'You have a great future!',
@server = 'mail.myserver.com'
END
GO
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 3, 2008 at 6:21 am
It is not working, i tried to execute the procedure it won't still work.
I need to get this done, can somebody help please.
Thanks
April 3, 2008 at 8:37 am
well, let's use a cursor to work trough the loop.
If it worked before (on that sql instance) , it should also work now.
CREATE PROCEDURE dbo.proc_happyBirthday2
AS
BEGIN
SET NOCOUNT ON
-- if you have more than 80 matches on any
-- possible day, you may want to use a cursor
-- instead - this will only handle 8000 chars.
DECLARE @BCCList VARCHAR(8000)
SET @BCCList = ''
declare mycsr cursor for
SELECT email
FROM Birthdays2
where COALESCE(email, '') <> '' -- Added by ALZDBA
for read only
open mycsr
FETCH NEXT FROM myscr
INTO @BCCList
WHILE @@FETCH_STATUS = 0
BEGIN
-- trim off leading ';' character from concatenation
--SET @BCCList = SUBSTRING(@BCCList, 2, LEN(@BCCList))
EXEC master.dbo.xp_smtp_sendmail
@TO=@BCCList,
-- @BCC = @BCCList,
@from = 'mymail@yahoo.com',
@subject = 'Happy Birthday to you!',
@message = 'You have a great future!',
@server = 'mail.myserver.com'
FETCH NEXT FROM myscr
INTO @BCCList
end
-- cleanup Cursor
CLOSE myscr
DEALLOCATE myscr
END
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 3, 2008 at 10:11 am
Hi - this thread is interesting to me, but it may not work as there is a typo with either
declare mycsr or the rest listed as myscr - so check that if you are not getting results.
declare mycsr cursor for
SELECT email
FROM Birthdays2
where COALESCE(email, '') <> '' -- Added by ALZDBA
for read only
April 3, 2008 at 11:40 pm
I am sorry but it is not working.
April 3, 2008 at 11:53 pm
Kenena Klosson (4/3/2008)
Hi - this thread is interesting to me, but it may not work as there is a typo with eitherdeclare mycsr or the rest listed as myscr - so check that if you are not getting results.
declare mycsr cursor for
SELECT email
FROM Birthdays2
where COALESCE(email, '') <> '' -- Added by ALZDBA
for read only
I need a pair of hawk eyes too 😎
Thank you for posting the flaw.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 4, 2008 at 12:03 am
adekunle (4/3/2008)
I am sorry but it is not working.
Did the xp_sendmail work on this server before this issue ?
If yes: What's the difference with the working one(s).
Here's the proc we use..
(you need to modify the mailalias en addresses for testing)
--testversie--
Declare @recipients varchar(2048) ,
@message varchar(8000) ,
@query varchar(7000) ,
@attachments varchar(1024) ,
@copy_recipients varchar(2048) ,
@blind_copy_recipients varchar(2048) ,
@subject varchar(255) ,
@width integer ,
@separator char(1) ,
@Headers integer ,
@attach_resultsYN char(1) ,
@iQueryResultName varchar(128) ,
@iQueryResultExtention varchar(3) ,
@iFrom varchar(128)
-- with encryption
--As
Select @recipients = null,
@message = '',
@query = null,
@attachments = null,
@copy_recipients = '',
@blind_copy_recipients = '',
@subject = null,
@width = null,
@separator = null,
@Headers = 0,
@attach_resultsYN = 'Y',
@iQueryResultName = 'TempALZQueryOut',
@iQueryResultExtention = 'TXT',
@iFrom = null
Select @recipients ='toALZDBA@mydomain.com',
@message = 'testbody',
@subject = 'testsubject',
@query = 'master.dbo.sp_helpdb',
@copy_recipients = '',
@blind_copy_recipients = '',
@width = null,
@separator = '',
@Headers = 0,
@iFrom = 'fromALZDBA@mydomain.com',
@attach_resultsYN = 'Y'--,
/*******************************************************************/
--Name : sp_ALZDBA_SQLSMTPMail
--Server : Generic
--Description : SQL smtp e-mail using CDOSYS, OLE Automation and a
-- network smtp server; For SQL Servers running on
-- windows 2000.
--
--Note : Be sure to set the default for @vcSMTPServer above to
-- the company network smtp server or you will have to
-- pass it in each time.
--
--Comments : Getting the network SMTP configured to work properly
-- may require engaging your company network or
-- server people who deal Width the netowrk SMTP server.
-- Some errors that the stored proc returns relate to
-- incorrect permissions for the various SQL Servers to
-- use the SMTP relay server to bouce out going mail.
-- Widthout proper permissions the SQL server appears as
-- a spammer to the local SMTP network server.
--
--Parameters : See the 'Syntax' Print statements below or call the
-- sp Width '?' as the first input.
--
--Date : 08/22/2001
--Author : Clinton Herring
--
--History : JOBI dd 04/06/2003 aanpassing voor vervanging van xp_Sendmail.
-- xp_Sendmail paramaters die niet geïmplementeerd werden
-- [,[@type =] 'type'] -- stond toch overal op NULL
-- [,[@attach_results =] 'attach_value'] -- true/false
-- [,[@no_output =] 'output_value'] -- true/false
-- [,[@no_header =] 'header_value'] -- true/false
-- [,[@echo_error =] 'echo_value']
-- [,[@set_user =] 'user']
-- [,[@dbuse =] 'database']
--
-- De bedoeling is dat sp_ALZDBA_SQLSMTPMail enkel onder SA-authority gebruikt wordt (afscherming van CMDshell)
/*******************************************************************/
Set nocount on
-- dit zijn de oorspronkelijke smtpmail parameters de interface-lijst werd aangepast voor makkelijke overschakeling van xp_sendmail naar sp_
Declare @vcTo varchar(2048)
Declare @vcBody varchar(8000)
Declare @vcSubject varchar(255)
Declare @vcAttachments varchar(1024)
Declare @vcQuery varchar(7000)
Declare @vcFrom varchar(128)
Declare @vcCC varchar(2048)
Declare @vcBCC varchar(2048)
Declare @vcSMTPServer varchar(255)
Declare @cSendUsing char(1)
Declare @vcPort varchar(3)
Declare @cAuthenticate char(1)
Declare @vcDSNOptions varchar(2)
Declare @vcTimeout varchar(2)
Declare @vcSenderName varchar(128)
Declare @vcServerName sysname
Declare @vcWidth integer
Declare @vcSeparator char(1)
Declare @vcHeader varchar(2)
Declare @tmpTbGuid varchar(50), @tmpTbCreate varchar(250), @tmpTbDrop varchar(250)
--initialisatie sys-parms en overname van interfacegegevens
Set @vcTo = @recipients
Set @vcBody = @message
Set @vcQuery = @query
Set @vcAttachments = @attachments
Set @vcCC = @copy_recipients
Set @vcBCC = @blind_copy_recipients
Set @vcSubject = @subject
set @vcFrom = @iFrom
Select @vcWidth = isnull(@width, 5000) -- indien null default van 5000 nemen
Select @vcSeparator = isnull(@separator,' ') -- indien NULL default een blanco nemen
if @iQueryResultName = ''
begin
set @iQueryResultName = 'TempALZQueryOut'
end
if coalesce(@iQueryResultExtention,'') = ''
begin
set @iQueryResultExtention = 'TXT'
end
set @vcSMTPServer = 'mymailserveralias.mydomain.COM' -- onze virtuele SMTP mail server op ALZ
set @cSendUsing = '2'-- Specifies the smpt server method, local or network. The default is network, a value of '2'
set @vcPort = '25' -- smtp poort
set @cAuthenticate = '0'-- The smtp server authentication method defaulted to anonymous, a value of '0'.
set @vcDSNOptions = '0'-- The smtp server delivery status defaulted to none, a value of '0'
set @vcTimeout = '30'-- The smtp server connection timeout defaulted to 30 seconds
set @vcSenderName = null-- Primary sender name defaulted to @@ServerName
--set @vcServerName = null-- SQL Server to which the query is directed defaulted to @@ServerName. je zou de query ook op een andere server kunnen uitvoeren (doen we dus niet op ALZ)
-- Determine if the user requested syntax.
If @vcTo = '?'
Begin
Print 'Syntax for sp_ALZDBA_SQLSMTPMail (based on CDOSYS):'
Print 'Exec master.dbo.sp_ALZDBA_SQLSMTPMail'
Print ' @recipients (varchar(2048)) - Recipient e-mail address list separating each Width a '';'' '
Print ' or a '',''. Use a ''?'' to return the syntax.'
Print ' @message (varchar(8000)) - Text body; use embedded char(13) + char(10)'
Print ' for carriage returns. The default is nothing'
Print ' @Query (varchar(8000)) - In-line query or a query file path; do not '
Print ' use double quotes Widthin the query.'
Print ' @Attachments (varchar(1024)) - Attachment list separating each Width a '';''.'
Print ' The default is no attachments.'
Print ' @copy_recipients (varchar(2048)) - CC list separating each Width a '';'' or a '','''
Print ' The default is no CC addresses.'
Print ' @blind_copy_recipients(varchar(2048)) - Blind CC list separating each Width a '';'' or a '','''
Print ' The default is no BCC addresses.'
Print ' @subject (varchar(255))) - E-mail subject. The default is a message from'
Print ' @@servername.'
Print ' @width (integer) - rowwidth when queryresult is to be returned'
Print ' @separator (char(1)) - used to separate columns when executing a query'
Print ' @Headers (integer) - -1 = no-headers '
Print ' @attach_resultsYN (char(1)) - attach query-results Y / N'
Print ' @iQueryResultName varchar(128) - prefix-filename voor query-result & attatchment'
Print ' @iQueryResultExtention varchar(3) - default = TXT'
Print ' @iFrom (varchar(128)) - Sender list defaulted to @@ServerName.'
Print ''
Return
End
-- Declare variables
Declare @iMessageObjId int
Declare @iHr int
Declare @iRtn int
Declare @iFileExists tinyint
Declare @vcCmd varchar(8000)
Declare @vcQueryOutPath varchar(255)
Declare @dtDatetime datetime
Declare @vcErrMssg varchar(255)
Declare @vcAttachment varchar(1024)
Declare @iPos int
Declare @vcErrSource varchar(255)
Declare @vcErrDescription varchar(255)
-- Set local variables.
Select @dtDatetime = getdate()
, @iHr = 0
, @vcErrMssg = ''
-- Check for minimum parameters.
If @vcTo is null
Begin
Set @vcErrMssg = 'You must supply at least 1 recipient.'
Goto ErrMssg
End
If isnull(@vcQuery,'') <> ''
Begin
declare @BevatDeleteStmt integer
Set @BevatDeleteStmt = 999
Select @BevatDeleteStmt = case
when PATINDEX('Delete %', @vcQuery) > 0 then 1
when PATINDEX('%(Delete %', @vcQuery) > 0 then 2
when PATINDEX('% Delete %', @vcQuery) > 0 then 3
else 0
end
if @BevatDeleteStmt > 0
begin
Set @vcErrMssg = 'Delete-statements not allowed.(ALZDBA)'
Goto ErrMssg
end
if datalength(@vcQuery) > 1000
begin
declare @tmpTbIsrt varchar(8000)
select @tmpTbGuid = newid()
select @tmpTbCreate = 'Create table [##ALZSMTPMail' + @tmpTbGuid + '] ( SQLText varchar(8000) not null ) '
, @tmpTbDrop = 'Drop table [##ALZSMTPMail' + @tmpTbGuid + '] '
, @tmpTbIsrt = 'Insert into [##ALZSMTPMail' + @tmpTbGuid + '] values (''' + replace(@vcQuery, '''', '''''') + ''')'
exec (@tmpTbCreate)
exec (@tmpTbIsrt)
select @vcQuery = 'declare @SqlTxt as varchar(8000)
Select top 1 @SqlTxt = SQLText from [##ALZSMTPMail' + @tmpTbGuid + ']
exec (@SqlTxt)'
--Set @vcErrMssg = 'Querytext is too long.(ALZDBA)'
--Goto ErrMssg
end
End
-- CDOSYS uses commas to separate recipients. Allow users to use
-- either a comma or a semi-colon by replacing semi-colons in the
-- To, CCs and BCCs.
Select @vcTo = Replace(@vcTo, ';', ',')
Select @vcCC = Replace(@vcCC, ';', ',')
Select @vcBCC = Replace(@vcBCC, ';', ',')
-- Set the default SQL Server to the local SQL Server if one
-- is not provided to accommodate instances in SQL 2000.
If @vcServerName is null
Set @vcServerName = @@servername
-- Set a default "subject" if one is not provided.
If @vcSubject is null
Set @vcSubject = 'Message from SQL Server ' + @vcServerName
-- Set a default "from" if one is not provided.
If @vcFrom is null
Set @vcFrom = 'ALZDBA-SQL-' + Replace(@vcServerName,'\','_')
-- Set a default "sender name" if one is not provided.
If @vcSenderName is null
Set @vcSenderName = 'ALZDBA-SQL-' + Replace(@vcServerName,'\','_')
-- Create the SMTP message object.
EXEC @iHr = sp_OACreate 'CDO.Message', @iMessageObjId OUT
IF @iHr <> 0
Begin
Set @vcErrMssg = 'Error creating object CDO.Message.'
Goto ErrMssg
End
-- Set SMTP message object parameters.
-- To
EXEC @iHr = sp_OASetProperty @iMessageObjId, 'To', @vcTo
IF @iHr <> 0
Begin
Set @vcErrMssg = 'Error setting Message parameter "To".'
Goto ErrMssg
End
-- Subject
EXEC @iHr = sp_OASetProperty @iMessageObjId, 'Subject', @vcSubject
IF @iHr <> 0
Begin
Set @vcErrMssg = 'Error setting Message parameter "Subject".'
Goto ErrMssg
End
-- From
EXEC @iHr = sp_OASetProperty @iMessageObjId, 'From', @vcFrom
IF @iHr <> 0
Begin
Set @vcErrMssg = 'Error setting Message parameter "From".'
Goto ErrMssg
End
-- CC
EXEC @iHr = sp_OASetProperty @iMessageObjId, 'CC', @vcCC
IF @iHr <> 0
Begin
Set @vcErrMssg = 'Error setting Message parameter "CC".'
Goto ErrMssg
End
-- BCC
EXEC @iHr = sp_OASetProperty @iMessageObjId, 'BCC', @vcBCC
IF @iHr <> 0
Begin
Set @vcErrMssg = 'Error setting Message parameter "BCC".'
Goto ErrMssg
End
-- DSNOptions
EXEC @iHr = sp_OASetProperty @iMessageObjId, 'DSNOptions', @vcDSNOptions
IF @iHr <> 0
Begin
Set @vcErrMssg = 'Error setting Message parameter "DSNOptions".'
Goto ErrMssg
End
-- Sender
EXEC @iHr = sp_OASetProperty @iMessageObjId, 'Sender', @vcSenderName
IF @iHr <> 0
Begin
Set @vcErrMssg = 'Error setting Message parameter "Sender".'
Goto ErrMssg
End
-- Is there a query to run?
If @vcQuery is not null and @vcQuery <> ''
Begin
--remove line feed and Carriage return characters (OSQL does not function when they are included)
SELECT @vcQuery = REPLACE(@vcQuery,char(10),' ') --JOBI
SELECT @vcQuery = REPLACE(@vcQuery,char(13),' ') --JOBI
-- We have a query result to include; temporarily send the output to the
-- drive Width the most free space. Use xp_fixeddrives to determine this.
-- If a temp table exists Width the following name drop it.
If (Select object_id('tempdb.dbo.#fixeddrives')) > 0
Exec ('Drop table #fixeddrives')
-- Create a temp table to work Width xp_fixeddrives.
Create table #fixeddrives(
Drive char(1) null,
FreeSpace varchar(15) null)
-- Get the fixeddrive info.
Insert into #fixeddrives Exec master.dbo.xp_fixeddrives
-- Get the drive letter of the drive Width the most free space
-- Note: The OSQL output file name must be unique for each call Widthin the same session.
-- Apparently OSQL does not release its lock on the first file created until the session ends.
-- Hence this alleviates a problem Width queries from multiple calls in a cursor or other loop.
Select @vcQueryOutPath = Drive + ':\' + @iQueryResultName + '_' +
ltrim(str(datepart(hh,getdate()))) +
ltrim(str(datepart(mi,getdate()))) +
ltrim(str(datepart(ss,getdate()))) +
ltrim(str(datepart(ms,getdate()))) + '.' + @iQueryResultExtention
from #fixeddrives
where FreeSpace = (select max(FreeSpace) from #fixeddrives )
-- Check for a pattern of '\\*\' or '?:\'.
-- If found assume the query is a file path.
If Left(@vcQuery, 35) like '\\%\%' or Left(@vcQuery, 5) like '_:\%'
Begin
Select @vcCmd = 'osql /S' + @vcServerName + ' /E /i' + convert(varchar(1024),@vcQuery) +
case @attach_resultsYN when 'Y' then '-o' + @vcQueryOutPath else '' end +
' -n -w' + ltrim(convert(varchar(6),@vcWidth)) +
' -s"' + @vcSeparator + '"' +
' -h' + ltrim(convert(varchar(6),@Headers))
End
Else
Begin
Select @vcCmd = 'osql /S' + @vcServerName + ' /E /Q"set nocount on '+ char(13) + @vcQuery + '" ' +
case @attach_resultsYN when 'Y' then '-o' + @vcQueryOutPath else '' end +
' -n -w' + ltrim(convert(varchar(6),@vcWidth)) +
' -s"' + @vcSeparator + '"' +
' -h' + ltrim(convert(varchar(6),@Headers))
End
-- PRINT '@vcCmd [' + @vcCmd + ']' --JOBI
-- Execute the query
Exec master.dbo.xp_cmdshell @vcCmd, no_output
-- Add the query results as an attachment if the file was successfully created.
-- Check to see if the file exists. Use xp_fileexist to determine this.
-- If a temp table exists Width the following name drop it.
If (Select object_id('tempdb.dbo.#fileexists')) > 0
Exec ('Drop table #fileexists')
-- Create a temp table to work Width xp_fileexist.
Create table #fileexists(
FileExists tinyint null,
FileIsDirectory tinyint null,
ParentDirectoryExists tinyint null)
-- Execute xp_fileexist
Insert into #fileexists exec master.dbo.xp_fileexist @vcQueryOutPath
-- Now see if we need to add the file as an attachment
If (select FileExists from #fileexists) = 1
Begin
-- Set a variable for later use to delete the file.
Select @iFileExists = 1
-- Add the file path to the attachment variable.
If @vcAttachments is null
Select @vcAttachments = @vcQueryOutPath
Else
Select @vcAttachments = @vcAttachments + '; ' + @vcQueryOutPath
End
else
Begin
Select @vcBody = @vcBody + char(13) + char(13) + '*** Query Returned No Results - No Results attached ***'
End
End
-- Check for multiple attachments separated by a semi-colon ';'.
If @vcAttachments is not null
Begin
If right(@vcAttachments,1) <> ';'
Select @vcAttachments = @vcAttachments + '; '
Select @iPos = CharIndex(';', @vcAttachments, 1)
While @iPos > 0
Begin
Select @vcAttachment = ltrim(rtrim(substring(@vcAttachments, 1, @iPos -1)))
Select @vcAttachments = substring(@vcAttachments, @iPos + 1, Len(@vcAttachments)-@iPos)
EXEC @iHr = sp_OAMethod @iMessageObjId, 'AddAttachment', @iRtn Out, @vcAttachment
IF @iHr <> 0
Begin
EXEC sp_OAGetErrorInfo @iMessageObjId, @vcErrSource Out, @vcErrDescription Out
Select @vcBody = @vcBody + char(13) + char(10) + char(13) + char(10) +
char(13) + char(10) + 'Error adding attachment: ' +
char(13) + char(10) + @vcErrSource + char(13) + char(10) +
@vcAttachment
End
Select @iPos = CharIndex(';', @vcAttachments, 1)
End
End
-- TextBody
EXEC @iHr = sp_OASetProperty @iMessageObjId, 'TextBody', @vcBody
IF @iHr <> 0
Begin
Set @vcErrMssg = 'Error setting Message parameter "TextBody".'
Goto ErrMssg
End
-- Other Message parameters for reference
--EXEC @iHr = sp_OASetProperty @iMessageObjId, 'MimeFormatted', False
--EXEC @iHr = sp_OASetProperty @iMessageObjId, 'AutoGenerateTextBody', False
--EXEC @iHr = sp_OASetProperty @iMessageObjId, 'MDNRequested', True
-- Set SMTP Message configuration property values.
-- Network SMTP Server location
EXEC @iHr = sp_OASetProperty @iMessageObjId,
'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value',
@vcSMTPServer
IF @iHr <> 0
Begin
Set @vcErrMssg = 'Error setting Message configuraton field "smtpserver".'
Goto ErrMssg
End
-- Sendusing
EXEC @iHr = sp_OASetProperty @iMessageObjId,
'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value',
@cSendUsing
IF @iHr <> 0
Begin
Set @vcErrMssg = 'Error setting Message configuraton field "sendusing".'
Goto ErrMssg
End
-- SMTPConnectionTimeout
EXEC @iHr = sp_OASetProperty @iMessageObjId,
'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/SMTPConnectionTimeout").Value',
@vcTimeout
IF @iHr <> 0
Begin
Set @vcErrMssg = 'Error setting Message configuraton field "SMTPConnectionTimeout".'
Goto ErrMssg
End
-- SMTPServerPort
EXEC @iHr = sp_OASetProperty @iMessageObjId,
'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/SMTPServerPort").Value',
@vcPort
IF @iHr <> 0
Begin
Set @vcErrMssg = 'Error setting Message configuraton field "SMTPServerPort".'
Goto ErrMssg
End
-- SMTPAuthenticate
EXEC @iHr = sp_OASetProperty @iMessageObjId,
'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/SMTPAuthenticate").Value',
@cAuthenticate
IF @iHr <> 0
Begin
Set @vcErrMssg = 'Error setting Message configuraton field "SMTPAuthenticate".'
Goto ErrMssg
End
-- Other Message Configuration fields for reference
--EXEC @iHr = sp_OASetProperty @iMessageObjId,
--'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/SMTPUseSSL").Value',True
--EXEC @iHr = sp_OASetProperty @iMessageObjId,
--'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/LanguageCode").Value','en'
--EXEC @iHr = sp_OASetProperty @iMessageObjId,
--'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/SendEmailAddress").Value', 'Test User'
--EXEC @iHr = sp_OASetProperty @iMessageObjId,
--'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/SendUserName").Value',null
--EXEC @iHr = sp_OASetProperty @iMessageObjId,
--'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/SendPassword").Value',null
-- Update the Message object fields and configuration fields.
EXEC @iHr = sp_OAMethod @iMessageObjId, 'Configuration.Fields.Update'
IF @iHr <> 0
Begin
Set @vcErrMssg = 'Error updating Message configuration fields.'
Goto ErrMssg
End
EXEC @iHr = sp_OAMethod @iMessageObjId, 'Fields.Update'
IF @iHr <> 0
Begin
Set @vcErrMssg = 'Error updating Message parameters.'
Goto ErrMssg
End
-- Send the message.
EXEC @iHr = sp_OAMethod @iMessageObjId, 'Send'
IF @iHr <> 0
Begin
Set @vcErrMssg = 'Error Sending e-mail.'
Goto ErrMssg
End
Else
Print 'Mail sent.'
Cleanup:
-- Destroy the object and return.
EXEC @iHr = sp_OADestroy @iMessageObjId
--EXEC @iHr = sp_OAStop
if @tmpTbDrop is not null
begin
--print @tmpTbDrop
exec (@tmpTbDrop)
end
-- Delete the query output file if one exists.
If @iFileExists = 1
Begin
Select @vcCmd = 'del ' + @vcQueryOutPath
Exec master.dbo.xp_cmdshell @vcCmd, no_output
End
if @vcErrMssg = ''
begin
print 'Return 0 '
-- Return
end
else
begin
print 'Return 12 - ' + @vcErrMssg
-- Return(12)
end
goto Einde
ErrMssg:
Begin
Print @vcErrMssg
If @iHr <> 0
Begin
EXEC sp_OAGetErrorInfo @iMessageObjId, @vcErrSource Out, @vcErrDescription Out
Print @vcErrSource
Print @vcErrDescription
End
-- Determine whether to exist or go to Cleanup.
If @vcErrMssg = 'Error creating object CDO.Message.'
goto Einde --Return
Else
Goto Cleanup
End
Einde:
GO
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 4, 2008 at 4:14 pm
Are you saying this procedure will work for me, i find it hard to grapple with. I am new to sql server.
April 7, 2008 at 12:35 pm
I ran your procedure as it exists (except I used my own mail server) and it worked fine for me. Are you getting any kind of an error message?
I would try using only a few email addresses to test ( I had 3 in mine)
SELECT top 5 @BCCList = @BCCList + ';' + COALESCE(email, '')
FROM Birthdays2
I would also try printing @BCCList after it is built to see if there is a bad character in the list.
SET @BCCList = SUBSTRING(@BCCList, 2, LEN(@BCCList))
print @bcclist
April 7, 2008 at 12:43 pm
adekunle (4/3/2008)
It is not working, i tried to execute the procedure it won't still work.I need to get this done, can somebody help please.
Thanks
What exactly happens? nothing? error message? is the failure occuring at the SQL server or is the SMTP relay throwing stuff out?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 7, 2008 at 3:30 pm
If you read through from the beginning of the post you will understand that i did not say my proc did not work, the problem is I DON'T WANT OTHER RECIPIENTS TO SEE OTHER PEOPLE'S EMAIL ADDRESS, i don't want to put my email address in the TO field, i want each recipient to have the feeling of been the only person that received a mail, they need to see only their email address in the TO field.
Thank you.
April 7, 2008 at 4:01 pm
adekunle (4/7/2008)
If you read through from the beginning of the post you will understand that i did not say my proc did not work, the problem is I DON'T WANT OTHER RECIPIENTS TO SEE OTHER PEOPLE'S EMAIL ADDRESS, i don't want to put my email address in the TO field, i want each recipient to have the feeling of been the only person that received a mail, they need to see only their email address in the TO field.Thank you.
With all due respect - you've said "it doesn't work" 4 times so far, the last 3 without it being clear what isn't working. Knowing WHERE it's failing (on stuff that's being proposed to you) is how we would know what to focus on.
Have you ALZDBA's cursor solution yes? It looks like it should work, albeit a little slower than just putting everyone on the BCC list.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply