Rename .mdf and .ldf files

Try the below steps to rename the .mdf and .ldf files

1) Run the below command to get the logical file name's and physical file names of the DB.

USE AXDB

GO

SELECT file_id, name as [logical_file_name], physical_name

FROM sys.database_files

 Result : 

2) Disconnect the existing connecting and bring the DB to offline by running below scripts

USE [master];

GO

--Disconnect all existing session.

ALTER DATABASE AXDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

--Change database in to OFFLINE mode.

ALTER DATABASE AXDB SET OFFLINE

3)  Refresh the DB's and check the status of AXDB should be offline

4)  Go to file locations and change the files names

5) then run the below cmd

ALTER DATABASE AXDB MODIFY FILE (Name='AXDBUAT', FILENAME='G:\MSSQL_DATA\AXDB.mdf')

GO

ALTER DATABASE AXDB MODIFY FILE (Name='AXDBUAT_log', FILENAME='H:\MSSQL_LOGS\AXDB_log.ldf')

GO

6) To bring back the DB online run below cmd

USE [master];

GO

ALTER DATABASE AXDB SET ONLINE

Go

ALTER DATABASE AXDB SET MULTI_USER

Go

7) Run the below cmd to check and confirm the status of the DB 

Select name as [AXDB],state_desc from sys.databases 

8) ReRun the below cmd to see the changed names

Sample excel export job (X++)

 static void CustomerDetailsExport(Args _args)

{

CustTable CustTable;

SysExcelApplication application;

SysExcelWorkbooks workbooks;

SysExcelWorkbook workbook;

SysExcelWorksheets worksheets;

SysExcelWorksheet worksheet;

SysExcelCells cells;

SysExcelCell         cell;

int                          row;

DimensionAttributeValueSetStorage  dimstorage;

int         i = 0;

str         fileName    = "C:\\Users\\XXX\\Desktop\\CustDetailsWithFinDim.xlsx";

;

application = SysExcelApplication::construct();

workbooks = application.workbooks();

workbook = workbooks.add();

worksheets = workbook.worksheets();

worksheet = worksheets.itemFromNum(1);

cells = worksheet.cells();

cells.range('A:A').numberFormat('@');

cell = cells.item(1,1);

cell.value("Customer");

cell = cells.item(1,2);

cell.value("Name");

cell = cells.item(1,3);

cell.value("Warehouse");

cell = cells.item(1,4);

cell.value("CostCenter");

cell = cells.item(1,5);

cell.value("Department");

cell = cells.item(1,6);

cell.value("BusinessUnit");

cell = cells.item(1,7);

cell.value("SalesType");

row = 1;

while select CustTable

    where CustTable.AccountNum 

{

    row++;

    cell = cells.item(row, 1);

    cell.value(CustTable.AccountNum);

    cell = cells.item(row, 2);

    cell.value(CustTable.name());

    dimstorage = DimensionAttributeValueSetStorage::find(CustTable.DefaultDimension);

    for(i=1;i <= dimstorage.elements();i++)

    {

       if(DimensionAttribute::find(dimstorage.getAttributeByIndex(i)).Name == 'Warehouse')

       {

            cell = cells.item(row, 3);

            cell.value(dimstorage.getDisplayValueByIndex(i));

       }

       if(DimensionAttribute::find(dimstorage.getAttributeByIndex(i)).Name == 'CostCenter')

       {

            cell = cells.item(row, 4);

            cell.value(dimstorage.getDisplayValueByIndex(i));

       }

       if(DimensionAttribute::find(dimstorage.getAttributeByIndex(i)).Name == 'Department')

       {

            cell = cells.item(row, 5);

            cell.value(dimstorage.getDisplayValueByIndex(i));

       }

        if(DimensionAttribute::find(dimstorage.getAttributeByIndex(i)).Name == 'BusinessUnit')

       {

            cell = cells.item(row, 6);

            cell.value(dimstorage.getDisplayValueByIndex(i));

       }

        if(DimensionAttribute::find(dimstorage.getAttributeByIndex(i)).Name == 'SalesType')

       {

            cell = cells.item(row, 7);

            cell.value(dimstorage.getDisplayValueByIndex(i));

       }

    }

}

    workbook.saveAs(fileName);

    application.visible(true);

    info(strFmt('Exported %1 records',row-1));

}

Button Click Event Handler in D365 FO

 class InventQuarantineParmEnd_Form_EventHandler

{   

    /// <summary>

    ///

    /// </summary>

    /// <param name="sender"></param>

    /// <param name="e"></param>

    [FormControlEventHandler(formControlStr(InventQuarantineParmEnd, OK), FormControlEventType::Clicked)]

    public static void OK_OnClicked(FormControl sender, FormControlEventArgs e)

    {

        InventParmQuarantineOrder   InvParmQuarantineOrder;

        FormDataSource              formDataSource;

        RecId                   calendarRecId;

        FiscalCalendarPeriod    fiscalCalendarPeriod;

        TransDate               transDate;

        formDataSource = sender.formRun().dataSource(tableStr(InventParmQuarantineOrder));

        InvParmQuarantineOrder = formDataSource.cursor();

        if(InvParmQuarantineOrder.TransDate)

        {

            transDate = InvParmQuarantineOrder.TransDate;

            calendarRecId = Ledger::fiscalCalendar(CompanyInfo::find().RecId);

            fiscalCalendarPeriod = FiscalCalendarPeriod::findPeriodByCalendarDate(calendarRecId, transDate, FiscalPeriodType::Operating);

            if (fiscalCalendarPeriod.currentLedgerPeriodStatus() != FiscalPeriodStatus::Open)

            {

                throw error(strFmt("Date %1 is not open in Ledger calender. Please select date from open period", date2StrUsr(transDate, DateFlags::FormatAll)));

            }

           

        }

        if(!InvParmQuarantineOrder.TransDate)

        {

            throw error(strFmt("Please select date to proceed"));

        }

    }

}

Excel upload

 

static void excelUpload(Args _args)

