Monitoring identity fields in SQL Server

 A common issue that I've encountered and certainly more than once, is where database inserts fail due to incorrectly sized identity fields. This is caused by the data type and its associated numerical limit being unable to store the next number in the identity sequence (known as the seed).

This is easily resolved by changing the fields data type, however this often means that users are unable to insert errors when this is discovered. Sizing all of your datatypes to be larger than necessary on the off chance of this issue is ill advised, as there are storage and performance issues with using the wrong datatype.

With this in mind I have created a simple script that identifies identity fields across a database, identifies the current seed and compares that against the associated limits based on the fields data type. This allows the monitoring of seed vs datatypes and more of a proactive approach taken to the problem.

https://github.com/mugleston/SqlServerScripts/blob/master/IdentifyIdentityColumnsApproachingDataTypeMax

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.

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

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!

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.

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.