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
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 7:34 AM
Points: 299, Visits: 498
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
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 11:12 PM
Points: 774, Visits: 4,978
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
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 7:34 AM
Points: 299, Visits: 498
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: Tuesday, September 2, 2014 9:21 AM
Points: 3, Visits: 136
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
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 11:12 PM
Points: 774, Visits: 4,978
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
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 7:34 AM
Points: 299, Visits: 498
Thank you !!

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

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 7:34 AM
Points: 299, Visits: 498

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