Increase SQL server Maximum Memory from Command Prompt

You can change the min and max amount of memory allocation for each instance in SQL Server Management Studio, the defaults are:

server properties

If you accidentally set the “Maximum Server Memory” to below the amount which could run the services, you might not be able to start the SQL server service from Services or SQL Server Configuration Manager console and you will get this error if you want to log in to SSMS :

Failed to connect to server myServer. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

 

Here are the steps to increase the SQL server memory:

1. If SQL server service is stopped, open the command prompt navigate to your SQL server instance folder (mine is C:\Program Files\Microsoft SQL  Server\MSSQL11.MSSQLSERVER\MSSQL\Binn) and run this command in order to start the service with minimum requirement and single user mode :

 sqlservr -f -m -s nameofyourinstance

command1

2.while keeping previous command prompt open, open a new command prompt as an administrator and navigate to your SQL server instance                folder (mine is: C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn) and run this command to increase instance  memory to 4GB:

 sqlcmd 
 EXEC sys.sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sys.sp_configure 'min server memory', 1024;
GO
EXEC sys.sp_configure 'max server memory', 4096;
GO
RECONFIGURE;
GO 

sql command2

3. Now SQL server memory is increased. close those cmd windows and make sure the SQL server service is running by going to SQL Server  Configuration Manager

sscm

 

You should be able to connect to your instance through SSMS.

Advertisements

Retrieve Multiple Dynamics CRM Records with XrmServiceToolkit

In order to install XrmServiceToolkit check this blog post.

In this library, I found RetrieveMultiple function really helpful and I use it for different of purposes like to create a custom numbering system in CRM records, for instance since you can retrieve multiple record, you can loop through all your record and get all the fields in those.

here is the signature of the function :

     retrieveMultipleRecords = function (type, options, successCallback, errorCallback, onComplete, async)

One important thing is that successCallback only returns one page of records (50) at the time so you should loop through the results and push the records into an array outside of the function.

Here are Some Sample retrieveMultipleRecords calls:

1. This sample retrieves all accounts with all fields in the account form and push them in accountArray :

      var accountArray = new Array();

        XrmServiceToolkit.Rest.RetrieveMultiple(
                     "AccountSet",
                     "",// if you leave it empty, it retrieves all fields
                     function (results) {
                         if (results.length >= 1)
                             for (var i = 0; i < results.length; i++) {
                                 accountArray.push(results[i]);
                             }
                     },
                     function (error) {
                         alert(error.message);
                     },
                     function onComplete() {
                         //alert(" records should have been retrieved.");
                     },
                     false
                 );

     alert(accountArray.length); //this should show the number of account records in CRM

2. This sample retrieves only AccountID and AcountNumber when “new_AccountingApproved”(custom field) check box is checked:

   var accountArray = new Array();

        XrmServiceToolkit.Rest.RetrieveMultiple(
                     "AccountSet",
                     "$select=AccountId, AccountNumber &$filter=new_AccountingApproved eq true",
                     function (results) {
                         if (results.length >= 1)
                           //accountArray = results;  //don't do this! instead use push to array. for instance if you have 58 accounts in CRM, accountArray will only get 8 of them, because successCallback will run twice.
                             for (var i = 0; i < results.length; i++) {
                                 accountArray.push(results[i]);
                             }
                     },
                     function (error) {
                         alert(error.message);
                     },
                     function onComplete() {
                         //alert(" records should have been retrieved.");
                     },
                     false
                 );

In case you are interested ,Here is RetrieveMultipleRecords function definition from Source code of the project:

