Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Email Report to list of people that are defined by the report ??? Expand / Collapse
Author
Message
Posted Wednesday, May 7, 2014 7:54 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 9:54 AM
Points: 300, Visits: 499
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
Post #1568483
Posted Wednesday, May 7, 2014 4:19 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 9:09 PM
Points: 886, Visits: 5,671
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.
Post #1568728
Posted Thursday, May 8, 2014 7:40 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 9:54 AM
Points: 300, Visits: 499
Thanks for getting back

we use 2008R2

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

Thanks
Again
Post #1568889
Posted Thursday, May 8, 2014 8:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 13, 2014 8:57 AM
Points: 3, Visits: 138
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.
Post #1568904
Posted Thursday, May 8, 2014 10:57 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 9:09 PM
Points: 886, Visits: 5,671
This is the article I was thought might help:
http://www.sqlservercentral.com/scripts/Miscellaneous/31733/
Post #1569027
Posted Thursday, May 8, 2014 3:16 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 9:54 AM
Points: 300, Visits: 499
Thank you !!

I'll be back with questions.... :)
Post #1569094
Posted Friday, May 9, 2014 9:56 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 9:54 AM
Points: 300, Visits: 499

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


Post #1569361
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse