RE: SQL MAIL through SMTP

RE: SQL MAIL through SMTP

 

  

Make sure SMTP is on.

Even if it is on, you may need to check the properties. Look under
Delivery tab, Advanced... If the Masquerade domain or smart host is set
incorrectly, you could have a problem.

Also, check in the mailroot directory to see if there is anything in the
BadMail, Queue or Pickup folders that could inidcate a problem. And as
always check the event log on the servers in the mail path as well.







"Gary"
<mssqldba-ezmlmsh
ield-x35334735.x1 To
[Email Address Removed] "LazyDBA Discussion"
.com> <[Email address protected]
cc
06/29/2006 10:37
AM Subject
RE: SQL MAIL through SMTP










Try this procedure. Make sure that you have the SMTP server configured on
the box correctly either as a SMTP server itself or forwarding to another.
You may want to test the SMTP server by TELNET <your box ip>:25 and
establishing a session, to make sure that you smtp is configured properly.

try here for the smtp commands once you create a telnet session:
http://www.angelfire.com/pop/cram/spam/SMTP.htm



CREATE PROCEDURE [dbo].[sp_send_cdontsmail]
@From varchar(100),
@To varchar(100),
@Subject varchar(150),
@Body text,
@CC varchar(100) = null,
@BCC varchar(100) = null
--@Reply_to varchar (100) =null
--@Attachment_Location varchar(200)
AS
Declare @MailID int
Declare @hr int
EXEC @hr = sp_OACreate 'CDONTS.NewMail', @MailID OUT
--Attachment
--Importance 2 = high
EXEC @hr = sp_OASetProperty @MailID, 'Importance',2
EXEC @hr = sp_OASetProperty @MailID, 'From',@From
EXEC @hr = sp_OASetProperty @MailID, 'Body', @Body
EXEC @hr = sp_OASetProperty @MailID, 'BCC',@BCC
EXEC @hr = sp_OASetProperty @MailID, 'CC', @CC
EXEC @hr = sp_OASetProperty @MailID, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @MailID, 'To', @To
--html email needs these properties
EXEC @hr = sp_OASetProperty @MailID, 'BodyFormat', 0
EXEC @hr = sp_OASetProperty @MailID, 'MailFormat', 0
EXEC @hr = sp_OAMethod @MailID, 'Send', NULL
EXEC @hr = sp_OADestroy @MailID


Gary D Baker


