Mail by Store Procedure
-
Hello Friends !!!
thank you for great help in past. but people i'm stucked again in the same problem.
------------
DECLARE @message int
DECLARE @config int
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)
EXEC @hr = sp_OACreate 'CDO.Message', @message OUT -- create the message object
---EXEC @hr = sp_OACreate 'CDO.Configuration', @config OUT -- create the configuration object
-- Configuration Object
/*EXEC @hr = sp_OASetProperty @message, 'Configuration.Fields(http://schemas.microsoft.com/cdo/configuration/sendusing).Value',2 -- Send the message using the network
EXEC @hr = sp_OASetProperty @message, 'Configuration.Fields(http://schemas. microsoft.com/cdo/configuration/smtpserver).Value', '172.16.2.53' -- SMTP Server
EXEC @hr = sp_OASetProperty @message, 'Configuration.Fields(http://schemas.microsoft.com/cdo/configuration/smtpserverport).Value',25 -- Server SMTP Port
EXEC @hr = sp_OASetProperty @message, 'Configuration.Fields(http://schemas.microsoft.com/cdo/configuration/sendusername).Value', 'datapoint@ncl.res.in' -- username
EXEC @hr = sp_OASetProperty @message, 'Configuration.Fields(http://schemas.microsoft.com/cdo/configuration/sendpassword).Value', dtpt --password
EXEC @hr = sp_OASetProperty @message, 'Configuration.Fields(cdosendusingMethod).Value', 2 -- Send the message using the network */
EXEC @hr = sp_OASetProperty @message, 'Configuration.Fields(cdosmtpserver).Value', '172.16.2.53' -- SMTP Server
EXEC @hr = sp_OASetProperty @message, 'Configuration.Fields(cdosmtpserverport).Value',25 -- Server SMTP Port
EXEC @hr = sp_OASetProperty @message, 'Configuration.Fields(cdosmtpauthenticate).Value',1
EXEC @hr = sp_OASetProperty @message, 'Configuration.Fields(cdosendusername).Value', 'datapoint@ncl.res.in' -- username
EXEC @hr = sp_OASetProperty @message, 'Configuration.Fields(cdosendpassword).Value', password --password
EXEC @hr =sp_OAMethod @message, 'Configuration.Fields.Update'
EXEC @hr = sp_OASetProperty @message, 'To','jagriti_23@yahoo.co.in'
EXEC @hr = sp_OASetProperty @message, 'cc','neetals@gmail.com'
EXEC @hr = sp_OASetProperty @message, 'From','datapoint@ncl.res.in'
EXEC @hr = sp_OASetProperty @message, 'Subject','newStored Proc Check'
EXEC @hr = sp_OASetProperty @message, 'HTMLBody','This is a stored proc generated mail'
EXEC @hr =sp_OAMethod @message, 'Send()'
-- Destroys the objects
EXEC @hr = sp_OADestroy @message
--EXEC @hr = sp_OADestroy @config
-- Errorhandler
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @message, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
GO
--------------------------------
above is my SPROC. This was working fine earlier suddenly it has stopped working anymore. when i run the Procedure i get the response "Command executed successfully". but when i see the mails, its not been recived on the user's end
Please Help me out
Regards
Neetal Shah
-
If it was working before, then the devil is in the data...
What creates the IP address on the SMTP Server? If it's DHCP and someone rebooted it, it may have a different address, now. Try pinging it to see.
Did the password expire automatically or, perhaps, did someone change it?
Has your account expired?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Dear Jeff,
As for IP address of SMTP is concern, it is Static. I try to ping it also, and no problem. Neither my password nor my account has expired. Because i use the same account for getting\sending my regular mails.
the problem is that though the procedure is executed the mail doesn't get fired from that procedure. I have checked it in Mail server log also.
Then what could be the reason for such erretic behavior????
Regards
Neetal Shah
-
Sorry, I don't know Neetal... I'm pretty much at a loss on this one...
Can anyone else help on this? I'm neither and sp_oa Ninja nor a mail ninja...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Is there some configuration that need to be setup or enable for sp_OA procedure to work? I have a similar procedure that was written by some consultant, and it work on a couple of servers. But when I recreated that proc on other servers and ran the proc, proc ran successfully but no mail was send. Any idea? thanks in advance
-
Hi Neetal,
Are you using email addresses or email aliases?
I had a similar problem before where I was using email aliases like 'Ronald San Juan' which was recognized by the mail server when the domain was up. Leter on, there were connection issues and the procedure (job actually) stopped working. There was no error message or anything. I replaced the email aliases with the email address (i.e. RonaldS@pcmall.com) and things were back to normal.
Hope this helps.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply