SQLMail setup question

[NicktheNewbie]

I am looking to setup SQL mail on my windows 2000/sql 2000 server.

I have office/outlook 2000 setup and configured to be able to send/receive email.

I run the sql server enterprise manager, and look under support services/SQL mail, then right click on properties.

Under "Enter MAPI profile"
It says "Enter Profile"...

What do i enter here?

Server name? Server IP address? username? Anytime I enter something in and click test, it says there is no default mail client.

However, i have outlook setup as the default... any ideas?

 

[tlbroadbent]

The profile is the name of the Outlook profile for a specified login. You should create a profile for SQL Server by logging into Windows on the Server with the SQL Server account. Then use the profile name you created in the SQL Mail Setup.

The following link should be helpful.

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q311231

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[NicktheNewbie]

When I enter in anything into the profile...
and click test... I get an error message.

Do I put the username here?

 

[tlbroadbent]

It would be helpful to know the error message.

You put the name of the MAIL profile on that SQL server. It is the profile that points to the SQL login account mailbox on the Exchange server. Open the Outlook properties on the Server to find the profile names. Make sure you use a profile associated with the SQL Server startup account.

If you don't use Exchange Server for mail, the setup process is different. I'm not familiar setting up SQLMail for other mail servers. Have you reviewed the documents I recommended? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[NicktheNewbie]

I have read extensively through what you sent me to, thanks!

I think I have everything setup properly, and outlook works fine from the server, I can send/receive no problem.

