Finding User Sessions from SPID in Dynamics AX 2012

October 20, 2017 No comment

While check issues it could be usefull to make the link between the Ax user and the related SQL session. In the post below it is explained properly how you should do this.

https://community.dynamics.com/ax/b/axfaqblog/archive/2016/08/27/disable-and-enable-users-in-ax-using-t-sql-scripts

(more…)

Creating a SQL user with consistent SID in all environment

October 13, 2017 No comment

When you add a SQL user with the same user ID’s manually on several environments you will see that after a restore this user has no permissions when he tries to acces that database although he seems to be present when you check the SQL properties. The explication is simple, the SID for that user is different, so the SQL Login and the database user are not the same.

SELECT 'Login' AS principal_type,SP.name,SP.sid
  FROM sys.server_principals AS SP
 WHERE name = 'readsoft'
UNION ALL
SELECT 'User' AS principal_type,DP.name,DP.sid
  FROM sys.database_principals AS DP
WHERE name = 'readsoft';

The solution is simple, you create the Login on 1 SQL server and for all other environments you create that login via a script. This way you ensure that the login and user are related.

CREATE Login readsoft WITH password = '******', SID = 0xF3CA088009A6A647A4C1BF84D2202EB8

Check SQL running processes

October 6, 2017 No comment

If you want to check the processes running on your SQL server you can find all the info you need in sys.dm_exec_requests.

SELECT command,
            s.text,
            start_time,
            percent_complete,
            CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
                  + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
                  + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
            CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
                  + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
                  + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
            dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s

In the example below you can see that there is a back-up running that about 17% completed and a estimated duration

Check SQL last back-date

September 29, 2017 No comment

If you want to check in SQL when your databases was last back-upped (+ the type of back-up), you can execute the script below. this will list you everything of the last 7 days.

SELECT 
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_start_date, 
msdb.dbo.backupset.backup_finish_date, 
msdb.dbo.backupset.expiration_date, 
CASE msdb..backupset.type 
WHEN 'D' THEN 'Database' 
WHEN 'I' THEN 'Differential database' 
WHEN 'L' THEN 'Log' 
WHEN 'F' THEN 'File/Group' 
WHEN 'G' THEN 'Differential file' 
WHEN 'P' THEN 'Partial' 
WHEN 'Q' THEN 'Differential partial' 
END AS backup_type, 
msdb.dbo.backupset.backup_size, 
msdb.dbo.backupmediafamily.logical_device_name, 
msdb.dbo.backupmediafamily.physical_device_name, 
msdb.dbo.backupset.name AS backupset_name, 
msdb.dbo.backupset.description 
FROM msdb.dbo.backupmediafamily 
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) 
ORDER BY 
msdb.dbo.backupset.backup_finish_date desc,
msdb.dbo.backupset.database_name

Check SQL last restore-date

September 29, 2017 No comment

If you want to check in SQL when your databases was last restored, you can execute the script below.

SELECT  [rs].[destination_database_name] ,
        [rs].[restore_date] ,
        [bs].[backup_start_date] ,
        [bs].[backup_finish_date] ,
        [bs].[database_name] AS [source_database_name] ,
        [bmf].[physical_device_name] AS [backup_file_used_for_restore]
FROM    msdb..restorehistory rs
        INNER JOIN msdb..backupset bs ON [rs].[backup_set_id] = [bs].[backup_set_id]
        INNER JOIN msdb..backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id]
ORDER BY [rs].[restore_date] DESC

Working with numbersequences – new NumberSequence

September 22, 2017 No comment

A few years a go I made a few posts about numbersequences, in these posts I forgot to mention that you need to execute the following job before you can find your new created Numbersequence.

static void NumberSeqLoadAll(Args _args)
{
    NumberSeqApplicationModule::loadAll();
}

Maximize a form in Ax

September 15, 2017 No comment

Change the properties width = ‘column width’ and height = ‘column height’ on the form.

Add the following code in the run-method of the form:

#WinApi ; 
super(); 
WinApi::showWindow(this.hWnd(), #SW_SHOWMAXIMIZED);

Microsoft Dynamics is sponsoring the Lotus formula1 team

March 16, 2012 No comment

Ax2012 An SQL Server 2012

March 15, 2012 No comment

20120315-074725.jpg

Microsoft SQL Server 2012 RTM is released and hotfix 2680186 is availible for compatibiliteit with Microsoft Dynamics Ax2012.

https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;en-us;2680186

My first workday with Windows 8

March 1, 2012 No comment

Yesterday evening I installed the Windows 8 Customer Preview on my work laptop (EliteBook 8740w i7-620M, 8GB Ram and 2x 500GB HDD). With a small hart I went to work and hoped that everything would work fine. Her is wat I found.

At first I plugged in the network cable and I Couldn’t get connection. The only way I could get connection was by removing the network card in the device manager and reïnstalling it. For the rest of the day I had no special errors. Underneed are some of the applications I used within Windows 8:

Next Page »