SQL 7 - Need Stored Procedure for SMTP Email

[hierge]

I have a .dll file called xp_SMTPSendMail that can be called from within an extended stored procedure and will send a formatted email to my SMTP server Mailmax 5.5.

I have an ASP script that updates a temporary table. Previously, I had a VB program that goes to the temporary table every two minutes, formats the information, sends it to the SMTP server and destroys the data in the temporary table.

I want to replace the VB program with the xp_SMTPSendMail extended stored procedure. I am considering the following:

1. Setting up a stored procedure that will run the extended stored procedure every two minutes.
2. Setting up the extended stored procedure that calls the xp_SMTPSendMail .dll file to send the email and then destroys the records in the temporary table.

I've never done this before. I set up the trigger which runs the extended stored procedure every two minutes and it seems to work. I registered the xp_SMTPSendMail .dll file, but I can't get it to send a message to the SMTP server yet.

Are the above two steps viable in terms of accomplishing my objective? And, what steps do I need to implement in the extended stored procedure to accomplish the calling of the .dll object, insertion of the variables from the fields in the temporary table, and finally the destruction of the data in the temp table?

Thanks,

Hierge
Virginia

 

[obislavu]

The above objectives could be achieved but you have to answer to the followings:
1. What happens with the temp table if the ASP script will start to update the temp table while you are reading this table ?
2. What happens if the the processing of the e-mails (format and send) will take more than 2 minutes and a new process will start ?
3. How this process will be up when the server is rebooting ?

I will design that as a service that will start automatically when the server reboots.
The service will have a timer to start every 2 minutes a new process (let name it SendMail) for formating and sending e-mails.
If the instance of the SendMail process is running then do not start another instance , reset timer and give it time to finish.
The SendMail process will use Lock (Intent on MSSQL) on the temp table in order to process all the records and push the e-mails to a SMTP server.
When done, clear the temp table.
Also the process which updates the temp table should be designed with above considerations.
-obislavu-

 

[cheyney]

I would create this as a scheduled job in SQL. You can set this to execute on a schedule. You can also set the SQL Server agent (which runs the jobs) to start on system start, so the job will always run. You can also set it up to notify you on sucess/failure events

As for the locking, here is what I would do:

Have an extra column on your table that can be updated to indicate a lock. Before you do anything, insert a unique identifier into that column to indicate that those rows are getting worked on. Something like i've outlined below

DECLARE @JobIdentifier uniqueidentifier

SET @JobIdentifier = newid()

update tblTable
set LockedByID = @JobIdentifier
where LockedByID is null

<do your thing here...only do it on rows where LockedByID = @JobIdentifier>

delete from tblTable
where LockedByID = @JobIdentifier

cheyney

 

[hierge]

(Thank you for your helping me with this problem. I am getting much closer. I received the following response to the problem from Thorpe Software the company that makes the .dll file that is part of the extended stored procedure. If possible can someone help me evaluate this?)

**************response follows*************

The best way to do it is to have a stored procedure that runs every two minutes that does the following :

1. Creates the temporary table with the list of addresses to send 2.
Iterates through each record and calls smtpsendmail 3. Exits the stored proc which destroys the temporary table.

Here is the general idea.

if exists (select * from sysobjects where name like 'spSendMyMails')
	drop proc spSendMyMails
go

create proc spSendMyMails
as

-- First create the temporary table
-- I've made up the details

SELECT	id,
		emailaddress,
		emailheader,
		emailbody
INTO		#tempmails
FROM		tablewithdatain

declare @id int
declare @emailaddress varchar(50)
declare @emailheader varchar(100)
declare @emailbody varchar(100)

-- Infinite loop that breaks out when the temporary table is empty

while (1>0)
begin

	set rowcount 1

	SELECT	@id = id,
			@emailaddress = emailaddress,
			@emailheader = emailheader,
			@emailbody = emailbody
	FROM		#tempmails

	if @@rowcount = 0
	begin
		set rowcount 0
		break
	end

	set rowcount 0

	master..xp_SMTPSendMail80	@recipient=@emailaddress,
	
@address='your.smtp.server.com',
						@subject=@emailheader,
						@body= @emailbody,
	
@from='david@newsolutions.com'

	DELETE
	FROM	#tempmails
	WHERE	id = @id


end
go

I haven't used cursors as I have found them unreliable in the past. I've also used a temporary table rather than the newer table variable as I'm not sure what version of SQL Server you are using.

Hopefully this will give you an idea how to proceed. I'm not sure what your ASP page does but you have to be a bit careful with temporary tables as they are only visible to the connection that created them, so if your ASP page creates it and then populates it, it will only be visible on that connection. If connection pooling is enabled then the next time that the ASP page runs it might pick up a different connection. It would be better if the ASP page created the temporary table and sent the mails by calling the above procedure or alternatively a SQL Server scheduled process created the temporary table and sent the mails automatically.

Calling the temporary table ## rather than # means that all connections can see it but only one can exist as it is actually a permanent table in tempdb.
This table is then dropped automatically when the creating connection finishes and all other connections have finished with it.

Another alternative is to use the CDO objects on Windows 2000 which can also send mails. There may be no need to do it via the database. It depends on your application.

Let me know how you get on.