quote:

---

One problem with sp_OACreate is that it requires the System Administrator's fixed server role in order to run. Most DBA's say, "NO WAY!"

---

CREATE PROCEDURE sp_ProcessQueue
as

DECLARE @ID integer, @Email varchar(128), @Subject varchar(255), @BodyLen integer, @ReplyTo varchar(128), @From varchar(128)

SELECT @From = 'yakoo@sqlteam.com'

DECLARE @ReturnValue varchar(5)

DECLARE WaitingMail CURSOR FOR
SELECT ID, RecpientEmail, Subject, DATALENGTH(Body), ReplyTo
FROM QueueMail
WHERE GetDate() > SendRequestTime and Sent = 0 and Len(RecpientEmail) > 1

OPEN WaitingMail

FETCH NEXT FROM WaitingMail INTO @ID, @Email, @Subject, @BodyLen, @ReplyTo
WHILE @@FETCH_STATUS = 0 BEGIN
	Exec sp_SendMail @ID, @From, @Email, @Subject, @BodyLen, @ReplyTo, @return = @ReturnValue OUTPUT
	If @ReturnValue = 'TRUE' BEGIN
		UPDATE QueueMail 
		SET Sent = 1, 
		        SentTime = GetDate()
		WHERE ID = @ID
	END
	FETCH NEXT FROM WaitingMail INTO @SendMailID, @RecpEmail, @Subject, @BodyLen, @ReplyTo
END
CLOSE WaitingMail
DEALLOCATE WaitingMail
GO

CREATE Procedure sp_SendMail (
@ID integer,
@From varchar(128),
@Email varchar(128),
@Subject varchar(300),
@BodyLen integer,
@ReplyTo varchar(128),
@return varchar(255) OUTPUT
)
AS
DECLARE @object int, @hr int, @PartLen int, @property varchar(255)
DECLARE @BodyPart varchar(4000)
-- Create an object.
EXEC @hr = sp_OACreate 'JMail.Message', @object OUT
EXEC @hr = sp_OASetProperty @object, 'Subject',@subject
EXEC @hr = sp_OASetProperty @object, 'From', @From
EXEC @hr = sp_OASetProperty @object, 'ReplyTo', @replyto
EXEC @hr = sp_OASetProperty @object, 'Silent','true'
EXEC @hr = sp_OAMethod @object, 'AddRecipient', null, @Email

DECLARE @totalBytes integer, @charCount integer
SELECT @charCount = 0, @totalBytes = 0
WHILE @totalBytes < @BodyLen  BEGIN
	SELECT @BodyPart = SUBSTRING(Body, @CharCount + 1, 4000) FROM QueueMail WHERE ID = @ID
	EXEC @hr = sp_OAMethod @object, 'AppendText', null, @BodyPart
	SELECT @CharCount = @CharCount + Len(@BodyPart)
	SELECT @totalBytes = @totalBytes + DATALENGTH(@BodyPart)
END
EXEC @hr = sp_OAMethod @object, 'Send("mail.sqlteam.com")' , @return OUT
GO

Declare @FromName VarChar(200),
			@FromAddress VarChar(200),
			@Subject VarChar(200),
			@Body VarChar(200)
			
	Select @FromName = 'Me', 
	Select @FromAddress = 'me@mydomain.com'
	Select @Subject = 'Hi there'
	Select @Body = 'The body of the message'
	
	--Insert mail into queue table	
	INSERT Into MailQueue (FromName, FromAddress, ToName, ToAddress, Subject, Body)
	Select
		@FromName, @FromAddress, UserFirstName + ' ' + UserSurname, 
		UserEmail, @Subject, @Body
	FROM
		Users

CREATE PROCEDURE sp_send_report

@Dave [varchar](8000) ,
@callid [nvarchar](50),
@operator [nvarchar] (36),
@takentime [nvarchar] (10),
@takendate [nvarchar] (10),
@UPRN [nvarchar] (10),
@BPArea [nvarchar](10),
@callerName [nvarchar] (50),
@ContactTel [nvarchar] (50),
@Address [nvarchar] (200),
@FaultID [nvarchar] (10),
@faultType [nvarchar] (50),
@reportedFault [nvarchar] (1000),
@cct [nvarchar](10),
@contractor [nvarchar] (50),
@notes [nvarchar] (4000),
@abortive [nvarchar] (1),
@mailbody [varchar](8000)


AS

DECLARE dave CURSOR FOR
SELECT * FROM tbl_calls

OPEN dave

FETCH NEXT FROM dave into @callid,@operator,@takentime,@takendate,@UPRN,@BPArea,@callerName,@ContactTel,@Address, 
@FaultID,@faultType,@reportedFault,@cct,@Contractor,@notes,@abortive

WHILE @@FETCH_STATUS = 0
BEGIN

SET @dave=@dave+ ('CALL ID: '+ @callid + CHAR(10) + '----------------------' + 
CHAR(10) +'operator:'+@operator +', Taken on: '+ @takentime+' at: '+@takendate + 
CHAR(10)+ '----------------------' + CHAR(10) +'CDS Property Reference: '+@UPRN+ 
CHAR(10)+'Caller Name: '+ISNULL(@callerName,'n/a')+' Tel: '+ISNULL(@contactTel,'n/a') + 
CHAR(10)+ '----------------------' + CHAR(10) +'Address: '+ISNULL(RTRIM(@Address),'n/a')+ 
CHAR(10) + '----------------------' + CHAR(10) +'Fault Type: '+ISNULL(RTRIM(@faultType), 'n/a') + 
CHAR(10) + '----------------------' + CHAR(10) +'Reported Fault: '+ISNULL(RTRIM(@reportedFault), 'n/a')+ 
CHAR(10) + '----------------------' + CHAR(10) +'Contractor Assigned: '+ ISNULL(RTRIM(@Contractor),'n/a') + 
CHAR(10) + '----------------------' + CHAR(10) +'Notes: '+ISNULL(RTRIM(@Notes),'n/a')+ 
CHAR(10) + '----------------------'+ CHAR(10) +'Abortive Call?: '+ISNULL(@Abortive,1)+' (0=No, 1=Yes)' + 
CHAR(10) + '----------------------' + CHAR(10) + CHAR(10)+ CHAR(10)+ CHAR(10))
INSERT INTO tbl_mailtemp (reportline)
VALUES (RTRIM(@dave))
FETCH NEXT FROM dave into @callid,@operator,@takentime,@takendate,@UPRN,@BPArea,@callerName,@ContactTel,@Address, 
@FaultID,@faultType,@reportedFault,@cct,@Contractor,@notes,@abortive
SET @dave=''
END

exec sp_sendSMTPmail 'dominic.jones@liverpool.gov.uk', 'Testing', @dave, 
                    @cc='', 
                    @Importance=1,  
                    @Attachments='' 

quote:

---

You can do A LOT more damage with xp_cmdshell than you can with sp_OACreate

---

quote:

---

quote:

---

One problem with sp_OACreate is that it requires the System Administrator's fixed server role in order to run. Most DBA's say, "NO WAY!"

---

Ummm, so does xp_cmdshell (it defaults to sysadmin, but you can grant it to anyone) If the DBA objects to sp_OACreate, they most certainly will do the same over xp_cmdshell. You can do A LOT more damage with xp_cmdshell than you can with sp_OACreate; a diligent security-minded DBA will almost certainly close it off from regular users. In any case, if this is wrapped up in an SP or a SQL job then you can use either method.

---