AX365 get Enum values from SQL through Enum name

 select t1.*, t2.* from ENUMIDTABLE t1 inner join ENUMVALUETABLE t2 on t1.ID=t2.ENUMID where t1.NAME='InventTransType'

SQL Server query to get all the deadlock process

SELECT db.name DBName, tl.request_session_id, wt.blocking_session_id, OBJECT_NAME(p.OBJECT_ID) BlockedObjectName, tl.resource_type, h1.TEXT AS RequestingText, h2.TEXT AS BlockingTest, tl.request_mode FROM sys.dm_tran_locks AS tl INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1 CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2

Get Enum value from SQL AX 2012 /365

 CREATE FUNCTION [dbo].[ENUM2STR](@name AS varchar(40), @value AS int)

RETURNS varchar(255)
AS
BEGIN
 DECLARE @bin AS varbinary(MAX);
 SET @bin = (SELECT TOP 1 Properties
    FROM MicrosoftDynamicsAXModel.dbo.ModelElement me
    JOIN MicrosoftDynamicsAXModel.dbo.ModelElementData med
     ON med.ElementHandle = me.ElementHandle
    WHERE me.Name = @name
     AND me.ElementType = 40
    ORDER BY med.LayerId DESC);
 DECLARE @pos AS int;
 DECLARE @flags AS int;
 DECLARE @count AS int;
 DECLARE @idx AS int;
 DECLARE @off AS int;
 DECLARE @ret AS varchar(255);
 SET @pos = 3;
 SET @off = CAST(SUBSTRING(@bin, @pos, 1) AS int) - 1;
 SET @pos = @pos + 1;
 WHILE @off > 0 --skip BaseEnum Label/Help/CountryRegionCode
	BEGIN
		WHILE SUBSTRING(@bin, @pos, 2) <> 0x0000
			SET @pos = @pos + 2;
		SET @pos = @pos + 2;
		SET @off = @off - 1;
	END
 SET @flags = CAST(SUBSTRING(@bin, @pos, 3) AS int);
 SET @pos = @pos + 3;
 IF @flags & 0x008000 = 0x008000 --skip BaseEnum ConfigurationKey
	BEGIN
		WHILE SUBSTRING(@bin, @pos, 2) <> 0x0000
			SET @pos = @pos + 2;
		SET @pos = @pos + 2;
	END
 IF @flags & 0x000002 = 0x000002 --skip BaseEnum ConfigurationKey
	SET @pos = @pos + 1;
 SET @pos = @pos + 1; --skip DisplayLength
 SET @count = CAST(SUBSTRING(@bin, @pos, 1) AS int);
 IF @count > 0
	BEGIN
		SET @pos = @pos + 1;
		IF @flags & 0x000200 = 0x000200 --UseEnumValue property
			SET @idx = @value;
		ELSE
			BEGIN
				SET @idx = 0;
				SET @off = 2 + CAST(CAST(REVERSE(SUBSTRING(@bin, @pos, 2)) AS binary(2)) AS int) * 2;
				SET @off = @off + 2 + CAST(CAST(REVERSE(SUBSTRING(@bin, @pos + @off, 2)) AS binary(2)) AS int) * 2;
				WHILE CAST(SUBSTRING(@bin, @pos + @off + @idx, 1) AS int) <> @value AND @idx < @count
					SET @idx = @idx + 1;
				IF CAST(SUBSTRING(@bin, @pos + @off + @idx, 1) AS int) <> @value
					SET @idx = -1;
			END
		IF @idx >= 0
			BEGIN
				SET @pos = @pos + 2;
				WHILE 1 = 1
					BEGIN
						SET @off = 0;
						SET @ret = '';
						WHILE SUBSTRING(@bin, @pos + @off, 2) <> 0x0000
							BEGIN
								SET @ret = @ret + CHAR(CAST(REVERSE(SUBSTRING(@bin, @pos + @off, 2)) AS binary(2)));
								SET @off = @off + 2;
							END
						SET @pos = @pos + @off + 2;
						IF @idx <= 0
							BREAK;
						SET @idx = @idx - 1;
					END
			END
		ELSE
			SET @ret = '<NOT FOUND';
	END
 ELSE
	SET @ret = '<ERROR>';
 IF SUBSTRING(@ret, 1, 1) = '@' --label file
	BEGIN
		DECLARE @module AS varchar(3);
		DECLARE @label AS int;
		SET @module = SUBSTRING(@ret, 2, 3);
		SET @label = CAST(SUBSTRING(@ret, 5, DATALENGTH(@ret) - 4) AS int);
		SET @ret = (SELECT TOP 1 Text FROM MicrosoftDynamicsAXModel.dbo.ModelElementLabel
					WHERE LabelId = @label
						AND Module = @module
						AND Language = 'en_us'
					ORDER BY LayerId DESC);
	END
 RETURN @ret;
END

AX365/2012 X++ SQL select statement inside AX

 

  Connection      connection;

        Statement       statement;

        str             query;

        Resultset       resultSet;

        container conComm;

        ;

        // create connection object

        connection = new Connection();

        // create statement

        statement = connection.createStatement();

        // Set the SQL statement

        query = "select ACQUISITIONPRICE,BookID,BookType,Status from AssetBookMerge where AssetID = '" + strAssetId + "'";

        // assert SQL statement execute permission

        new SqlStatementExecutePermission(query).assert();

        // when the query returns result,

        // loop all results for processing

        //BP Deviation documented

        resultSet = statement.executeQuery(query);

        while(resultSet.next())

        {

            // do something with the result

            conComm =[resultSet.getString(1),resultSet.getString(2),resultSet.getString(3),resultSet.getString(4)] ;

        }

        // limit the scope of the assert call

        CodeAccessPermission::revertAssert();

        return conComm;

SQL Server T-SQL check the query execution status

 SELECT percent_complete, start_time, status, command, estimated_completion_time, cpu_time, total_elapsed_time

FROM sys.dm_exec_requests