error: Cannot create record. The Record already exists.

Problem :
Our client recently facing this error during creating any journal lines (LedgerJournalTrans) .

we did restart aos, full CIL, and any standard technical troubleshoot hoping it was a cache error or something like that. And we tried drop the table and inject it with the copied data. After doing some checking on table SystemSequences with table id 212 (ledgerJournalTrans), its nextVal RecId value record is already exist on that table.

reference for RecId on AX :http://www.axaptapedia.com/RecId

solution :
1. Stop the AOS
2. get the max recid from the table on SQL with:

select max(recId) from ledgerjournaltrans;

3. update the systemSequences table on SQL with:

update SYSTEMSEQUENCES set NEXTVAL = 'MAXRECID from table' where tabid= 'tableID'

4. restart AOS and run AX

Conclusion :
they did not doing some sort of AX Version upgrade that will impact whole data
and the main problem why the AX stored procedure on SQL returning next RecId value for the next record that already exist on the table remains a mystery.

Reference :
1. https://community.dynamics.com/ax/f/33/t/109379
2. https://waytoax.wordpress.com/2014/06/01/cannot-create-a-record-in-table-the-record-already-exists-2/

Advertisements

Search and get table name with parameter table name and have data inside. SQL

this query is useful to check table name with data inside after running the class SysDatabaseTransDelete.

i put another parameter table name, for only search only customize table on AX with Developer identifier.

preview :
Capture

code below

DECLARE
    @search_string  VARCHAR(100),
    @table_name     SYSNAME,
    @table_id       INT,
    @column_name    SYSNAME,
    @sql_string     VARCHAR(2000)

SET @search_string = 'TID%'

DECLARE tables_cur CURSOR FOR SELECT name, object_id FROM sys.objects WHERE type = 'U'

OPEN tables_cur

FETCH NEXT FROM tables_cur INTO @table_name, @table_id
WHILE (@@FETCH_STATUS = 0)
BEGIN
    IF 
	(@table_name LIKE @search_string)
	BEGIN
		SET @sql_string = 'IF EXISTS (SELECT * FROM ' + @table_name + ')'+ 'PRINT ''' + @table_name +''''
		EXECUTE(@sql_string)
	END
    FETCH NEXT FROM tables_cur INTO @table_name, @table_id
END

CLOSE tables_cur

DEALLOCATE tables_cur

SQL Query Search all DB

i found nice query for searching all db. this query is useful when i need to trace some data.

/* Reto Egeter, fullparam.wordpress.com */

DECLARE	@SearchStrTableName nvarchar(255), @SearchStrColumnName nvarchar(255), @SearchStrColumnValue nvarchar(255), @SearchStrInXML bit, @FullRowResult bit, @FullRowResultRows int
SET @SearchStrColumnValue = '%searchthis%' /* use LIKE syntax */
SET @FullRowResult = 1
SET @FullRowResultRows = 3
SET @SearchStrTableName = NULL /* NULL for all tables, uses LIKE syntax */
SET @SearchStrColumnName = NULL /* NULL for all columns, uses LIKE syntax */
SET @SearchStrInXML = 0 /* Searching XML data may be slow */

IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
CREATE TABLE #Results (TableName nvarchar(128), ColumnName nvarchar(128), ColumnValue nvarchar(max),ColumnType nvarchar(20))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256) = '',@ColumnName nvarchar(128),@ColumnType nvarchar(20), @QuotedSearchStrColumnValue nvarchar(110), @QuotedSearchStrColumnName nvarchar(110)
SET @QuotedSearchStrColumnValue = QUOTENAME(@SearchStrColumnValue,'''')
DECLARE @ColumnNameTable TABLE (COLUMN_NAME nvarchar(128),DATA_TYPE nvarchar(20))

WHILE @TableName IS NOT NULL
BEGIN
	SET @TableName = 
	(
		SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
		FROM 	INFORMATION_SCHEMA.TABLES
		WHERE 		TABLE_TYPE = 'BASE TABLE'
			AND TABLE_NAME LIKE COALESCE(@SearchStrTableName,TABLE_NAME)
			AND	QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
			AND	OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
	)
	IF @TableName IS NOT NULL
	BEGIN
		DECLARE @sql VARCHAR(MAX)
		SET @sql = 'SELECT QUOTENAME(COLUMN_NAME),DATA_TYPE
				FROM 	INFORMATION_SCHEMA.COLUMNS
				WHERE 		TABLE_SCHEMA	= PARSENAME(''' + @TableName + ''', 2)
				AND	TABLE_NAME	= PARSENAME(''' + @TableName + ''', 1)
				AND	DATA_TYPE IN (' + CASE WHEN ISNUMERIC(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@SearchStrColumnValue,'%',''),'_',''),'[',''),']',''),'-','')) = 1 THEN '''tinyint'',''int'',''smallint'',''bigint'',''numeric'',''decimal'',''smallmoney'',''money'',' ELSE '' END + '''char'',''varchar'',''nchar'',''nvarchar'',''timestamp'',''uniqueidentifier''' + CASE @SearchStrInXML WHEN 1 THEN ',''xml''' ELSE '' END + ')
				AND COLUMN_NAME LIKE COALESCE(' + CASE WHEN @SearchStrColumnName IS NULL THEN 'NULL' ELSE '''' + @SearchStrColumnName + '''' END  + ',COLUMN_NAME)'
		INSERT INTO @ColumnNameTable
		EXEC (@sql)
		WHILE EXISTS (SELECT TOP 1 COLUMN_NAME FROM @ColumnNameTable)
		BEGIN
			PRINT @ColumnName
			SELECT TOP 1 @ColumnName = COLUMN_NAME,@ColumnType = DATA_TYPE FROM @ColumnNameTable
			SET @sql = 'SELECT ''' + @TableName + ''',''' + @ColumnName + ''',' + CASE @ColumnType WHEN 'xml' THEN 'LEFT(CAST(' + @ColumnName + ' AS nvarchar(MAX)), 4096),''' 
			WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + '),'''
			ELSE 'LEFT(' + @ColumnName + ', 4096),''' END + @ColumnType + ''' 
					FROM ' + @TableName + ' (NOLOCK) ' +
					' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))' 
					WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')'
					ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue
			INSERT INTO #Results
			EXEC(@sql)
			IF @@ROWCOUNT > 0 IF @FullRowResult = 1 
			BEGIN
				SET @sql = 'SELECT TOP ' + CAST(@FullRowResultRows AS VARCHAR(3)) + ' ''' + @TableName + ''' AS [TableFound],''' + @ColumnName + ''' AS [ColumnFound],''FullRow>'' AS [FullRow>],*' +
					' FROM ' + @TableName + ' (NOLOCK) ' +
					' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))' 
					WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')'
					ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue
				EXEC(@sql)
			END
			DELETE FROM @ColumnNameTable WHERE COLUMN_NAME = @ColumnName
		END	
	END
END
SET NOCOUNT OFF

SELECT TableName, ColumnName, ColumnValue, ColumnType, COUNT(*) AS Count FROM #Results
GROUP BY TableName, ColumnName, ColumnValue, ColumnType

source : https://fullparam.wordpress.com/2012/09/07/fck-it-i-am-going-to-search-all-tables-all-collumns/