Stored procedure version comment and database extended property

[drstonephd]

Please allow it to be easy to identify what version of the maintenance solution is installed. For example, include the last updated date of the maintenance solution as a comment in the code of each procedure.
--// Source: https://ola.hallengren.com
can become
--// Source: https://ola.hallengren.com
--// Version: 2016-04-02

Code can then be used to the check the version is installed. The code can also add or update a database extended property with the version. If the incorrect version is installed, the value NULL can be stored in the extended property.

`USE [master] -- Specify the database in which the objects will be created.

SET NOCOUNT ON

DECLARE @CreateJobs nvarchar(max)
DECLARE @BackupDirectory nvarchar(max)
DECLARE @Cleanuptime int
DECLARE @OutputFileDirectory nvarchar(max)
DECLARE @LogToTable nvarchar(max)
DECLARE @Version numeric(18,10)
DECLARE @error int
DECLARE @LastUpdatedExtPropName sysname
DECLARE @LastUpdatedExtPropValue datetime

SET @CreateJobs = 'Y' -- Specify whether jobs should be created.
SET @BackupDirectory = N'C:\Backup' -- Specify the backup root directory.
SET @Cleanuptime = NULL -- Time in hours, after which backup files are deleted. If no time is specified, then no backup files are deleted.
SET @OutputFileDirectory = NULL -- Specify the output file directory. If no directory is specified, then the SQL Server error log directory is used.
SET @LogToTable = 'Y' -- Log commands to a table.

SET @error = 0
SET @LastUpdatedExtPropName = N'https://ola.hallengren.com last updated' -- set to NULL to disable setting the extended property
SET @LastUpdatedExtPropValue = '2018-04-02 00:00:00' -- set to NULL to disable stored procedure version check

SET @Version = CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - 1) + '.' + REPLACE(RIGHT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)), LEN(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))),'.','') AS numeric(18,10))

IF IS_SRVROLEMEMBER('sysadmin') = 0
BEGIN
RAISERROR('You need to be a member of the SysAdmin server role to install the solution.',16,1)
SET @error = @@error
END

IF OBJECT_ID('tempdb..#Config') IS NOT NULL DROP TABLE #Config

CREATE TABLE #Config ([Name] nvarchar(max),
[Value] nvarchar(max))

IF @CreateJobs = 'Y' AND @OutputFileDirectory IS NULL AND SERVERPROPERTY('EngineEdition') <> 4 AND @Version < 12
BEGIN
IF @Version >= 11
BEGIN
SELECT @OutputFileDirectory = [path]
FROM sys.dm_os_server_diagnostics_log_configurations
END
ELSE
BEGIN
SELECT @OutputFileDirectory = LEFT(CAST(SERVERPROPERTY('ErrorLogFileName') AS nvarchar(max)),LEN(CAST(SERVERPROPERTY('ErrorLogFileName') AS nvarchar(max))) - CHARINDEX('',REVERSE(CAST(SERVERPROPERTY('ErrorLogFileName') AS nvarchar(max)))))
END
END

IF @CreateJobs = 'Y' AND RIGHT(@OutputFileDirectory,1) = '' AND SERVERPROPERTY('EngineEdition') <> 4
BEGIN
SET @OutputFileDirectory = LEFT(@OutputFileDirectory, LEN(@OutputFileDirectory) - 1)
END

INSERT INTO #Config ([Name], [Value])
VALUES('CreateJobs', @CreateJobs)

INSERT INTO #Config ([Name], [Value])
VALUES('BackupDirectory', @BackupDirectory)

INSERT INTO #Config ([Name], [Value])
VALUES('CleanupTime', @Cleanuptime)

INSERT INTO #Config ([Name], [Value])
VALUES('OutputFileDirectory', @OutputFileDirectory)

INSERT INTO #Config ([Name], [Value])
VALUES('LogToTable', @LogToTable)

INSERT INTO #Config ([Name], [Value])
VALUES('DatabaseName', DB_NAME(DB_ID()))

INSERT INTO #Config ([Name], [Value])
VALUES('Error', CAST(@error AS nvarchar))

INSERT INTO #Config ([Name], [Value])
VALUES('LastUpdatedExtPropName', @LastUpdatedExtPropName)

INSERT INTO #Config ([Name], [Value])
VALUES('LastUpdatedExtPropValue', CONVERT(varchar(23), @LastUpdatedExtPropValue, 120))
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS(SELECT 1 FROM #Config WHERE Name = 'LastUpdatedExtPropValue' AND ISDATE(value) = 1)
BEGIN

DECLARE @currentversion datetime
DECLARE @error int
DECLARE @LastUpdatedExtPropName sysname
DECLARE @LastUpdatedExtPropValue datetime
DECLARE @rowcount int
DECLARE @text nvarchar(max)
DECLARE @tempver datetime
DECLARE @VersionExtPropValue sql_variant

SELECT @LastUpdatedExtPropName = CAST(Value as sysname)
FROM #Config
WHERE [Name] = 'LastUpdatedExtPropName'

SELECT @LastUpdatedExtPropValue = CAST(Value as datetime)
FROM #Config
WHERE [Name] = 'LastUpdatedExtPropValue'

SET @currentversion = @LastUpdatedExtPropValue

IF NOT @currentversion IS NULL
BEGIN
SET @text = OBJECT_DEFINITION (OBJECT_ID(N'[dbo].[CommandExecute]'))
SET @tempver = CASE CHARINDEX('--// Version: ', @text) WHEN 0 THEN NULL ELSE SUBSTRING(@text, 14 + CHARINDEX('--// Version: ', @text), 23) END
IF @tempver IS NULL OR @tempver <> @currentversion SET @currentversion = NULL
END

IF NOT @currentversion IS NULL
BEGIN
SET @text = OBJECT_DEFINITION (OBJECT_ID(N'[dbo].[DatabaseBackup]'))
SET @tempver = CASE CHARINDEX('--// Version: ', @text) WHEN 0 THEN NULL ELSE SUBSTRING(@text, 14 + CHARINDEX('--// Version: ', @text), 23) END
IF @tempver IS NULL OR @tempver <> @currentversion SET @currentversion = NULL
END

IF NOT @currentversion IS NULL
BEGIN
SET @text = OBJECT_DEFINITION (OBJECT_ID(N'[dbo].[DatabaseIntegrityCheck]'))
SET @tempver = CASE CHARINDEX('--// Version: ', @text) WHEN 0 THEN NULL ELSE SUBSTRING(@text, 14 + CHARINDEX('--// Version: ', @text), 23) END
IF @tempver IS NULL OR @tempver <> @currentversion SET @currentversion = NULL
END

IF NOT @currentversion IS NULL
BEGIN
SET @text = OBJECT_DEFINITION (OBJECT_ID(N'[dbo].[IndexOptimize]'))
SET @tempver = CASE CHARINDEX('--// Version: ', @text) WHEN 0 THEN NULL ELSE SUBSTRING(@text, 14 + CHARINDEX('--// Version: ', @text), 23) END
IF @tempver IS NULL OR @tempver <> @currentversion SET @currentversion = NULL
END

IF @currentversion IS NULL
BEGIN
RAISERROR('Warning: The maintenance stored procedures installed are not current.',16,1)
SET @error = @@error
END

IF LEN(@LastUpdatedExtPropName) > 0
BEGIN

SELECT @VersionExtPropValue = [value]
FROM sys.extended_properties
WHERE class = 0 AND major_id = 0 AND minor_id = 0 AND [name] = @LastUpdatedExtPropName;

SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT

IF @rowcount = 0 
  EXEC sys.sp_addextendedproperty @name = @LastUpdatedExtPropName, @value = @CurrentVersion;
ELSE IF @VersionExtPropValue <> @CurrentVersion OR (@VersionExtPropValue IS NULL AND NOT @CurrentVersion IS NULL) OR (NOT @VersionExtPropValue IS NULL AND @CurrentVersion IS NULL) 
  EXEC sys.sp_updateextendedproperty @name = @LastUpdatedExtPropName, @value = @CurrentVersion;

END

END`

