Envio de emails desde procedimiento almacenado

YA LO TENGO FUNCIONADO!!!

ME FALTABA ESPECIFICARLE LA AUTENTIFICACION DE LA CUENTA DE CORREO

GRATXE.

"Penta" wrote:

> Estimado.
> Aca esta :
>
> IF (EXISTS (SELECT * FROM dbo.sysobjects WHERE name > > N'cdosysmail_failures' AND type='U')) DROP TABLE [dbo].
> [cdosysmail_failures]
> GO
> CREATE TABLE [dbo].[cdosysmail_failures]
> ([Date of Failure] datetime,
> [Spid] int NULL,
> [From] varchar(100) NULL,
> [To] varchar(100) NULL,
> [Subject] varchar(100) NULL,
> [Body] varchar(4000) NULL,
> [iMsg] int NULL,
> [Hr] int NULL,
> [Source of Failure] varchar(255) NULL,
> [Description of Failure] varchar(500) NULL,
> [Output from Failure] varchar(1000) NULL,
> [Comment about Failure] varchar(50) NULL)
> GO
>
> IF (EXISTS (SELECT * FROM dbo.sysobjects WHERE name > > N'sp_send_cdosysmail' AND type='P')) DROP PROCEDURE [dbo].
> [sp_send_cdosysmail]
> GO
>
> CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
> @From varchar(100) ,
> @To varchar(100) ,
> @Subject varchar(100)=" ",
> @Body varchar(4000) =" "
> /
> *********************************************************************
>
> This stored procedure takes the parameters and sends an e-mail.
> All the mail configurations are hard-coded in the stored procedure.
> Comments are added to the stored procedure where necessary.
> References to the CDOSYS objects are at the following MSDN Web site:
> http://msdn.microsoft.com/library/d...saging.asp
>
>
> ***********************************************************************/
> AS
> Declare @iMsg int
> Declare @hr int
> Declare @source varchar(255)
> Declare @description varchar(500)
> Declare @output varchar(1000)
>
> ************************
> EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
> IF @hr <>0
> BEGIN
> SELECT @hr
> INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(),
> @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source,
> @description, @output, 'Failed at sp_OACreate')
> 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
> INSERT INTO [dbo].[cdosysmail_failures] VALUES
> (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source,
> @description, @output, 'sp_OAGetErrorInfo for sp_OACreate')
> RETURN
> END
> ELSE
> BEGIN
> PRINT ' sp_OAGetErrorInfo failed.'
> RETURN
> END
> END
>
> EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://
> schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
> IF @hr <>0
> BEGIN
> SELECT @hr
> INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(),
> @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source,
> @description, @output, 'Failed at sp_OASetProperty sendusing')
> 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
> INSERT INTO [dbo].[cdosysmail_failures] VALUES
> (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source,
> @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty
> sendusing')
> GOTO send_cdosysmail_cleanup
> END
> ELSE
> BEGIN
> PRINT ' sp_OAGetErrorInfo failed.'
> GOTO send_cdosysmail_cleanup
> END
> END
> schemas.microsoft.com/cdo/configuration/smtpserver").Value',
> cdoSMTPServerName
> schemas.microsoft.com/cdo/configuration/smtpserver").Value',
> cdoSMTPServerName
> EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.Fields("http://
> schemas.microsoft.com/cdo/configuration/
> smtpserver").Value','172.31.11.12'
> IF @hr <>0
> BEGIN
> SELECT @hr
> INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(),
> @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source,
> @description, @output, 'Failed at sp_OASetProperty smtpserver')
> 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
> INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(),
> @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source,
> @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty
> smtpserver')
> GOTO send_cdosysmail_cleanup
> END
> ELSE
> BEGIN
> PRINT ' sp_OAGetErrorInfo failed.'
> GOTO send_cdosysmail_cleanup
> END
> END
>
> EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
> IF @hr <>0
> BEGIN
> SELECT @hr
> INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(),
> @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source,
> @description, @output, 'Failed at sp_OASetProperty Update')
> 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
> INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(),
> @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source,
> @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty
> Update')
> GOTO send_cdosysmail_cleanup
> END
> ELSE
> BEGIN
> PRINT ' sp_OAGetErrorInfo failed.'
> GOTO send_cdosysmail_cleanup
> END
> END
>
> EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
> IF @hr <>0
> BEGIN
> SELECT @hr
> INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(),
> @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source,
> @description, @output, 'Failed at sp_OASetProperty To')
> 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
> INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(),
> @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source,
> @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty
> To')
> GOTO send_cdosysmail_cleanup
> END
> ELSE
> BEGIN
> PRINT ' sp_OAGetErrorInfo failed.'
> GOTO send_cdosysmail_cleanup
> END
> END
>
> EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
> IF @hr <>0
> BEGIN
> SELECT @hr
> INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(),
> @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source,
> @description, @output, 'Failed at sp_OASetProperty From')
> 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
> INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(),
> @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source,
> @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty
> From')
> GOTO send_cdosysmail_cleanup
> END
> ELSE
> BEGIN
> PRINT ' sp_OAGetErrorInfo failed.'
> GOTO send_cdosysmail_cleanup
> END
> END
>
> EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
> IF @hr <>0
> BEGIN
> SELECT @hr
> INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(),
> @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source,
> @description, @output, 'Failed at sp_OASetProperty Subject')
> 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
> INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(),
> @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source,
> @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty
> Subject')
> GOTO send_cdosysmail_cleanup
> END
> ELSE
> BEGIN
> PRINT ' sp_OAGetErrorInfo failed.'
> GOTO send_cdosysmail_cleanup
> END
> END
>
> 'TextBody'.
> EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
> IF @hr <>0
> BEGIN
> SELECT @hr
> INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(),
> @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source,
> @description, @output, 'Failed at sp_OASetProperty TextBody')
> 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
> INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(),
> @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source,
> @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty
> TextBody')
> GOTO send_cdosysmail_cleanup
> END
> ELSE
> BEGIN
> PRINT ' sp_OAGetErrorInfo failed.'
> GOTO send_cdosysmail_cleanup
> END