HELP !!! sp_send_cdontsmail dont work for me...
-
all ,
i have this proc "sp_send_cdontsmail" in my master db and when i run it from QA it seems to work. and the QA said it was sucessful.. but i dont get my mail. i am testing it by sening my self mail... any help would be great !!
here is the proc by the way..
ALTER 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
-
Hi,
I don't know if this really helps, but I think depending on your OS you have to use CDOSYS.
When I'm back in office on monday I hopefully can tell you more!
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hi,
quote:
Hi,I don't know if this really helps, but I think depending on your OS you have to use CDOSYS.
When I'm back in office on monday I hopefully can tell you more!
Monday morning
I remember not getting emails to work properly when using CDONTS, while CDOSYS works.
Maybe a good starting point for explanation is the following
http://coveryourasp.com/Snippet.asp?snip=48
I must also mention that I haven't utilized that from within SQL Server, but that's no difference
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Make sure that you have updated the Stored Proc with your own SMTP server name and that you can access that server accross your network by pinging it by name as well as by IP address.
CDONTS/CDOSYS does not report an error if the server is not found - the messaage just vanishes into the ether!
-
good monday morning ?!?
i have changed the CDONTS to CDOSYS and still nothing, i have the correct copy of the proc on the server. i can ping the SQL Server. i am running this in the QA and all i get is
Stored Procedure: master.dbo.sp_send_cdontsmail
Return Code = 0
and no mail no where.. is there a way i can debug this ???
-
Sorry, I meant can you ping the SMTP server?
-
Hi,
quote:
good monday morning ?!?i have changed the CDONTS to CDOSYS and still nothing, i have the correct copy of the proc on the server. i can ping the SQL Server. i am running this in the QA and all i get is
Stored Procedure: master.dbo.sp_send_cdontsmail
Return Code = 0
and no mail no where.. is there a way i can debug this ???
hmmm,... do you have full Outlook installed?
http://www.core-support.co.uk/kb/db/mssql/sqlmail.htm
http://dbforums.com/t757372.html
also cover this feature. Myabe they are of any help?
BTW, what configuration are you running?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
This is my CDOSYS (Win 2000 Server/SQL 2000) mail routine, which is originally from Microsoft's MSDN. There is also a script in the SQL Server Central script library which is more fully featured.
NB: CDONTS only runs on NT4 servers.
Looking at your code again you don't seem to have stated the SMTP server at all!
CREATE PROCEDURE [dbo].[spMailSendCDOSYSMail]
@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=' ',
@Body varchar(4000) =' '
/*********************************************************************
This stored procedure takes the above parameters and sends an e-mail.
All of the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
Reference 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,
@hr int,
@source varchar(255),
@description varchar(500),
@output varchar(1000)
--************* Create the CDO.Message Object ************************
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
--***************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' -- cdoSendUsingPort
-- This is to configure the Server Name or IP address.
-- 'mysmtpserver' is the name of the SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'mysmtpserver'
-- This is to configure the SMTP Connection Timeout
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout").Value', '10'
-- This is to configure the URL Proxy Server if used
--EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/urlproxyserver").Value', '10.0.0.0:8080'
-- This is to configure the URL Proxy Bypass
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/urlproxybypass").Value', '<local>'
-- This is to configure the URL Get Latest Version
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/urlgetlatestversion").Value', 'True'
-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
-- Set the e-mail parameters.
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
ELSE
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
/*Then use the stored procedure you created and provide the proper parameters:
declare @Body varchar(4000)
select @Body = 'This is a Test Message'
exec spMailSendCDOSYSMail 'someone@microsoft.com','someone2@microsoft.com','Test of CDOSYS',@Body
*/
GO
Edited by - ed harling on 07/14/2003 07:20:33 AM
-
Sorry !! Config = SQL Server 2000 / o/s = win 2k server. and yes i have outlook 2k mail installed on the server. and it uses a company SQL_Server id for mail.
-
The script library for this site has the code for a CDO proc called "sp_SQLSMTPMail". It might be worth checking out.
Randy
Helpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply