Thursday, May 26, 2011

SQL Returning Output Parameter values from Stored Procedure

This is the stored procedure

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



      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


To retrieve output value, you must use this code

   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;
   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;
  14:                  //Add the parameter to the Parameters collection. 
  15:                  command.Parameters.Add(pNewMonth);
  16:                  command.Parameters.Add(pCurrentMonth);
  18:                  command.ExecuteScalar();
  20:                  mth = Convert.ToInt32(command.Parameters["@currentMonth"].Value); 

Friday, April 29, 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.

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…

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.

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"

Wednesday, April 27, 2011

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

Friday, April 22, 2011

How to write good code

How do you justify "Good Code"?

Do them fast or do them right?