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
