SQL Returning Output Parameter values from Stored Procedure

This is the stored procedure

ALTER PROCEDURE [dbo].[Usp_purging_updateretentionmth]
@newMonth     INT,
@currentMonth INT OUTPUT

AS

  BEGIN

      SET nocount ON;

      UPDATE tbl_purgingmonth
      SET    currentstatus = 'Archive'
      WHERE  currentstatus = 'Active'

      INSERT INTO tbl_purgingmonth
      VALUES ( @newMonth, Getdate(), system_user, 'Active' )

      SET @currentMonth = @newMonth

  END 

To retrieve output value, you must use this code
command.Parameters["@currentMonth"].Value

   1:                  // Add the input parameter and set its properties.

   2:                  SqlParameter pNewMonth = new SqlParameter();

   3:                  pNewMonth.ParameterName = "@newMonth";

   4:                  pNewMonth.SqlDbType = SqlDbType.Int;

   5:                  pNewMonth.Direction = ParameterDirection.Input;

   6:                  pNewMonth.Value = newMonth;

   7:   

   8:                  // Add the output parameter and set its properties.

   9:                  SqlParameter pCurrentMonth = new SqlParameter();

  10:                  pCurrentMonth.ParameterName = "@currentMonth";

  11:                  pCurrentMonth.SqlDbType = SqlDbType.Int;

  12:                  pCurrentMonth.Direction = ParameterDirection.Output;

  13:   

  14:                  //Add the parameter to the Parameters collection. 

  15:                  command.Parameters.Add(pNewMonth);

  16:                  command.Parameters.Add(pCurrentMonth);

  17:   

  18:                  command.ExecuteScalar();

  19:   

  20:                  mth = Convert.ToInt32(command.Parameters["@currentMonth"].Value); 

Microsoft Dynamics CRM 2011 Exams - update

Microsoft finally released 3 CRM 2011 exams :

Exam MB2-868: Microsoft Dynamics CRM 2011 Applications Published : 5/13/2011

Exam MB2-867: Microsoft Dynamics CRM 2011 Installation and Deployment Published : 5/3/2011

Exam MB2-866: Microsoft Dynamics CRM 2011 Customization and Configuration Published : 4/12/2011

CRM 2011 Installation–Specify Service Accounts

CRM 2011 now allows us to specify service accounts during the setup process. You can specify the service account for the Application Service, Deployment Web Service, Sandbox Service and Asynchronous Service to run as. In a single server environment, you can just use NETWORK SERVICES for the installation.

However if you are installing CRM in an enterprise multi server environment, the recommendation is to have a specify service accounts each of the services. For best practices and instructions on how to install CRM in a multi server environment, please refer to the CRM 2011 Implementation Guide.

Problem
The reason that we got the warnings is because the admin is using the installing user account for the service accounts. What happens is when the first organization is created, the installing user is created as the first user in the organization. Since there is a user in the organization with the same credential as the service accounts for the asynchronous service, application service and the sandbox service, all the sudden the “SYSTEM” user is now subject to the same constraints as an actual user which means that the user must be enabled, need a user role and etc…, otherwise the system will stop functioning. A lot of bad things could happen. For example, some grids in CRM are populated with data that is retrieved as SYSTEM, when data is retrieved as SYSTEM, it is retrieve in GMT format. However if the Application Service is running under a service account which is also an user in CRM, when retrieving data the data will return with the users time zone setting instead of GMT. There are more bad things could happen…


Solution
If it’s for a non-production environment, you may ignore the warnings and proceed with your installation. But for production environment, this will cause problems later on. The recommendation is to use a different service account for each of those services. However if you preferred not to manage extra service accounts, you may use a same service account for Application Service, Deployment Web Service, Sandbox Processing Service, Asynchronous Process Service as long as the installing account is different than the service account for the services.

If you decided to use a different service account for each of the services, just create the service accounts in your AD. you don’t have to grant any permissions to the accounts, the installation process will take care of the permissions for you! For your reference, here’s a list of accounts and permissions that we used for our installation.

Notes:
If you are running into an error telling you that “This account doesn’t have Performance Counter Permissions”, you need to follow the steps below to resolve the problem.
1.Open Server Manager.
2.Go to Configuration > Local Users and Groups > Groups.
3.Add the service accounts to the Performance Log Users group.
4.Install CRM with again.

Minimum permissions required for Microsoft Dynamics CRM Setup, services, and components

I found the article below from the CRM 2011 IG.
Here you go. Minimum permissions required for Microsoft Dynamics CRM Setup, services, and components

Troubleshooting CRM "Generic Sql Error"

Refer to this link : Troubleshooting CRM "Generic Sql Error"

New Prices for Microsoft Certifications Exams

I got an email today from Microsoft informing that effective July 1, 2011, the retail price of Microsoft Certifications will increase worldwide.

You can find more information here.

If you consider the number of certifications need to be a CRM gold partner. 
You need :

• 6 people certified in CRM certifications
• 3 people certified in Sure Step
• 1 people certified in selling CRM

