How to determine SQL Server Version with SQL Server Year Name

One of the frustrations with SQL Server is that they do not expose the SQL Server Marketing Name that contains the year in the name as a Server Property.  Instead, to get that you either need your own lookup table, or you have to parse it from the @@Version string.

The following query returns every attribute that most people would want in discrete columns:

, SUBSTRING(@@Version, 11, CHARINDEX(‘ (‘, @@VERSION, 1) – 11) as SQLVersionLabel
, SUBSTRING(@@Version, 22, CHARINDEX(‘ (‘, @@VERSION, 1) – 22) as SQLYearReleaseLabel
, SERVERPROPERTY(‘ProductLevel’) AS ProductLevel
, SERVERPROPERTY(‘ProductUpdateLevel’) AS ProductUpdateLevel
, SERVERPROPERTY(‘ProductBuildType’) AS ProductBuildType
, SERVERPROPERTY(‘ProductUpdateReference’) AS ProductUpdateReference
, SERVERPROPERTY(‘ProductVersion’) AS ProductVersion
, SERVERPROPERTY(‘ProductMajorVersion’) AS ProductMajorVersion
, SERVERPROPERTY(‘ProductMinorVersion’) AS ProductMinorVersion
, SERVERPROPERTY(‘ProductBuild’) AS ProductBuild

Combining a query like that with a product like Toad for SQL Server’s group execute feature, it becomes possible to pull the SQL Version info from an entire group of servers all at once.

Office 365 Version 1912 Build 12325.20288 Released 2020-01-08 64-bit Issue Linking SQL Tables with Identity True

Microsoft Access users may start experiencing #Deleted in all columns when adding new records to forms bound to SQL Server tables containing identity columns as of 64-bit Office 365 Version 1911 Build 12228.20364.

The issue occurs when linking SQL Server tables to a Microsoft Access database using the 64-bit version of Office 365 Version 1912 build 12325.20288 released on January 8th, 2020.

UPDATE: Office 365 Version 1912 build 12325.20298 released on January 14th, 2020 is ALSO affected by this issue.

UPDATE: 2020-01-17: Microsoft confirms the issue publicly:

Direct Link:

UPDATE: 2020-01-23: Microsoft Released Version 1912.12325.20344 that fixes the issue covered in this blog post.  From that URL “This update fixes an issue that can cause Microsoft Access to fail to identify an Identity Column in a linked SQL Server table, which can cause rows to be reported as deleted incorrectly”

The issue is as follows:

In SQL Server, assume you have a SQL Table with a column where the Identity property is true like this:

SQL Server Management Studio Table Designer – Showing (Is Identity) = Yes

When linked to Microsoft Access via an ODBC Link, that ID column normally appears in Microsoft Access as Data Type “AutoNumber”:

Microsoft Access Linked SQL Table Shows Identity Column Data Type as "AutoNumber"

When linked from Build 12325.20288, you can see that the same field now links as “Number” (Field Size: Long Integer), but it no longer recognizes the identity property from SQL Server:

Microsoft Access Linked SQL Table Shows Identity Column Data Type as "Number"
on Office 365 Build 12325.20288 64-bit

The effect of the Data Type being set incorrectly on the linked table is that an end-user after entering a new record in the Access Form bound to that table will see #Deleted in every field/column of the new record when they leave the record like this:


If the user forces a refresh of the data by using the SHIFT+F9 keyboard shortcut, the refreshed screen will display the new record since the record actually was committed to the database. It is worth noting that end users who do not realize this may panic since the #Deleted in every cell may appear to them as data loss.

Work Around:

Two different workarounds appear to work.

Work Around #1 – Change the bitness level back to 32-BIt by using the MigrateArch setting in the ODT:

If you have a business reason why you must run Version 1912 Build 12325.20288, switching from architecture from 64-bit back to 32-bit does seem to work as a workaround. However if you do not need to run that build a much better solution is simply to revert to the stable Dec 10, 2019 Version 1911 Build 12228.20364. To switch the architecture, download a current version of the ODT Setup, then use the following XML:

< Add SourcePath="C:\odt" OfficeClientEdition="32" MigrateArch="TRUE" Channel="Monthly" >
< Product ID="O365ProPlusRetail">
< Language ID="en-us" />
< /Product>
< /Add>
< Updates Enabled="FALSE" />
< Display Level="Full" AcceptEULA="TRUE" />
< Logging Path="%temp%" />
< Property Name="SharedComputerLicensing" Value="1" />
< Property Name="AUTOACTIVATE" Value="1" />
< /Configuration>
Office ODT XML File Sample with "MigrateArch" Setting

Work Around #2 – Force Installation of Previous Build or the Fixed Version Build by specifying Version Explicitly in the ODT:

A better solution is simply to change to a version that does not contain the issue.

To force the installation of a particular build, first, use Control Panel Programs and Features to uninstall the existing Office 365 ProPlus build, then using the most recent ODT Installer download and then install the specific build using a config.xml file with the following contents:

OPTION 1: Revert to the last known good release of Office 365, which in my testing is Version 1911 Build 12228.20364.

  <Add SourcePath="C:\odt" Version="16.0.12228.20364" OfficeClientEdition="64" Channel="Monthly" >
    <Product ID="O365ProPlusRetail">
      <Language ID="en-us" />
  <Updates Enabled="FALSE" TargetVersion="16.0.12228.20364" />
  <Display Level="Full" AcceptEULA="TRUE" />
  <Logging Path="%temp%" />
  <Property Name="SharedComputerLicensing" Value="1" />
  <Property Name="AUTOACTIVATE" Value="1" />
< /Configuration>
Office ODT XML File Sample to Install Specific Build 12228.20364

OPTION 2: Skip to the fix Version 1912 12325.20344

The complete list of versions /builds can be found here:

The sample below jumps you to Build 12325.20344 – the first build where this issue is fixed:  

  <Add SourcePath="C:\odt" Version="16.0.12325.20344" OfficeClientEdition="64" Channel="Monthly" >
    <Product ID="O365ProPlusRetail">
      <Language ID="en-us" />
  <Updates Enabled="FALSE" TargetVersion="16.0.12325.20344" />
  <Display Level="Full" AcceptEULA="TRUE" />
  <Logging Path="%temp%" />
  <Property Name="SharedComputerLicensing" Value="1" />
  <Property Name="AUTOACTIVATE" Value="1" />
< /Configuration>
Office ODT XML File Sample to Install Specific Build 12325.20344

IMPORTANT: After you have changed the version of the ODT, you must RE-ATTACH the Linked Tables.  Those tables previously re-attached by the corrupted application are still linked as Number, not “AutoNumber” so you must RE-ATTACH the tables for the identity columns to be re-linked as AutoNumber once again by the database engine.

The following instructions are intended for customers of Blue Link who encounter this issue with Blue Link Elite:

  <Add SourcePath="C:\odt" Version="16.0.12325.20344" OfficeClientEdition="64" Channel="Monthly" >
    <Product ID="O365ProPlusRetail">
      <Language ID="en-us" />
  <Updates Enabled="FALSE" TargetVersion="16.0.12325.20344" />
  <Display Level="Full" AcceptEULA="TRUE" />
  <Logging Path="%temp%" />
  <Property Name="SharedComputerLicensing" Value="1" />
  <Property Name="AUTOACTIVATE" Value="1" />
< /Configuration>

  • Run setup.exe /download Install20191210Build.xml from Administrative Command Prompt
  • Un-Install Microsoft Office 365 ProPlus using CONTROL PANEL “Uninstall a Program” option:

Control Panel > Programs > Programs and Features

When uninstall is complete, install with the new ODT XML:

  • From Administrative Command Prompt, run:
  • C:\ODT\Setup.exe /configure Install20191210Build.xml

When install is complete:

  • Open Access as Administrator  (this is so that the Blue Link Add-In can be registered correctly with the updated build of Access)
  • Do not open a database, instead, hit the escape key on your keyboard and then click File > Account and verify that the build installed is the correct build:

Microsoft Access File > Account Screen Showing Build 12228.20364

  • Finally, re-link the app to the SQL Database by re-selecting the data file from the Choose Company List:
    • Open "D:\BlueLink\Master\BLElite64.accde"
    • Click Choose Company
    • Select the company in question and click Use

To test: Open a sales order, add a new line, leave the row.  If no #Deleted, then the issue is solved.

Please comment and/or share this article with anyone else performing patch maintenance or managing a WSUS server / Office Updates.

Microsoft Access Error: Query ” is corrupt caused by KB4484127, KB4484119, KB4484113

As confirmed in this Microsoft Article:

Microsoft Access application users may suddenly start to receive “Error Query ‘’ is corrupt”.

Microsoft released a security update as of November 12th, which can cause the following error in Microsoft Office and Blue Link applications:


The following KB’s released by Microsoft, are known to cause the issue:


Admins who manage WSUS servers should block the three KBs listed above to prevent additional end user machines or servers from receiving these updates.

If you already have these updates installed, and have admin rights to your computer, follow these steps to remove the updates:

(1) Open Programs & Features


(2) Click on view installed updates.


(3) Search for KB KB4484119 & KB4484113
(4) Once you find the update, click on Uninstall update. Please note, this may require a server reboot.


Tip: you can search the list:


  • Tip: To save time, since these KBs all start with “KB4484” searching for that will narrow the list, saving you time.

To Block the update on Windows 10 you now have to download a special tool since Microsoft removed the built-into-the-os version of the feature.

If running Windows 10, you’ll have to download the wushowhide.diagcab file from here:


Click Next, To Block the update if already un-installed, choose the hide option and then you’ll be presented with a list of updates that have NOT YET BEEN APPLIED to your machine.  If the KB is in this list, you may selected it and then click through the wizard.  This will prevent it from being auto-reapplied.  (NOTE: This step is not required if you use a WSUS Server and have already blocked the update there).



Click Start, Type “Check for Update”


Look at the list of pending updates – if the server has already downloaded and partially installed the update it may show as shown in this screen capture.  If it does, the update is already partially installed – it is too late to block it.  You must force the server restart and then after the server comes back up, then un-install the update.

Update: 2019-11-17

For servers where the KB update was PENDING (showed in the Updates screen as about to be installed) if actual installation happened on Nov 17th or later it appears not to actually install that KB, but rather installs the updated KB:

In such instance, there is no “un-install” step required.

Access 365 (Office 365)

UPDATE: This issue is also affecting Access 365 (installed via Office 365 with Monthly Channel (Confirmed issue in Build 12130.20344 Click-to-Run).

Apparently, you can force the version of Office to a previous update by following these instructions:

Here’s how to determine the “previous version” build numbers:

The previous build (October 30, 2019) is build: 12130.20272

2019-11-25 – UPDATE: Microsoft has released a patched version 12130.20410 – instructions below are designed to force you to the fixed version, instead of forcing you backwards to the prior version.

To update the app, the Office Deployment Tool (ODT) has to be used.  Assuming you extracted it to C:\ODT, then you would update the ODT XML as follows:

     <Add SourcePath="C:\odt" OfficeClientEdition="64" Channel="Monthly" >
          <Product ID="O365ProPlusRetail">
               <Language ID="en-us" />
    <Updates Enabled="TRUE" TargetVersion="16.0.12130.20410" />
     <Display Level="Full" AcceptEULA="TRUE" />
     <Logging Path="%temp%" />
     <Property Name="SharedComputerLicensing" Value="1" />
     <Property Name="AUTOACTIVATE" Value="1" />

If you have a C:\ODT\Office folder, delete it.

setup /download MyConfigFile.xml

setup /configure MyConfigFile.xml

For me this worked.  All I had to do was kick everyone off the server to allow the update to complete.  Minutes later the error was gone.

IMPORTANT: After the install completes click File, Account, Update Options then select Disable Updates to prevent the update from just being installed again:

For Access 2019 (Office 2019)

Same as Office 365, except the build numbers are different.  In my case, the affected build was Build 10352.20042 Click-to-Run. 

Here’s how to determine the “previous version” build numbers:

Need to the October 8, 2019 build: 10351.20054

  <Add OfficeClientEdition="64" Channel="PerpetualVL2019">
      <Product ID="ProPlus2019Volume"  PIDKEY="XXXXX-XXXXX-XXXXX-XXXXX-XXXXX" >
         <Language ID="en-us" />
  < RemoveMSI />
  <Updates Enabled="TRUE" TargetVersion="16.0.10351.20054" />
  <Display Level="None" AcceptEULA="TRUE" /> 
  <Property Name="AUTOACTIVATE" Value="1" />

SQL Server 2016 SP2 (KB) Causes SQL Backups to Fail with error 3601

After applying SQL Server 2016 SP2 Backups of Databases that had SQL Change Tracking enabled at the time SP2 was applied to the server no longer work.  Backups will fail with this message:


TITLE: Microsoft SQL Server Management Studio

Backup failed for Server ‘ServerName’.  (Microsoft.SqlServer.SmoExtended)

For help, click:


An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)


Failed to flush the commit table to disk in dbid 10 due to error 3601. Check the errorlog for more information. (Microsoft SQL Server, Error: 3999)

For help, click:


My Scenario: Server OS: Windows Server 2016 Datacenter

SQL Server 2016 Standard, SP2 Applied

Databases affected are those that had SQL Change Tracking enabled at the time of SQL Server 2016 SP2 being applied to the server, like this:

Ola Hallengren Backup Scripts Also Fail

If you happen to be using the Ola Hallengren DatabaseBackup SPROC, you may also notice that SPROC start to fail with error


Msg 207, Level 16, State 1, Line 1 Invalid Column name ‘modified_extent_page_count’.

That failure is happening because there is no column ‘modified_extent_page_count’ in the DatabaseName.sys.dm_db_file_space_usage views after the application of SQL 2016 SP2.

This is the specific line from the DatabaseBackup SPROC that fails:

SET @CurrentCommand07 = ‘SELECT @ParamAllocatedExtentPageCount = SUM(allocated_extent_page_count), @ParamModifiedExtentPageCount = SUM(modified_extent_page_count) FROM ‘ + QUOTENAME(@CurrentDatabaseName) + ‘.sys.dm_db_file_space_usage’

Solution: Remove SP2:Click Start, type “Update” and then choose “View Installed Updates”:


Scroll down to “Microsoft SQL Server 2016 (64-Bit)


Select “Service Pack 2 for SQL Server 2016 (KB4052908) (64-bit) 

Click Uninstall


If as in my case a restart is required, the setup will tell you that.  Restart the server then repeat this process and you’ll be able to click through the wizard to the end.

When the service pack removal is complete, backups will work once again.

Disabling change tracking does not solve this problem only uninstall of SP2 worked for me

I’m adding the steps to disable change tracking here for your easy reference, but in my experience this DOES NOT actually solve the problem.  Using a script like this you can disable change tracking:

FROM sys.change_tracking_tables ctt
JOIN sys.tables t
    ON t.object_id = ctt.object_id
JOIN sys.schemas s
    ON s.schema_id = t.schema_id

–Copy the results of the script above to a new query window and execute to turn SQL Change Tracking off on the tables for which it was enabled, and then run a command like this to disable change tracking on the database.


MSAccess Date Picker Not Available for Unbound Text Fields with Input Mask

Starting with Microsoft Access 2007, 2010, 2013, 2016, if you create a database of type .accdb, text controls bound to date datatype fields will automatically display a date picker with or without an input mask:


Unbound text fields will also display the date picker if you set the Format property to a valid date format and set the Show Date Picker property to “For dates”:


… but only if the Input Mask property is left blank.

As soon as you specify an input mask on the unbound text field, the date picker immediately stops working.  Here’s an example:

Input Mask Property is Blank:


Input Mask Property is set to any value:


Result: Date Picker is available:


Result: Date Picker is Not Available:


This is true even if you happen to use Microsoft’s own Input Mask Wizard to set the input mask.

If you happen to hit this bug and would also like to have it fixed, please vote for the idea on the Access user voice site:

Simply removing the input mask solves the problem – if you can convince your end users that they no longer need the input mask!

Windows Server 2016 RDP–You must change your password before logging on the first time

As I start to roll out Windows Server 2016 to replace Windows Server 2008 R2 RDP Servers, I’ve been encountering issues that just didn’t exist with Server 2008 R2.  Today’s issue is that by default, if a new user account is created, or if the user’s password expires, or if an admin simply checks the Active Directory “User must change password at next login” property, when the user attempts to connect via RDP instead of getting a password change prompt they instead see this error:


Title: Remote Desktop Connection

You must change your password before logging on the first time.  Please update your password or contact your system administrator or technical support.

This has the unusual effect of preventing the user from changing their password themselves, leaving the administrator with one of two undesirable options:

  • Turn off the “User must change password at next login” property.
  • Change the password to some other password yourself, and also make sure that property is not set.

In either case, the implication is that some person other than that user now knows what that user’s password.

How to solve the issue – Change the RDP Security Layer

To solve the issue, you have to edit the Session Collection, Security, Configure Security settings and then change the Security Layer setting from Auto-Negotiate to RDP Security Layer.


Once that change has been applied, remote RDP users return to being able to set a new password.

As an added bonus, this RDP Security Layer is actually more secure, and is also PCI Compliant (at least as of this writing anyway).

Facebook Copy-Paste Threads

Why are so many people falling for copy/paste bait threads lately? Copy/paste Facebook threads are the modern day equivalent of chain letters. The people who start them are doing so to see how many people that they can trick into doing it.

It is a form of social engineering, a psychological test to see what works and what doesn’t – what words and phrases can we use to evoke sufficient emotion in the reader to make them do what they were told to do? (i.e. copy/paste).

It is not hard to imagine this being done on purpose so that the data of who follows the instructions can be data-mined and correlated with which technique worked. You could be telling a data-miner which techniques will work ON YOU.

If you haven’t figured it out yet, the copy/paste threads all follow similar patterns. There are many different patterns, but the worst in my opinion are these two:

Pattern #1 – Prey on fear:

These are the messages that start with a warning or something scary that sounds true. The people who share these are concerned about their friends and probably want to protect them. These are the "add this legal notice to your profile to stop your pictures from being used in ads" ones, the "someone is spoofing profiles" ones, the "make sure you change these privacy settings or else" ones. They all say something scary and they all share in common that you must "copy/paste" the message and "Not just share it".

If you’re being told to copy/paste it then it should immediately seem suspicious to you. Take some time, research the issue, find an article in a reputable news media outlet, search the Internet to see if that phrase or keyword shows up as a known fraud or as something that has been disproven. Really want to project your friends? Stop spreading fear.

Pattern #2 – Prey on insecurities:

These are the messages that try to make you "test" who your friends are. The "did you read this whole post" ones, the "I’m trying to figure out who only likes my photos and doesn’t read my text so read this and copy/paste it to prove your friendship" ones, etc.

All you figure out when you do this are which of your friends are gullible, and depending on the content of the message, which of them you can manipulate using guilt.

This is not data that you should be sharing about yourself. This is not data that you should be asking your friends and family to share about themselves.

If you really are feeling insecure and want to know who your real friends are? Ask them to private message you or to share a story about a great moment they shared with you or of a great memory that they have of you. Better yet, reach out to them and share one of your memories.


So how do you know if something is worth copying or not? The answer is simple: if the thread ends with the phrase "copy/paste" just DON’T DO IT. If if the thread ends with instructions that "you must follow exactly" then it should be considered highly suspicious. Just like chain mail, these threads will only stop when a critical mass of people STOP DOING IT.

Stop and think about WHY the original message is asking you to copy/paste instead of sharing or adding your own original thoughts. The message is likely crafted to make it seem to your friends and family reading the message as though the thoughts expressed were yours. But by definition since you just copy/pasted it they are NOT your own original thoughts! There is a word for passing someone else’s thoughts off as if they were your own: Plagiarism.

If you agree with what someone is saying, like it, or share the thought and add your own original thought. Do the research, credit the original author, but if a friend asks you to copy/paste something, be a real friend: Just Don’t.