mercredi 6 août 2014

Error: Unable to log on to Microsoft Dynamics AX.


If you get the error message "Unable to log on to Microsoft Dynamics AX" when opening the AX client and you find nothing on the windows Events of the AOS, Client or SQL server.

This is a problem with stored passwords in the client machine.

Just navigate to the Control Panel - Stored passwords or Network passwords (Depending on OS), and delete any entries related to the AOS server.

Performance problem in CRM 2011 with the function fn_POARetrieveMultiple



After some month of usage, we find a performance problem in CRM and regularly with find the waring mesage in the windows events
Query execution time of 10.7 seconds exceeded the threshold of 10 seconds. .....
After investigation the problem is with the table valued function fn_POARetrieveMultiple.

ALTER function [dbo].[fn_POARetrieveMultiple](@userid as uniqueidentifier, @otc as int)
returns @poa table
(
 ObjectId uniqueidentifier primary key clustered
)
as
begin
 insert into @poa
 select distinct POA.ObjectId from PrincipalObjectAccess POA with (NOLOCK)
  join SystemUserPrincipals sup with (NOLOCK) on POA.PrincipalId = sup.PrincipalId
  where sup.SystemUserId = @userid
  and POA.ObjectTypeCode = @otc and
  ((POA.AccessRightsMask|POA.InheritedAccessRightsMask) & 1) = 1
 return
end



If we execute the query in the function, the query return result in 0 seconds.

select distinct POA.ObjectId from PrincipalObjectAccess POA with (NOLOCK)
  join SystemUserPrincipals sup with (NOLOCK) on POA.PrincipalId = sup.PrincipalId
  where sup.SystemUserId = 'd811762f-f682-e111-939f-0050568373d7'
  and POA.ObjectTypeCode = '4200' and
  ((POA.AccessRightsMask|POA.InheritedAccessRightsMask) & 1) = 1

 

But if we we call the function directly, the query return result in 10 seconds.

select ObjectId from fn_POARetrieveMultiple('d811762f-f682-e111-939f-0050568373d7', 4200)

After some search on Google I discovered that if we alter the function, the query (select ObjectId from fn_POARetrieveMultiple('d811762f-f682-e111-939f-0050568373d7', 4200) ) return result in 0 seconds.
But after some minutes, it take 10 seconds again.

I think that the problem is solved in this patch http://support.microsoft.com/kb/2566163/en-us, but it was not possible for us to update sql server quickly.
The workaround was to modify the function to force a recompilation for each call, I added the line
OPTION (RECOMPILE)

ALTER function [dbo].[fn_POARetrieveMultiple](@userid as uniqueidentifier, @otc as int)
returns @poa table
(
 ObjectId uniqueidentifier primary key clustered
)
as
begin
 insert into @poa
 select distinct POA.ObjectId from PrincipalObjectAccess POA with (NOLOCK)
  join SystemUserPrincipals sup with (NOLOCK) on POA.PrincipalId = sup.PrincipalId
  where sup.SystemUserId = @userid
  and POA.ObjectTypeCode = @otc and
  ((POA.AccessRightsMask|POA.InheritedAccessRightsMask) & 1) = 1
  OPTION (RECOMPILE)
 return
end

It's a temporary solution that worked for me, we will remove the line after the update of sql server.
!!unsupported fix

Links that can help you

http://030bacf.netsolhost.com/WordPress/?author=1&paged=2
http://sqlperformance.com/2013/08/t-sql-queries/parameter-sniffing-embedding-and-the-recompile-options
http://support.microsoft.com/kb/2566163/en-us
http://pratchev.blogspot.fr/2007/08/parameter-sniffing.html