| Author |
 Topic  |
|
|
esthera
Flowing Fount of Yak Knowledge
1340 Posts |
 Posted - 10/24/2004 : 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
United Kingdom
22191 Posts |
Posted - 10/24/2004 : 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 |
 |
|
|
esthera
Flowing Fount of Yak Knowledge
1340 Posts |
Posted - 10/24/2004 : 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. |
|
|
|
esthera
Flowing Fount of Yak Knowledge
1340 Posts |
Posted - 10/24/2004 : 14:18:48
|
| alternatively, is there anyway for me to call an asp page (That will send the email) from the stored proc? |
|
|
|
derrickleggett
Pointy Haired Yak DBA
USA
4184 Posts |
Posted - 10/24/2004 : 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. |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 10/25/2004 : 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 |
|
|
|
esthera
Flowing Fount of Yak Knowledge
1340 Posts |
Posted - 10/25/2004 : 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. |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 10/25/2004 : 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 |
|
|
|
esthera
Flowing Fount of Yak Knowledge
1340 Posts |
Posted - 10/25/2004 : 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'.
|
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 10/25/2004 : 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 |
|
|
|
esthera
Flowing Fount of Yak Knowledge
1340 Posts |
Posted - 04/10/2005 : 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? |
|
|
|
eyechart
Flowing Fount of Yak Knowledge
USA
3575 Posts |
Posted - 04/10/2005 : 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 |
|
|
|
esthera
Flowing Fount of Yak Knowledge
1340 Posts |
Posted - 04/10/2005 : 12:28:26
|
Iv'e tried cdosys - it returns -2147220982 and no email is sent.
what can I do to debug? |
|
|
| |
Topic |
|
|
|
sp sending mail
Forum Locked
