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
 General SQL Server Forums
 New to SQL Server Programming
 Sending mail from Sql Server

Author  Topic 

Avinaash.S786
Starting Member

9 Posts

Posted - 2014-02-13 : 01:12:37
When I execute the following stored procedure,
I get the following error

Source: CDO.Message.1
Description: The "SendUsing" configuration value is invalid.

My SP is

--sp_configure 'show advanced options',1
-- go
-- reconfigure
-- go
-- sp_configure 'Ole Automation Procedures',1
-- go
-- reconfigure
--
CREATE procedure [dbo].[SP_Trn_Send_Remainder_Mails]
@vcBody ntext,
@subject varchar(max),
@to varchar(100)
as
begin
DECLARE @From varchar(50)
DECLARE @CC varchar(50)

DECLARE @CurrDate datetime
DECLARE @MailServerName VARCHAR(100)
DECLARE @BodyType varchar(100)
DECLARE @iMsg int
DECLARE @hr int
DECLARE @source varchar(255)
DECLARE @description varchar(500)
DECLARE @output varchar(1000)


SET @From = 'test@test.com'/*Some EmailID*/
SET @MailServerName = '123.123.xx'/*Some MailServerName*/
SET @BodyType ='HTMLBODY'

EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
EXEC @hr = sp_OASetProperty @iMsg,'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','3'
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @MailServerName
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport").Value', '25'

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 , @vcBody
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
--PRINT @hr
select
@MailServerName as MailServerName,
@BodyType as BodyType,
@iMsg as iMsg,
@hr as Hr,
@source as Source,
@description asDesc,
@output as OP
-- Sample error handling.
IF @hr <>0
BEGIN
select @hr
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you need to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
end


Can any one help me ASAP.
This is an urgent issue

Avinaash S

edit: moved to proper forum

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2014-02-13 : 01:48:29
Have you thought about using Database Mail , which comes built in to SQL Server - since 2005. It is very straightforward to implement. Read here on how to set up Database Mail http://www.sqlserver-dba.com/2011/01/set-up-database-mail-on-sql-server-2005.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

Avinaash.S786
Starting Member

9 Posts

Posted - 2014-02-13 : 07:50:29
Hi jackv,

Thanks jackv for reply. The problem got solved.
It was due to a firewall issue. The stored procedure that I have used is perfect. It has no problem.

Thanks,
Avinaash S
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2014-03-03 : 01:49:19
Hi , Good news you solved the problem. The only problem of maintaining the stored procedure - is it's one other component you need to maintain- whereas if you used Database Mail - it's built in to sql server

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-03-03 : 04:12:04
quote:
Originally posted by Avinaash.S786

Hi jackv,

Thanks jackv for reply. The problem got solved.
It was due to a firewall issue. The stored procedure that I have used is perfect. It has no problem.

Thanks,
Avinaash S



I agree to Jackv's sugestion
We use database mail as its built in. Alternatively we use Send mail component with SSIS when we've some logic implemented using SSIS.
Using inbuilt features makes it easy to maintain

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2014-03-04 : 01:42:56
One of the issues we found was maintaining the stored procedure in a db - such as "master" - created problems around moving databases and services around- while forgetting about moving supporting scripts\procedures etc

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page
   

- Advertisement -