This isn't really a blog, its more of a holding page for my domain (seems a shame not to have a page), if I know you then add me on either LinkedIn or Facebook (links are on the right), however if I don't know you then I won't add you!

Tuesday 20 November 2018

SSRS - Cannot edit data driven subscription / delete report


Issue

Recently when trying to edit an existing data driven subscription I received an error message through the web front end which didn’t prove very useful. 
 







This all happened shortly after I encountered the server responding slowly, in hindsight I think I may have pressed finish twice when editing the subscription and created two copies of it.


Steps I took to investigate

These are the steps I took to investigate, it might prove useful for looking for similar problems yourself.

I looked at the internal logs on the reporting services server (which in SSRS 2014 were located in C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\LogFiles), inside I eventually found;

library!ReportServer_0-9!1514!11/20/2018-13:05:00:: i INFO: Call to GetSystemPropertiesAction().
library!ReportServer_0-9!1514!11/20/2018-13:05:00:: i INFO: Call to GetItemTypeAction(/email_reports/HR - Leaver Email).
library!ReportServer_0-9!1514!11/20/2018-13:05:00:: i INFO: Call to GetReportParametersAction(/email_reports/HR - Leaver Email).
library!ReportServer_0-9!1268!11/20/2018-13:05:00:: i INFO: Call to GetSubscriptionPropertiessAction(ed4ccbeb-2907-45db-b911-84d16c547e0c).
library!ReportServer_0-9!1268!11/20/2018-13:05:00:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: Adding more than one data source with null original name, Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details.;

Looking at the entry in the log file I can see the report folder/report name in yellow which leads me to believe I am in the right place in the log file (corresponding with the date/time of the error) and the error description in red leads me to believe the issue is relating to the data source. 

I decided that the easiest course of action was to delete the subscription and recreate it, I went into SSMS on the reports server and found the table “[ReportServer].[dbo].[Subscriptions]” this gave me sufficient from the “datasettings” column to recreate the subscription.

Wednesday 31 October 2018

Oracle - Caution when using sequences within case statements

Some logic within an SSRS report that I was working on had a case statement that either pulled a QR code from an existing field or ran a sequence to allocate a new QR code, this then got called in a separate dataset in the report that carried out in insert of the new QR code (by sharing it through an internal parameter).  An example of this code is below;

select
M.staff_number,
CASE
WHEN IM.code is not null THEN im.code
WHEN IM.code is null THEN qr_code.next_val
END QR_CODE

FROM master M
LEFT JOIN issue_numbers IM
ON (M.staff_number = IM.primary_key AND IM.domain = 'QR_CODE')
WHERE
M.staff_number = :staff_number

However I found that the sequence was incrementing the numbers faster than expected and leaving gaps of numbers that never got used against a record, this even happened when the report ran the dataset for staff who had a record in the IM table (and as such shouldn't have got past the first WHEN row), I couldn't work out why until it became apparent that the SQL is evaluated in its entirety and as such running the sequence regardless of where the case statement resolved for that record.

After thinking about it the issue was resolved by embedding the sequence within a function that has a parameter passed to it, so that the function only ran the sequence when it received a certain value (which was keyed to an embedded case statement that passed the key value only when IM.code is null).