How to write good code

How do you justify "Good Code"?

Do them fast or do them right?

Introducing Enhanced Transcripts and Certificates!

Today Microsoft proudly announces its going to add the words inactive to peoples certificates if they are in a technology which Microsoft no longer supports. It seems a pointless thing to do and not really related to the certification. In my mind people who are certified in a technology which is no longer supported by microsoft might well be more in demand rather than inactive.

Source :
Introducing Enhanced Transcripts and Certificates!

CRM Web Services Performance Tweaks

Found a post regarding the CRM Web Services performance tweaks:

"The results are based on a test that involved performing 250 Account create operations in a single-threaded clean CRM environment.

Test Results

Raw Dog
15402.1472

PreAuthenticate
14450.7792

PreAuthenticate & Unsafe
12638.1728

Just Unsafe
9633.8528

Unsafe + IIS Tweaks
8862.744

So what does all this mean? The answers are below. For simplicity of the code samples, I assume you have already declared and setup a CrmService, the credentials are set and the URL is configured. I also assume there is a CRM Account called "acc" that is ready to go. Something like this:

CrmService crm = new CrmService();
crm.Credentials = System.Net.CredentialCache.DefaultCredentials;
crm.Url = http://localhost/MSCRMServices/2006/CrmService.asmx;

account acc = new account();
acc.name = "Test";

"Raw Dog" - This is the most straightforward and basic way of calling the CRM service, the code looks something like this:

crm.Create(acc);


PreAuthenticate - This is the first optimization that people seem to use and it does indeed provide a small benefit (~7%) over the default settings. The code looks like this:

crm.PreAuthenticate = true;
crm.Create(acc);

So why does this work? Reading the documentation suggests that this simply saves a round-trip required by NTLM's challenge-response authentication system. MSDN: "With the exception of the first request, the PreAuthenticate property indicates whether to send authentication information with subsequent requests without waiting to be challenged by the server. When PreAuthenticate is false, the WebRequest waits for an authentication challenge before sending authentication information." - Of course since most systems have "Keep Alives" enabled and my scenario is using the same connection over and over, the savings are minimal.


PreAuthenticate & Unsafe - This attempt adds the "UnsafeAuthenticatedConnectionSharing" option to the mix and we get yet another boost in performance (~12% over our last test and ~18% for our first test). The code looks like this:

crm.PreAuthenticate = true;
crm.UnsafeAuthenticatedConnectionSharing = true;
crm.Create(acc);

So why does this help? When used in conjunction with "Keep Alives" this option keeps an authenticated connection open to the server. MSDN: "The default value for this property is false, which causes the current connection to be closed after a request is completed. Your application must go through the authentication sequence every time it issues a new request. If this property is set to true, the connection used to retrieve the response remains open after the authentication has been performed. In this case, other requests that have this property set to true may use the connection without re-authenticating. In other words, if a connection has been authenticated for user A, user B may reuse A's connection; user B's request is fulfilled based on the credentials of user A."

This option is very powerful and before using it be sure to read up on it here. Since the connection is authenticated and shared, you need to make sure that two different users don't come in on the same connection. If they do, the server will think the user is the first user that opened the connection and allow the 2nd user to do whatever the 1st user could and to do it as if they were the same person. There is a way around this using the property "ConnectionGroupName" property. For my scenario of a bulk import, the only user that will be using this connection is the migration user and it will be the same user from start to end, so we are ok.


Unsafe - Now something curious happens when we keep PreAuthenticate off, but leave UnsafeAuthenticatedConnectionSharing on. This is where things get a bit odd, this is faster than have both options on; a full 38% faster than the original test as a matter of fact! The code looks like this:

crm.UnsafeAuthenticatedConnectionSharing = true;
crm.Create(acc);

Why does this work? Well the answer is I don't know and I have talked to people on the CRM team and the .NET team and nobody has a really good answer. The good new is that it does, perhaps it is best to leave it at that.


Tweaks - Finally, if you follow all the recommended steps for configuring a high-performance ASP.NET application (disable logging, enable ISAPI caching, make sure ASP.Net is tweaked) you can get a final little nip of performance. Basically do what this article says and we get another 9% bump in performance.



Be warned that not all settings are "safe" to use in all scenarios... please read up on the suggestions prior to implementing them in a production system."

Update Rollup 1 for Microsoft Dynamics CRM 2011

http://support.microsoft.com/kb/2466084

CRM stop working after imported SiteMap

Sometimes change the SiteMap by adding privileges to hide area for some users will cause an error.

From event log,

Exception type: ArgumentException

Exception message: Invalid privilegeId: ReadDeep

Parameter name: privilegeId


Solution:

Just browse through following link and do import of the backup Sitemap

http://HostName/OrganizationName/tools/solution/import/SolutionImportWizard.aspx

CRM 2011 Javascript Intellisense

With Visual Studio 2008 onwards, we can have some javascripts intellisense when writing our codes. However, Visual Studio doesn’t know anything about MS CRM. So we don’t get a lot of advantage from this but we can extend the use of the intellisense functionality by using MS CRM Javascript Intellisense Generator
Once downloaded the tool, all you need to do is enter the name of the discovery server and (optional) the credentials, select the organization and save the output. This will create the folder with 1 javascript file for each entity.
 
If you have Visual Studio open you will need to close and reopen your project to see the new folder.All that is left is adding a reference in you account.js file to let visual studio know where to look for intellisense.
 

*Updated for CRM 2011 Xrm.Page.
Download this js file XrmPage-vsdoc.js and now you can have IntelliSense for Xrm.Page.

Tips - Installing Microsoft CRM for Outlook Offline with SQL 2008 Express

You have to name your SQL 2008 Express instance “CRM” before installing CRM for Outlook with Offline Access.

When CRM for Outlook installation, it looks for an instance called “CRM,” and if it doesn’t exist, it downloads and installs SQL 2008 Express.

Impersonation

//createdby     - user 1
//owner         - user 2
//impersonate   - crm-service
CrmAuthenticationToken token = new CrmAuthenticationToken();
token.AuthenticationType = 0; // Use Active Directory authentication.
token.OrganizationName = "LP";

// Use the global user ID of the system user that is to be impersonated.
token.CallerId = new Guid("C0C0A8E9-A72D-E011-80B9-00155DC85005"); //user 1

CrmService crmService = new CrmService();
crmService.Url = "http://tl:5555/MSCRMServices/2007/CrmService.asmx";
crmService.CrmAuthenticationTokenValue = token;
crmService.Credentials = new System.Net.NetworkCredential("CRM-Service", "Password2k", "company");

// Create a new account owned by the impersonated user.
account account = new account();
account.name = "Fabrikam !!";
Owner owner = new Owner();
owner.Value = new Guid("D57CC3F3-A92D-E011-80B9-00155DC85005"); //user 2
owner.type = EntityName.systemuser.ToString();
account.ownerid = owner;
Guid accountid = crmService.Create(account);

Showing Number of Notes on Notes Tab

var totalNotes = getTotalNotes(crmForm.ObjectId);
setNoteTabName(totalNotes);

function setNoteTabName(count) {
    /* update note tab */
    if (crmForm.FormType != 1) {
        var cells = document.getElementsByTagName("A");
        for (var i = 0; i < cells.length; i++) {
            if (cells[i].innerText == "Notes") {
                if (count > 0) {
                    cells[i].innerText = "Notes (" + count + ")";
                    document.all.crmTabBar.style.width = "auto";
                }
                break;
            }
        }
    }
}
// Helper method to return the total notes associated with an object
function getTotalNotes(objectId) {
    // Define SOAP message
    var xml =
[
"<?xml version='1.0' encoding='utf-8'?>",
"<soap:Envelope xmlns:soap=\"http://schemas.xmlsoap.org/soap/envelope/\" ",
"xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" ",
"xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">",
GenerateAuthenticationHeader(),
"<soap:Body>",
"<RetrieveMultiple xmlns='http://schemas.microsoft.com/crm/2007/WebServices'>",
"<query xmlns:q1='http://schemas.microsoft.com/crm/2006/Query' ",
"xsi:type='q1:QueryExpression'>",
"<q1:EntityName>annotation</q1:EntityName>",
"<q1:ColumnSet xsi:type=\"q1:ColumnSet\"><q1:Attributes><q1:Attribute>createdon</q1:Attribute></q1:Attributes></q1:ColumnSet>",
"<q1:Distinct>false</q1:Distinct><q1:Criteria><q1:FilterOperator>And</q1:FilterOperator>",
"<q1:Conditions><q1:Condition><q1:AttributeName>objectid</q1:AttributeName><q1:Operator>Equal</q1:Operator>",
"<q1:Values><q1:Value xsi:type=\"xsd:string\">",
objectId,
"</q1:Value></q1:Values></q1:Condition></q1:Conditions></q1:Criteria>",
"</query>",
"</RetrieveMultiple>",
"</soap:Body>",
"</soap:Envelope>"
].join("");
    var resultXml = executeSoapRequest("RetrieveMultiple", xml);
    return getMultipleNodeCount(resultXml, "q1:createdon");
}
// Helper method to execute a SOAP request
function executeSoapRequest(action, xml) {
    var actionUrl = "http://schemas.microsoft.com/crm/2007/WebServices/";
    actionUrl += action;
    var xmlHttpRequest = new ActiveXObject("Msxml2.XMLHTTP");
    xmlHttpRequest.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);
    xmlHttpRequest.setRequestHeader("SOAPAction", actionUrl);
    xmlHttpRequest.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
    xmlHttpRequest.setRequestHeader("Content-Length", xml.length);
    xmlHttpRequest.send(xml);
    var resultXml = xmlHttpRequest.responseXML;
    return resultXml;
}
// Helper method to return total # of nodes from XML
function getMultipleNodeCount(tree, el) {
    var e = null;
    e = tree.getElementsByTagName(el);
    return e.length;
}