-----Original Message-----
From: Gary [mailto:Gary.[Email address protected]
Sent: Thursday, June 29, 2006 7:13 AM
To: Gordon Rayburn
Subject: RE: SQL MAIL through SMTP

**A LazyDBA.com subscriber has responded to your lazydba.com post**
**LazyDBA.com mail shield has forwarded you this email, **and removed
any attachments, and kept your email address secret **from this person,
and any viruses/trojans.
**If you reply to this email, the person will see your email address as
normal **Anything below this line is the original email text


Try this procedure. Make sure that you have the SMTP server configured
on the box correctly either as a SMTP server itself or forwarding to
another.
You may want to test the SMTP server by TELNET <your box ip>:25 and
establishing a session, to make sure that you smtp is configured
properly.

try here for the smtp commands once you create a telnet session:
http://www.angelfire.com/pop/cram/spam/SMTP.htm



CREATE PROCEDURE [dbo].[sp_send_cdontsmail] [Email address protected]
varchar(100), [Email address protected] varchar(100), [Email address
protected] varchar(150), [Email address protected] text, [Email address
protected] varchar(100) = null, [Email address protected] varchar(100) =
null [Email address protected] varchar (100) =null [Email address
protected] varchar(200) AS Declare [Email address protected] int Declare
[Email address protected] int EXEC [Email address protected] =
sp_OACreate 'CDONTS.NewMail', [Email address protected] OUT --Attachment
--Importance 2 = high EXEC [Email address protected] = sp_OASetProperty
[Email address protected] 'Importance',2 EXEC [Email address protected]
= sp_OASetProperty [Email address protected] [Email address protected]
EXEC [Email address protected] = sp_OASetProperty [Email address
protected] 'Body', [Email address protected] EXEC [Email address
protected] = sp_OASetProperty [Email address protected] [Email address
protected] EXEC [Email address protected] = sp_OASetProperty [Email
address protected] 'CC', [Email address protected] EXEC [Email address
protected] = sp_OASetProperty [Email address protected] 'Subject',
[Email address protected] EXEC [Email address protected] =
sp_OASetProperty [Email address protected] 'To', [Email address
protected] --html email needs these properties EXEC [Email address
protected] = sp_OASetProperty [Email address protected] 'BodyFormat', 0
EXEC [Email address protected] = sp_OASetProperty [Email address
protected] 'MailFormat', 0 EXEC [Email address protected] = sp_OAMethod
[Email address protected] 'Send', NULL EXEC [Email address protected] =
sp_OADestroy [Email address protected]


Gary D Baker
MsMIS, MCSE, MCDBA, MCDST, MCSA



|---------+-------------------------------------------------------->
| | "Gordon Rayburn " |
| | [Email address protected]
| | azydba.com> |
| | |
| | 06/29/2006 09:57 AM |
| | |
|---------+-------------------------------------------------------->

>-----------------------------------------------------------------------
------------------------------------------------------------------------
---|
|
|
| To: "LazyDBA Discussion" [Email address protected]
|
| cc:
|
| Subject: RE: SQL MAIL through SMTP
|

>-----------------------------------------------------------------------
------------------------------------------------------------------------
---|




As you stated, Outlook is a MAPI client. It's not needed if he's using
SMTP (or trying). MAPI and SMTP are two separate protocols.

You might want to look over on http://www.sqldev.net for the xp_smtp
package that Gert wrote. It will do what you wwant quite easily and is
THE solution for sending SMTP mail via SQL server.

-----Original Message-----
From: Stig Kagan-Ditlevsen
[mailto:mssqldba-ezmlmshield-x41655682.[Email address protected]
Sent: Thursday, June 29, 2006 1:52 AM
To: LazyDBA Discussion
Subject: SV: SQL MAIL through SMTP

Hi,
Do you have Outlook or Exchange installed on the SqlServer - it's my
experience that that is required to use Sql Mail (Mapi Client)

/Stig Kagan-Ditlevsen

-----Oprindelig meddelelse-----
Fra: Vinoth R
[mailto:mssqldba-ezmlmshield-x5547818.[Email address protected]
Sendt: 29. juni 2006 10:49
Til: LazyDBA Discussion
Emne: SQL MAIL through SMTP

Hi guys,



I have a problem when I send mails through SQL SERVER using SMTP. It
doesn't generate any errors but the mail is not going, I don't know why

Please help me in the same, am I missing something important

The following is the code





create Procedure dbo.SP_SMTPMail



[Email address protected] varchar(100),

[Email address protected] varchar(100),

[Email address protected] varchar(100),

[Email address protected] varchar(100),

[Email address protected] varchar(200),

[Email address protected] varchar(8000),

[Email address protected] varchar(100) = 'localhost'

AS



SET nocount on



declare [Email address protected] int --Object reference

declare [Email address protected] int



EXEC [Email address protected] = sp_OACreate
'SMTPsvg.Mailer', [Email address protected] OUT
---------- and I replace this 'SMTPsvg.Mailer' with the domain name
found in IIS



if [Email address protected] = 0

BEGIN

EXEC [Email address protected] =
sp_OASetProperty [Email address protected] 'RemoteHost', [Email address
protected]

EXEC [Email address protected] =
sp_OASetProperty [Email address protected] 'FromName', [Email address
protected]

EXEC [Email address protected] =
sp_OASetProperty [Email address protected] 'FromAddress', [Email
address protected]



EXEC [Email address protected] = sp_OAMethod
[Email address protected] 'AddRecipient', NULL, [Email address
protected] [Email address protected]



EXEC [Email address protected] =
sp_OASetProperty [Email address protected] 'Subject', [Email address
protected]

EXEC [Email address protected] =
sp_OASetProperty [Email address protected] 'BodyText', [Email address
protected]





EXEC [Email address protected] = sp_OAMethod
[Email address protected] 'SendMail', NULL



EXEC sp_OADestroy [Email address protected]

END





SET nocount off







------------------- EXECUTING USING



exec sp_SMTPMail [Email address protected] [Email address protected]
address protected]

[Email address protected] = 'uwv', [Email address protected] = '[Email
address protected]

[Email address protected] TEST MAIL', [Email address protected] this is
a test email from SQL Server. Please Ignore'







Thanks in advance



Vini



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html




------------------------------------------------------------------------
---------------------------

NOTICE OF CONFIDENTIALITY
The information contained in this communication and any accompanying
document(s) is proprietary and confidential and is intended solely for
the above-named individual or entity. If you are not the intended
receiver, recipient or entity, you are advised that any distribution,
copying, disclosure or communication of this email is strictly
prohibited. If you have received this email in error, please contact me
at the telephone number listed above or 858.716.1500.
========================================================================
======




---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html





************************************************************************
**************
This e-mail message and all attachments transmitted with it may contain
legally privileged and/or confidential information intended solely for
the use of the addressee(s). If the reader of this message is not the
intended recipient, you are hereby notified that any reading,
dissemination, distribution, copying, forwarding or other use of this
message or its attachments is strictly prohibited. If you have received
this message in error, please notify the sender immediately and delete
this message and all copies and backups thereof.

Thank you.
************************************************************************
**************





---------------------------------------------------------------------------------------------------


NOTICE OF CONFIDENTIALITY
The information contained in this communication and any accompanying
document(s) is proprietary and confidential and is intended solely for the
above-named individual or entity. If you are not the intended receiver,
recipient or entity, you are advised that any distribution, copying,
disclosure or communication of this email is strictly prohibited. If you
have received this email in error, please contact me at the telephone
number listed above or 858.716.1500.
==============================================================================







**************************************************************************************

This e-mail message and all attachments transmitted with it may contain
legally privileged and/or confidential information intended solely for the
use of the addressee(s). If the reader of this message is not the intended
recipient, you are hereby notified that any reading, dissemination,
distribution, copying, forwarding or other use of this message or its
attachments is strictly prohibited. If you have received this message in
error, please notify the sender immediately and delete this message and all
copies and backups thereof.

Thank you.
**************************************************************************************



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html




MS Sql Server LazyDBA home page