SQL Query for obtaining min and max sizes from product variants

 With Sizes_CTE (Product, DisplayOrder, Size)  
 AS (  
 select DISPLAYPRODUCTNUMBER Product, RETAILDISPLAYORDER DisplayOrder , NAME Size from ECORESPRODUCTMASTERDIMENSIONVALUE   
 inner join ECORESSIZE on ECORESSIZE.RecId = SIZE_  
 inner join ECOResProduct on EcoresProduct.RECID = SIZEPRODUCTMASTER)  
 select Z.Product, Concat(max(Z.MinSize), ' - ', max(Z.MaxSize)) SizeGroup from  
 (Select Product, Size MinSize, '' MaxSize from Sizes_CTE  
 where Sizes_CTE.DisplayOrder = (select min(DisplayOrder) from Sizes_CTE b where b.Product = Sizes_CTE.product )  
 union   
 Select Product, '' MinSize, Size MaxSize from Sizes_CTE  
 where Sizes_CTE.DisplayOrder = (select max(DisplayOrder) from Sizes_CTE b where b.Product = Sizes_CTE.product )) AS Z  
 group by Z.Product  
 order by Z.Product  

SharePoint 2013 Installation and Configuration Issues

During Installing SharePoint 2013 Prerequisites on Windows Server 2012 there was an error in installing Application Server Role , Web Server (IIS) Role : Configuration error

To resolve this error, go to windows\system32 directory.

Copy and rename servermanager.exe to servermanagercmd.exe

SQL query to extract primary address from CUSTTABLE

The following query can be used to extract the primary address from the CUSTTABLE table:

 select CUSTTABLE.ACCOUNTNUM, DIRPARTYTABLE.NAME, Address.ADDRESS  
 from CUSTTABLE  
 left outer join DIRPARTYTABLE ON DIRPARTYTABLE.RECID = CUSTTABLE.PARTY  
 left outer join LOGISTICSLOCATION ON LOGISTICSLOCATION.RECID = DIRPARTYTABLE.PRIMARYADDRESSLOCATION  
 left outer join LOGISTICSPOSTALADDRESS AS Address ON Address.LOCATION = LOGISTICSLOCATION.RECID  
 Order by CUSTTABLE.ACCOUNTNUM  

SQL query to extract financial dimension value from CUSTTABLE

The following SQL query can be used to extract a financial dimension value from the CUSTTABLE table:

 select CustTable.ACCOUNTNUM, SubscriberType.DISPLAYVALUE as SubscriberType  
 from CustTable  
 left outer join DIMENSIONATTRIBUTEVALUESETITEM AS SubscriberType ON SubscriberType.DIMENSIONATTRIBUTEVALUESET = CUSTTABLE.DEFAULTDIMENSION  
 left outer join DIMENSIONATTRIBUTEVALUE ON DIMENSIONATTRIBUTEVALUE.RECID = SubscriberType.DIMENSIONATTRIBUTEVALUE  
 left outer join DIMENSIONATTRIBUTE ON DIMENSIONATTRIBUTE.RECID = DIMENSIONATTRIBUTEVALUE.DIMENSIONATTRIBUTE and DIMENSIONATTRIBUTE.NAME = 'SubscriberType'  

Customer contact list for AX2012

Query to extract a quick customer contact list from Dynamcis AX 2012

 SELECT   CUSTTABLE.ACCOUNTNUM AS CUSTID, DIRPARTYTABLE.NAME AS CUSTNAME,   
            CASE LOGISTICSELECTRONICADDRESS.TYPE WHEN 1 THEN 'Phone' WHEN 2 THEN 'Email' END AS CONTACTTYPE,   
            LOGISTICSELECTRONICADDRESS.DESCRIPTION AS CONTACTNAME, LOGISTICSELECTRONICADDRESS.LOCATOR AS CONTACTDETAILS  
 FROM     DIRPARTYTABLE AS DIRPARTYTABLE INNER JOIN  
            CUSTTABLE ON DIRPARTYTABLE.RECID = CUSTTABLE.PARTY INNER JOIN  
            DIRPARTYLOCATION ON DIRPARTYTABLE.RECID = DIRPARTYLOCATION.PARTY INNER JOIN  
            LOGISTICSELECTRONICADDRESS ON DIRPARTYLOCATION.LOCATION = LOGISTICSELECTRONICADDRESS.LOCATION  
 ORDER BY DIRPARTYTABLE.NAME  

Consuming WCF web service from .NET assembly in Dynamics AX 2012

X++ code for consuming a WCF web service:

 static server str serverSendToEndPoint(str _endPoint, str _soapAction, str _serverThumbprint, str _clientThumbprint, str _xmlString)  
 {  
   str                 ret;  
   System.String       clrEndPoint;  
   System.String       clrSoapAction;  
   System.String       clrServerThumbprint, clrClientThumbprint;  
   System.String       clrXmlString;  
   System.String       clrResponse;  
   System.Exception     ex;  
   Namespace.WebService webService;  
   try  
   {  
     webService = new Namespace.WebService();  
     clrEndPoint         = CLRInterop::getObjectForAnyType(_endPoint);  
     clrSoapAction        = CLRInterop::getObjectForAnyType(_soapAction);  
     clrServerThumbprint     = CLRInterop::getObjectForAnyType(_serverThumbprint);  
     clrClientThumbprint     = CLRInterop::getObjectForAnyType(_clientThumbprint);  
     clrXmlString        = CLRInterop::getObjectForAnyType(_xmlString);  
     if (VendParameters::find().AVAMutualAuthentication == NoYes::Yes)  
     {  
       clrResponse = webService.callWCFWebService(clrEndPoint, clrSoapAction, clrServerThumbprint, clrClientThumbprint, clrXmlString);  
     }  
     else  
     {  
       clrResponse = webService.callWCFWebService(clrEndPoint, clrSoapAction, clrServerThumbprint, clrXmlString);  
     }  
     return CLRInterop::getAnyTypeForObject(clrResponse);  
   }  
   catch (Exception::CLRError)  
   {  
     ex = CLRInterop::getLastException();  
     throw error(strFmt("The request failed with the following response %1", CLRInterop::getAnyTypeForObject(ex.ToString())));  
   }  
 }  

