Job to load a web page

[okiiyama]

I am currently using Coldfusion to run a scheduled task that executes code on a webpage that checks for new records in a table, and then sends an email to all the email addresses for each new record stored in the table. Occaisonally it appears that Coldfusion will not fully execute the scheduled task and then caches the page which may or may not run at a later date, which in some cases has been a month later. When this happens, records are in my table, and also emails are being sent out more than once. This is bad and is a pain in the ass to fix. So, I would like to remove the Coldfusion scheduled task, and create a Job in SQL Server that requests this page. Is there anyway to do this? ActiveX http request maybe? I haven't worked with ActiveX so I'm not sure how I would set this up.


Thanks.

 

[Walid]

Maybe I am missing something - (Well, I am not, I am just being polite ) – but why you need a middle man for again? If you will remove the ColdFusion thing and the records are in the database and the emails are stored in the records and the email supposed to be sent when a new record is being added to the table, what is the web page has to do with all of that??

Unless I do really miss something, I would build a trigger to do the job for me having the system stored procedure send the email and that is it.



Walid Magd (MCP)

The primary challenge of every software development team is to engineer the illusion of simplicity in the face of essential complexity.
-Grady Booch

 

[mrdenny]

I agree with Walid. If all the data that you need is stored within the SQL Server, this can all be done within SQL, without accessing anything outside of SQL (except for the mail server).

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[okiiyama]

The main reason for running the processes in Coldfusion is the ease of it. Or at least, I have become am a bit more familiar and comfortable writing a process like this in Coldfusion.

The idea of using a trigger is intriguing. I shall look into that, though the last time I heard that term I was in Intro to SQL 2. Ruts kind of suck, but I may get out of this one.

 

[mrdenny]

A trigger isn't what you want to use. A stored procedure is.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[okiiyama]

Maybe now its a good time to explain my process that is currently running.

The process runs once every 45 minutes. It sends emails to recipients asking for feedback on a property they have shown. The recipients are real estate agents who have shown a property. The sender is the real estate agent who is selling the property. For each property a real estate agent is selling, they can opt-in to this process which will automatically send out feedback requests on their behalf after a house showing has occured. Also, the emails are html-emails, and the "From" needs to be the selling agent.

There are 2 types of emails.
1. Re-sends: An agent can send a request up to 3 times without a response. Each re-send will go out at least 24 hours after the previous request has been sent. Once a response has been received/submitted, they can no longer send a request to the showing agent for this particular showing.

2. First time sends: A record of sending is only created after the first email has been sent out. So, first, I get the records of each showing and showing data, where that showing has happened, for all properties that have "auto-send" turned on.

*Not every agent elects to have their properties use the auto-send, so there is a flag to mark those that have auto-send turned on. I use this flag for the "re-sends". Also, while auto-send is turned on, they can still manually send out a feedback request.

There is a lot more specifications/requirements, but the above is the meat of it.

I guess if xp_sendmail allows me to modify the "From" so that the emails seem to be coming from the listing agent, and if the email message can be in HTML format, I would use xp_sendmail. If not, then I would like to know how to have SQL Server request my processing page so that I don't need to rely on a Coldfusion scheduled task to process this page.

 

[123FakeSt]

You could also use a DTS package to do this, but I think a stored procedure would be easier if you are not proficient in VBScript. If your data is already in SQL server, there's no reason to involve other systems.

DTS Package runs every 45 minutes
Task 1 ... Execute SQL script
Calls stored procedure or contains SQL to define who gets an email as a field or seperate table

Task 2 ... executes ActiveX script:

'Pseudocode Begins
'1 Dim necessary ADODB(database) and CDO(email) variables
'2 Create ADODB connection to table with records to check
'3 Create SQL string to get new records
'4 Open recordset of new records
'5 Move through recordset calling CDO email function
'6 Mark record as being sent 
'7 Upon loop completion, destroy objects

This will make the processes very scalable and maintainable ... unless you're planning on upgrading to SQL Server 2005, when DTS support is moved out.

The early bird gets the worm, but the second mouse gets the cheese.

 

[123FakeSt]

Or just do it all from an ActiveX script job.

The early bird gets the worm, but the second mouse gets the cheese.

 

[mrdenny]

I would recommend against ActiveX as it's much harded to debug that T/SQL.

If you download and install xp_smtpsendmail from devsql.com you can modify the from address (xp_smtpsendmail is also much faster than xp_sendmail as well).

All this could be easily done via a stored proc (or ActiveX).

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com