1

We've recently upgraded a server to SQL Server 2016 (Enterprise). But then the syspolicy_purge_history started failing on the 3rd step. I traced this back to msdb.dbo.syssubsystems which had an invalid agent_exe value for PowerShellStart. When I tried to update this using sp_verify_subsystems I found that the paths in this SP hadn't been updated as part of the 2016 upgrade to point to the 130 folder and were still point at the 120 folder.

2014 SP Version

     EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\Microsoft Sql Server\120', N'VerSpecificRootDir', @VersionRootPath OUTPUT
 IF @VersionRootPath IS NULL
 BEGIN
   RAISERROR(14659, -1, -1) WITH LOG
   RETURN(1)
 END

2016 SP Version

        EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\Microsoft Sql Server\130', N'VerSpecificRootDir', @VersionRootPath OUTPUT

    IF @VersionRootPath IS NULL
    BEGIN
        RAISERROR(14659, -1, -1) WITH LOG
        RETURN(1)
    END

I checked another server recently upgraded to 2016 and the stored procedure had been updated successfully.

Is there any reason why an in place upgrade would complete successfully but not update the system stored procedures? All databases are running in 2016 compatibility mode.

Obviously I can update the stored procedure myself, but I am just wondering if any others have been missed?

  • 1
    This has come up on this site before, so you're definitely not alone. There might be an item on connect.microsoft.com/sql about it... if there is, upvote, comment, and confirm repro. If not, file a new bug, because this is definitely a bug. – Aaron Bertrand Jan 16 '17 at 12:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.