SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 sp sending mail
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

esthera
Flowing Fount of Yak Knowledge

1388 Posts

Posted - 10/24/2004 :  10:15:04  Show Profile
I am using the following stored procedure to send mail.

CREATE Procedure sp_SMTPMail @SenderName varchar(100), @SenderAddress varchar(100), @RecipientName varchar(100), @RecipientAddress varchar(100), @Subject varchar(200), @Body varchar(8000)AS SET nocount on declare @oMail int --Object reference declare @resultcode int EXEC @resultcode = sp_OACreate 'CDONTS.NewMail', @oMail OUT if @resultcode = 0 BEGIN EXEC @resultcode = sp_OASetProperty @oMail, 'From', @SenderAddress EXEC @resultcode = sp_OASetProperty @oMail, 'To', @RecipientAddress EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject EXEC @resultcode = sp_OASetProperty @oMail, 'Body', @Body EXEC @resultcode = sp_OAMethod @oMail, 'Send', NULL EXEC sp_OADestroy @oMail END SET nocount off GO
GO


It is executing successfuly but I am not recieving a message. Why?

Kristen
Test

United Kingdom
22403 Posts

Posted - 10/24/2004 :  10:37:46  Show Profile
Best I can suggest is that you try testing for errors from each of the EXEC statements:

CREATE Procedure sp_SMTPMail 
	@SenderName	varchar(100), 
	@SenderAddress	varchar(100), 
	@RecipientName	varchar(100), 
	@RecipientAddress varchar(100), 
	@Subject	varchar(200), 
	@Body		varchar(8000)
AS 
	SET nocount on 
declare @oMail int --Object reference 
declare @resultcode int 
	EXEC @resultcode = sp_OACreate 'CDONTS.NewMail', @oMail OUT 
PRINT 'sp_OACreate ''CDONTS.NewMail'', @oMail OUT : ' + CONVERT(varchar(20), @resultcode) + ' : ' + CONVERT(varchar(20), @@ERROR)
	if @resultcode = 0 
	BEGIN 
		EXEC @resultcode = sp_OASetProperty @oMail, 'From', @SenderAddress 
PRINT 'sp_OASetProperty @oMail, ''From'', @SenderAddress : ' + CONVERT(varchar(20), @resultcode) + ' : ' + CONVERT(varchar(20), @@ERROR)
		EXEC @resultcode = sp_OASetProperty @oMail, 'To', @RecipientAddress 
PRINT 'sp_OASetProperty @oMail, ''To'', @RecipientAddress : ' + CONVERT(varchar(20), @resultcode) + ' : ' + CONVERT(varchar(20), @@ERROR)
		EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject 
PRINT 'sp_OASetProperty @oMail, ''Subject'', @Subject : ' + CONVERT(varchar(20), @resultcode) + ' : ' + CONVERT(varchar(20), @@ERROR)
		EXEC @resultcode = sp_OASetProperty @oMail, 'Body', @Body 
PRINT 'sp_OASetProperty @oMail, ''Body'', @Body : ' + CONVERT(varchar(20), @resultcode) + ' : ' + CONVERT(varchar(20), @@ERROR)
		EXEC @resultcode = sp_OAMethod @oMail, 'Send', NULL 
PRINT 'sp_OAMethod @oMail, ''Send'', NULL : ' + CONVERT(varchar(20), @resultcode) + ' : ' + CONVERT(varchar(20), @@ERROR)
		EXEC @resultcode = sp_OADestroy @oMail 
PRINT 'sp_OADestroy @oMail : ' + CONVERT(varchar(20), @resultcode) + ' : ' + CONVERT(varchar(20), @@ERROR)
	END 
	SET nocount off 
GO
GO

Please put [code]...[/code] around your code to make it easier to read

Kristen
Go to Top of Page

esthera
Flowing Fount of Yak Knowledge

1388 Posts

Posted - 10/24/2004 :  13:47:20  Show Profile
I ran this and it said The command(s) completed successfully. How do I go about debugging why i'm not getting any emails.
Go to Top of Page

esthera
Flowing Fount of Yak Knowledge

1388 Posts

