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).


Tuesday, 8 August 2017

Microsoft Onedrive - "Something went wrong and we can't sign you in at the moment. Please try again later." error in Edge

Had the following error when trying to sign into Microsoft Onedrive using the Microsoft Edge Browser, assumed it was something wrong with the service generally, however after trying in Chrome (it worked) it seems onedrive sometimes doesn't let you sign in using their own browser!

Hopefully this post will flag up anyone else who has the same issue!


Wednesday, 6 July 2016

Issue/Problem – Error when running a select/insert into table SQL server (Attempt to fetch logical page (1:28791) in database 12 failed. It belongs to allocation unit X not to Y.)

When running a SQL server agent job that carries out a large insert (64k records), the job fails after attempting to insert the records with the following error text.

Attempt to fetch logical page (1:28791) in database 12 failed. It belongs to allocation unit X not to Y.

This error also occurred when querying the table.

Answer/solution

This is related to issues regarding the storage location of the table being mismatched, SQL gurus more experienced than me will understand this further.  However as this example was a table that was cleared down and repopulated from another data source, there is not data loss implications.  The priority was to get the table populated with data so that the other related systems could continue functioning.

This was done by right clicking on the table and click Script Table as > CREATE to > New query window.  Then to rename the table with the prefix old (so that I could debug/investigate it later) and then run the script generated as the create table, then rerun the insert script and test with a select.

Obviously the above does not solve the root cause and doesn’t work if the table concerned contains data native to the database that cannot be replaced/repopulated.

Issue/Problem – Inserting records into SQL Server table doesn’t result in entries in table

When carrying out a simple insert into a table in a SQL server table the X number of records affected text is displayed, although a select * from the table immediately afterwards does not yield any results.

Answer/solution
There isn’t necessarily any single answer to this, so look at the following areas;
  • Refine your insert statement to use some hard coded values in place of a SQL select statement to remove any complication and identify if the error lies in the select statement as opposed to the insert transaction.  
  • If the table is a high turnover table (i.e. one that is cleared down and then repopulated with large amounts of data) check to see if there are any auto number columns that will have hit their limit (i.e. the limit of a integer etc).
  • Check the database server itself to ensure there is sufficient room remaining on the drives that run both the operating system and store the database files, also check that autogrow is on.
  • Attempt to rebuild any indexes on the table (this was what resolved the issue for me)


The frustrating fact with the issue above is that the “X number of records affected” following the insert leads any SQL developer to assume the records have been inserted successfully.

Friday, 18 March 2016

SSRS Error - There was an exception running the extensions specified in the config file. ---> Maximum request length exceeded

Hopefully this can help someone else out that has this issue, I had the error "Error 1 There was an exception running the extensions specified in the config file. ---> Maximum request length exceeded" displayed when publishing what I believed was a relatively straight forward SSRS report.

Upon googling most of the solutions tended to hint at it being related to a large mapping component being present, however this wasn't present in my report.

I then realised that the report I had written was done by copying an existing report as a starting point, that report had a few very large (file size wise) images, although I had deleted them from the report.  Looking in the Report Data > Images location I can still see them referenced in there, deleting them from there resolved the issue.

Friday, 24 October 2014

Sharepoint not saving changes made to Word documents

Opening a file in Sharepoint from Internet Explorer in Microsoft Word allows the user to (subject to permissions) make amendments to the file and use the save button to save straight back to the Sharepoint repository.  

However in certain circumstances users may make changes however not see those changes replicated in Sharepoint (despite no errors being displayed).


One main indicator that Sharepoint and Word aren't playing nice is that when editing the document the green "sync" arrows aren't displayed (see icon above).  The one cause of this I have encountered is where users are using the 64 bit edition of Internet Explorer, it seems to be related to the support within 64bit IE of ActiveX controls.

The solution is don't use the 64 bit edition of Internet Explorer, however users ma have been making changes oblivious to the fact that they aren't being saved.