Mail to be sent based on data

Question

Post reply

Mail to be sent based on data

Anurag Nayar

Mr or Mrs. 500

Points: 515
More actions
January 12, 2006 at 10:52 am

#111294

Hi ,
I have a requirement for a project where they want to send an e-mail to some of their staff . Now this email will have some excel report attached which needs to extracted from database table based on certain criteria.
To add to this they want that the mail be sent to the person(Branch head) for his branch only and only when it meets the criteria like SLA missed by one of the resource in the Branch which is calculated by (Followupdate-registrationDate>2) and the data in excel should only be for his particular branch.
Can someone advise the best way of doing this. I was thinking of looking some articles on DTS but couldnt find one.
Thanks..
Anurag

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

Go to microsoft and search on Notification Services.

They made this "add on" similar to Reporting services that will do exactly what you are talking about.

mark Jones-135146 SSC Rookie Points: 46 More actions · Answer 2

I came accross this problem a year ago, but we wanted mail 15.000 reports to customers using varying report types (.txt,csv,.xls).

You need to first assess what do you need in your excel file, if you dont need graphs or other such objects (like I did) you can perform this task simply with stored procedures - (DTS is all well and good, but I assure you you wont get the performance or scaleability sp's give you).

Im not going to sit here and write out your code, but will give some pointers. If you are just looking at simple values in an excel spreadsheet use csv format. Now what I did and it does work very well, is Select your report as Text into a temporty table with a vachar(8000) column.

Something like this:-

CREATE TABLE #ReportOutput (ReportID uniqueidentifer, ReportText varchar(8000))

Every report has a UniqueIdentifier and the ReportText column holds the report text.

For every line of the report, add a new row, so each line of the report can be up to 8000 chars long. If you need longer (my god) use text or image. When inserting your report into the temp table format the report as require, i.e. if its CSV then add a ',' to deliminate your columns.

example

SELECT NEW_ID(), ColumnA + ',' + ColumnB + ','

INTO #ReportOutput

FROM SorceTable

When it comes to attaching and sending the file, BCP the contents of the report (by reportid) into a filename (with the correct extension, .csv in this case) and then use xp_sendmail to create a mail, attach the file and send it.

You can then update control table to keep progress of what was sent and to where when etc, as you see fit.

V.Kadal Amutham Old Hand Points: 343 More actions · Answer 3

How to configure SQL serve to send mail?

IS IIS to be installed? If the SQL server with internet connection will be sufficient to send mail to anybody?

V.Kadal Amutham

Anurag Nayar Mr or Mrs. 500 Points: 515 More actions · Answer 4

OK now this tells me how to push the report into table and use SP to send a mail. My problem is that I need to send selecttive data to relevant peole. For example The Branch manager of Branch A001 should get the SLA report for his Branch only.

Secondly this needs to be scheduled for daily. Can I schedule an SP.

thanks again for all help.

philcart SSC-Forever Points: 47794 More actions · Answer 5

How about using Reporting Services?

Using Web Service calls you can render any given report in the format you require and then you can email out the result.

--------------------
Colt 45 - the original point and click interface