After reading a tweet by Ray Martinez, I decided to share the scripts for Reporting Services that I often use.

One of the scripts I used a lot, is a script that shows the Reports that were successfully executed in 2012. For these Reports you will see the last execution time, rows and bytes returned, and how long it took to gather and show the results:

USE ReportServer
SELECT
CL.Name AS ReportName,
CL.Description AS ReportDescription,
CL.Path AS ReportPath,
CL.CreationDate AS ReportCreationDate,
SUM(1) AS TotalNumberOfTimesExecuted,
MAX(EL.TimeStart) AS LastTimeExecuted,
AVG(EL.[RowCount]) AS AVG_NumberOfRows,
AVG(EL.TimeDataRetrieval) AS AVG_DataRetrievalTime,
AVG(EL.TimeProcessing) AS AVG_TimeProcessing,
AVG(EL.TimeRendering) AS AVG_TimeRendering
FROM ExecutionLog EL
JOIN Catalog CL
ON CL.ItemID = EL.ReportID
WHERE 1 = 1
AND CL.Name IS NOT NULL
AND EL.Status ='rsSuccess'
GROUP BY
CL.Name,
CL.Path,
CL.CreationDate,
CL.Description
HAVING YEAR(MAX(EL.TimeStart)) = 2012
ORDER BY COUNT(EL.ReportID) DESC

The second script I want to share with you, shows the first 1000 successfully executed Reports. The data that is returned includes Report format, parameters used to query the data, information about the returned resultset and time needed to return and render the data and Report, etc. I uses a top 1000 because our Report server returned a lot of rows, where I only needed a few for my analysis.

USE ReportServer
SELECT TOP 1000
EL.InstanceName AS SQLInstanceName,
EL.UserName AS ExecuterUserName,
EL.Format AS ReportFormat,
EL.Parameters AS ReportParameters,
EL.TimeStart AS TimeStarted,
EL.TimeEnd AS TimeEnded,
EL.TimeDataRetrieval AS TimeDataRetrieval,
EL.TimeProcessing AS TimeProcessing,
EL.TimeRendering AS TimeRendering,
EL2.Source AS Source,
EL.ByteCount AS ReportInBytes,
EL.[RowCount] AS ReportRows,
CL.Name AS ReportName,
CL.Path AS ReportPath,
CL.Hidden AS ReportHidden,
CL.CreationDate AS CreationDate,
CL.ModifiedDate AS ModifiedDate,
EL2.Format AS RenderingFormat,
EL2.ReportAction AS ReportAction,
EL2.Status AS ExectionResult,
DS.Name AS DataSourceName,
DS.Extension AS DataSourceExtension
FROM ExecutionLog EL
JOIN Catalog CL
ON CL.ItemID = EL.ReportID
LEFT JOIN ExecutionLog2 EL2
ON EL2.ReportPath = CL.Path
JOIN DataSource DS
ON DS.ItemID = CL.ItemID
WHERE 1 = 1
AND EL.Status = 'rsSuccess'
ORDER BY EL.TimeStart DESC

The next script returns an overview of the folder and Reports on your Report server. For every object on your server, you can see the creation- and modify date. The joined Executionlog table is used to get the number of times the Report was executed, by which user, and how much data was returned in which time:

USE ReportServer
SELECT
CASE CL.Type
WHEN 1 THEN 'Folder'
WHEN 2 THEN 'Report'
WHEN 3 THEN 'Resource'
WHEN 4 THEN 'Linked Report'
WHEN 5 THEN 'Data Source'
END AS ObjectType,
CP.Name AS ParentName,
CL.Name AS Name,
CL.Path AS Path,
CU.UserName AS CreatedBy,
CL.CreationDate AS CreationDate,
UM.UserName AS ModifiedBy,
CL.ModifiedDate AS ModifiedDate,
CE.CountStart AS TotalExecutions,
EL.InstanceName AS LastExecutedInstanceName,
EL.UserName AS LastExecuter,
EL.Format AS LastFormat,
EL.TimeStart AS LastTimeStarted,
EL.TimeEnd AS LastTimeEnded,
EL.TimeDataRetrieval AS LastTimeDataRetrieval,
EL.TimeProcessing AS LastTimeProcessing,
EL.TimeRendering AS LastTimeRendering,
EL.Status AS LastResult,
EL.ByteCount AS LastByteCount,
EL.[RowCount] AS LastRowCount,
SO.UserName AS SubscriptionOwner,
SU.UserName AS SubscriptionModifiedBy,
SS.ModifiedDate AS SubscriptionModifiedDate,
SS.Description AS SubscriptionDescription,
SS.LastStatus AS SubscriptionLastResult,
SS.LastRunTime AS SubscriptionLastRunTime
FROM Catalog CL
JOIN Catalog CP
ON CP.ItemID = CL.ParentID
JOIN Users CU
ON CU.UserID = CL.CreatedByID
JOIN Users UM
ON UM.UserID = CL.ModifiedByID
LEFT JOIN ( SELECT
ReportID,
MAX(TimeStart) LastTimeStart
FROM ExecutionLog
GROUP BY ReportID) LE
ON LE.ReportID = CL.ItemID
LEFT JOIN ( SELECT
ReportID,
COUNT(TimeStart) CountStart
FROM ExecutionLog
GROUP BY ReportID) CE
ON CE.ReportID = CL.ItemID
LEFT JOIN ExecutionLog EL
ON EL.ReportID = LE.ReportID
AND EL.TimeStart = LE.LastTimeStart
LEFT JOIN Subscriptions SS
ON SS.Report_OID = CL.ItemID
LEFT JOIN Users SO
ON SO.UserID = SS.OwnerID
LEFT JOIN Users SU
ON SU.UserID = SS.ModifiedByID
WHERE 1 = 1
ORDER BY CP.Name, CL.Name ASC

