X++ Job to list all the elements which are not included in version control

 X++ Job to list all the elements which are not included in version control

static void listNonVersionControlled(Args _args)
{
    SysVersionControllable  controllable;
    SysModelElement         sme_root, sme;
    SysModelElementData     smeData;
    SysModelLayer           layer;   
   
    setPrefix('Not version controlled elements');
   
    while select sme_root
        order by elementType
        exists join sme
        exists join smeData
        exists join layer
         where sme.RootModelElement     == sme_root.RecId
            && sme.RecId                == smeData.ModelElement       
            && smeData.layer            == layer.RecId          
            && smeData.ModelId          == xInfo::getCurrentModelId()
            && layer.Layer              == currentAOLayer()
        {       
        controllable = SysTreeNode::newTreeNodePath(SysTreeNode::modelElement2Path(sme_root));       
       
        if (VersionControl.parmSysVersionControlSystem().allowCreate(controllable))
        {
            info(SysTreeNode::modelElement2Path(sme_root));
        }
    }
}

Powershell command to list all the elements in AX model and DB Sync

                               Powershell commands

Install-AXModel

Parameter Set: Default
Install-AXModel -File  [-Config  ] [-Conflict  ] [-CreateParents] [-Database  ] [-Details] [-NoOptimize] [-NoPrompt] [-Replace  ] [-Server  ] [-TargetLayer  ] [ ] 

PS C:\>Install-AXModel -File MyModel.axmodel -Conflict Push

Export-AXModel

Parameter Set: Default
Export-AXModel -File  -Model  [-Config  ] [-Database  ] [-Key  ] [-ManifestFile  ] [-Server  ] [ ]  

PS C:\>Export-AXModel -model Packaging -file c:\models\PackagingSigned.axmodel -key c:\keys\mykey.snk

 ----------------------------------------------------------------------------------------------------
Command to list all the elements in the model in AX (Installed )
(Note: here -Model 15 is user layer model)
------------------------------------------------------------------------------------
&"C:\Program Files\Microsoft Dynamics AX\60\ManagementUtilities\Microsoft.Dynamics.ManagementUtilities.ps1"
$s = 'D-WBX-DEV06'
$d = 'Weetabix_Dev_Model'
(get-axmodel -server $s -database $d -model 15 -details).elements | format-table -property path -autosize
--------------------------------------------------------------------------------------------------------------------

Command to list all the elements in the model File (Not Installed).
-------------------------------------------------------------------------------------
(get-axmodel -server $s -database $d -file "C:\Lasernet\Lasernet Connector for Microsoft Dynamics AX2012 R3 CU13\Model\VAR\LAC_VAR_5002_17102018_110314_AX2012R3CU13.axmodel" -details).elements | format-table -property path -autosize

Ex:
$filepath = "F:\DevOpsRepository\Builds\1.0.0.316\Application\Appl\MK.axmodel"
$s= "DataBaseServerName"
$d= "AXDatabaseName"
(get-axmodel -server $s -database $d -file $filepath -details).elements | format-table -property path -autosize | Out-File -FilePath "F:\Tmp\WCC model content.txt" -NoClobber
-------------------------------------------------------------------------------------------------------------

AX DB Sync
---------------------------------------------------------------------------------------------------------
$axProcess = Start-Process -PassThru ($axClientPath + "C:\Program Files (x86)\Microsoft Dynamics AX\60\Client\Bin\Ax32.exe") -ArgumentList ($params + " -StartupCmd=Synchronize")
if ($axProcess.WaitForExit($AXSYNCTIMEOUT) -eq $false)
{
    Throw ("Error: Synchronize did not complete within " + $AXSYNCTIMEOUT / 60000 + " minutes")
}
---------------------------------------------------------------------------------------------------------------

Export list of model elements to Excel using PowerShell

(Get-AXModel -Model 'MyModel' -Details).Elements | select path, elementtype | `

Export-Csv -NoTypeInformation -Delimiter ';' c:\mymodel.csv

--------------------------------------------------------------------------

Uninstall-AXModel

Parameter Set: Default
Uninstall-AXModel -Model [-Config ] [-Database ] [-Details] [-Layer ] [-ManifestFile ] [-NoPrompt] [-Server ] [ ] 



PS C:\>Uninstall-AXModel -Model TestModel -Details

SQL DB backup and Restore script

Backup DB

USE [master]
BACKUP DATABASE [DB_NAME] TO  DISK = N'F:\SQLBackup\build_AxDB.bak' WITH NOFORMAT, INIT,  NAME = N'DBNAME', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
BACKUP DATABASE [AB_NAME_model] TO  DISK = N'F:\SQLBackup\build_AxDB_model.bak' WITH NOFORMAT, INIT,  NAME = N'DBNAME', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10

GO

Restore DBs

#USE [master]

RESTORE DATABASE [DB_NAME] FROM  DISK = N'F:\SQLBackup\build_AxDB.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5

Find clients using the DB

DECLARE @AllConnections TABLE(
    SPID INT,
    Status VARCHAR(MAX),
    LOGIN VARCHAR(MAX),
    HostName VARCHAR(MAX),
    BlkBy VARCHAR(MAX),
    DBName VARCHAR(MAX),
    Command VARCHAR(MAX),
    CPUTime INT,
    DiskIO INT,
    LastBatch VARCHAR(MAX),
    ProgramName VARCHAR(MAX),
    SPID_1 INT,
    REQUESTID INT
)
INSERT INTO @AllConnections EXEC sp_who2
SELECT * FROM @AllConnections WHERE DBName = 'DB_NAME'

To Kill

USE [master];

DECLARE @kill varchar(8000) = '';  

SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'  

FROM sys.dm_exec_sessions

WHERE database_id  = db_id('DBNAME')

EXEC(@kill);

Find Top #n tables with max size

USE [D_WBX_BUILD]
select top 30 schema_name(tab.schema_id) + '.' + tab.name as [table],
    cast(sum(spc.used_pages * 8)/1024.00 as numeric(36, 2)) as used_mb,
    cast(sum(spc.total_pages * 8)/1024.00 as numeric(36, 2)) as allocated_mb
from sys.tables tab
join sys.indexes ind
     on tab.object_id = ind.object_id
join sys.partitions part
     on ind.object_id = part.object_id and ind.index_id = part.index_id
join sys.allocation_units spc
     on part.partition_id = spc.container_id
group by schema_name(tab.schema_id) + '.' + tab.name
order by sum(spc.used_pages) desc;


Command to delete data from the table

use [DB_NAME]
--    TRUNCATE TABLE        dbo.DMFWBXASNPACKSTRUCTUREENTITY