May 5, 2008 at 8:42 am
Environment: SQL Server 2000 running under Windows 2000.
and developper's machine running under Windows XP.
Requirment:
Send a an e-mail form a stored procedure called every 5 minutes by a job.
WITHOUT having to install and run Outlook, and WITHOUT using IIS.
A REMOTE smtp sever is used.
Microsoft knowledge base proposes athe following:
[font="Courier New"] at http://support.microsoft.com/kb/312839/%5B/font%5D
[font="Courier New"] EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', cdoSMTPServerName [/font]
I have tried the http://schemas.microsoft.com/cdo/configuration/smtpserver link but The page cannot be found.
After hours of searching for information. I have reached the point I no longer wish to suffer in silence. Help!
How do I get this to work?
[font="Courier New"]-- drop old cdosysmail_failures table if exists
IF (EXISTS (SELECT * FROM dbo.sysobjects WHERE name = N'cdosysmail_failures' AND type='U')) DROP TABLE [dbo].[cdosysmail_failures]
GO
-- Create new cdosysmail_failures table
CREATE TABLE [dbo].[cdosysmail_failures]
([Date of Failure] datetime,
[Spid] int NULL,
[From] varchar(100) NULL,
[To] varchar(100) NULL,
[Subject] varchar(100) NULL,
[Body] varchar(4000) NULL,
[iMsg] int NULL,
[Hr] int NULL,
[Source of Failure] varchar(255) NULL,
[Description of Failure] varchar(500) NULL,
[Output from Failure] varchar(1000) NULL,
[Comment about Failure] varchar(50) NULL)
GO
IF (EXISTS (SELECT * FROM dbo.sysobjects WHERE name = N'sp_send_cdosysmail' AND type='P')) DROP PROCEDURE [dbo].[sp_send_cdosysmail]
GO
CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "
/*********************************************************************
This stored procedure takes the parameters and sends an e-mail.
All the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
References 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
IF @hr <>0
BEGIN
SELECT @hr
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OACreate')
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
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OACreate')
RETURN
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
IF @hr <>0
BEGIN
SELECT @hr
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty sendusing')
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
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty sendusing')
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END
-- 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', cdoSMTPServerName
IF @hr <>0
BEGIN
SELECT @hr
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty smtpserver')
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
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty smtpserver')
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END
-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
IF @hr <>0
BEGIN
SELECT @hr
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty Update')
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
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty Update')
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END
-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
IF @hr <>0
BEGIN
SELECT @hr
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty To')
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
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty To')
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
IF @hr <>0
BEGIN
SELECT @hr
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty From')
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
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty From')
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
IF @hr <>0
BEGIN
SELECT @hr
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty Subject')
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
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty Subject')
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
IF @hr <>0
BEGIN
SELECT @hr
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty TextBody')
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
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty TextBody')
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
IF @hr <>0
BEGIN
SELECT @hr
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OAMethod Send')
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
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OAMethod Send')
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
send_cdosysmail_cleanup:
If (@iMsg IS NOT NULL) -- if @iMsg is NOT NULL then destroy it
BEGIN
EXEC @hr=sp_OADestroy @iMsg
-- handle the failure of the destroy if needed
IF @hr <>0
BEGIN
select @hr
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OADestroy')
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
-- if sp_OAGetErrorInfo was successful, print errors
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OADestroy')
END
-- else sp_OAGetErrorInfo failed
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
END
ELSE
BEGIN
PRINT ' sp_OADestroy skipped because @iMsg is NULL.'
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, '@iMsg is NULL, sp_OADestroy skipped')
RETURN
END[/font]
May 5, 2008 at 8:51 am
And I forgot to mention it:
WITHOUT using SQLMail.
Regards
May 5, 2008 at 9:17 am
xp_smtp?
Why not Outlook?
Without testing, the code looks ok and you're in the write approach. Have you tried doing this in VBScript first to check your object code?
May 5, 2008 at 9:31 am
Outlook is not installed on the SQL Server machine.
Even if I install it, I will always have to run.
Not using Outlook avoids an unecessary dependency.
And no, I am not using VB. The idea is to have an SQL job check every 5 mintues if a file has been added in a specific directory (I already have the T-SQL that queries the windows directories). Since it apperently is possible to use a stored procedure to send an e-mail, I would like to avaoid having an external VB application that would have also have to run at all times.
CDOSYS is supposed to do this.
But the code snipped I posted produces no error message it just does not send anything that I can find in the Outlook running on my development machine (not the SQL Server 2000) machine.
If you could point me to a description / explanation of this command
[font="Courier New"]EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas. microsoft.com/cdo/configuration/smtpserver").Value', 'MailServerName'[/font].
I would appreciate it. I have no idea how this command works, what are the parameters and least of all how to find out why nothing happens (hr always returns 0).
Any reference link to Microsoft CDOSYS in various articles systemetically results in the
Page not found.
Thanks
May 8, 2008 at 2:06 pm
Ach so... No takers eh ?
OK Fixed it.
1. Lack of documentation
As I mentioned, all references to Microsoft documentation regarding CDOSYS given in the various articles found on the net systematically forced the browser to this page
[font="Courier New"]http://msdb.microsoft.com/en-us/library/ms123402.aspx[/font] with the title Contents not found.
For the time being, the proper url is:
MSDN -> MSDN Library -> Win32 and COM Development -> Messaging and Collaboration -> CDO For Windows 2000
[font="Courier New"]http://msdn.microsoft.com/en-us/library/ms527568(EXCHG.10).aspx[/font]
I was bitching about this lack of documentation because the syntax was, for an SQL coder, weird looking:
[font="Courier New"]EXEC @hr = sp_OASetProperty @Message, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @@SERVERNAME[/font]
Well, lo and behold, CDO.Message has a Configuration Module which has over 30 fields, such as [font="Courier New"]cdoSMTPServer[/font] and its value is [font="Courier New"]"http://schemas.microsoft.com/cdo/configuration/smtpserver"[/font].
2. Lack of response
My first attempts had typing errors. The emails ended in on the server's hard drive in
[font="Courier New"]c:\inetpub\mailroot\badmail[/font]. So they were sent after all. This was the default setting on the database server which was running IIS and SMTP.
With errors corrected, the e-mails end up in [font="Courier New"]c:\inetpub\mailroot\queue/font]. And within 2 miinutes, in my Outlook InBox.
Regards
May 8, 2008 at 4:38 pm
Hi:
Try this out...
/* This sp is referred from the following web site: http://www.orcsweb.com/articles/sendmailsql.aspx */
create procedure sendnotification @cfrom varchar(100), @cto varchar(100), @csubject varchar(50),
@cbody text, @csmtp varchar(20), @cretvalue varchar(1000) = '' output
as
begin
declare @iMsg int, @hr int, @source varchar(255), @description varchar(500), @output varchar(1000)
/* Create the CDO.Message Object */
execute @hr = sp_OACreate 'CDO.Message', @iMsg OUT
/* Configuring the Message Object
This is to configure a remote SMTP server.
http://msdn.microsoft.com/library/default.asp? =/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp */
execute @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. */
execute @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @csmtp
/* Save the configurations to the message object. */
execute @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
/* Set the e-mail parameters. */
execute @hr = sp_OASetProperty @iMsg, 'To', @cto
execute @hr = sp_OASetProperty @iMsg, 'From', @cfrom
execute @hr = sp_OASetProperty @iMsg, 'Subject', @csubject
/* If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'. */
execute @hr = sp_OASetProperty @iMsg, 'TextBody', @cbody
execute @hr = sp_OAMethod @iMsg, 'Send', NULL
/* Sample error handling. */
if @hr <>0
-- select @hr
begin
execute @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
if @hr = 0
begin
select @output = ' Source: ' + @source
print @output
-- select @output = ' Description: ' + @description
select @cretvalue = ' Description: ' + @description
--print @output
end
else
begin
-- print ' sp_OAGetErrorInfo failed.'
select @cretvalue = ' sp_OAGetErrorInfo failed.'
return
end
end
select @cretvalue as 'cRetValue'
/* Do some error handling after each step if you have to.
Clean up the objects created. */
execute @hr = sp_OADestroy @iMsg
end
This is a simple stored procedure that will send it directly to your recipient as long as you have the SMTP server. I was basing this in http://www.orcsweb.com/articles/sendmailsql.aspx and I just modified some of it.
The parameters that I used base from the script above is somewhat like this:
execute vsp_am_sendnotification @cfrom = "jan_cerna@yahoo.com", @cto = "someone@someone.com", @csubject = "Hello World",
@cbody = "Helloooo", @csmtp = "88.8.344.259"
Take note: If you have any antivirus software/firewall please allow this protocol so it won't give you any headache.
Hope this helps at your end.
Jan
May 9, 2008 at 8:41 am
Mr. Jan Michaels,
Thanks for your help.
One point is was making was that in all the examples I found on the net, the following lines confused me no end:
[font="Courier New"]/* Configuring the Message Object
This is to configure a remote SMTP server.
http://msdb.Microsoft.com/library/default.asp? =/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp */
execute @hr = sp_OASetProperty @iMsg, 'Configuration.fields ("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
[/font]
First of, I did not know that the http was a field of the Configuration module of CDOSYS. I thought it was a URL where I could find help. Imagine my frustration when I always ended on a Microsoft MSDN web page title Contents not found.
I was looking for help about the execute line. Since I knew nothing about SMTP, I just could not understand the syntax. Try debugging code you do not understand and can't find any info.
Worst, even after everything ran without error (hr was always 0) the e-mail just were not sent. Rolling up my sleeves, I had to delve into SMTP and Windows 2000 settings to find they ended in a default directory because of errors.
So I hope my 2-pennies worth can make things clearer for others who are in the same situation.
And I have been advised that CDOSYS is part of the Miicrosoft's System.Webmail and that a newer approach is now available: System.Web.Mail
The two following references provide FAQ's on both mail systems:
Thanks to "Caddre, Moderator" for the above.
[font="Courier New"]http://forums.microsoft.com/msdn/ShowPost.aspx?postid=3313692&isthread=false&siteid=1&authhash=5080d8e75d01a1540507eed94ca8feec47b9dff5&ticks=633458530323118899[/font]
Regards
J.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply