SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Sending mail from Sql Server
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Avinaash.S786
Starting Member

9 Posts

Posted - 02/13/2014 :  01:12:37  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
2004 Posts

Posted - 02/13/2014 :  01:48:29  Show Profile  Visit jackv's Homepage  Reply with Quote
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 - 02/13/2014 :  07:50:29  Show Profile  Reply with Quote
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

Edited by - Avinaash.S786 on 02/26/2014 00:20:50
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2004 Posts

Posted - 03/03/2014 :  01:49:19  Show Profile  Visit jackv's Homepage  Reply with Quote
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

India
52309 Posts

Posted - 03/03/2014 :  04:12:04  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
2004 Posts

Posted - 03/04/2014 :  01:42:56  Show Profile  Visit jackv's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000