Email Report to list of people that are defined by the report ???

Question

Email Report to list of people that are defined by the report ???

jbalbo

SSCertifiable

Points: 7580
More actions
May 7, 2014 at 7:54 am

#285388

Hi
is there a way to email a report to the people who are on the report
For example I run a repot for caseworker who haven't seen a client in 21 days
I want to email the caseworkers that come out on the report. Lets say it runs weekly and the list changes.
Do I need Enterprise version
Thanks

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

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

pietlinden SSC Guru Points: 63425 More actions · Answer 1

What you're describing is called a data-driven subscription. What version of SQL Server are you using? Enterprise supports them natively. If not, I am pretty sure that Jason Selburg posted an article explaining how to get the non-enterprise versions to accomplish the same thing.

jbalbo SSCertifiable Points: 7580 More actions · Answer 2

Thanks for getting back

we use 2008R2

Where/How would I find the article. I know we don't have enterprise version

Thanks

Again

geoff.daly SSC Enthusiast Points: 183 More actions · Answer 3

Unfortunately this is one of the features of The Enterprise Edition which by the sounds of it you don't have. I seem to rememeber there are some third party tools which can give you this functionality.

There are a couple of ways of doing this. Idf you only have a handful of people to mail to then setup individual subscriptions set to run to each person. Set them to run once at 02:00 in the morning. This creates your subscription.

You can query subscription to find which SQL Agent job runs your report:

SELECT rs.ScheduleID as JOBID, rs.ReportID, c.Name, dbo.Subscriptions.ExtensionSettings, dbo.Subscriptions.Description,

dbo.Subscriptions.LastStatus, dbo.Subscriptions.LastRunTime, dbo.Subscriptions.EventType, dbo.Subscriptions.Parameters, dbo.Subscriptions.DeliveryExtension,

dbo.Subscriptions.Version, dbo.Schedule.EventData, dbo.Subscriptions.SubscriptionID, c.Path

FROM dbo.ReportSchedule AS rs WITH (nolock) INNER JOIN

dbo.Catalog AS c WITH (nolock) ON c.ItemID = rs.ReportID INNER JOIN

dbo.Subscriptions WITH (nolock) ON rs.SubscriptionID = dbo.Subscriptions.SubscriptionID AND c.ItemID = dbo.Subscriptions.Report_OID INNER JOIN

dbo.Schedule WITH (nolock) ON rs.ScheduleID = dbo.Schedule.ScheduleID

Where c.Name = 'Report Name'

ORDER BY rs.ScheduleID

So I would then create a Recipient Table with a field you can look up your recipient on and the ReportID, so you can use the table for multiple reports, and the EventData value from the above query.

Any of these report can now be sent through reporting services by executing the following:

insert into reportserver.dbo.[Event]

([EventID], [EventType], [EventData], [TimeEntered], [ProcessStart], [BatchID])

select NewID(), 'TimedSubscription', @EventData, GETUTCDATE(), NULL, NULL

It's then just a matter of querying your resultset to see which you need to run.

There are some other more complex and fiddly ways of doing it but this should get you there.

Geoff.

pietlinden SSC Guru Points: 63425 More actions · Answer 4

This is the article I was thought might help:

http://www.sqlservercentral.com/scripts/Miscellaneous/31733/

jbalbo SSCertifiable Points: 7580 More actions · Answer 5

jbalbo

SSCertifiable

Points: 7580

May 8, 2014 at 3:16 pm

#1712021

Thank you !!

I'll be back with questions.... 🙂

jbalbo SSCertifiable Points: 7580 More actions · Answer 6

Thanks for the info...

Ok I sort of get it....

The SP sets up the email addresses...

But how do I get my data there

I have a SP that writes out Clients which belong to providers , etc...

clientname providername provideremail

example

mickey goofey goofey@Dw.com

donald snowwhite snowwhite@Dw.com

so I would want to send goofey mickey's name

and snowwhite donald's name

Thanks

Joe

if someone know a same i can view that would be great