Modify Nav Database for automatic source code archiving

I searched a way to automaticly export Nav objects source code when modified/compiled. This save the powershell export command time. This was destinated to a TFS server and structured by object type but you can easly modify file name/structure as you want if you’re ok with SQL.

Warning, this modification do not concern Azure Nav installations. And this is actually not working with separated SQL server than Nav service, SQL need to access finsql.exe.

I Found that you can influence interessting SQL trigger. Here is the magic trick, when you create or recompil a Nav object, the insert or update trigger is call on this SQL system table. We can use this trigger to export Nav object with finsql export command. Download link at the bottom.

NAV PART

Create a record with following information that will be needed by the archiving system :
– finsql.exe path (autmatically retireved in Nav)
– Archiving activation
– Archive root folder (with objects type subfolder in)
– Debugg mode (keep navcommandresult.txt created by finsql)
And associated page

/!\ Do not rename thoses objects or any fields (see why in SQL part)

And you will need those subfolder to get it work (sql can’t create them it self) : Codeunit, Menusuite, Page, Query, Report, Table, Xmlport

I didn’t create a menu hook to allow this page exclusively from dev. environement.

SQL PART

I created a sql script that export Nav object as text. To do this you need to use finsql command discuss above. I’ve used xp_cmdshell SQL command. xp_cmdshell is disable by default on all SQL server installation (it’s a security issue but this trick is for dev. base so I guess it’s ok). I create an all in one sql script that enable xp_cmdshell, create the trigger then affect the code to execute when you create or modify an object 🙂

This is the interesting part when SQL call the finsql command in the trigger :

DOWNLOAD

Nav-Automatic-Source-Code-Archiving-1.zip (tested on Nav 2017)

 

How to install :

DO NOT EXECUTE SQL BEFORE IMPORTING OBJECTS IN NAV

1. Import the txt or fob table and page in Nav (fob are compiled in 2017 version), be sure the table 78000 is compiled
2. Go to nav page 7800 to configure the functionnality (root folder must finish with a “/” and contain object type subfolders)
3. Open SQL management studio and execute the query “SQL installation.sql/txt”
4. Juste open any object and save it back, If no file is created in provided folder check for a log file or navcommandresult.txt in root folder

 

How to uninstall :

1. Open SQL management studio and find your database table named “Object”
2. Expand/open the table Object, then expend the Trigger folder
3. Delete “Object_Source Control_Create” and “Object_Source_Control_Delete” (right click delete or disable)
4. Delete table and page in Nav is optionnal

“The Object table does not exist” Error when saving/compiling objects :'(

SQL try to do select on the 78000 table that do not exist.
Do not rename or remove the table or any fields in it if SQL trick is enable!!
Don’t worry, just delete the trigger as explain in “How to uninstall” or reimport original 78000 tabl