During our upgrade journey from AX 2012 to Microsoft Dynamics 365 Finance and Operations we have noticed some problems. The automated code upgrade tool available on LifeCycle Services for your modelstore did an incorrect auto upgrade on custom methods.
When we did the first builds, the following error message was showing up for various code pieces:
Compile Fatal Error: Table dynamics://Table/CCMTmpQuestions: [(39,5),(39,6)]: Unexpected token '/' specified outside the scope of any class or model element.
After checking the source code we have identified that somehow it has inserted extra lines at the end of some table methods, with a single / sign in them:
The quickest way to locate all those methods are to use our favorite file manager and search tool, Total Commander.
You can press <ALT>+<F7> and do a file search for *.xml in your \AOSService\PackagesLocalDirectory\[YourPackageName] folder for the following Regular expression value. You must tick the RegEx checkbox:
(^ \/)$
This has revealed all incorrect files, which we could fix in bulk. You can use similar approach as above to quickly find anything in the file-based Dynamics 365 Finance and Operations code repository.
Once you quick-replace these characters with a blank line, you are done fixing the incorrect auto upgrade on custom methods.
If you have ever encountered SQL Server Jobs which looks like a GUID value, you might have wondered what they are used for.
Our AX 2012 deployment is using a SQL Server Reporting Services instance, which is used for two purposes:
AX 2012 SSRS reports
Custom reporting from our Data warehouse
These jobs are actually periodically generated SSRS reports scheduled by our staff. Each job belongs to a different person and report.
By just having a look at the job itself does not reveal what is actually being executed, but the following T-SQL script can extract the details when you run it against the ReportServer database:
SELECT
c.Name AS ReportName,
S.ExtensionSettings,
'Next Run Date' = CASE next_run_date
WHEN 0 THEN null
ELSE
substring(convert(varchar(15),next_run_date),1,4) + '/' +
substring(convert(varchar(15),next_run_date),5,2) + '/' +
substring(convert(varchar(15),next_run_date),7,2)
END,
'Next Run Time' = isnull(CASE len(next_run_time)
WHEN 3 THEN cast('00:0'
+ Left(right(next_run_time,3),1)
+':' + right(next_run_time,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))
WHEN 5 THEN cast('0' + Left(right(next_run_time,5),1)
+':' + Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))
WHEN 6 THEN cast(Left(right(next_run_time,6),2)
+':' + Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))
END,'NA'),
Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="TO"])[1]','nvarchar(100)') as [To]
,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="CC"])[1]','nvarchar(100)') as [CC]
,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="BCC"])[1]','nvarchar(100)') as [BCC]
,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="RenderFormat"])[1]','nvarchar(50)') as [Render Format]
,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="Subject"])[1]','nvarchar(50)') as [Subject]
/*,Convert(XML,[Parameters]).value('(//ParameterValue/Value[../Name="StartDateMacro"])[1]','nvarchar(50)') as [Start Date]
,Convert(XML,[Parameters]).value('(//ParameterValue/Value[../Name="EndDateMacro"])[1]','nvarchar(50)') as [End Date]
,Convert(XML,[Parameters]).value('(//ParameterValue/Value[../Name="Currency"])[1]','nvarchar(50)') as [Currency]*/
,[LastStatus]
,[EventType]
,[LastRunTime]
,[DeliveryExtension]
,[Version]
FROM
dbo.[Catalog] c
INNER JOIN dbo.[Subscriptions] S ON c.ItemID = S.Report_OID
INNER JOIN dbo.ReportSchedule R ON S.SubscriptionID = R.SubscriptionID
INNER JOIN msdb.dbo.sysjobs J ON Convert(nvarchar(128),R.ScheduleID) = J.name
INNER JOIN msdb.dbo.sysjobschedules JS ON J.job_id = JS.job_id
Now we have the details of:
what reports were scheduled
the report format
execution status
schedule and last successful run time.
This should give you a better overview about what are the SQL Server Jobs which looks like a GUID value in your database instance.
Once a company has been live for a while and they are doing Business As Usual, they often forget about maintaining one critical area. The system is using Number Sequences as identifiers, for which we have fixed, allocated range of values. Typical example is a general financial journal with a format like JNL18-###### with values between 000001 and 999999. Number Sequence consumption monitoring is essential!
If the sequence reaches the maximum value, you are no longer able to create new financial journals. Sometimes it is obvious when a number is about to run out, but in many cases AX has it hidden on a transactional level that is not necessarily visible for the users. When the sequence runs out, it can cause serious issues:
database locking
error messages
rolled back transactions.
Proactive monitoring is key to a healthy ERP system on many levels. Number sequence consumption monitoring is no exception. We are running a Transact-SQL script that keeps tracking of the number sequence utilization, and sends out an e-mail with entries reaching a set threshold. We are running the job based on a weekly schedule. We include any sequences that have used up at least 70% of their available range.
The Trace parser is an excellent tool for troubleshooting business functionality within Microsoft Dynamics AX 2012 and Dynamics 365 for Finance and Operations. Your Trace database can quickly grow large and it does affect the tools’ performance adversely.
Removing the old trace collections one-by-one is time consuming. You could utilize a stored procedure for cleaning up your AX trace database efficiently in SQL:
-- Replace AXTrace with your database name
USE [AXTrace]
GO
-- Truncate transaction log to reduce size
DBCC SHRINKFILE (N'AXTrace_log' , 0, TRUNCATEONLY)
GO
-- Iterate through the list of traces in the database
DECLARE cur CURSOR FOR
SELECT [TraceId] FROM [dbo].[Traces]
DECLARE @Id AS int
OPEN cur;
FETCH NEXT FROM cur into @Id;
-- Remove all traces in the database with the DeleteTrace stored procedure
WHILE (@@FETCH_STATUS = 0)
BEGIN
EXEC [dbo].[DeleteTrace] @TraceId = @Id
FETCH NEXT FROM cur into @Id;
END
CLOSE cur;
DEALLOCATE cur;
-- Truncate transaction log to reduce size
DBCC SHRINKFILE (N'AXTrace_log' , 0, TRUNCATEONLY)
GO
The other day we ran into a serious issue during our regular Production system maintenance. The Data dictionary synchronization step has started creating indexes with a DEL_ prefix on large tables such as InventTrans. We have quickly identified that AX configuration keys got changed by opening a checklist.
It turned out that some of the users had the System_Checklist security privilege assigned, and they have clicked it by accident. Standard code can add or update License configuration keys when you run the checklist.
In our case only a couple of new entries were activated, such as SysObsoleteObjects60. It has added a lot of DEL_ objects which could be easily rolled back. At another company it has done irreversible damage for inventory dimensions. You could imagine how bad the situation could get if important config keys are turned off. Synchronization would start dropping columns and entire tables.
The quick fix was to use the Export functionality on the License configuration form from our Quality Assurance AX (daily copy of Production). Then we could import the correct configurations to Prod and revert changes by doing a Data dictionary synchronization.
Please ensure that your users only have access to security privileges they really need, to avoid unintentional damage. Remove the System_Checklist and similar features from everyone, except the administrators.
