Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 sp sending mail

Author  Topic 

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2004-10-24 : 10:15:04
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

22859 Posts

Posted - 2004-10-24 : 10:37:46
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
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2004-10-24 : 13:47:20
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
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2004-10-24 : 14:18:48
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

4184 Posts

Posted - 2004-10-24 : 21:46:23
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

22859 Posts

Posted - 2004-10-25 : 01:04:52
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
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2004-10-25 : 02:10:29
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

22859 Posts

Posted - 2004-10-25 : 07:58:29
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
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2004-10-25 : 08:29:36
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

22859 Posts

Posted - 2004-10-25 : 08:42:00
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
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-04-10 : 05:38:43
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
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-04-10 : 12:19:31
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
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-04-10 : 12:28:26
Iv'e tried cdosys - it returns -2147220982 and no email is sent.
what can I do to debug?
Go to Top of Page
   

- Advertisement -