Step 3.Next, you create the trigger on the Subscriptions table
to translate the user login to a valid email address. Listing
2 shows the FIX_EMAIL_TR trigger code, which fires when a user adds a new
subscription to Report Server. Reporting Services offers two types of subscriptions,
so the trigger'sWHERE clause checks and updates only the Send email to type,
not the File system publication subscriptions type. After verifying that
the added subscription is the email type, the trigger stores the login in a
variable and selects the correct email address from the ADSI_USERS table. The
trigger then updates the new subscription with the correct email address.
Step 4. You're now ready to create the stored procedure that
deletes subscriptions for users no longer in AD.The nightly SQL Server Agent
job that you create in the next step will execute this stored procedure after
running the stored procedure that updates the ADSI_USERS table. Listing
3 shows the code for the DELETE _ SUBSCRIPTIONS_SP stored procedure.The
procedure parses all email-type subscriptions and compares them to the list
of email addresses from AD. If the email address for the subscription doesn't
exist in the ADSI_USERS table, the procedure removes the subscription.The code
also ensures that the transactions won't commit if errors occur; you can tailor
the text in the RAISERROR statement to your needs.
Step 5. The final step is to schedule a SQL Server Agent job
to execute the stored procedures that load the ADSI_USERS table and delete subscriptions.
Web Listing 1 (http://www.sqlmag.com,
Instant-Doc ID 93771), shows the Manage_RS_ Subscriptions job definition. The
code first adds a job category called Batch if that category doesn't already
exist, then determines whether a job with the same name exists, and if so, drops
the existing one. The job steps specify the job, category, and type as well
as the notification method. If an operator hasn't been defined in SQL Server
Agent or you don't want to net send to an operator, eliminate this notification
code. Last, the definition sets the job schedule, which you can modify to fit
with nightly batch processing and database maintenance.
Note that you need to replace the domain and account names as well as the log
file path and notification name in the code example.You might also want to adjust
the execution time based on your nightly batch cycle.You can run the code manually,
or you can use Enterprise Manager to add a new job that performs these tasks.
Remember that SQL Server Agent must be enabled and running to add and schedule
jobs.
Reducing the Load
Scheduling the SQL Server Agent job to run during off-peak hours alleviates
any additional load on AD. And besides increasing in-house and mobile user access
to reports, using subscriptions can reduce the load on SQL Server by enabling
scheduled delivery of reports during off-peak database-access times.
The only caveat for user-managed subscriptions is that users who already have
email-type subscriptions will get an error message when they try to update the
subscription because their login won't match their email address. Users who
want to modify an email-type subscription will need to delete the old subscription,
then recreate the subscription, incorporating any new schedule or file-format
changes.This task might be a one-time event, but you still need to train users
about best practices for creating subscriptions so that they can successfully
manage report delivery. Knowing what report delivery formats are available and
understanding when to schedule subscriptions empowers users to better help themselves.
End of Article
Prev. page
1
[2]
next page -->