The last query is a short one. This returns the Reports that don’t inherit permissions, that are set in the site settings menu.

USE ReportServer
SELECT
Path,
Name
FROM Catalog
WHERE PolicyRoot = 1

Hopefully there will be more where this came from. So follow my blog, or contact me by email or Twitter!

For a project I’m working on right now, we (the team I’m currently a part of) decided to research the use of GIS data. The GIS data is available for free at DIVA-GIS. If you want to download the GIS data, choose the country and in the Subject drop-down, choose “Administrative areas”.

To import the data, I’ve used an easy to use .NET Tool: Shape2SQL. This tool is created by Morten Nielsen (Blog | @dotMorten), and allows the user to import Shapefiles (.SHP) into SQL Server without problems. If you want to download this tool-set, I advise you to download the “SqlSpatialTools”, which also contains “SQLSpatial.exe”, which allows you to query and visualize the data.

After downloading the GIS data and tools, run the “Sharp2Sql.exe”. You will see the following screen pop up (at first run only):

Fill in the server and database information. In my case, I imported the data on a local SQL Server:

After that, you will see a start screen like this:

Now you need to select a SHP file. If you press the button, the following window shows:

As you can see, I picked the GIS data of The Netherlands as an example. After selecting a source file, you need to chance the settings of the import:

I also changed the “Geometry Name” on the right from “geom” to “geog”, just to remind myself that the content of the column is Geography- and not Geometry-data.

Once you decided about the options and naming conventions, press “Upload to Database”, and wait for the file to be processed:

Once the processing is completed, you can start using the GIS data. You can do this straight from SQL Server, but you could also use the “SqlSpatial.exe” that you downloaded as part of the “SqlSpatialTools”. If you choose to use this tool, it would look something like this:

You can run the same query in SQL Server Management Studio (SSMS), and you will get an extra tab in the resultset:

There’s only one more thing to remember: In SQL Server 2008 and 2008 R2 you can only select 1 hemisphere at a time. SQL Server 2012 has a new version of the Geography assembly, and supports querying multiple hemispheres at the same time. For more information about this, read the MSDN article about Spatial Data Types.

In SQL Server you will encounter a lot of cases, in which an operator combines two expressions of different data types. The rules that specify which value is converted to another data type, can be found on MSDN. But the precedence of data types are different for the different versions of SQL Server. Therefore I created the schema below, so you can compare the different versions:

SQL Server 2005

SQL Server 2008 / 2008 R2

SQL Server 2012

1

user-defined data types (highest)

user-defined data types (highest)

user-defined data types (highest)

2

sql_variant

sql_variant

sql_variant

3

xml

xml

xml

4

datetime

datetimeoffset

datetimeoffset

5

smalldatetime

datetime2

datetime2

6

float

datetime

datetime

7

real

smalldatetime

smalldatetime

8

decimal

date

date

9

money

time

time

10

smallmoney

float

float

11

bigint

real

real

12

int

decimal

decimal

13

smallint

money

money

14

tinyint

smallmoney

smallmoney

15

bit

bigint

bigint

16

ntext

int

int

17

text

smallint

smallint

18

image

tinyint

tinyint

19

timestamp

bit

bit

20

uniqueidentifier

ntext

ntext

21

nvarchar (including nvarchar(max))

text

text

22

nchar

image

image

23

varchar (including varchar(max))

timestamp

timestamp

24

char

uniqueidentifier

uniqueidentifier

25

varbinary (including varbinary(max))

nvarchar (including nvarchar(max))

nvarchar (including nvarchar(max))

26

binary (lowest)

nchar

nchar

27

varchar (including varchar(max))

varchar (including varchar(max))

28

char

char

29

varbinary (including varbinary(max))

varbinary (including varbinary(max))

30

binary (lowest)

binary (lowest)

The same counts for Operators. There are differences in the precedence between SQL Server versions. I took the data from different versions of SQL Server, and created the schema below: