MS SQL SSRS – HTTP Error 500

April 15, 2016

Recently we got an MS SQL SSRS – HTTP Error 500.


The Reporting services was working just fine the day before?

EventViewer ID’s. you see the message that there is a security issue on the RSTempFiles folder.


Message: The current identity (NT AUTHORITY\LOCAL SERVICE) does not have write access to ‘C:\Program Files\Microsoft SQL Server\MSRS10_50.SIGMA\Reporting Services\RSTempFiles\’.


The main reason is that before the SSRS server was a local server in the Domain.

Afterwards we promote the server to a Domain Controller.

As you can see on the RSTempFiles folder Security, you see that the local account SYSTEM account has become obsolete.




Next add the new Domain AD Account to grant access to the Reporting Folders.


MS SQL Backup Compression

September 13, 2015

Recently I was wondering how much effect the DB compression would have on saving disk space

Well if you use this script you can follow up and check it.

[database_name] AS "Database",
DATEPART(month,[backup_start_date]) AS "Month",
AVG([backup_size]/1024/1024) AS "Backup Size MB",
AVG([compressed_backup_size]/1024/1024) AS "Compressed Backup Size MB",
AVG([backup_size]/[compressed_backup_size]) AS "Compression Ratio"
FROM msdb.dbo.backupset
WHERE [database_name] = N'msdb'
AND [type] = 'D'
GROUP BY [database_name],DATEPART(mm,[backup_start_date]);



Result is much better


QlikView – Access Data from SSRS

March 3, 2015

Since QlikView can’t access certain data sources like MS Analysis Services (SSAS) or other exotic data sources (SAP NetWeaver BI, Hyperion, TERADATA) natively.

We can fall back on the perfect middleware for this being MS SQL Reporting services


The approach is a simple as can be. Setup an SSRS server (can even be the MS SQL Express (Free) Edition & SSRS add-on)

The SSRS report server has natively a web service interface, exposing a SOAP and URL Interface.

Next develop your SSRS reports (which can handle multi data sources in 1 report Smile)


Like for example a SharePoint List combined with an Oracle database, or anything else.

Simular to PowerPivot that can access an SSRS Data Source. We can do the same with QlikView.

Use a Web File connection as Data Source


Fill in your report URL link and add the rs:Format=XML parameter to get an XML output from you report


If all goes well you will get the Report XML output and see the SSRS TABLIX and FIELDS Smile


That’s it, now you are ready to build your QlikView GUI

Once you know this technique you can as well use this to access an SSRS in the MS Azure cloud.Winking smile


SharePoint – SSRS Web Part Parameter Issue

January 31, 2015

We had an annoying problem with a published SSRS report in SharePoint that uses a Parameter.

Most likely this appears only in combination with a external data source like an ODBC connection that retrieves data from a data source not within the current domain ?

This strange ‘bug’ I would call it so far is not showing when your run the report from within the Report Designer, but only from within SharePoint.

So I suspect that this is a malfunction in the SSRS front end Web Part.

Problem :

When selecting the first time a parameter in the report, it returns all the filtered data. But when selecting a new parameter value, for the second time, it returns a blank page ? And the parameter box jumps down.


Solution :

After debugging with trial and errors, we found out that if you add a Default Value, in the parameter field, the problem is solved.


If you get the parameter values from a different Dataset, you can also specify the Default Value using the Get Values from a Query option. But this is not needed to make it work.

Just give any valid default value in the Specify Values field will do the job as well.


Again, this problem is not occurring in the Report Designer itself?

So the SharePoint users are happy to see that this annoyance is gone Smile

SharePoint – BLOB Statistics

January 19, 2015

Ever wanted to have a quick statistics on how many document types or size per document type you have in SharePoint.

Each Site collection storage has some default SQL Views and 1 of them is a view called Docs.


So we can use this to get insight into the BLOB storage statistics.

To get the Total Storage in a document library.

  select  Round(Sum(Cast(Size AS numeric )/(1024*1024)),2) as Size_GB
  from  [WSS_Content_Innovation].[dbo].[Docs]
  where ListId= '63B9C392-89E5-47CA-AEB7-1F0C4909A392' and IsCurrentVersion=1 and Type = 0

Or the number of extensions in a Site collection.

select ExtensionForFile as Extension,COUNT(*) as Count_Files
from  [WSS_Content_Innovation].[dbo].[Docs]
  where ListId= '63B9C392-89E5-47CA-AEB7-1F0C4909A392'
  and IsCurrentVersion=1 and TYPE = 0
  group by ExtensionForFile
  order by COUNT(*) desc


Or the total size per extension

select ExtensionForFile as Extension,COUNT(*) as Count_Files, CAST(Sum(Size)/1024 AS VARCHAR) + ' Kb' as Sum_FilesSize

from  [WSS_Content_Innovation].[dbo].[Docs]

  where ListId= '63B9C392-89E5-47CA-AEB7-1F0C4909A392'
  and IsCurrentVersion=1 and TYPE = 0

  group by ExtensionForFile
  order by  Sum(Size) desc


QlikView – A BI Reporting Tool for MS SQL CE

October 6, 2014

There are numerous tools on the net which will help you to access a MS SQL CE  database.

Even PowerShell can do the job, but it is not the same as have a full blown BI Reporting Tool under you fingertips.

So let’s look at QlikView to be able to access the .sdf database.

I used the Northwind.sdf example database

When creating a new report you need to select the data source for a Compact Ed. Database.

But as you can see it it not available in the list Sad smile


No worries.

Just type in the connection string yourself like this.

OLEDB CONNECT32 TO [Provider=Microsoft.SQLSERVER.CE.OLEDB.4.0;Data Source=”C:\Northwind.sdf”];

and complete with the SQL statement

SQL SELECT * FROM Customers;

SQL SELECT [Order ID],LOWER(Freight) as Freight, [Customer ID] FROM Orders;


Refresh and you are done.

PS: If it does not work make sure you have installed the MS SQL CE framework in your PC.


Connecting to Analysis Services in Another Domain

October 6, 2014

There are several tools available on the next to query an SSAS OLAP instance using MDX queries.

But most of the time you have the OLEDB drivers on your machine.


But some tools lack the authentication mechanism to poke into an OLAP cube that is not in your domain.

Like this MDX Query generation for QlikView


It allows you to connect but you can’t send across any authentication to connect remotely.



runas.exe /user:YourDomain\User /netonly “C:\Program Files (x86)\MDX Query Generator for Qlikview\QlikviewMDX.exe”

And it will just do fine ! Smile