.NET Assembly

namespace Namespace
{
    public class WebService
    {
      public String callWCFWebService(string url = "", string soapAction = "", string thumbPrint = "", string thumbPrint2 = "", string xmlString = "")  
     {  
       ServicePointManager.ServerCertificateValidationCallback = delegate(object sender, X509Certificate certificate, X509Chain chain, SslPolicyErrors sslPolicyErrors) { return true; };  
       //Validate parameters  
       if (url == "")  
       {  
         throw new System.ArgumentException("Url missing");  
       }  
       if (soapAction == "")  
       {  
         throw new System.ArgumentException("Soap Action missing");  
       }  
       if (thumbPrint == "")  
       {  
         throw new System.ArgumentException("Server Thumbprint missing");  
       }  
       if (thumbPrint2 == "")  
       {  
         throw new System.ArgumentException("Client Thumbprint missing");  
       }  
       if (xmlString == "")  
       {  
         throw new System.ArgumentException("xmlString missing");  
       }  
       // Create a new HttpWebRequest object for the specified resource.  
       HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);  
       // Request mutual authentication.  
       request.AuthenticationLevel = AuthenticationLevel.MutualAuthRequired;  
       // Supply client credentials.  
       X509Store store = new X509Store(StoreName.My, StoreLocation.CurrentUser);  
       store.Open(OpenFlags.ReadOnly | OpenFlags.IncludeArchived);  
       // Find Server Certificate by thumbprint  
       X509Certificate2Collection col =  
       store.Certificates.Find(X509FindType.FindByThumbprint, thumbPrint.Replace(" ", ""), false);  
       X509Certificate2 cert = col.OfType<X509Certificate2>().FirstOrDefault();  
       if (cert == null)  
       {  
         throw new System.ArgumentException("Server Certificate not found in store ");  
       }  
       request.ClientCertificates.Add(cert);  
       // Find Client Certificate by thumbprint  
       col = store.Certificates.Find(X509FindType.FindByThumbprint, thumbPrint2.Replace(" ", ""), false);  
       cert = col.OfType<X509Certificate2>().FirstOrDefault();  
       store.Close();  
       if (cert == null)  
       {  
         throw new System.ArgumentException("Client Certificate not found in store");  
       }  
       request.ClientCertificates.Add(cert);  
       ASCIIEncoding encoding = new ASCIIEncoding();  
       byte[] bytesToWrite = encoding.GetBytes(xmlString);  
       request.Method = "POST";  
       request.ContentLength = bytesToWrite.Length;  
       request.Headers.Add("SOAPAction: \"" + soapAction + "\"");  
       request.ContentType = "text/xml; charset=utf-8";  
       request.KeepAlive = false;  
       request.ProtocolVersion = HttpVersion.Version10;  
       request.PreAuthenticate = true;  
       //Send Request  
       Stream dataStream = request.GetRequestStream();  
       dataStream.Write(bytesToWrite, 0, bytesToWrite.Length);  
       dataStream.Close();  
       //Get Response  
       string responseString = "";  
       try  
       {  
         HttpWebResponse response = (HttpWebResponse)request.GetResponse();  
         // Read and display the response.  
         Stream streamResponse = response.GetResponseStream();  
         StreamReader streamRead = new StreamReader(streamResponse);  
         responseString = streamRead.ReadToEnd();  
         //Console.WriteLine(responseString);  
         // Close the stream objects.  
         streamResponse.Close();  
         streamRead.Close();  
         //Release the HttpWebResponse.  
         response.Close();  
       }  
       catch (Exception e)  
       {  
         if (e is WebException)  
         {  
           WebResponse errResp = ((WebException)e).Response;  
           if (errResp != null)  
           {  
             using (Stream streamResponse = errResp.GetResponseStream())  
             {  
               StreamReader streamRead = new StreamReader(streamResponse);  
               responseString = streamRead.ReadToEnd();  
               //Console.WriteLine(responseString);  
               // Close the stream objects.  
               streamResponse.Close();  
               streamRead.Close();  
             }  
           }  
           else  
           {  
             responseString = e.Message;  
             //Console.WriteLine(e.Message);  
           }  
         }  
       }  
       return responseString;  
     }
}  

IIS 7.0 returns HTTP "403.13 Client Certificate Revoked" error message although certificate is not revoked‏

I had this error today with a web service configured with client certificates on IIS.

This issue happens when Certificate Revocation List (CRL) is enabled and the IIS server doesn't have Internet access

The following Microsoft support article describes the problem http://support.microsoft.com/kb/294305

CRL can be disabled via the following registry change:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\HTTP\Parameters\SslBindingInfo\0.0.0.0:443]DefaultSslCertCheckMode=1

 

Then Reboot the server for the change to take effect