FlushsystemSequence

 static void FlushsystemSequence(Args _args)

{

    systemSequence              systemSequence = new  systemSequence();

    int                         tablenumber = 65482;//tableNum(SysEvent);

    ;

    info(int2str(tablenumber));

    systemSequence.suspendRecIds(tablenumber);

    systemSequence.suspendTransIds(tablenumber);

    systemSequence.flushValues(tablenumber);

    systemSequence.removeRecIdSuspension(tablenumber);

    systemSequence.removeTransIdSuspension(tablenumber);

    //new numberseqnumcache().flushcacheinternal(12345);

    //new numberseqnumcache().flushcache(12345);

    //systemsequence.flush

}

SQL error description: [Microsoft][SQL Server Native Client 11.0][SQL Server]There is already an object named 'I_XXXXXRECID' in the database.

1. Temporarily change the ConfigurationKey on the table to SysDeletedObjects63.
2. Right-click the table, click Synchronize. It will delete the table from the SQL DB.
3. Try to run full DB sync again, make sure there are no errors.
4. Delete the table from the layer you are working in. It will restore the SYS layer version of the table with ConfigurationKey to the old attribute.
5. Right-click the table, click Synchronize. It will create the table in the SQL DB. If you start getting DB sync errors at that point it will mean something else is wrong in your DB e.g. another table has an index with the same name (I_XXXXRECID) or something like that.

This query for check where index was store.

select object_name(object_id) from sys.indexes WHERE name =  'I_100013RECID'

SELECT OBJECT_NAME(object_id) AS TableName, * FROM sys.indexes AS I ORDER BY TableName, i.name;

Microsoft Dynamics AX R3 – New Warehouse Management solutions impact on InventDim extensibility and migration scenarios

To enable license plate dimension for warehouse management you need to enable Inventoty dimension extensibility.

The class you need to execute is InventDimUniquenessEnabling .


Reference link
https://blogs.msdn.microsoft.com/dynamicsaxscm/2015/08/13/microsoft-dynamics-ax-r3-new-warehouse-management-solutions-impact-on-inventdim-extensibility-and-migration-scenarios/

วิธีดูว่า table ไหน field ไหนเป็น NULL

DECLARE @tb NVARCHAR(255) = N'dbo.[table]';

DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM ' + @tb
    + ' WHERE 1 = 0';

SELECT @sql += N' OR ' + QUOTENAME(name) + ' IS NULL'
    FROM sys.columns
    WHERE [object_id] = OBJECT_ID(@tb);

EXEC sp_executesql @sql;

วิธีดู user ที่ online อยู่ใน SQL

exec sp_who

คำสั่งหยุดการทำงานของ thread นั้น

kill [XXX]

Dynamics Ax 2012 Error Code 90, Unable to connect to database

-- This query will rollback any transaction which is running on that database

-- and bring SQL Server database in a "single user" mode

ALTER DATABASE DATABASENAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE

-- Marking database READ_ONLY, disable logging,

-- and limiting access only to members of the sysadmin fixed server role

ALTER DATABASE DATABASENAME SET EMERGENCY

-- Checks the logical and physical integrity of all the objects in the specified database

DBCC checkdb(DATABASENAME)

-- In case of simple failure then we can use allow data loss option to recover Database

--  DBCC CheckDB (DATABASENAME, REPAIR_ALLOW_DATA_LOSS)

-- Set database accessibility to it's original state, allowing all logins

ALTER DATABASE DATABASENAME SET MULTI_USER

ALTER DATABASE DATABASENAME  SET ONLINE;

EXEC sp_resetstatus DATABASENAME

Monitor how many memory usage by index in SQL server

sys.dm_os_buffer_descriptors - Returns information about all the data pages that are currently in the SQL Server buffer pool

sys.dm_db_index_usage_stats - Returns counts of different types of index operations and the time each type of operation was last performed in SQL Server.

These query for search index usage in buffer pool

SELECT count(*) AS cached_pages_count, obj.name, index_id, i.name AS IndexName
FROM sys.dm_os_buffer_descriptors AS bd
    INNER JOIN
    (
        SELECT object_id, object_name(object_id) AS name
            ,index_id ,allocation_unit_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p
                ON au.container_id = p.hobt_id
                    AND (au.type = 1 OR au.type = 3)
        UNION ALL
        SELECT object_id, object_name(object_id) AS name  
            ,index_id, allocation_unit_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p
                ON au.container_id = p.partition_id
                    AND au.type = 2
    ) AS obj
        ON bd.allocation_unit_id = obj.allocation_unit_id
    LEFT JOIN sysindexes i ON obj.object_id = i.id AND obj.index_id = i.indid
WHERE database_id = db_id()
GROUP BY obj.name, index_id, i.name
--HAVING obj.name = 'INVENTSERIAL'

ORDER BY cached_pages_count DESC;