SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
jbalbo
jbalbo
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1371 Visits: 688
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
pietlinden
pietlinden
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13198 Visits: 14085
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
jbalbo
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1371 Visits: 688
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
geoff.daly
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 145
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
pietlinden
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13198 Visits: 14085
This is the article I was thought might help:
http://www.sqlservercentral.com/scripts/Miscellaneous/31733/
jbalbo
jbalbo
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1371 Visits: 688
Thank you !!

I'll be back with questions.... Smile
jbalbo
jbalbo
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1371 Visits: 688
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search