However, no matter what i put in (I found the name of the profile "MS Exchange Settings&quot when i click test, it always says "Error 18025 xp_test_mapi_profile: failed with mail error."

I CAN just enter in the profile name, and click
OK, but I get the following error:

This MAPI profile was not found on the server, are you sure this is what you want?


Any suggestions?

 

[tlbroadbent]

Usually there is a hex number following the error description. That number can give you a clue as to the reason for the failure. Example: 0x8004011

Check the following page for an explanation of the numbers.

http://support.microsoft.com/default.aspx?scid=kb;

[LN];Q238119

How are you logging in on the server when running Outlook? Do you use the same account that is used to start SQL Server? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[NicktheNewbie]

My error code is:
MAPI_E_LOGON_FAILED 80040111 -2147221231
-I login with an account I created called SQL - which I gave admin rights. On the exchange server (on a different domain) I have created a "SQL" username and mailbox.
I use this same account to start and run the mssql service... are there other services that I need to start as this service?

 

[tlbroadbent]

80040111 indciates the MAPI_E_LOGON_FAILED. This means the SQL Admin account cannot login to the exchange server. So there is some problem in the profile you are selecting or the accounts are not the same.

You may need to stop and restart the SQL Service if you haven't done that since creating the profile. If that doesn't help you may need to go theroughh the mail setup steps again. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[schwarzs]

I will tell you this I hate SQL mail with exchnage and it most likely because I have learned it in the place I currently work with does not use,know or care about exchange. If you are looking for quick and easy emails with some attchments this is my solutions. SMTP services need to be installed on the SQL machine not hard just an add on in the win2000 server components. Then run the code that will follow here in the query analyzer against the database that you want to send mail from. After query is complete this line (exec sp_sendSMTPmail 'to@anyone.com','THis is a test subject','this is a test 345il',@from = 'from@anyone.com) will send mail from the query analyzer or trigger or stored procedure with no orther passwords configuration or network administration. I LOVE IT!!! Hope this Helps!

/* Name: sp_sendSMTPmail

Purpose: Send an SMTP mail using CDONTS object. 

Requirements: SMTP server (comes with IIS but doesn't require IIS) must be loaded.

Returns: 0 if successful, 1 if any errors

Sample Usage:
sp_sendSMTPmail 'vince.iacoboni@db.com', 'Testing', 'testing sp_sendSMTPmail, please reply if you receive this', 
@cc='irmsqlmail@db.com', 
@Importance=1, 
@Attachments='c:\boot.ini;c:\autoexec.bat'

History:
02/07/2001 VRI Created.
11/05/2001 VRI Updated usage comment to @Importance=1 since =3 was invalid. Swapped single for
double quotes.
*/
USE webusers
go

IF OBJECT_ID('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO 

CREATE PROCEDURE sp_hexadecimal (@binvalue varbinary(255),
@hexvalue varchar(255) OUTPUT)
AS
DECLARE @charvalue varchar(255)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH(@binvalue)
SELECT @hexstring = '0123456789abcdef'

WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO


IF OBJECT_ID('sp_displayoaerrorinfo ') IS NOT NULL
DROP PROCEDURE sp_displayoaerrorinfo 
GO 

CREATE PROCEDURE sp_displayoaerrorinfo (@object int,
@hresult int)
AS
DECLARE @output varchar(255)
DECLARE @hrhex char(10)
DECLARE @hr int
DECLARE @source varchar(255)
DECLARE @description varchar(255)

SET CONCAT_NULL_YIELDS_NULL OFF

PRINT 'OLE Automation Error Information'
EXEC sp_hexadecimal @hresult, @hrhex OUT
SELECT @output = ' HRESULT: ' + @hrhex
PRINT @output
EXEC @hr = sp_OAGetErrorInfo @object, @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
GO


IF OBJECT_ID('sp_sendSMTPmail') IS NOT NULL
DROP PROCEDURE sp_sendSMTPmail
GO

CREATE PROCEDURE sp_sendSMTPmail (@To varchar(8000),
@Subject varchar(255),
@Body text = null,
@Importance int = 1, -- 0=low, 1=normal, 2=high
@Cc varchar(8000) = null,
@Bcc varchar(8000) = null,
@Attachments varchar(8000) = null, -- delimeter is ;
@HTMLFormat int = 0,
@From varchar(255) = null)

/* Name: sp_sendSMTPmail

Purpose: Send an SMTP mail using CDONTS object. 

Requirements: SMTP server (comes with IIS but doesn't require IIS) must be loaded.

Returns: 0 if successful, 1 if any errors

Sample Usage:
sp_sendSMTPmail 'vince.iacoboni@db.com', 'Testing', 'testing sp_sendSMTPmail, please reply if you receive this', 
@cc='irmsqlmail@db.com', 
@Importance=1, 
@Attachments='c:\boot.ini;c:\autoexec.bat'

History:
02/07/2001 VRI Created.
*/

AS

SET NOCOUNT ON

DECLARE @object int,
@hr int,
@StrEnd int,
@Attachment varchar(255),
@return int,
@Msg varchar(255)

SELECT @From = isnull(@From, @@SERVERNAME)

-- Create the CDONTS NewMail object.
EXEC @hr = sp_OACreate 'CDONTS.NewMail', @object OUT
IF @hr <> 0 GOTO ObjectError

-- Add the optional properties if they are specified
IF @Body IS NOT NULL
BEGIN
EXEC @hr = sp_OASetProperty @object, 'Body', @Body
IF @hr <> 0 GOTO ObjectError
END 

IF @Cc IS NOT NULL
BEGIN
EXEC @hr = sp_OASetProperty @object, 'Cc', @Cc
IF @hr <> 0 GOTO ObjectError
END 

IF @Bcc IS NOT NULL
BEGIN
EXEC @hr = sp_OASetProperty @object, 'Bcc', @Bcc
IF @hr <> 0 GOTO ObjectError
END

IF @HTMLFormat <> 0
BEGIN
EXEC @hr = sp_OASetProperty @object, 'MailFormat', 0
IF @hr <> 0 GOTO ObjectError
END

-- Loop through the ; delimited files to attach
CREATE TABLE #FileExists (FileExists int, FileIsDir int, ParentDirExists int)

WHILE isnull(len(@Attachments),0) > 0
BEGIN
SELECT @StrEnd = CASE charindex(';', @Attachments)
WHEN 0 THEN len(@Attachments)
ELSE charindex(';', @Attachments) - 1
END 
SELECT @Attachment = substring(@Attachments, 1, @StrEnd)
SELECT @Attachments = substring(@Attachments, @StrEnd+2, len(@Attachments))

-- Ensure we can find the file we want to send.
DELETE #FileExists
INSERT #FileExists
EXEC master..xp_fileexist @Attachment

IF NOT EXISTS (SELECT * FROM #FileExists WHERE FileExists = 1)
BEGIN
RAISERROR ('File %s does not exist. Message not sent.', 16, 1, @Attachment)
RETURN 1
END 

EXEC @hr = sp_OAMethod @object, 'AttachFile', NULL, @Attachment
IF @hr <> 0 GOTO ObjectError

SELECT @Msg = 'File ' + @Attachment + ' attached.'
PRINT @Msg
END

-- Call the Send method with parms for standard properties
EXEC @hr = sp_OAMethod @object, 'Send', NULL, @From, @To, @Subject, @Importance=@Importance
IF @hr <> 0 GOTO ObjectError

-- Destroy the NewMail object.
EXEC @hr = sp_OADestroy @object
IF @hr <> 0 GOTO ObjectError

PRINT 'Message sent.'
RETURN 0

ObjectError:
BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN 1
END


GO



GRANT EXEC ON sp_sendSMTPmail TO public
GO