January 16, 2003 at 9:21 am
I have struggled for years to make SQLMail work right outside of Exchange, and I'm giving up. We have several 2000 and 7.0 machines that have processes hung by MAPISP32.DLL.
So now I'd like to use the excellent contributed SP_SqlSmtpMail procedure for the 2000 boxes, which does work fine. But I'll miss the integration built-in mailing for Operators, Jobs, and Maintenance Plans.
Has anyone found a general-purpose way to redirect MAPI mails to SMTP without using the MAPI dlls? I don't want to create a MAPI profile that uses SMTP, since that's what I was doing before. I doubt it's possible to do this in a general way, since the MAPI APIs would need to be supported, but I thought I'd ask.
Barring that, what workarounds are used to generate meaningful alerts? Do you execute a program from the screen? What alerts do you use?
Thanks for any input...
January 16, 2003 at 10:19 am
I may have some answers for you. This is something I found and have been using it for awhile. We don't have exchnage and outlook inbox thing was a total waste of time. The Following code needs to be runned against a databse. This makes 3 sp. Then use this line in code, job, sp, DTS Package, whatever. It seems to work great for me. the only drawback is the formatting of long emails, not good, but you can work with it. You must have SMTP services running on the sql server and rights to the master database. If you are in a enterprise that should be no problem.
I HOPE THIS HELPS. I am new to this board and hope to help all I can! GOOD Luck! I would be very interested to see how it goes.
Requirements: SMTP server (comes with IIS but doesn't require IIS) must be loaded.
SINGLE LINE EXAMPLE:
exec sp_sendSMTPmail 'scott@any.com','This is a test subject','This is a test message'
SINGLE LINE FORMATED EXAMPLE:
set @Mess = 'This is a Professional Development signup confirmation!
' + rtrim(@HGIFIRST) + ' ' + rtrim( @HGILAST) + ' has signed up for
"' + upper(rtrim(@Activity_Title)) + '".'+ @MessDates + '
Cick link to delete.
PLACEURLHERE?bla=' + cast(@captureID as nvarchar)
exec sp_sendSMTPmail @Email, 'Workshop Notifcation', @Mess,@from = 'pdmanagerPLACEURLHERE'
continued---
January 16, 2003 at 10:23 am
CODE TO RUN:
/* Name: sp_sendSMTPmail
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'
*/
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.
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'
*/
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
good bye
January 16, 2003 at 11:06 am
sschwarze,
Funny thing is, I'm the one that originally posted the code for sp_sendSMTPMail on Swynk.com some years back. I believe pieces of it were snarfed from others, but I can't recall completely. Nice to know its still in use!
My problem is not how to send mail through SMTP (obviously). Its how to integrate it as a replacement of the thoroughly well-integrated but horribly unstable SQLMail (w/o Exchange). When we used Exchange as an organization, SQLMail was tolerable, but mandates have been issued that our Exchange servers will go away.
For example, if I was to drop in a new extended stored procedure with the name of xp_sendmail, does anyone know if the built-in mail protocols on alerts and jobs would use it? Or, as I feel is more likely, is Sql using the behind-the-scenes APIs rather than the extended SP to send the mail?
Are there sample alert/job configurations that use alternative mail procedures like sp_sendSMTPmail that can be shared?
January 17, 2003 at 10:59 am
I want to thank you SO MUCH for that code. It has saved my enterprise. We use a peice of email software called "First Class" No intergration, no help at all. As far as the other you are over my head. Hey all you DBA's out there, How's about a hand.
January 20, 2003 at 4:23 am
If An error occurs in a job step you could specify that the job step to goto which would be the execution of the mail send stored procedure. See the Advanced tab of the job step
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply