Usefull Query information (SQL and AX)

//Usefull Query information (SQL and AX)
--Check the Microsoft Dynamics AX Kernel and Application versions installed with the restored database.
select * from SYSSETUPLOG where DESCRIPTION = 'finished' order by CREATEDDATETIME desc

select * from SYS.sysprocesses --Query get to Some blocked object information
sp_GetNextRecId - genearte next recid seq
select * from SystemSequences - check next RecId seq for specific table
select * from modelElement where Name = 'objectname' --based on element type, Query will return are parent and root object in ax
select * from ElementTypes - Related objects in AX

--The record returned at the top of the list indicates the last time the setup or upgrade checklist was run, note the APPBUILD and KERNELBUILD versions.
select distinct(KERNELBUILD) from SYSSETUPLOG
select * from SysLastValue -- AX usage data records
select * from sysClientSessions - AX login user list
select * from sysServerSessions - AX server login list
select SID, Networkdomain, networkalias from userinfo where networkalias = 'Admin' --user information records
select * from SysBCProxyUserAccount
select * from SysWorkflowParameters
select * from SysServerConfig
select * from BatchServerConfig
select * from SysClusterConfig
select * from BatchGroup
select * from Batch
update batch set SERVERID = '<01@NewAOS>' where serverid = '<02@OldAOS>'
update sysserverconfig set enablebatch = 1 where serverid = '<01@NewAOS>'
update BATCHSERVERCONFIG set SERVERID = '<01@NewAOS>' where serverid = '<02@OldAOS>'
select * from SYSEMAILPARAMETERS
select * from SYSEMAILSMTPPASSWORD
select * from SYSGLOBALCONFIGURATION
select * from SysFileStoreParameters
select * from BIANALYSISSERVER
select * from BICONFIGURATION
select * from SRSSERVERS
select * from EPGLOBALPARAMETERS
select * from EPWEBSITEPARAMETERS
select * from COLLABSITEPARAMETERS
select * from SYNCPARAMETERS
select * from AIFWEBSITES
select * from SysSQMSettings
AxUpdatePortal -listvirtualservers
AxUpdatePortal -redeploy [-updatewebsites] [-iisreset]
AxUpdatePortal -deploy [-createsite] -websiteurl <value>
AxUpdatePortal -createsite -websiteurl <value>
AxUpdatePortal -updateall -websiteurl <value>
AxUpdatePortal -proxies -websiteurl <value>
AxUpdatePortal -images -websiteurl <value>
AxUpdatePortal -updatewebcomponent -treenodepath <value>  -websiteurl <value>

//DB backup script
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(100) -- used for file name
Declare @fullpath nvarchar(250)
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name IN (
'AXDB_BI1',
'AXDB_BI1_model',
'AXDB_DEV1',
'AXDB_DEV1_model'
)

SET @path = 'F:\AzureShare\SQL_Backup\';
OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @name 
WHILE @@FETCH_STATUS = 0 
BEGIN 
       SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)                        
       SET @fileName = @name +'_'+@fileDate +'.BAK'
       SET @fullpath=@path+@fileName;
       SET @fileDate=''''+@fileName+'''Full Backup';
       print @name
       Backup Database @name TO
                   DISK=@fullpath
       WITH NOFORMAT,NOINIT,
                   NAME=@fileDate,
                   SKIP,NOREWIND,NOUNLOAD,STATS=10
      
       FETCH NEXT FROM db_cursor INTO @name 
END 
CLOSE db_cursor 
DEALLOCATE db_cursor
//Shrinking DB ax 2012
USE MicrosoftDynamicsAX;
ALTER DATABASE DBNAME
SET RECOVERY SIMPLE;
DBCC SHRINKFILE (DBNAME_Log, 0);
ALTER DATABASE DBNAME
SET RECOVERY FULL;
//Finding Modified Element in SQL DB model
select E.[Name] as [Element Name],
EY.[ElementTypeName] as [Element Type],
E1.[Name] as [Root Element Name],
EY1.[ElementTypeName] as [Root Element Type],
ED.[LayerId],
L.[Name] as [Layer Name],
ED.[MODIFIEDDATETIME],
ED.[MODIFIEDBY],
ED.[CREATEDDATETIME],
ED.[CREATEDBY],
ED.[ElementVersion],
ED.[ModelId]
from [ModelElement] as E
inner join [ModelElementData] as ED on E.[ElementHandle] = ED.[ElementHandle]
inner join [ModelElement] as E1 on E1.[ElementHandle] = E.[RootHandle]
inner join [ElementTypes] as EY on EY.[ElementType] = E.[ElementType]
inner join [ElementTypes] as EY1 on EY1.[ElementType] = E1.[ElementType]
inner join [Layer] as l on l.[Id] = ED.[LayerId]
where ED.[LayerId] = 10
//Get Server /DB/ Domain name in x++
    str               database,servername;
   InteropPermission   permission;
    str                 userName;
    str                 userDomain;
    ;
    database        =   SysSQLSystemInfo::construct().getloginDatabase();
    servername   =   SysSQLSystemInfo::construct().getLoginServer();
    info(strFmt("ServerName - %1  Data Base -- %2",servername,database));
   
    permission = new InteropPermission(InteropKind::ClrInterop);
    permission.assert();
    userDomain  = System.Environment::get_UserDomainName();
    userName    = System.Environment::get_UserName();
    info(strFmt(@"%1\%2", userDomain, userName));
//Ax Build in AX 2012
Delete Log file fromt his Folder Path:
C:\Users\administrator.FCCI\Microsoft\Dynamics Ax\Log
C:\Program Files\Microsoft Dynamics AX\60\Server\MicrosoftDynamicsAX\Log
Cmd
//C:\Program Files (x86)\Microsoft Dynamics AX\6.0\Client\Bin
C:\Program Files\Microsoft Dynamics AX\60\Server\MicrosoftDynamicsAX\bin
To Start AX build
axbuild.exe  xppcompileall  /s=01 /altbin="C:\Program Files (x86)\Microsoft Dynamics AX\6.0\Client\Bin"
axbuild.exe  xppcompileall  /s=01

// Deploy POS Report AX
Retail > Periodic > Data distribution > Distribution schedule.
Select job, click Run
//Deploy EP Pages
cd C:\Program Files\Microsoft Dynamics AX\60\Setup
AxUpdatePortal -updateWebSites -iisreset -verbose > "C:\EPUpdate.log"
//Deploy ssrs Reprot in Management shell
open Dynamics ax 2012 management shell run as administrator
Get-AXReport -ReportName *
Publish-AXReport –Id SSRSConfigID -ReportName ReportName,Report
Publish-AXReport –Id SSRSConfigID –ReportName *
Publish-AXReport –ReportName *