How to send mail from SQLServer w_out exchange
-
May appreciate any pointers or articles that will help set up sql server to send emails without having to install exchange on the server
Thanks in advance for the assistance
Mike
-
Take a look at Gert Draapers' xp_smtp_sendmail. It is an extended stored procedure that uses SMTP to send mail. In SQL Server 2005 there is a new component called Database Mail that lets you do the same.
-
Thanks for your responses.
Before exploring the newer options.You may be interested to know that I have outlook installed.xp_startmail runs just fine and xp_sendmail runs for hours with no feedback .
Any response will be received with gratitude
Mike
-
What version of MS Outlook are you using?
If you are using something other than the base Outlook 2000 version or newer, you will experience this. As I understand, in Outlook 2000 SR-1 and later, Microsoft had put in some code in to prevent viruses from being automatically sent via code and this broke this feature in SQL (Outlook Object Model Guard).
We had a similar thing with our servers--Mapi starts and stops but when an email is sent it never arrives. When we went back to the base install of Outlook 2000, it started working again. I have since switched to a customized method to send emails and alerts.
Can't wait for the new version of SQL Database Mail.
SJ
-
Code snippet from a Visual basic script that I use to send email without outlook. Just set msgHTML to any HTml or text.
Sub MailMsg ()
'
'Objective: Build a report and send it via email
'
Dim objEmail
Dim iBp
dim iBp1
dim Flds
Dim Stm
Dim strSubject
'
strSubject =strSubject & " for " & strDatabase ' Mail subject
'
' Message body is built with web content, set the headers first
'
msgHTML ="<H3> Backup Database report for " & strSqlInstance & " " & strDatabase & "</H3>"
msgHTML = msgHTML & "<H3> Job Started at: " & JStartTS & " </H3>"
msgHTML = msgHTML & "<TABLE border=2><TR><TH><B>Job Step</B></TH>"
msgHTML = msgHTML & "<TH><B>Step Message</B></TH><TH><B>Step Status</B></TH>"
msgHTML = msgHTML & "<TH><B>Log Time </B></TH><TH><B>Elapsed Time</B></TH></TR>"
'
'Now Process the Report recordset to create the table rows
'
'
'
rsReport.MoveFirst
Do Until rsReport.EOF
msgHTML = msgHTML & "<TR>"
msgHTML = msgHTML & "<td>" & rsReport.Fields.Item("JobStep") & "</td>"
msgHTML = msgHTML & "<td>" & rsReport.Fields.Item("JobStepMsg") & "</td>"
msgHTML = msgHTML & "<td>" & rsReport.Fields.Item("JobStepStatus") & "</td>"
msgHTML = msgHTML & "<td>" & rsReport.Fields.Item("TimeNow") & "</td>"
msgHTML = msgHTML & "<td>" & DateDiff("n",JStartTS,rsReport.Fields.Item("TimeNow")) & "</td>"
msgHTML = msgHTML & "</TR>"
rsReport.MoveNext
Loop
msgHTML = msgHTML & "</table>"
'
' Message body done, send the mail message
'
' Do not change these lines, they work
'
Set objEmail = CreateObject("CDO.Message")
objEmail.From = strFrom
objEmail.To = strTo
objEmail.Subject = strSubject
set iBp= objEmail.BodyPart
Set iBp1 = iBp.AddBodyPart
set Flds =Ibp1
Set Flds = iBp1.Fields
Flds("urn:schemas:mailheader:content-type") = "text/html"
Flds.Update
' get the stream and add message HTML text to it
Set Stm = iBp1.GetDecodedContentStream
Stm.WriteText msgHTML
Stm.Flush
Set Flds = iBp.Fields
Flds("urn:schemas:mailheader:content-type") = "multipart/alternative"
Flds.Update
objEmail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objEmail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _
"mail.cplc.com"
objEmail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objEmail.Configuration.Fields.Update
objEmail.Send
Set objEMail = Nothing
call JobLog("","MailMsg","Ended",JobErr)
End sub
-
You can very much send mail from SQL Server without using exchange.
Follow the Microsoft Knowledge Base article :
http://support.microsoft.com/default.aspx?scid=kb;en-us;312839
Thanks.
Naras.
-
Something in SJs reply reminded me of something I found very helpful since the Outlook Security updates "broke" the ability to send a batch of emails from the Address Book.
This applies especially to MAPI. I found a small "freebie" programme which can be downloaded and it can be incorporated into VBA, etc code. It is called "Express Click Yes". Cannot recall the web site but if you search on that name, it is bound to turn up for you.
Basically, when turned on, it automatically clicks the "Yes" button at eavch dispatch, meaning you can walk away from the workstation asnd let it do the job for you.
MY sincerew thanks to the generous author if this little programme!
Regards,
Lester Vincent
Sydney
Australia
-
Something in SJs reply reminded me of something I found very helpful since the Outlook Security updates "broke" the ability to send a batch of emails from the Address Book.
This applies especially to MAPI. I found a small "freebie" programme which can be downloaded and it can be incorporated into VBA, etc code. It is called "Express Click Yes". Cannot recall the web site but if you search on that name, it is bound to turn up for you.
Basically, when turned on, it automatically clicks the "Yes" button at each dispatch, meaning you can walk away from the workstation and let it do the job for you.
MY sincere thanks to the generous author of this little programme!
Regards,
Lester Vincent
Sydney
Australia
Accepting all invitesViewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply