{"action":"created","issue":{"url":"https://api.github.com/repos/olahallengren/sql-server-maintenance-solution/issues/39","repository_url":"https://api.github.com/repos/olahallengren/sql-server-maintenance-solution","labels_url":"https://api.github.com/repos/olahallengren/sql-server-maintenance-solution/issues/39/labels{/name}","comments_url":"https://api.github.com/repos/olahallengren/sql-server-maintenance-solution/issues/39/comments","events_url":"https://api.github.com/repos/olahallengren/sql-server-maintenance-solution/issues/39/events","html_url":"https://github.com/olahallengren/sql-server-maintenance-solution/issues/39","id":312727011,"number":39,"title":"Stored procedure version comment and database extended property","user":{"login":"drstonephd","id":37226128,"avatar_url":"https://avatars1.githubusercontent.com/u/37226128?v=4","gravatar_id":"","url":"https://api.github.com/users/drstonephd","html_url":"https://github.com/drstonephd","followers_url":"https://api.github.com/users/drstonephd/followers","following_url":"https://api.github.com/users/drstonephd/following{/other_user}","gists_url":"https://api.github.com/users/drstonephd/gists{/gist_id}","starred_url":"https://api.github.com/users/drstonephd/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/drstonephd/subscriptions","organizations_url":"https://api.github.com/users/drstonephd/orgs","repos_url":"https://api.github.com/users/drstonephd/repos","events_url":"https://api.github.com/users/drstonephd/events{/privacy}","received_events_url":"https://api.github.com/users/drstonephd/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2018-04-09T23:53:50Z","updated_at":"2018-04-10T09:58:03Z","closed_at":null,"author_association":"NONE","body":"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.\r\n`--// Source: https://ola.hallengren.com`\r\ncan become\r\n`--// Source: https://ola.hallengren.com`\r\n`--// Version: 2016-04-02`\r\n\r\nCode 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.  \r\n\r\n`USE [master] -- Specify the database in which the objects will be created.\r\n\r\nSET NOCOUNT ON\r\n\r\nDECLARE @CreateJobs nvarchar(max)\r\nDECLARE @BackupDirectory nvarchar(max)\r\nDECLARE @CleanupTime int\r\nDECLARE @OutputFileDirectory nvarchar(max)\r\nDECLARE @LogToTable nvarchar(max)\r\nDECLARE @Version numeric(18,10)\r\nDECLARE @Error int\r\nDECLARE @LastUpdatedExtPropName sysname\r\nDECLARE @LastUpdatedExtPropValue datetime\r\n\r\nSET @CreateJobs          = 'Y'          -- Specify whether jobs should be created.\r\nSET @BackupDirectory     = N'C:\\Backup' -- Specify the backup root directory.\r\nSET @CleanupTime         = NULL         -- Time in hours, after which backup files are deleted. If no time is specified, then no backup files are deleted.\r\nSET @OutputFileDirectory = NULL         -- Specify the output file directory. If no directory is specified, then the SQL Server error log directory is used.\r\nSET @LogToTable          = 'Y'          -- Log commands to a table.\r\n\r\nSET @Error = 0\r\nSET @LastUpdatedExtPropName = N'https://ola.hallengren.com last updated' -- set to NULL to disable setting the extended property\r\nSET @LastUpdatedExtPropValue = '2018-04-02 00:00:00' -- set to NULL to disable stored procedure version check\r\n\r\nSET @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))\r\n\r\nIF IS_SRVROLEMEMBER('sysadmin') = 0\r\nBEGIN\r\n  RAISERROR('You need to be a member of the SysAdmin server role to install the solution.',16,1)\r\n  SET @Error = @@ERROR\r\nEND\r\n\r\nIF OBJECT_ID('tempdb..#Config') IS NOT NULL DROP TABLE #Config\r\n\r\nCREATE TABLE #Config ([Name] nvarchar(max),\r\n                      [Value] nvarchar(max))\r\n\r\nIF @CreateJobs = 'Y' AND @OutputFileDirectory IS NULL AND SERVERPROPERTY('EngineEdition') <> 4 AND @Version < 12\r\nBEGIN\r\n  IF @Version >= 11\r\n  BEGIN\r\n    SELECT @OutputFileDirectory = [path]\r\n    FROM sys.dm_os_server_diagnostics_log_configurations\r\n  END\r\n  ELSE\r\n  BEGIN\r\n    SELECT @OutputFileDirectory = LEFT(CAST(SERVERPROPERTY('ErrorLogFileName') AS nvarchar(max)),LEN(CAST(SERVERPROPERTY('ErrorLogFileName') AS nvarchar(max))) - CHARINDEX('\\',REVERSE(CAST(SERVERPROPERTY('ErrorLogFileName') AS nvarchar(max)))))\r\n  END\r\nEND\r\n\r\nIF @CreateJobs = 'Y' AND RIGHT(@OutputFileDirectory,1) = '\\' AND SERVERPROPERTY('EngineEdition') <> 4\r\nBEGIN\r\n  SET @OutputFileDirectory = LEFT(@OutputFileDirectory, LEN(@OutputFileDirectory) - 1)\r\nEND\r\n\r\nINSERT INTO #Config ([Name], [Value])\r\nVALUES('CreateJobs', @CreateJobs)\r\n\r\nINSERT INTO #Config ([Name], [Value])\r\nVALUES('BackupDirectory', @BackupDirectory)\r\n\r\nINSERT INTO #Config ([Name], [Value])\r\nVALUES('CleanupTime', @CleanupTime)\r\n\r\nINSERT INTO #Config ([Name], [Value])\r\nVALUES('OutputFileDirectory', @OutputFileDirectory)\r\n\r\nINSERT INTO #Config ([Name], [Value])\r\nVALUES('LogToTable', @LogToTable)\r\n\r\nINSERT INTO #Config ([Name], [Value])\r\nVALUES('DatabaseName', DB_NAME(DB_ID()))\r\n\r\nINSERT INTO #Config ([Name], [Value])\r\nVALUES('Error', CAST(@Error AS nvarchar))\r\n\r\nINSERT INTO #Config ([Name], [Value])\r\nVALUES('LastUpdatedExtPropName', @LastUpdatedExtPropName)\r\n\r\nINSERT INTO #Config ([Name], [Value])\r\nVALUES('LastUpdatedExtPropValue', CONVERT(varchar(23), @LastUpdatedExtPropValue, 120))\r\nGO\r\nSET ANSI_NULLS ON\r\nGO\r\nSET QUOTED_IDENTIFIER ON\r\nGO\r\n\r\n\r\n\r\n\r\nIF EXISTS(SELECT 1 FROM #Config WHERE Name = 'LastUpdatedExtPropValue' AND ISDATE(value) = 1)\r\nBEGIN\r\n\r\n  DECLARE @CurrentVersion datetime\r\n  DECLARE @error int\r\n  DECLARE @LastUpdatedExtPropName sysname\r\n  DECLARE @LastUpdatedExtPropValue datetime\r\n  DECLARE @rowcount int\r\n  DECLARE @text nvarchar(max)\r\n  DECLARE @tempver datetime\r\n  DECLARE @VersionExtPropValue sql_variant\r\n\r\n  SELECT @LastUpdatedExtPropName = CAST(Value as sysname)\r\n  FROM #Config\r\n  WHERE [Name] = 'LastUpdatedExtPropName'\r\n\r\n  SELECT @LastUpdatedExtPropValue = CAST(Value as datetime)\r\n  FROM #Config\r\n  WHERE [Name] = 'LastUpdatedExtPropValue'\r\n\r\n  SET @CurrentVersion = @LastUpdatedExtPropValue\r\n\r\n  IF NOT @CurrentVersion IS NULL\r\n  BEGIN\r\n    SET @text = OBJECT_DEFINITION (OBJECT_ID(N'[dbo].[CommandExecute]'))\r\n    SET @tempver = CASE CHARINDEX('--// Version: ', @text) WHEN 0 THEN NULL ELSE SUBSTRING(@text, 14 + CHARINDEX('--// Version: ', @text), 23) END\r\n    IF @tempver IS NULL OR @tempver <> @CurrentVersion SET @CurrentVersion = NULL\r\n  END\r\n\r\n  IF NOT @CurrentVersion IS NULL\r\n  BEGIN\r\n    SET @text = OBJECT_DEFINITION (OBJECT_ID(N'[dbo].[DatabaseBackup]'))\r\n    SET @tempver = CASE CHARINDEX('--// Version: ', @text) WHEN 0 THEN NULL ELSE SUBSTRING(@text, 14 + CHARINDEX('--// Version: ', @text), 23) END\r\n    IF @tempver IS NULL OR @tempver <> @CurrentVersion SET @CurrentVersion = NULL\r\n  END\r\n\r\n  IF NOT @CurrentVersion IS NULL\r\n  BEGIN\r\n    SET @text = OBJECT_DEFINITION (OBJECT_ID(N'[dbo].[DatabaseIntegrityCheck]'))\r\n    SET @tempver = CASE CHARINDEX('--// Version: ', @text) WHEN 0 THEN NULL ELSE SUBSTRING(@text, 14 + CHARINDEX('--// Version: ', @text), 23) END\r\n    IF @tempver IS NULL OR @tempver <> @CurrentVersion SET @CurrentVersion = NULL\r\n  END\r\n\r\n  IF NOT @CurrentVersion IS NULL\r\n  BEGIN\r\n    SET @text = OBJECT_DEFINITION (OBJECT_ID(N'[dbo].[IndexOptimize]'))\r\n    SET @tempver = CASE CHARINDEX('--// Version: ', @text) WHEN 0 THEN NULL ELSE SUBSTRING(@text, 14 + CHARINDEX('--// Version: ', @text), 23) END\r\n    IF @tempver IS NULL OR @tempver <> @CurrentVersion SET @CurrentVersion = NULL\r\n  END\r\n\r\n  IF @CurrentVersion IS NULL\r\n  BEGIN \r\n    RAISERROR('Warning: The maintenance stored procedures installed are not current.',16,1)\r\n    SET @Error = @@ERROR\r\n  END\r\n\r\n  IF LEN(@LastUpdatedExtPropName) > 0\r\n  BEGIN\r\n\r\n    SELECT @VersionExtPropValue = [value]\r\n    FROM sys.extended_properties\r\n    WHERE class = 0 AND major_id = 0 AND minor_id = 0 AND [name] = @LastUpdatedExtPropName;\r\n\r\n    SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT\r\n\r\n    IF @rowcount = 0 \r\n      EXEC sys.sp_addextendedproperty @name = @LastUpdatedExtPropName, @value = @CurrentVersion;\r\n    ELSE IF @VersionExtPropValue <> @CurrentVersion OR (@VersionExtPropValue IS NULL AND NOT @CurrentVersion IS NULL) OR (NOT @VersionExtPropValue IS NULL AND @CurrentVersion IS NULL) \r\n      EXEC sys.sp_updateextendedproperty @name = @LastUpdatedExtPropName, @value = @CurrentVersion;\r\n\r\n  END\r\n\r\nEND`\r\n\r\n "},"comment":{"url":"https://api.github.com/repos/olahallengren/sql-server-maintenance-solution/issues/comments/380043822","html_url":"https://github.com/olahallengren/sql-server-maintenance-solution/issues/39#issuecomment-380043822","issue_url":"https://api.github.com/repos/olahallengren/sql-server-maintenance-solution/issues/39","id":380043822,"user":{"login":"olahallengren","id":29208801,"avatar_url":"https://avatars1.githubusercontent.com/u/29208801?v=4","gravatar_id":"","url":"https://api.github.com/users/olahallengren","html_url":"https://github.com/olahallengren","followers_url":"https://api.github.com/users/olahallengren/followers","following_url":"https://api.github.com/users/olahallengren/following{/other_user}","gists_url":"https://api.github.com/users/olahallengren/gists{/gist_id}","starred_url":"https://api.github.com/users/olahallengren/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/olahallengren/subscriptions","organizations_url":"https://api.github.com/users/olahallengren/orgs","repos_url":"https://api.github.com/users/olahallengren/repos","events_url":"https://api.github.com/users/olahallengren/events{/privacy}","received_events_url":"https://api.github.com/users/olahallengren/received_events","type":"User","site_admin":false},"created_at":"2018-04-10T09:58:03Z","updated_at":"2018-04-10T09:58:03Z","author_association":"OWNER","body":"Thank you for the feedback. One question:\r\n\r\nLet's say that I release a new version, where I have only updated one of the stored procedures.\r\n\r\nThe date in MaintenanceSolution.sql would then get updated.\r\n\r\nNow 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?"}}
{"actor":{"display_login":"olahallengren"}}