Sql Mail And Sql Server Agent Mail With Lotus Notes Domino Server

Hello Everyone,
I am trying to configure SQL MAIL and SQL SERVER AGENT MAIL feature of SQL SERVER 2000 with Lotus Domino server (Not Microsoft Exchange). Every thing goes fine and it works also. Problem starts when I try to restart SQL Agent Service. It hangs and the status of service remain 'starting'...
Also the size of the sqlagent service process goes upto 12MB. I don't understand why? Can any one help me in solving this problem.
I am using Windows 2000 Advanced Server and SQL Server 2000 with SP3. Domino client is R5.0.5 and Outlook 2000.

Look in sql server log, sql agent log and event viewer for warnings or errors

I checked all the log files, there is no error message. The only message I can see is Agent is starting MAPI session...

Also, After setting up SQL MAIL/SQL AGENT MAIL, behaviour of Outlook 2000 changes abruptly. It hangs.

Found something on this issue
http://support.microsoft.com/service...1/WC091301.ppt

I always find this useful for SQL Mail

http://support.microsoft.com/default...b;en-us;311231

did you consider other options like SMTPSEND?

http://www.databasejournal.com/sqlet...le.php/1585201

Hello Everyone,
Thanks for your help. Those documents are really helpful, but they don't tell any thing about Domino Server and SQL MAIL/Agent Mail. I explored all the possibilities but didn't get any success so far. Also I am tied with internet security issues, so I can't have access to my mail server (using POP3/SMTP services). The only option left is using Domino Server as corporate/workgroup mail server. I don't have any idea about SMTP Server. Can any one tell me, in order to send e-mail using SMTP(after creating a stored procedue, as suggested in one of the reply to this thread), what information about the mail server( Domino Server - I can send/receive e-mail to any internet domain using my id) do I need to have? Once again I am thankful for your help - Suresh

Here is an alternative to SQL Mail.

http://www.sqldev.net/xp/xpsmtp.htm

For sending smtp emails you need to enable one smtp service. Ask your Network Admin for the relay IP address you can use for smtp.

Smtpsend.exe can be found anywhere on the internet. shoot me an email if you need that .exe file. its a shareware.

Your network admin can enable it.

Thanks to everyone, I am NOW working on SMTP USING netsend.exe...

send your email id i will send the smtpsend.exe that i was using, so that you dont have to update the procedure,switches and codes.

mak_999@yahoo.com

Hi,
I am using CDOSYS for sending e-mails using SMTP server, its working... thanks to every one

For KB purpose, could you please copy and paste the CODE on how you are using CDOsys?

MAK,

I used same code as given by Microsoft KB - 312839

This is working fine.

CREATE PROCEDURE dbo.sp_send_cdosysmail
@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "
AS
Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
EXEC @hr = sp_OASetProperty @iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'

-- 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', 'MailServerName'

EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
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


Then use the stored procedure you created and provide the proper
parameters:

declare @Body varchar(4000)
select @Body = 'This is a Test Message'
exec sp_send_cdosysmail 'someone@microsoft.com', 'someone2@microsoft.com','Test of CDOSYS', @Body

Thanks

http://support.microsoft.com/default...NoWebContent=1

customise mails that are sent ?

Hi all,
i was unable to find how to add the actual MS SQL SERVER name in my mail subject :

declare @Body varchar(4000)
select @Body = 'This mail was sent by server : ' + @@SERVERNAME

Doesn't seem to be correct syntax ?
+ how could i send the content of a specific log file (text file) in the mail body ?

thanks Florent

Tried with @attachments argument in xp_sendmail? Check books online for detailed syntax.

If you really need to do what you ask I have a tip: import the content of the textfile into a table (I use a scheduled job to run a DTS-package DTS: source textfile -> Data transformation -> destination database-table). Then you use select from the table to create the message. When using xp_sendmail I find it easier to populate the parameters first with a set-command.
declare @mymessage varchar(255)
set @mymessage=(select acolumn from atable where...)
xp_sendmail @message=@mymessage
...

I find that if you do like above with all parameters you need it will work better than

xp_sendmail @message=(select...)

Emailing the content of a textfile is quite "advanced" but I got it to work after a long time. :-)

Well, ok for reading file.

Now, i try to monitor freespace of my sevrer's disks, but there is something i can't understand :

i use the "sp_send_cdosysmail" to send an email in case of low freespace but am unable to insert a variable value into a text one :


USE Administration
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[DE_FreeSpace]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [DE_FreeSpace]
GO

declare @MB_Free int
declare @Body varchar(4000)

create table DE_FreeSpace(Drive char(1), MB_Free int)

insert into DE_FreeSpace exec master.dbo.xp_fixeddrives

select @MB_Free = MB_Free from DE_FreeSpace where Drive = 'C'

-- Free Space on C drive Less than Threshold
if @MB_Free < 1024
select @Body = 'FreeSpace of C: < 1Go on ' + @@SERVERNAME
exec sp_send_cdosysmail 'alerts@mydomain.com', 'alertes@mydomain.com','SERVER FREE SPACE ALERT', @Body

I receive an email without any body into it...

Congratulations. You have more free disk space than 1024 MB.

(Increase the MB value to something very high when you test this and maybe you will get your body.)

oupsSS....you're right...

sorry, that permit me to learn a bit more T-SQL :

BEGIN END and convert() ;-))