Enviar email desde SQL

Nop

"Piolin Net" wrote:

Hola!

Para enviar emails desde SQL2000 estoy utilizando los sps extendidos del
MASTER,
sp_OACreate, etc ¿Hay alguna otra forma de enviar emails sin utilizar
estos procedimientos del MASTER? (el caso es que en uno de los hosting
compartido que tenemos no nos dan permiso par usarlos)

Gracias

Piolin.
Espero te sirva:

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
END

EXEC @hr = sp_OAMethod @iMsg, 'Send', 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_OAMethod Send')
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_OAMethod Send')
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END


send_cdosysmail_cleanup:
If (@iMsg IS NOT NULL) -- if @iMsg is NOT NULL then destroy it
BEGIN
EXEC @hr=sp_OADestroy @iMsg

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_OADestroy')
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_OADestroy')
END

ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
END
ELSE
BEGIN
PRINT ' sp_OADestroy skipped because @iMsg is NULL.'
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid,
@From, @To, @Subject, @Body, @iMsg, @hr, @source, @description,
@output, '@iMsg is NULL, sp_OADestroy skipped')
RETURN
END
/*
declare @Body varchar(4000)
select @Body = 'This is a Test Message'
exec sp_send_cdosysmail 'Mail1','Mail2,'Test of CDOSYS',@Body
select * from [dbo].[cdosysmail_failures]
*/