August 2019 – Page 6 – Reporting Services

August 3, 2019August 7, 2019

SSRS advanced configuration

In this post we take a quick overview on Reporting Services configuration.

There are few configuration sections of Reporting Services based on what part of functionality you would like to control. Here are briefly described fundamental configuration settings with short description.

Reporting Services Configuration manager
- Configuration tool for basic settings of Report Server (Report Service URL, Report Manger URL, Reporting Services state, etc…)
Connect to Reporting Services via Management Studio
- Setting of Execution log expiration, Reporting Services Report Manager text etc.

Reporting Services Installation directory

Report manager directory C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportManager
- rsmgrpolicy.config
  - Report Manager policy configuration file.

Report Server installation directory C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer

rsreportserver.config – Stores configuration settings for feature areas of the Report Server service

orting Services URL, Report Manager URL, Extension Configuration (e.a. csv extension delimiter), new Reporting Services Extensions adding, etc.

<Extension Name="Report Server Email" Type="Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider,ReportingServicesEmailDeliveryProvider"> <MaxRetries>3</MaxRetries> <SecondsBeforeRetry>900</SecondsBeforeRetry> <Configuration> 
<RSEmailDPConfiguration> <SMTPServer>smtpserver.cz</SMTPServer> <SMTPServerPort> </SMTPServerPort> <SMTPAccountName> </SMTPAccountName> <SMTPConnectionTimeout> </SMTPConnectionTimeout> <SMTPServerPickupDirectory> </SMTPServerPickupDirectory> <SMTPUseSSL> </SMTPUseSSL> <SendUsing>2</SendUsing> <SMTPAuthenticate> </SMTPAuthenticate> <From>DWHReports@email.cz</From> <EmbeddedRenderFormats> <RenderingExtension>MHTML</RenderingExtension> </EmbeddedRenderFormats> <PrivilegedUserRenderFormats> </PrivilegedUserRenderFormats> <ExcludedRenderFormats> <RenderingExtension>HTMLOWC</RenderingExtension> <RenderingExtension>NULL</RenderingExtension> <RenderingExtension>RGDI</RenderingExtension> </ExcludedRenderFormats> <SendEmailToUserAlias>True</SendEmailToUserAlias> <DefaultHostName> </DefaultHostName> <PermittedHosts> </PermittedHosts> </RSEmailDPConfiguration> </Configuration> </Extension>

web.config – ASP.NET configuration
- here you can for example change max limit for file upload for example
```
<httpRuntime  maxRequestLength="100000" executionTimeout="9000" requestValidationMode="2.0" />
```
rssrvpolicy.config
- Stores the code access security policies for the server extensions.
  - To set security to extension library

<CodeGroup class="UnionCodeGroup" version="1" Name="SSRSCustomReportExtension" Description="" PermissionSetName="FullTrust"><IMembershipCondition class="UrlMembershipCondition" version="1" Url="C:\Program Files\Microsoft SQL Server\MSRS10.MSSQL2008\Reporting Services\ReportServer\bin\SSRSCustomReportExtension.dll"/>

You can find more info on MSDN web site https://bit.ly/2ZkDZ2l.

August 3, 2019December 31, 2019

Extended events advanced

I would like to describe useful queries for extended events in more detail in this post Extended events get description. I will follow my previous post where I put few basic queries to get some extended events info.

Extended events are triggers occurring in SQL system. Each extended event has its own set of properties -data you get from result. Extended events are collected in packages, events can be combined from different packages. List of packages defined in SQL server instance

SELECT * FROM sys.dm_xe_packages

Beside of that each event can fire other actions synchronously int the same thread within event was invoked. Action extend extended events with own data in response.

For example, if you wold like to get sql text of a query it is defined by an action.

SELECT *
  FROM sys.dm_xe_objects o 
 WHERE o.object_type='action'

To filter event data, you can define Predicates. There is a list of objects you can use as predicate source.

 SELECT *
 FROM sys.dm_xe_objects o 
WHERE o.object_type='pred_source'

List of operators

SELECT *
  FROM sys.dm_xe_objects o 
 WHERE o.object_type='pred_compare'

To start collecting data you must define session object which is set of actions, event data, predicates and target object.

Target object collects output event data by synchronous or asynchronous way. Data can be collected for example to buffer, file, Windows Events, etc.

SELECT * FROM sys.dm_xe_objects o WHERE o.object_type='target'

User – friendly description of internal key words which could be returned from events data

SELECT *
  FROM sys.dm_xe_objects o 
 WHERE o.object_type='map'

Relationship between events implemented in created sessions and actions

SELECT * FROM sys.dm_xe_session_event_actions

Data types you can expect from data collection.

SELECT *
  FROM sys.dm_xe_objects o 
 WHERE o.object_type='type'

Next time I would like to share some solutions implemented using extended events. Stay tuned.

August 3, 2019August 7, 2019

SSRS execution log expiration settings

Short description to change execution log expiration settings. SSRS execution log is good tool to monitor reports activity. When using it you can find that the data are not stored forever. The default time period for which the data are stored in Reporting Services database are 60 days. If you would like to change this setting, follow these steps.

Open SQL Management Studio and connect to Reporting Services by selecting Server type to Reporting Services and select your server name.

When connected to the server go to Object Inspector, obviously place on left side in Management Studio, go to Report Server name at the top and right click to get Popup menu.

Go to Properties to get Server Properties window. Here you can see set of Reporting Services settings you can change from Management Studio. If you look at the first section, General, thee is URL address for report server (it is available in Reporting Services Configuration manager too). The name of instance is visible when you go to Reporting Services Manager at the top of the web interface. You can change it here.

But we would like to change Execution log settings so go to Logging section on the left menu.

Here we can set the number of days the data will be stored in execution log. Or if to disable Report Execution logging at all.