Lunarpages Web Hosting Forum

Author Topic: send auto mails  (Read 7586 times)

Offline Rockiaraj

  • Space Explorer
  • ***
  • Posts: 7
send auto mails
« on: February 19, 2010, 10:10:01 PM »
Hi,
we are having the shared hosting account. ( window plan, asp.net2.0 and SQL server 2005)
we need to send every day auto email alert from SQL server.
How to configured? or how to send ?
Please help me to send...
i read many articles in the net but when i run that code(in my Pc) i am getting the error like not found some stored procedure.
I tried in server also same kind of error i am getting
So i thing i am missing the configuration .
some body please get me in right way
Thanks and Best Regards
Raj

Offline Rockiaraj

  • Space Explorer
  • ***
  • Posts: 7
Re: send auto mails
« Reply #1 on: February 20, 2010, 12:31:24 AM »
Hi, I find the code that is sending mail very fine from local SQL QUERY Analysis. but when I run in server its giving the error like access denied what permission i need to give...
Please help me out....
GO
/****** Object:  StoredProcedure [dbo].[sp_send_cdosysmail]    Script Date: 02/12/2009 21:20:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


Alter PROCEDURE [dbo].[stp_SendMail]
      @from varchar(500) ,
      @password varchar(500) ,
      @to varchar(500) ,      
      @subject varchar(500),
      @body varchar(4000) ,
      @bodytype varchar(10),
      @output_mesg varchar(10) output,
      @output_desc varchar(1000) output
AS
DECLARE @imsg int
DECLARE @hr int
DECLARE @source varchar(255)
DECLARE @description varchar(500)

EXEC @hr = sp_oacreate 'cdo.message', @imsg out

--SendUsing Specifies Whether to send using port (2) or using pickup directory (1)
EXEC @hr = sp_oasetproperty @imsg,
'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").value','2'

--SMTP Server
EXEC @hr = sp_oasetproperty @imsg,
  'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").value',
  'mail.myserver.com'

--UserName
EXEC @hr = sp_oasetproperty @imsg,
  'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").value',
  @from

--Password
EXEC @hr = sp_oasetproperty @imsg,
  'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").value',
  @password

--UseSSL
EXEC @hr = sp_oasetproperty @imsg,
  'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpusessl").value',
  'false'

--PORT
EXEC @hr = sp_oasetproperty @imsg,
  'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport").value',
  '25'

--Requires Aunthentication None(0) / Basic(1)
EXEC @hr = sp_oasetproperty @imsg,
  'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").value',
  '1'

EXEC @hr = sp_oamethod @imsg, 'configuration.fields.update', null
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, @bodytype, @body
EXEC @hr = sp_oamethod @imsg, 'send', null

SET @output_mesg = 'Success'

-- sample error handling.
IF @hr <>0
   SELECT @hr
   BEGIN
      EXEC @hr = sp_oageterrorinfo null, @source out, @description out
      IF @hr = 0
      BEGIN
         --set @output_desc = ' source: ' + @source
         set @output_desc =  @description
      END
   ELSE
   BEGIN
      SET @output_desc = ' sp_oageterrorinfo failed'
   END
   IF not @output_desc is NULL
         SET @output_mesg = 'Error'
END
EXEC @hr = sp_oadestroy @imsg


--- end of procedure
---start of sending mail
DECLARE
@out_desc VARCHAR(1000),
@out_mesg VARCHAR(10)

DECLARE @name VARCHAR(20),
@birthdate datetime,
@email NVARCHAR(50)


DECLARE @body NVARCHAR(1000)
 
set @email='raj@myservercom'
SET @body = '<b>Happy Birthday Raj </b><br />Many happy returns of the day'
+ '<br /><br />Customer Relationship Department'
EXEC stp_sendmail
'sales@Myserver.com', --- add your Email Address here
'xxxxxxxxxxxxxxxxx',       ----add your Password here
@email,
'Birthday Wishes',
@body,
'htmlbody', @output_mesg = @out_mesg output, @output_desc = @out_desc output

PRINT @out_mesg
PRINT @out_desc


----Error IS

Msg 229, Level 14, State 5, Line number 1
EXECUTE permission denied on object 'sp_OACreate', database 'mssqlsystemresource', schema 'sys'.

Msg 229, Level 14, State 5, Line number 1
EXECUTE permission denied on object 'sp_OASetProperty', database 'mssqlsystemresource', schema 'sys'.

Msg 229, Level 14, State 5, Line number 1
EXECUTE permission denied on object 'sp_OASetProperty', database 'mssqlsystemresource', schema 'sys'.

Msg 229, Level 14, State 5, Line number 1
EXECUTE permission denied on object 'sp_OASetProperty', database 'mssqlsystemresource', schema 'sys'.

Msg 229, Level 14, State 5, Line number 1
EXECUTE permission denied on object 'sp_OASetProperty', database 'mssqlsystemresource', schema 'sys'.

Msg 229, Level 14, State 5, Line number 1
EXECUTE permission denied on object 'sp_OASetProperty', database 'mssqlsystemresource', schema 'sys'.

Msg 229, Level 14, State 5, Line number 1
EXECUTE permission denied on object 'sp_OASetProperty', database 'mssqlsystemresource', schema 'sys'.

Msg 229, Level 14, State 5, Line number 1
EXECUTE permission denied on object 'sp_OASetProperty', database 'mssqlsystemresource', schema 'sys'.

Msg 229, Level 14, State 5, Line number 1
EXECUTE permission denied on object 'sp_OAMethod', database 'mssqlsystemresource', schema 'sys'.

Msg 229, Level 14, State 5, Line number 1
EXECUTE permission denied on object 'sp_OASetProperty', database 'mssqlsystemresource', schema 'sys'.

Msg 229, Level 14, State 5, Line number 1
EXECUTE permission denied on object 'sp_OASetProperty', database 'mssqlsystemresource', schema 'sys'.

Msg 229, Level 14, State 5, Line number 1
EXECUTE permission denied on object 'sp_OASetProperty', database 'mssqlsystemresource', schema 'sys'.

Msg 229, Level 14, State 5, Line number 1
EXECUTE permission denied on object 'sp_OASetProperty', database 'mssqlsystemresource', schema 'sys'.

Msg 229, Level 14, State 5, Line number 1
EXECUTE permission denied on object 'sp_OAMethod', database 'mssqlsystemresource', schema 'sys'.

Msg 229, Level 14, State 5, Line number 1
EXECUTE permission denied on object 'sp_OAGetErrorInfo', database 'mssqlsystemresource', schema 'sys'.

Msg 229, Level 14, State 5, Line number 1
EXECUTE permission denied on object 'sp_OADestroy', database 'mssqlsystemresource', schema 'sys'.

Msg 0, Level 0, State 1, Line number 23
Error

Msg 0, Level 0, State 1, Line number 24
sp_oageterrorinfo failed


Please help me i am getting more difficult in this
Thanks and Best Regards
Raj







MWaqas

  • Guest
Re: send auto mails
« Reply #2 on: February 10, 2011, 05:23:25 AM »
To send emails out via code; one will have to use SMTP-Authentication which means to use an actual email address and its password in the code to send emails out, hence any emails being sent out from the webapplications in PHP/Perl/ASP/ASP.net need the SMTP verification to be done prior to the mail() command in PHP with any email account, created from Plesk's "Remote Email" section.

The smtp verification can be done either with smtp server address "mail.your-domain.com" if you are using asp.net please use CDOSYS (with SMTP authentication) and if you are using PHP please use php-pear module. Samples of both codes are provided below:

---Pear module with Smtp-auth----
require_once "Mail.php";
$from = "<you@your.com>";
$to = "Waqas <m.waqas@lunarpages.com>";
$subject = "Hi!";
$body = "Hi,\n\nHow are you?";
$host = "mail.your.com";
$username = "you@your.com";
$password = "pass_of_you@your.com";
$headers = array ('From' => $from,
'To' => $to,
'Subject' => $subject);
$smtp = Mail::factory('smtp',
array ('host' => $host,
'auth' => true,
'username' => $username,
'password' => $password));
$mail = $smtp->send($to, $headers, $body);
if (PEAR::isError($mail)) {
echo("<p>" . $mail->getMessage() . "</p>");
} else {
echo("<p>Message successfully sent!</p>");
}

Here is a working example of -----------====CDOSYS====---------- without the opening ASP tags:

 Const cdoSendUsingPickup = 1 'Send message using the local SMTP service pickup directory.
 Const cdoSendUsingPort = 2 'Send the message using the network (SMTP over the network).
 Const cdoAnonymous = 0 'Do not authenticate
 Const cdoBasic = 1 'basic (clear-text) authentication
 Const cdoNTLM = 2 'NTLM
 Set objMessage = Server.CreateObject("CDO.Message")
 objMessage.Subject = "Example CDO Message"
 objMessage.Sender = "me@mydomain.com"
 objMessage.From = "me@mydomain.com"
 objMessage.To = "recipient@test.com"
 objMessage.TextBody = "This is some sample message text.." & vbCRLF & "It was sent using SMTP authentication."
 '==This section provides the configuration information for the remote SMTP server.
 objMessage.Configuration.Fields.Item _
 ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
 'Name or IP of Remote SMTP Server
 objMessage.Configuration.Fields.Item _
 ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mail.your.com"
 'Type of authentication, NONE, Basic (Base64 encoded), NTLM
 objMessage.Configuration.Fields.Item _
 ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasic
 'Your UserID on the SMTP server
 objMessage.Configuration.Fields.Item _
 ("http://schemas.microsoft.com/cdo/configuration/sendusername") = "you@your.com"
 'Your password on the SMTP server
 objMessage.Configuration.Fields.Item _
 ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password_of_you@your.com"
 'Server port (typically 25)
 objMessage.Configuration.Fields.Item _
 ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 587
 'Use SSL for the connection (False or True)
 objMessage.Configuration.Fields.Item _
 ("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = False
 'Connection Timeout in seconds (the maximum time CDO will try to establish a connection to the SMTP server)
 objMessage.Configuration.Fields.Item _
 ("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
 objMessage.Configuration.Fields.Update
 objMessage.Send