{

    //dialog

    dialog d = new dialog("Excel upload Tool");

    dialogfield dExcelFilepath,dTablename,dFieldName1,dFieldName2,dFieldName3,dFieldName4,dFieldName5,dExcelStart,dExcelEnd;

    dialogfield type1,type2,type3,type4,type5;

    DialogGroup fGroup1,fGroup2,fGroup3,fGroup4,fGroup5;

    //general variables

    filepath excelFilepath;

    tablename tableName;

    FieldName fieldName1,fieldName2,fieldName3,fieldName4,fieldName5;

    str 25   fieldtype1,fieldtype2,fieldtype3,fieldtype4,fieldtype5;

    str 100    NexcelField1,NexcelField2,NexcelField3,NexcelField4,NexcelField5;

    int     excelStart,excelEnd;

    boolean ok = true;

    #AviFiles

    SysOperationProgress    progress = new SysOperationProgress();

    //dictTable variables

    dicttable dictTable;

    dictField  dictField1,dictField2,dictField3,dictField4,dictField5;

    common common;

    //excel variables

    SysExcelApplication ldA;

    SysExcelWorkbooks   ldWbs;

    SysExcelWorkbook    ldWb;

    SysExcelWorksheets  ldWss;

    SysExcelWorksheet   ldWs;

    SysExcelCells       ldCs;

    SysExcelCell        ldCA;

    SysExcelRange       ldR;

    int                 j;

    container value( str _Value,types _type,fieldName _fieldName = '')

    {

        container conLocal [1];

        dictEnum    dictEnum;

        int         i;

        ;

        switch (_type)

        {

            case types::Integer :

            conlocal[1] = [str2int(_value)];

            break;

            case types::Real :

            conlocal[1] = [str2num(_value)];

            break;

            case types::Date :

            conlocal[1] = [str2date(_value,1)];

            break;

            case types::Int64:

            conlocal[1] = [str2int64(_value)];

            break;

            case types::UtcDateTime:

            conlocal[1] = [str2datetime(_value,1)];

            break;

            case types::Enum:

            {

                if(_fieldName != '')

                {

                    dictEnum = new DictEnum(EnumName2Id(_fieldName));

                    for(i=0;i<= dictEnum.values();i++)

                    {

                        if(dictEnum.index2Label(i) == _value)

                        {

                            conlocal[1] = [dictEnum.name2Value(_value)];

                            Break;

                        }

                    }

                }

            }

            break;

            default:

            conlocal[1] = [strfmt(_value)];

            break;

        }

        return conlocal[1];

    }

    boolean validate()

    {

        Dictionary            dictionary = new Dictionary();

        tableName         tableNameLocal;

        ;

        if(!excelFilepath)

        ok = ok && checkFailed("Please give the Excel path to import data");

        if(!tableName && ok)

        ok = ok && checkFailed("Table Name is mandatory");

        if(tablename && ok)

        {

            tableNameLocal = Dictionary.tableName(tableName2Id(tableName));

            if(!tableNameLocal)

            ok = ok && checkFailed(strfmt("Table %1 does not exist",tablename));

        }

        if(!fieldName1 && ok)

        {

            ok = ok && checkfailed("Primary Key is mandatory Proceed");

        }

        if((!FieldName2 && !fieldname3 && !FieldName4 && !Fieldname5) && ok)

        {

            ok = ok && checkFailed("Atleast one field is mandatory Proceed");

        }

        if(ok && !ExcelStart || !Excelend)

        {

            ok = ok && checkFailed("Excel start and end rows are mandatory to proceed");

        }

        if(ok && (ExcelStart > Excelend))

        {

            ok = ok && checkfailed("Excel Start row number cannot be less than Excelend row number");

        }

        if((!FieldName2 || !fieldname3 || !FieldName4 || !Fieldname5) && ok )

        {

            ok = box::yesNo("All fields are not given, you still want to proceed ?",dialogbutton::Yes,"Check validation") == dialogButton::Yes;

        }

        return ok;

    }

    ;

    dExcelFilepath = d.addField(extendedtypestr(FilePath),"Excel file path","Please select the excel");

    dTableName     = d.addField(extendedTypeStr(tablename),"Table name","Please enter tablename");

    fgroup1 = d.addGroup("Primary Key");

    dFieldName1    = d.addField(extendedtypestr(FieldName),"Primary Key","Please enter Field-1");

    type1 = d.addField(extendedtypestr(fieldType),"Primary Key","Select the field type");

    fgroup2 = d.addGroup("Field list 1");

    dFieldName2    = d.addField(extendedtypestr(FieldName),"Field-1","Please enter Field-2");

    type2 = d.addField(extendedtypestr(fieldType),"Type","Select the field type");

    fgroup3 = d.addGroup("Field list 2");

    dFieldName3    = d.addField(extendedtypestr(FieldName),"Field-2","Please enter Field-3");

    type3 = d.addField(extendedtypestr(fieldType),"Type","Select the field type");

    fgroup4 = d.addGroup("Field list 3");

    dFieldName4    = d.addField(extendedtypestr(FieldName),"Field-3","Please enter Field-4");

    type4 = d.addField(extendedtypestr(fieldType),"Type","Select the field type");

    fgroup5 = d.addGroup("Field list 4");

    dFieldName5    = d.addField(extendedtypestr(FieldName),"Field-4","Please enter Field-5");

    type5 = d.addField(extendedtypestr(fieldType),"Type","Select the field type");

    dExcelStart    = d.addField(extendedtypestr(integer),"Excel Start","Enter excel Starting row");

    dExcelEnd    = d.addField(extendedtypestr(integer),"Excel End","Enter excel ending row");

    if(d.run()== noyes::Yes)

    {

        //reading dialog

        excelFilepath = dExcelFilepath.value();

        tablename = dtablename.value();

        dictTable = new dicttable(tableName2Id(tableName));

        if(!dictTable)

           throw error(strfmt("Table %1 is not found ",tableName));

        FieldName1 = dFieldName1.value();

        if(FieldName1)

            dictField1 = new dictField(dictTable.id(),fieldname2id(dictTable.id(),FieldName1));

        if(FieldName1 && !dictField1)

            throw error(strfmt("Field %1 Not found in table %2",FieldName1,tableid2name(dictTable.id())));

        FieldName2 = dFieldName2.value();

        if(FieldName2)

            dictField2 = new dictField(dictTable.id(),fieldname2id(dictTable.id(),FieldName2));

        if(FieldName2 &&!dictField2)

            throw error(strfmt("Field %1 Not found in table %2",FieldName2,tableid2name(dictTable.id())));

        FieldName3 = dFieldName3.value();

        if(FieldName3)

            dictField3 = new dictField(dictTable.id(),fieldname2id(dictTable.id(),FieldName3));

        if(FieldName3 && !dictField3)

            throw error(strfmt("Field %1 Not found in table %2",FieldName3,tableid2name(dictTable.id())));

        FieldName4 = dFieldName4.value();

        if(FieldName4)

            dictField4 = new dictField(dictTable.id(),fieldname2id(dictTable.id(),FieldName4));

        if(FieldName4 && !dictField4)

            throw error(strfmt("Field %1 Not found in table %2",FieldName4,tableid2name(dictTable.id())));

        FieldName5 = dFieldName5.value();

        if(FieldName5)

            dictField5 = new dictField(dictTable.id(),fieldname2id(dictTable.id(),FieldName5));

        if(FieldName5 && !dictField5)

            throw error(strfmt("Field %1 Not found in table %2",FieldName5,tableid2name(dictTable.id())));

        ExcelStart = dExcelStart.value();

        ExcelEnd =    dExcelEnd.value();

        if(validate())

        {

            if(winapi::pathExists(excelFilepath))//&& winapi::fileExists(excelFilepath +'test2.Xlsx'))

            {

                // reading excel

                ldA = SysExcelApplication::construct();

                ldA.visible(false);

                ldWbs = ldA.workbooks();

                ldWbs.open(excelFilepath+'\\'+'excel.Xlsx',0,true);

                ldWb = ldWbs.item(1);

                ldWss = ldWb.worksheets();

                ldws  = ldwss.itemFromName('Sheet1');

                ldCs = ldWs.cells();

                ldR = ldCs.range('A1');

                for (j=ExcelStart;j<=ExcelEnd;j++)

                {

                    progress.setCaption("Data update is in progress...");

                    progress.setAnimation(#AviUpdate);

                    progress.setTotal(ExcelEnd);

                    progress.setText(strfmt("Record %1 is been updated", j-1));

                    progress.setCount(j-1, 1);

                    ldCA         = ldCs.item(j,1);

                    NexcelField1 =  ldCA.value().bStr();

                    ldCA         = ldCs.item(j,2);

                    NexcelField2 =  ldCA.value().bStr();

                    ldCA         = ldCs.item(j,3);

                    NexcelField3 =  ldCA.value().bStr();

                    ldCA         = ldCs.item(j,4);

                    NexcelField4 =  ldCA.value().bStr();

                    ldCA         = ldCs.item(j,5);

                    NexcelField5 =  ldCA.value().bStr();

                    common = dictTable.makeRecord();

                    ttsbegin;

                    select forupdate common where common.(fieldname2id(dicttable.id(),fieldname1)) == conpeek(value(NexcelField1,type1.value(),fieldname1),1);//str2int64(NexcelField1);//;

                    if(Common)

                    {

                        if(NexcelField2 && fieldName2)

                        {

                                common.(fieldname2id(dicttable.id(),fieldname2)) = conpeek(value(NexcelField2,type2.value(),fieldname2),1);

                         }

                        if(NexcelField3 && fieldName3)

                        {

                                common.(fieldname2id(dicttable.id(),fieldname3)) = conpeek(value(NexcelField3,type3.value(),fieldname3),1);

                        }

                        if(NexcelField4 && fieldName4)

                        {

                            common.(fieldname2id(dicttable.id(),fieldname4)) = conpeek(value(NexcelField4,type4.value(),fieldname4),1);

                        }

                        if(NexcelField5 && fieldName5)

                        {

                            common.(fieldname2id(dicttable.id(),fieldname5)) = conpeek(value(NexcelField5,type5.value(),fieldname5),1);

                        }

                        common.update();

                    }

                    ttscommit;

                    common = null;

                }

                ldWbs.close();

                info(strFmt("Task has completed,Succcessfully updated %1 records",j));

            }

            else

            {

             throw error(strfmt("Please check excel path - %1",excelFilepath));

            }

        }

    }

    else

        info("Task has cancelled");

}

 Query to get list of all table level indexes ....

select i.[name] as index_name,

    substring(column_names, 1, len(column_names)-1) as [columns],

    case when i.[type] = 1 then 'Clustered index'

        when i.[type] = 2 then 'Nonclustered unique index'

        when i.[type] = 3 then 'XML index'

        when i.[type] = 4 then 'Spatial index'

        when i.[type] = 5 then 'Clustered columnstore index'

        when i.[type] = 6 then 'Nonclustered columnstore index'

        when i.[type] = 7 then 'Nonclustered hash index'

        end as index_type,

    case when i.is_unique = 1 then 'Unique'

        else 'Not unique' end as [unique],

    schema_name(t.schema_id) + '.' + t.[name] as table_view, 

    case when t.[type] = 'U' then 'Table'

        when t.[type] = 'V' then 'View'

        end as [object_type]

from sys.objects t

    inner join sys.indexes i

        on t.object_id = i.object_id

    cross apply (select col.[name] + ', '

                    from sys.index_columns ic

                        inner join sys.columns col

                            on ic.object_id = col.object_id

                            and ic.column_id = col.column_id

                    where ic.object_id = t.object_id

                        and ic.index_id = i.index_id

                            order by key_ordinal

                            for xml path ('') ) D (column_names)

where t.is_ms_shipped <> 1

and index_id > 0

order by i.[name]

A simple job to get the user related roles information

 static void HSIN_UserAndRoles(Args _args)

{

    SecurityUserRole         securityUserRole;

    SecurityRole             securityRole;

    HSecurityRoleforUser  securityRoleforUserTmp; // My own table to store the info

    boolean                  Created;

    delete_from securityRoleforUserTmp;

    while select securityRole

        join securityUserRole

            where securityUserRole.SecurityRole == securityRole.RecId &&

                  securityUserRole.User like '*'

    {

        securityRoleforUserTmp.clear();

        securityRoleforUserTmp.User     = securityUserRole.User +" "+"("+UserInfoHelp::userName(securityUserRole.User)+")";

        securityRoleforUserTmp.Role     = securityRole.AotName;

        securityRoleforUserTmp.RoleName = securityRole.Name;

        securityRoleforUserTmp.insert();

        Created = true;

    }

    if(Created)

    {

        Info(' User roles info generated  sucessfully');

    }

}

A simple job to get roles and assigned duties

 static void HRolesAndDuties(Args _args)

{

    SecurityRole            securityRole;

    SecurityRoleTaskGrant   securityRoleTaskGrant;

    SecurityTask            securityTask;

    HSecurityDutiesperUser   hsecurityDutiesperUser; // my table to store the details

    boolean                    Created;

delete_from hsecurityDutiesperUser;

    while select  securityRole

        where securityRole.AotName like '*'

            join  securityRoleTaskGrant

            where securityRoleTaskGrant.SecurityRole == securityRole.RecId

                join securityTask

                where securityTask.RecId == securityRoleTaskGrant.SecurityTask &&

                      securityTask.Type  == SecurityTaskType::Duty

    {

       hsecurityDutiesperUser.Role   = securityRole.Name;

        hsecurityDutiesperUser.Duty   = SysLabel::labelId2String(securityTask.Name) ? SysLabel::labelId2String(securityTask.Name) : securityTask.Name;

        hsecurityDutiesperUser.Type   = securityTask.Type;

        hsecurityDutiesperUser.insert();

        Created = true;

    }

    if(Created)

    {

        Info(' Roles and duties info generated successfully');

    }

}