[olahallengren]

Thank you for the feedback. One question:

Let's say that I release a new version, where I have only updated one of the stored procedures.

The date in MaintenanceSolution.sql would then get updated.

Now what about the dates in the stored procedures? Should I only update the date in the stored procedure that has been updated, or should I update the dates in all of the stored procedures?

[drstonephd]

Once installed, I can't easily determine which version of "MaintenanceSolution.sql" was used to install the procedures. I would make it simple and make all the dates the same as the last updated date. This would insure that the code is from one release and compatible. If I see I have an older version of a procedure, I see no reason not to replace all the procedures. If you version each separately, then it would be harder to figure out which if I have a consistent set.

Conceptually, I think the difference is version tracking for development verses version tracking for deployment. Source control can track all changes for a developer. Procedures can be tracked separately or all together (tag/branch). As a client, my interest is that I have the latest and greatest complete set of code (e.g., deployed from a tag on stable code). If you include a version date specific for the procedure, I would make it a separate comment in addition to the deployment version.

If there is a breaking change to the CommandLog table or to a job, it would be really nice to be able to check the version of the table or job. Without renaming the table (e.g., version in the name), I think a extended property on the object would be the way to do this. For the jobs, a comment in the job or in the step code might be enough to determine if it is compatible. Store version info in extended properties on the procedures, tables, jobs, etc.?

[drstonephd]

PS I worry that the object can be changed without the extended property being updated. That's why in my sample I pull the version for the extended property out of the text of the procedure. I don't know what to do with a table without coding detection of column changes. Would a computed column that specifies the version be a poor hack to insure we have the correct table version? An unused output parameter in a stored procedure to hold the procedure version? I have not really thought about this before. There probably is a more elegant way to track versions.

[dataman]

Concurring with drstonephd. I realize you are publishing minor updates, but perhaps, at some point, you would package and publish a release version. It would be good to be able to query the package and return a product version. We currently have a package push procedure and artificially name maintenance solution versions as the date we download the updated scripts. We keep track of the last version we pushed in a local dba database. Charley Jones, PMP/MCDBA Dataman in Las Vegas —

…

You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub, or mute the thread.

[olahallengren]

I think that the best would be to have both the last modified date and the release date as comments in the stored procedures. I will work on adding this to my release process.

[olahallengren]

I have updated my release procedure. It is now adding a version timestamp in the header of the stored procedures, and in the header of MaintenanceSolution.sql.

You can query the version timestamps like this:
https://ola.hallengren.com/scripts/misc/VersionCheck.sql

I have also included the version timestamp in the output from the stored procedures.

[olahallengren]

I have currently no plans to add extended properties.