September 14, 2014 at 11:31 am
Hi
I have a view which will fetch data from database and it contains mail ids also.
When ever a new event is fetched, i need send the data to the mail ids on the same.
Please help on this to achieve the same.
September 14, 2014 at 2:50 pm
When ever a new event is fetched, i need send the data to the mail ids on the same.
You can't add a trigger to a SELECT statement. How is the selection happening? If this were a stored procedure, you could process the rows, but you can't add an "event" to a view.
September 15, 2014 at 2:59 am
Hi
Can we create a SP to achieve the requirement?
Below is the query which i am using to fetch data.
select * from euromaster..vw_vischeckinout
And the result is below.
visitIdvisitorIdfirstNamelastNamesiteObjIdlocationcompanyNamepurposeauthorisedsiteNamecardHolderIdtypeIdempCodeempNameemailreportManagerEmailadditionalPersonsadditionalPersonDetailschkInOutIdcheckInPeriodcheckOutPeriodstartVisitDateendVisitDatevisitTypecreatedlastModifiedoperatorNamechkinchkoutvisitorStatus
11TestSubu2testtest companyNULLRoot Site113312345Athi Athitest@test.comtest1@test.com0NULLNULLNULL9/12/2014 12:409/12/2014 23:59NULL9/12/2014 7:119/12/2014 7:11admin1NULLNULL1
22SubuTest22testtest companyNULLRoot Site113312345Athi Athitest@test.comtest1@test.com0NULLNULLNULL9/12/2014 12:459/12/2014 23:59NULL9/12/2014 7:169/12/2014 7:16admin1NULLNULL1
33TestSubu32testtest companyNULLRoot Site113312345Athi Athitest@test.comtest1@test.com019/12/2014 12:47NULL9/12/2014 12:469/12/2014 23:59NULL9/12/2014 7:169/12/2014 7:17admin147:05.0NULL1
44SubuTest42testtest companyNULLRoot Site113312345Athi Athitest@test.comtest1@test.com029/12/2014 13:18NULL9/12/2014 13:179/12/2014 23:59NULL9/12/2014 7:489/12/2014 7:48admin118:41.0NULL1
55TestSubu52testtest companyNULLRoot Site113312345Athi Athitest@test.comtest1@test.com039/12/2014 15:49NULL9/12/2014 15:499/12/2014 23:59NULL9/12/2014 10:199/12/2014 10:19admin149:31.0NULL1
66TestSubu62testtest companyNULLRoot Site113312345Athi Athitest@test.comtest1@test.com049/12/2014 15:58NULL9/12/2014 15:579/12/2014 23:59NULL9/12/2014 10:289/12/2014 10:28admin158:40.0NULL1
77TestSubu72testtest companyNULLRoot Site113312345Athi Athitest@test.comtest1@test.com059/12/2014 16:06NULL9/12/2014 16:069/12/2014 23:59NULL9/12/2014 10:369/12/2014 10:36admin106:46.0NULL1
Can you please help me on this ?
September 15, 2014 at 12:17 pm
What do you mean by an "event"?
How do you determine whether a row returned is a new event?
I'd suggest a stored procedure that you put in a SQL Agent job scheduled to run every N minutes (based on how near real time you have to send the email) that loops over results returned and calls sp_send_dbmail. Pseudocode:
Declare [variables to hold data from cursor];
Declare mailCursor CURSOR FAST_FORWARD AS
Select [columns] from [view] Where [criteria that shows new event];
OPEN mailCursor;
FETCH NEXT FROM mailCursor INTO [variables to hold data from cursor];
WHILE @@FETCH_STATUS = 0
BEGIN;
Declare @Subject Varchar(1000) = [what ever the subject will be];
Declare @Body varchar(8000) = [what ever the body will be]
EXEC msdb.dbo.sp_send_dbmail [parameters = variables];
Mark row as processed so you don't process the same row again next run.
END;
Another option is to use a Service Broker Queue where there is an activation procedure that sends the email, but I've never done that so I can't give you details. I know you'd have to write some code to get the event to the queue and the best way might be a trigger on the source table.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply