Do I need to have a mail server loaded to use sql mail? 1

[bigfoot]

I just loaded a server. I did not put any mail clients on it except what server 2000 came with. Is there anyway to have sql server send me a mail message to my domain without using a mail client?

This is a remote mail server.

 

[ClaireHCM]

I think you need to have MAPI MAIL server or SMTP to send SQL Mail.
Just by installing SQL server.You wont be able to send SQL mail.But if it's ok for you,you can use net send to prompt your domain user.

 

[meckeard]

ClairHsu is correct. SQLMail needs a mail server to send the email.

I use SQLMail and tap into our local exchange server.

Mark

 

[JayKusch]

Here is a great link for you:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q263556&sd=tech

Thanks

J. Kusch

 

[bigfoot]

So I need something like Exchange server running on the remote box? Any way around this? All I want is to get an e-mail here at the office if SQL Server crashes, or if one of my DTS packages fail.

Is there any other way?

 

[ClaireHCM]

At home,I call cable company and apply a mail account for me.Use their mail server as exchange server.

 

[roccorocks]

Here is a great link:

http://www.marzie.com/devtools/sql/sqlcdonts.asp

(with the above you need SMTP services running on the same machine)

Try this creating this stored procedure. I put this in the master DB. This works for me, just make sure to change the email server accordingly. (This uses microsofts remote SMTP "Service", no local service or client is needed)

CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "
/*********************************************************************

This stored procedure takes the above parameters and sends an e-mail.
All of the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
Reference 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)

--************* 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/d...ml/_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',

'Mailbox'

-- 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'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
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 need to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO

Hope this helps for you.

Rocco

 

[bigfoot]

Thanks. I'll check it out. I am fighting with someone's DTS script at the moment.

 

[aroggeband]

I found the same code as above on the microsoft site at

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q312839&sd=tech#3

although now the article seems to be missing without a trace from their servers. Maybe this threatens the sales of MS Exchange?

I enhanced the error handling by putting a label as follows:
...
-- Sample error handling.
OOPS:
IF @hr <>0
select @hr
...

I also added the ability to add attachments
optional parameter @attachment is the path to a file saved on the server:
...
-- Add attachments
if rtrim(@Attachment)<>''
EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment', @returnval out, @Attachment
if @hr<>0 goto OOPS --error handling if file not found
...
also CC, etc
...
EXEC @hr = sp_OASetProperty @iMsg, 'CC', @CC
EXEC @hr = sp_OASetProperty @iMsg, 'BCC', @BCC
EXEC @hr = sp_OASetProperty @iMsg, 'ReplyTo', @ReplyTo
...

 

[bigfoot]

Ok. I found that I have SMTP loaded. It is running. NOw how do I get to it? Remember that I am a developer, not a systems' person. What is the name of my smtp server? The server's name?

Remember there is not any mail server on this stand alone server. It's a remote server off site. I don't care about getting mail there, just sending it from a web form, and from SQL server.

Thanks all.

 

[roccorocks]

Try out this SP, this one is very simple. One note, notice that the @MailServer variable shows 'localhost' next to it? This is what you will be sending to this variable, if you decide to remove that value in the SP. When you designate that it will &quot;look&quot; localhost, the code will utilize your local SMTP service (hence the mail should send fine!). Sorry for all the confusion.

Create Procedure sp_SMTPMail

@SenderName varchar(100),
@SenderAddress varchar(100),
@RecipientName varchar(100),
@RecipientAddress varchar(100),
@Subject varchar(200),
@Body varchar(8000),
@MailServer varchar(100) = 'localhost'

AS

SET nocount on

declare @oMail int --Object reference
declare @resultcode int

EXEC @resultcode = sp_OACreate 'SMTPsvg.Mailer', @oMail OUT

if @resultcode = 0
BEGIN
EXEC @resultcode = sp_OASetProperty @oMail, 'RemoteHost', @mailserver
EXEC @resultcode = sp_OASetProperty @oMail, 'FromName', @SenderName
EXEC @resultcode = sp_OASetProperty @oMail, 'FromAddress', @SenderAddress
EXEC @resultcode = sp_OAMethod @oMail, 'AddRecipient', NULL, @RecipientName, @RecipientAddress
EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject
EXEC @resultcode = sp_OASetProperty @oMail, 'BodyText', @Body
EXEC @resultcode = sp_OAMethod @oMail, 'SendMail', NULL
EXEC sp_OADestroy @oMail
END

SET nocount off


GO


-- Rocco

 

[bigfoot]

Very cool. I also got ASPMail. This is a com object that runs under ASP, or VB. It can be scripted in SQL Sqrver.