webdork
asked on
SQL Server Email Debug
SQL Server 2000
Win XP Pro
I'm trying to send email from sql server stored procedure. (see attached snippet.) Its not working and I cant figure out how to debug. I dont know how to set up the smart host in IIS
Win XP Pro
I'm trying to send email from sql server stored procedure. (see attached snippet.) Its not working and I cant figure out how to debug. I dont know how to set up the smart host in IIS
CREATE PROCEDURE [dbo].[sp_send_cdontsmail]
@From varchar(100),
@To varchar(100),
@Subject varchar(100),
@Body varchar(4000),
@CC varchar(100) = null,
@BCC varchar(100) = null
AS
Declare @MailID int
Declare @hr int
EXEC @hr = sp_OACreate 'CDONTS.NewMail', @MailID OUT
EXEC @hr = sp_OASetProperty @MailID, 'From',@From
EXEC @hr = sp_OASetProperty @MailID, 'Body', @Body
EXEC @hr = sp_OASetProperty @MailID, 'BCC',@BCC
EXEC @hr = sp_OASetProperty @MailID, 'CC', @CC
EXEC @hr = sp_OASetProperty @MailID, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @MailID, 'To', @To
EXEC @hr = sp_OAMethod @MailID, 'Send', NULL
EXEC @hr = sp_OADestroy @MailID
GO
exec sp_send_cdontsmail 'from@email.com','to@email.com','Test of CDONTS','It works'
Sorry, I have procedure using CDOSYS Mail which is different.
What is an error? You should set smart host in your SMTP settings (IIS manager).
Patrik
What is an error? You should set smart host in your SMTP settings (IIS manager).
Patrik
ASKER
SQL server 2000
I see no error, the mail is just not delivered. I see where to set smarthost, I dont know what value to enter there.
I see no error, the mail is just not delivered. I see where to set smarthost, I dont know what value to enter there.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
So, smarthost should be the same as my outgoing mail server in my email client? I think I have tried that. I'm still trying to find a way to debug when mail does not arrive.
ASKER
Also, i've heard that XP does not support cdonts
The smart host can be any SMTP which will send mail for you. The one you have in email client you know that it will send. Only check if this SMTP requires authentication than you should set Outbound Secutity also.
You can check if SQL side is OK. Go to mailroot folder and check subfolders there.
I'm not sure about CDONTS on XP. If find it not working try newer CDOSYS (procedure is there http://support.microsoft.com/kb/312839)
Patrik
You can check if SQL side is OK. Go to mailroot folder and check subfolders there.
I'm not sure about CDONTS on XP. If find it not working try newer CDOSYS (procedure is there http://support.microsoft.com/kb/312839)
Patrik
ASKER
I ran the following sp against the attached.
declare @Body varchar(4000)
select @Body = 'This is a Test Message'
exec sp_send_cdosysmail 'me@mydomain.com','me@mydo main.com', 'Test of CDOSYS',@Body
Error: The transport failed to connect to the server.
declare @Body varchar(4000)
select @Body = 'This is a Test Message'
exec sp_send_cdosysmail 'me@mydomain.com','me@mydo
Error: The transport failed to connect to the server.
-- 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.
-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
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
What version of SQL you have? If SQL 2005 you need no CDONTS, there is good working DB Mail.
Patrik