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.
visitId visitorId firstName lastName siteObjId location companyName purpose authorised siteName cardHolderId typeId empCode empName email reportManagerEmail additionalPersons additionalPersonDetails chkInOutId checkInPeriod checkOutPeriod startVisitDate endVisitDate visitType created lastModified operatorName chkin chkout visitorStatus
1 1 Test Subu 2 test test company NULL Root Site 1 133 12345 Athi Athi test@test.com test1@test.com 0 NULL NULL NULL 9/12/2014 12:40 9/12/2014 23:59 NULL 9/12/2014 7:11 9/12/2014 7:11 admin1 NULL NULL 1
2 2 Subu Test2 2 test test company NULL Root Site 1 133 12345 Athi Athi test@test.com test1@test.com 0 NULL NULL NULL 9/12/2014 12:45 9/12/2014 23:59 NULL 9/12/2014 7:16 9/12/2014 7:16 admin1 NULL NULL 1
3 3 Test Subu3 2 test test company NULL Root Site 1 133 12345 Athi Athi test@test.com test1@test.com 0 1 9/12/2014 12:47 NULL 9/12/2014 12:46 9/12/2014 23:59 NULL 9/12/2014 7:16 9/12/2014 7:17 admin1 47:05.0 NULL 1
4 4 Subu Test4 2 test test company NULL Root Site 1 133 12345 Athi Athi test@test.com test1@test.com 0 2 9/12/2014 13:18 NULL 9/12/2014 13:17 9/12/2014 23:59 NULL 9/12/2014 7:48 9/12/2014 7:48 admin1 18:41.0 NULL 1
5 5 Test Subu5 2 test test company NULL Root Site 1 133 12345 Athi Athi test@test.com test1@test.com 0 3 9/12/2014 15:49 NULL 9/12/2014 15:49 9/12/2014 23:59 NULL 9/12/2014 10:19 9/12/2014 10:19 admin1 49:31.0 NULL 1
6 6 Test Subu6 2 test test company NULL Root Site 1 133 12345 Athi Athi test@test.com test1@test.com 0 4 9/12/2014 15:58 NULL 9/12/2014 15:57 9/12/2014 23:59 NULL 9/12/2014 10:28 9/12/2014 10:28 admin1 58:40.0 NULL 1
7 7 Test Subu7 2 test test company NULL Root Site 1 133 12345 Athi Athi test@test.com test1@test.com 0 5 9/12/2014 16:06 NULL 9/12/2014 16:06 9/12/2014 23:59 NULL 9/12/2014 10:36 9/12/2014 10:36 admin1 06:46.0 NULL 1
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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply