Automated Version Management

Administrator

Automated Version Management
This is a simple utility which will enable automated version management on any Microsoft Dynamics NAV database on SQL Server 2005.

Whenever a user saves an object in Object Designer (or imports objects from a FOB file), this utility will create a object history entry together with all object information (including BLOB). Using this utility you can see all of the version history for any object, and also choose which of the saved versions is the active version of the object (the one which is available in Object Designer).

The utility consists of a fob file, the text version of the fob file, an SQL Server script file, an instructions file and a disclaimer. Fob file contains two forms and a table. The form 94092 Object Versions is used to give an Object-Designer-Look-And-Feel overview of the changes history of any object modified since the utility was installed. The SQL Server script file contains a trigger which executes against Objects table on insert or update, and maintains the history of objects so that any change ever done to any object is persisted for future reference or use.

Known limitations:
- It doesn't store initial versions of the objects. This will be added later.
- It doesn't work with "native" Microsoft Dynamics NAV Database Server or database files
- It doesn't keep true user name (it just lists either DBO or application role name)

This file is provided AS IS, with no warranties, and confers no rights. You can use it only on your own responsibility.

http://www.mibuso.com/dlinfo.asp?FileID=947

Discuss this download here.

sc00by

Hello,

I haven't loaded this but it does look quite useful =D>
What I did do though, is take a look at the trigger and other bits and thought that a similar thing we've done here for auditing changes may be a useful enhancement to the trigger and logging table.

We also capture both the user name and the machine they were logged in from - this has been quite useful for tracking down the "culprit" when changes are put in that give unexpected behaviour #-o

These two SQL Server functions can be added into the INSERT statement
system_user
host_name()

I'm sure it's pretty obvious what each one returns

Hope this is useful to somebody.

Rob.

lyot

This is really awesome... :shock:

But in order to avoid 100 versions of 1 object... couldn't it be possible
to remove versions. With the proper warnings off course.... O:)

raphael.fehlmann

I also tried the mentiones SQL statements to show the user and it worked.
Anothe suggestion is to only save entries if the date and time has changed. Since everytime when is just compile an object withour saving. It also adds a version entry with the same time.
It happend to me that i compiled all object in order to find out if all are compilable and it added a version entry for all objects.

For initializing der version management i was first running a report which just did a modify(False) on all objects.

raphael.fehlmann

if versions between are removed it doesn't work anymore. Beacuse it counts the version to find the next version no.

It would be better to find the last version and add 1 to find the next version no.
SELECT TOP(1) @Version = Version + 1 FROM dbo.[Object Version] WHERE [Type] = @Type AND ID = @ID ORDER BY Version DESC

tlarson

Run this code to perform an initial 'backup' of all objects:

USE [CronusPortal]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================

	SET NOCOUNT ON

	DECLARE @NumObjectsSaved int
	SELECT @NumObjectsSaved = COUNT([Version]) FROM dbo.[Object Version]

	IF @NumObjectsSaved <> 0 BEGIN
		PRINT N'ERROR: The ''Object Version'' table is not empty.  It must be empty to perform an initial backup';
	END ELSE BEGIN

		INSERT INTO [Object Version] ([Type], [ID], [Name], [Modified], [Compiled], [BLOB Reference], [BLOB Size],
			[Date], [Time], [Version List], [Version], [Active], [User ID])
		SELECT o.[Type], o.[ID], o.[Name], o.[Modified], o.[Compiled], o.[BLOB Reference], o.[BLOB Size], 
			o.[Date], o.[Time], o.[Version List], 1, 1, USER_NAME()
		FROM dbo.[Object] o
		WHERE o.[Type] IN (1, 2, 3, 4, 5, 6, 7)

		PRINT N'Objects Backed up to the ''Object Version'' table';

	END;

tlarson

And try using this code for the trigger. It has 2 enhancements:

1. It uses SYSTEM_USER, which seems to do a better job of grabbing the real username
2. To create a Version number, it finds the highest version number and adds one. That way, if you delete a version, it'll still be able to generate a new number

USE [CronusPortal]
GO
/****** Object:  Trigger [dbo].[Object_SaveVersion]    Script Date: 02/22/2008 10:02:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE TRIGGER [dbo].[Object_RevertToSavedVersion]
   ON  [dbo].[Object Version] 
   AFTER UPDATE
AS 
BEGIN
	SET NOCOUNT ON
	DECLARE
		@Type int,
		@ID int,
		@Version int,
		@Count int

	SELECT @Type = [Type], @ID = [ID] FROM inserted
	IF @Type IN (1, 2, 3, 4, 5, 6, 7) BEGIN

		UPDATE [Object Version] SET [Active] = 0 WHERE [ID] = @ID AND [Type] = @Type

		SELECT @Version = ISNULL(MAX([Version]),1)+1 FROM dbo.[Object Version] WHERE [Type] = @Type AND ID = @ID

		INSERT INTO [Object Version] ([Type], [ID], [Name], [Modified], [Compiled], [BLOB Reference], [BLOB Size],
			[Date], [Time], [Version List], [Version], [Active], [User ID])
		SELECT o.[Type], o.[ID], o.[Name], o.[Modified], o.[Compiled], o.[BLOB Reference], o.[BLOB Size], 
			o.[Date], o.[Time], o.[Version List], @Version, 1, SYSTEM_USER
		FROM dbo.[Object] o INNER JOIN inserted i ON i.[Type] = o.[Type] AND i.[ID] = o.[ID]

	END
END

dgroeser

This is very usefull, but i have a question.

i would save the vomplete C/AL Code additional per SQL-Trigger, my idea is the per sql trigger the txt export per handler (com-dll) and import the file per sql trigger too, could your help me??