Posted - 10/24/2004 :  14:18:48  Show Profile
alternatively, is there anyway for me to call an asp page (That will send the email) from the stored proc?
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 10/24/2004 :  21:46:23  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message
Are you sure you have the smtp settings correctly? Open up Outlook and use the same parameters to setup a service for sending mail. Try to send it and see if you get a message.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 10/25/2004 :  01:04:52  Show Profile
Did you run my code in Query Analyser? (The PRINT statements won't show up from ASP etc.)

Do any of the PRINT statements produce a @resultcode or @@ERROR which is non-zero?

Kristen
Go to Top of Page

esthera
Flowing Fount of Yak Knowledge

1388 Posts

Posted - 10/25/2004 :  02:10:29  Show Profile
It didn't print anything. The sql server is on a hosting companies machine so I can't open up outlook on the machine.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 10/25/2004 :  07:58:29  Show Profile
Ah, OK. I'll change then to SELECT statements so that you get some output in ASP or whatever client tool you are using

CREATE Procedure sp_SMTPMail 
	@SenderName	varchar(100), 
	@SenderAddress	varchar(100), 
	@RecipientName	varchar(100), 
	@RecipientAddress varchar(100), 
	@Subject	varchar(200), 
	@Body		varchar(8000)
AS 
	SET nocount on 
declare @oMail int --Object reference 
declare @resultcode int 
	EXEC @resultcode = sp_OACreate 'CDONTS.NewMail', @oMail OUT 
SELECT 'sp_OACreate ''CDONTS.NewMail'', @oMail OUT', [@resultcode]=@resultcode, [@@ERROR]=@@ERROR
	if @resultcode = 0 
	BEGIN 
		EXEC @resultcode = sp_OASetProperty @oMail, 'From', @SenderAddress 
SELECT 'sp_OASetProperty @oMail, ''From'', @SenderAddress', [@resultcode]=@resultcode, [@@ERROR]=@@ERROR
		EXEC @resultcode = sp_OASetProperty @oMail, 'To', @RecipientAddress 
SELECT 'sp_OASetProperty @oMail, ''To'', @RecipientAddress', [@resultcode]=@resultcode, [@@ERROR]=@@ERROR
		EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject 
SELECT 'sp_OASetProperty @oMail, ''Subject'', @Subject', [@resultcode]=@resultcode, [@@ERROR]=@@ERROR
		EXEC @resultcode = sp_OASetProperty @oMail, 'Body', @Body 
SELECT 'sp_OASetProperty @oMail, ''Body'', @Body', [@resultcode]=@resultcode, [@@ERROR]=@@ERROR
		EXEC @resultcode = sp_OAMethod @oMail, 'Send', NULL 
SELECT 'sp_OAMethod @oMail, ''Send'', NULL', [@resultcode]=@resultcode, [@@ERROR]=@@ERROR
		EXEC @resultcode = sp_OADestroy @oMail 
SELECT 'sp_OADestroy @oMail', [@resultcode]=@resultcode, [@@ERROR]=@@ERROR
	END 
	SET nocount off 

Kristen
Go to Top of Page

esthera
Flowing Fount of Yak Knowledge

1388 Posts

Posted - 10/25/2004 :  08:29:36  Show Profile
I am using sql query analyzer.
when running teh sp I get

Server: Msg 229, Level 14, State 5, Procedure sp_OACreate, Line 12
EXECUTE permission denied on object 'sp_OACreate', database 'master', owner 'dbo'.


Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 10/25/2004 :  08:42:00  Show Profile
Were you using query analyzer before? It occurs to me you might have been in GRID mode, in which case the debug info I added would ahve been in the "messages" tab

I wonder where your permissions to 'sp_OACreate' have gone since you previously ran the script, and ideas - different Login ID prehaps? Can the DBA grant you access to sp_OACreate?

Kristen
Go to Top of Page

esthera
Flowing Fount of Yak Knowledge

1388 Posts

Posted - 04/10/2005 :  05:38:43  Show Profile
I am back to this old thread as I finally have right to whatever I want.
Now I found cdonts is not installed as it is a windows 2003 server.
What other ways can I use to send emails from sql server?
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 04/10/2005 :  12:19:31  Show Profile
quote:
Originally posted by esthera

I am back to this old thread as I finally have right to whatever I want.
Now I found cdonts is not installed as it is a windows 2003 server.
What other ways can I use to send emails from sql server?



search on CDOSYS in our forums and Google.



-ec
Go to Top of Page

esthera
Flowing Fount of Yak Knowledge

1388 Posts

Posted - 04/10/2005 :  12:28:26  Show Profile
Iv'e tried cdosys - it returns -2147220982 and no email is sent.
what can I do to debug?
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000