October 11, 2012 at 6:15 pm
Hi all, I am using stored procedures and potentially temp tables for the first time. What I am trying to accomplish is to check the database for certain data pattern associated with a customer, then send an email to that customer (essentially the customer uploads documents and I need to email them when all documents are uploaded). I cant use triggers because it is a proprietary application that tends to break when triggers are attached.
I am really looking for direction on my basic logic to ensure I am walking in the right direction.
First I run a select to identify customers who uploaded all documents and return the customerid and their email.
I also have another table that I use to store who I have notified as follows:
create table dbo.rfpdocumentsalerts
(agencynumber int not null unique,
uploadcomplete bit,
completenotificationsent datetime,
approvalcomplete bit,
approvednotificationsent datetime,
primary key(agencynumber))
I would like this 'job' to run every 15 minutes, check for users who uploaded all their documents, write to the alerts table so I dont email them every time the job runs, then send the actual email. I have tried nesting the exec sp_send_dbmail into my select query, but was trying to use a case statement to determine whether or not to send the email based on the alerts table above.
I plan to send the email with the following basic code (using variables).
select case when uploadcomplete=1 then (
exec sp_send_dbmail
@recipients ='customer@email.com',
@from_address = 'me@email.com',
@subject ='Document Upload Complete!',
@body = 'You have successfully uploaded your documents!')
This did not work.
I dont expect many people to simultaneously uplaod all their documents, so I am okay using a select top(1) to iterate through the users who completed their uploads with the understanding that I would send a max of 1 email every 15 minutes (or whatever the job schedule is).
Again, I have been asked to do this and am in new territory, so I appreciate any guidance you can give.
October 11, 2012 at 6:30 pm
hkflight (10/11/2012)
Hi all, I am using stored procedures and potentially temp tables for the first time. What I am trying to accomplish is to check the database for certain data pattern associated with a customer, then send an email to that customer (essentially the customer uploads documents and I need to email them when all documents are uploaded). I cant use triggers because it is a proprietary application that tends to break when triggers are attached.I am really looking for direction on my basic logic to ensure I am walking in the right direction.
First I run a select to identify customers who uploaded all documents and return the customerid and their email.
I also have another table that I use to store who I have notified as follows:
create table dbo.rfpdocumentsalerts
(agencynumber int not null unique,
uploadcomplete bit,
completenotificationsent datetime,
approvalcomplete bit,
approvednotificationsent datetime,
primary key(agencynumber))
I would like this 'job' to run every 15 minutes, check for users who uploaded all their documents, write to the alerts table so I dont email them every time the job runs, then send the actual email. I have tried nesting the exec sp_send_dbmail into my select query, but was trying to use a case statement to determine whether or not to send the email based on the alerts table above.
I plan to send the email with the following basic code (using variables).
select case when uploadcomplete=1 then (
exec sp_send_dbmail
@recipients ='customer@email.com',
@from_address = 'me@email.com',
@subject ='Document Upload Complete!',
@body = 'You have successfully uploaded your documents!')
This did not work.
I dont expect many people to simultaneously uplaod all their documents, so I am okay using a select top(1) to iterate through the users who completed their uploads with the understanding that I would send a max of 1 email every 15 minutes (or whatever the job schedule is).
Again, I have been asked to do this and am in new territory, so I appreciate any guidance you can give.
As odd as this may sound given my signature line, I would suggest you create a CURSOR based on:
SELECT b.email
FROM dbo.rfpdocumentsalerts a
INNER JOIN dbo.YourAgencyTable b ON a.agencynumber = b.agencynumber
WHERE uploadcomplete=1
Then loop WHILE @@FETCH_STATUS <> 0 through all the records generated by the CURSOR until you've sent each agency their email notification.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply