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.
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)asbeginDECLARE @From varchar(50)DECLARE @CC varchar(50)DECLARE @CurrDate datetimeDECLARE @MailServerName VARCHAR(100)DECLARE @BodyType varchar(100)DECLARE @iMsg intDECLARE @hr intDECLARE @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 OUTEXEC @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', nullEXEC @hr = sp_OASetProperty @iMsg, 'To', @ToEXEC @hr = sp_OASetProperty @iMsg, 'From', @FromEXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.EXEC @hr = sp_OASetProperty @iMsg, @BodyType , @vcBodyEXEC @hr = sp_OAMethod @iMsg, 'Send', NULL--PRINT @hrselect @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 ENDEND-- Do some error handling after each step if you need to.-- Clean up the objects created.EXEC @hr = sp_OADestroy @iMsgendCan any one help me ASAP.This is an urgent issueAvinaash Sedit: moved to proper forum |
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
|
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 |
|
|
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 serverJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
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 sugestionWe 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 etcJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
|
|
|
|