var retrieveMultipleRecords = function (type, options, successCallback, errorCallback, onComplete, async) {
        ///<summary>
        /// Sends synchronous/asynchronous request to retrieve records.
        ///</summary>
        ///<param name="type" type="String">
        /// The Schema Name of the Entity type record to retrieve.
        /// For an Account record, use "Account"
        ///</param>
        stringParameterCheck(type, "XrmServiceToolkit.REST.retrieveMultipleRecords requires the type parameter is a string.");
        ///<param name="options" type="String">
        /// A String representing the OData System Query Options to control the data returned
        ///</param>
        if (options != null)
            stringParameterCheck(options, "XrmServiceToolkit.REST.retrieveMultipleRecords requires the options parameter is a string.");
        ///<param name="successCallback" type="Function">
        /// The function that will be passed through and be called for each page of records returned.
        /// Each page is 50 records. If you expect that more than one page of records will be returned,
        /// this function should loop through the results and push the records into an array outside of the function.
        /// Use the OnComplete event handler to know when all the records have been processed.
        /// </param>
        callbackParameterCheck(successCallback, "XrmServiceToolkit.REST.retrieveMultipleRecords requires the successCallback parameter is a function.");
        ///<param name="errorCallback" type="Function">
        /// The function that will be passed through and be called by a failed response.
        /// This function must accept an Error object as a parameter.
        /// </param>
        callbackParameterCheck(errorCallback, "XrmServiceToolkit.REST.retrieveMultipleRecords requires the errorCallback parameter is a function.");
        ///<param name="OnComplete" type="Function">
        /// The function that will be called when all the requested records have been returned.
        /// No parameters are passed to this function.
        /// </param>
        callbackParameterCheck(onComplete, "XrmServiceToolkit.REST.retrieveMultipleRecords requires the OnComplete parameter is a function.");
        ///<param name="async" type="Boolean">
        /// A Boolean representing if the method should run asynchronously or synchronously
        /// true means asynchronously. false means synchronously
        /// </param>
        booleanParameterCheck(async, "XrmServiceToolkit.REST.retrieveMultipleRecords requires the async parameter is a boolean.");

SQL server Logical Query Processing Phases

Many aspect of SQL language are based on the solid mathematical foundations like Set theory and predicate logic, and those foundations have given it a long life span compare to other computation subjects. T-SQL is Microsoft flavor of standard SQL and it has two sides in term of processing the queries: Physical and logical.

Logical aspect of T-SQL is conceptualized interpretation of different phases involved in the query to return an appropriate result from the data. Order of processing of these phases is important when you write the queries. The keyed in order of query phases are:

  1. SELECT > 2. FROM > 3.  WHERE> 4.  GROUP BY >5.  HAVING> 6.  ORDER BY

But the engine would not process the query in that order and logically the orders of query processing are different which is:

  1. From
  2. Where
  3. Group By
  4. Having
  5. Select
  6. Order By

Each phase output would be virtual table input to the next phase. It’s important to notice there is a hierarchy notion in this phases, for instance aliases that are defend in Select phase are unavailable in higher phases and  accessible only in lower phase which is Order By  phase.

SELECT
 EmployeeID AS [Employee ID]
 ,Name AS [Full Name]
 ,Title AS [Job Title]
FROM Employees
WHERE Name='King' --You Can't use [Full Name] alias, because it’s not defined yet
ORDER BY [Full Name]

This is really important concept that is mostly neglected.

Resolve Connectivity Issue to Microsoft Dynamics GP Web Service in Dynamics Connector

If you are using Dynamics Connector to integrate GP and CRM, you need GP web services installed, and defined and in Adapter setting of dynamics connector. The error that I’ve seen a lot during Test Setting for GP web services in Connector is:

Dynamics Connector error

Dynamics Connector GP web service error

 

To resolve it make sure:

  1.  You have entered correct credentials and URL.

GP web service URL format should be like:

http://YourServer:48620/Dynamics/GPService/GPService

(YourServer is the server GP web services installed on and default port is 48620)

If you want to test the credential to see you have appropriate access, you can run IE as username defined in your setting and browse to this address:

http://YourServer:48620/Dynamics/GPService

The result should be like this screen:

GPWebService

 

To verify that the Microsoft Dynamics GP Web services run correctly, follow this  link.

  1. If you still can’t run the test setting successfully it might be user role in Dynamics Security Console. The user must have “Microsoft Dynamics Integration-All Companies” and “Microsoft Dynamics Integration-Integrated Company”

In order to grant those role follow these steps:

  • Log in to GP server and run “Dynamics Security Console” from administrative tools.
  • Under “Microsoft Dynamics GP Web Services” go to “Role Assignment”

dynamicsSecurityConsole

  • Click on Add on the right side of console and make sure you add the user with “Microsoft Dynamics Integration-All Companies” and “Microsoft Dynamics Integration-Integrated Company” Roles.

hopefully you will get this result in the end:

result

 

 

 

Removing added new line break after using SSI with Charset UTF-8 in Visual Studio or Notepad++

When using SSI in your HTML page with charset=”UTF-8″  the new line will be added after each time you use includes. it’s because your file encoding is saved with BOM or signature.

In order to overcome this, if you are using Visual Studio go to:  file> Advanced Saved Options  and change the Encoding to  UTF-8(Unicode without signature).

If you use Notepad++ go to: Encoding > Encode in UTF-8 Without BOM you should save all your Include files(.inc) to remove that extra space happen.

Merging Entities in Dynamics CRM using Webservices and MergeRequest

If you have lots of duplicated accounts in you system that has lots of records like Orders or Invoices under them, you can use MergeRequest to merge those accounts.

The good thing about merge is that it will handle all relationships for you and you don’t have to worry about related order or invoices ,etc. you also define what fields you want to copy from duplicated record to main record.

Here is a sample code for merging all duplicated accounts in CRM:

I retrieve all accounts in CRM where their account number contains “-0” and then based on that criteria retrieved the duplicated.

If you have more than 5000 records , the Retrieve Multiple will only return first 5000 records so it’s better to use “ConditionOperator” to restrict the number of accounts to be retrieved in your QueryExpression. I find it really useful and fast. Use  “ConditionOperator.Like“…

private void btnMerge_Click(object sender, EventArgs e)
        {
            //connecting to the service and getting the credentials from app.confi
            ClientCredentials Credentials = new ClientCredentials();
            ClientCredentials oClientCredential = new ClientCredentials();
            oClientCredential.UserName.UserName = System.Configuration.ConfigurationManager.AppSettings["userName"];
            oClientCredential.UserName.Password = System.Configuration.ConfigurationManager.AppSettings["password"];

            Uri OrganizationUri = new Uri(System.Configuration.ConfigurationManager.AppSettings["CRMURI"]);
            Uri HomeRealmUri = null;

            using (OrganizationServiceProxy serviceProxy = new OrganizationServiceProxy(OrganizationUri, HomeRealmUri, oClientCredential, null))
            {
                try
                {
                    IOrganizationService service = (IOrganizationService)serviceProxy;

                    QueryExpression query = new QueryExpression();

                    query.EntityName = "account";

                    query.ColumnSet = new ColumnSet() { AllColumns = true };

                    query.Criteria = new FilterExpression();

                    query.Criteria.FilterOperator = LogicalOperator.And;

                    query.Criteria.Conditions.Add

                    (
                     new ConditionExpression("statuscode", ConditionOperator.Equal, 1)//all the active accounts
                    );

                    query.Criteria.Conditions.Add(new ConditionExpression("accountnumber", ConditionOperator.Like, "%-0%"));

                    EntityCollection entities = service.RetrieveMultiple(query);

                    //loop through the list of retrieved entities and find the duplicate that match the criteria 

                    foreach (var item in entities.Entities)
                    {
                        string accountNumber = item.GetAttributeValue<string>("accountnumber").ToString();
                        string mainAccountNumber = item.GetAttributeValue<string>("dtm_mainaccountnumber").ToString();
                        string accountName = item.GetAttributeValue<string>("name").ToString();

                        if (accountNumber.Contains("-0"))
                        {
                            int start = accountNumber.IndexOf('-');
                            string strAdressCode = accountNumber.Substring(start + 1, accountNumber.Length - start - 1);
                            int number;
                            if (Int32.TryParse(strAdressCode, out number))
                            {
                                EntityReference target = new EntityReference();
                                target.Id = item.Id;
                                target.LogicalName = "account";

                                string newAccountNumber = accountNumber.Substring(0, start + 1) + number;

                                QueryExpression innerQuery = new QueryExpression();
                                innerQuery.EntityName = "account";
                                innerQuery.ColumnSet = new ColumnSet() { AllColumns = true };
                                innerQuery.Criteria = new FilterExpression();
                                innerQuery.Criteria.FilterOperator = LogicalOperator.And;
                                innerQuery.Criteria.Conditions.Add(new ConditionExpression("statuscode", ConditionOperator.Equal, 1));
                                innerQuery.Criteria.Conditions.Add(new ConditionExpression("dtm_mainaccountnumber", ConditionOperator.Equal, mainAccountNumber));
                                //innerQuery.Criteria.Conditions.Add(new ConditionExpression("name", ConditionOperator.Equal, accountName));
                                innerQuery.Criteria.Conditions.Add(new ConditionExpression("accountnumber", ConditionOperator.Equal, newAccountNumber));

                                EntityCollection innerEntities = service.RetrieveMultiple(innerQuery);

                                if (innerEntities.Entities.Count >= 1)
                                {

                                    // Create the request for Merge
                                    MergeRequest merge = new MergeRequest();
                                    merge.SubordinateId = innerEntities.Entities[0].Id;
                                    merge.Target = target;
                                    merge.PerformParentingChecks = false;
                                    
                                    // Create another account to hold new data to merge into the entity.
                                    // If you use the subordinate account object, its data will be merged.
                                    Entity updateContent = new Entity("account");
                                    updateContent["dtm_ismerged"] = "merged";

                                    // Set the content you want updated on the merged account
                                    merge.UpdateContent = updateContent;

                                    service.Execute(merge);
                                }
                            }
                            continue;
                        }
                    }
                }
                catch (Exception ex)
                {
                    System.Windows.Forms.MessageBox.Show(ex.ToString());
                    string fileloc = "C:\\myLog.txt";

                    if (File.Exists(fileloc))
                    {
                        using (StreamWriter sw = new StreamWriter(fileloc))
                        {
                            sw.Write(ex.ToString());
                        }
                    }

                }
            }
        }

Inventory Available to Promise (ATP) in Microsoft Dynamics GP

The Inventory Available to Promise screen will give you insight into your current inventory situation for any item. For any item that you select in this screen you will see the supply and demand for the item in a time sorted list. You can drill back into any listed item to get more information.

This feature of Dynamics GP will empower your employees with the knowledge they need to make and keep firm delivery promises.It delivers fast access to vital inventory information—including current and future positions—to help you make effective use of inventory, improve service, and maximize profitability.

ATP

 

Using this query from your GP database you can get ATP quantity for specific site in your organization:

SELECT [Item Number]
,[QTY In Use]
,[QTY In Service]
,[QTY Returned]
,[Location Code]
,[Record Type]
,[QTY On Order]
,[QTY On Hand]
,[QTY Allocated]
,[QTY Available]
,[Item Description]
,[QTY Damaged]
,[QTY On Order from Vendor Item]
,[QTY Sold]
,[U Of M Schedule]
,[U Of M Schedule Description]
,[Location Code]
FROM [<em>YourSchema</em>].[dbo].[ItemQuantities]
WHERE [Location Code]=<em>’your site location code’</em>
ORDER BY [QTY Available] desc