SQL Server - Read Excel, CSV 2003, 2007 files using OpenRowSet

In SQL Server, we can read the data directly from an Excel file or a .csv file using OpenRowSet function as given below:

For OPENROWSET function to work properly in SQL Server 2005, you need to check "Enable OPENROWSET and OPENDATASOURCE Support" using SQL Server Surface Area Configuration for Features.

 Once you are done with that you can make use of the below queries to read the data:

--Read .CSV file from a 32 bit SQL Server 
SELECT * FROM OPENROWSET
('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DEFAULTDIR=D:\DWP Deliverables\CSPRojectIntegration\;Extensions=CSV;','SELECT * FROM CSProjects.csv')

--Read Excel file from a 32 bit SQL Server 
SELECT *  FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\DWP Deliverables\CSProjects.xls',
'SELECT * FROM [Sheet1$]')

--Read Excel 2007 file from a 32 bit SQL Server 
Another workaround is trying to install Microsoft.Jet.OLEDB.12.0, and use your connection string with OLEDB 12.0, it can be downloaded at :
2007 Office System Driver:  http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=7554f536-8c28-4598-9b72-ef94e038c891

On the other hand, you can save the 2007 file to 2003 format and try reding it from OLEDB4.0 driver

--Read files from 64 bit SQL Server
JET OLEDB Provider works only with 32 bit. For 64-bit OLEDB Provider for ODBC you have to install MSDASQL using the below link http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=20065#overview


Hope this helps!!

MS CRM SQL Server CPU Utilization shows 100% - Troubleshooting steps

We had a weird issue with MS CRM SQL server recently where the CPU utilization of sql server constantly shows 100% for a long time. When we checked in the task manager we found that sql process alone is taking around 98 - 99%. We have application and database on different servers. We tried to reboot both the servers couple of time and the behavior is such a way that the utilization goes down whenever we restart the machine and raises up to 100% after 10-15 min.

But the only relief from our side is the CRM application is not entirely down but the performance was less.So, we got some time to debug into the issue.

We tried the following steps in our process of troubleshooting the issue:

1. First thing to check if there are any recent changes or deployments made to the system.
2. In the task manager check the process that is consuming the cpu utilization.
3. Check the application logs and system logs in EventViewer of the crm server. If the app server and sql servers are hosted in different servers then check the event logs in both the servers.
4. Stop the Asynchronous Service in MS CRM server and check the cpu utilization. If it gets down then issue is probably with the ms crm app server. It could be due to customizations you made which is running into deadlock situations.
5. If it is still showing 100% then stop the sql server services and check the utilization.
6. Start the CRM AsyncService and see the utilization.If it works fine, then start the sql services as well.
7. If you still find the issue, you can Start Running the SQL Profiler to see if any query is taking long time to execute. If you find any, then disable the functionality that is making use of that query for a while and check the utilization.
8. Check if any event log appears in System. Check if automatic windows updates are turned ON on the server. Sometimes windows updates might not run successfully and causes the issue so check the last updated time from windows and see if the error in the event log starts occurring from that time. It will  be set when the windows updates run for the next time or you can manually run the windows updates and restart the server.

 The reason in my case is the automatic window updates failed to run successfully that impacted the sql server process.

If the above steps did not resolve your issue and if you are making use of some third party services running on the same server then, instead of restarting the services, try stopping the services one at a time, check if there is any impact on the utilization and once you are done with stopping all the services and start the services one at a time. This way you can get closer to the root cause of the issue. 

Hope this saves sometime in your debugging process!!

MS CRM 4.0 How to view detailed Error Message

It is a common practice for the developers working on MS CRM to view the exact error message to fix the issues instead of seeing the custom error page displayed to the end user.
In order to view the error message, below are the different options available

1. Enable Trace which logs the details of the error. This can be implemented on live server so that end users still see the custom error message instead of the original error
2. Modify the web.config file to set "DevErrors" to "On" on the MS CRM server as per the below microsoft article http://support.microsoft.com/kb/857192

How to create a dropdown or picklist column with a list of values in ms excel

Say we would like to create a drop down column in excel sheet with a list of predefined values. Create a list of predefined values in a new sheet and select the list of values and right click and select Name a Range..and give some name say "MyCustomList"

Now click on the column on which you would like to create a dropdown. 
Select Data on the top menu and go to Data Validation -> Data Validation. In the validation criteria, select list in the allow section and give the Source as the name of the range you have chosen like
 =MyCustomList

Click ok to see the dropdown column with the list of values defined in the range. If you want to remove the dropdown then under Data validation, choose Allow Any value.

MS CRM 4.0 Enrich Data - Update MS CRM Records using Excel

By using native data import feature in MS CRM 4.0, we have the ability to update the existing data apart from creating new records. This is called Data Enrichment which is the new feature brought up in MSCRM 4.0. This can be done by exporting your data into an Excel Sheet. In general, when we export the data to a static excel sheet, you find the excel sheet columns start from B instead of A. This column A is in hidden state which holds the GUID of existing record.
If you are so curious, you can unhide the column A to see the GUID. Please follow the steps in this link to unhide the excel columns.

Make your changes to the data in the same excel sheet and save it as a .CSV file. Now, re-import the data using standard data import wizard. This time when you upload the file, you will be pointed to a new checkbox option 'Enrich data by updating existing records rather than creating new records'. This option is used to update the changes you made for the existing records instead of creating new ones.

MS Excel How to view hidden columns

You can unhide any column in MS Excel by using the following steps.
1. Select all the columns using Ctrl+A
2. Point the cursor on any column and right click and Unhide. That's it. Now you can see all the hidden columns in the excel sheet.

Scribe Integration with MS CRM Mapping Picklist field in DTS

1. Open up your DTS file and select your source and target fields and click Datalink

2. Now, Right click on the target field and Edit Data Formula.. Click on calculated variable and put the source field like S1 and point the dropdown to String

3 Next, insert this calculated variable in the formula and voila!

4. This maps your source picklist field with text values to the target picklist fields

Scribe Integration with MS CRM Map Lookup field in DTS

1. Open DTS file and select your source field and targte lookup field and click DataLink
2. Right click on the target lookup field and click on Edit Data Formula..
3. Use DBLookup function as

Syntax: DBLOOKUP( TextSourceField, "connection", "table", "lookup_field", "substitution_field" )
Where connection = "T" for Scribe versions below 7.0 and connection = "Adapter for Dynamics CRM" for Scribe 7.0 and later.
Example for an Account lookup field
DBLOOKUP( S1, "Adapter for Dynamics CRM", "account", "name", "accountid" )

MS CRM Workflows Waiting and Failed - How to check the error details

1. Open Advanced Find and point the entity to System Jobs

2. Add the filter criteria as defined below:

   SystemJobName Equals your_customworkflow_name

   ErrorCode ContainsData

3. Edit the columns and include MessageName, ErrorCode

4. Click Find to get the list of records that got failed in running the custom workflow with a

   detailed error message

MS CRM Page Limit - Increase Number of Records Per Page more than 250

The default Page limit for MS CRM is 50. This can be increased to 250 using Tools -> Options from the main menu. This is the maximum value in MS CRM 4.0. 
Sometimes you might want to view records more than 250 which can be used to run the bulk edit feature or run a workflow on more than 250 records which saves time. 
To display a count more than 250, you can modify the PagingLimit value in the database for a particular user using the below query.

UPDATE UserSettingsBase SET PagingLimit=500 WHERE SystemUserId = Guid of the SystemUser

MS CRM 4.0 Timeout Issue - Timeout period elapsed prior to completion of the operation

Error: "The timeout period elapsed prior to completion of the operation or the server is not responding"

You can come across the timeout issue in MS CRM in one of the following scenarios:

1. when installing the update rollup for MS CRM

2. when importing the customization files

3. when running advanced find query that runs more than the expected time to get the results 

4. when importing New Organization

By default, the oledb timeout is set to 30 seconds in MS CRM. It is obvious to increase this value to meet your requirements. To accomplish this, you can modify the OLEDBTimeout and ExtendedTimeout values which is briefly described in the below article:

Increase MS CRM Timeout

MS CRM 4.0 - Set OLEDBTimeout and the ExtendedTimeout registry subkeys

To set the values of OLEDBTimeout and ExtendedTimeout, you have to login to the MS CRM server as administrator.


OLEDBTimeout: 

1. Start, Run, type regedit, and then click OK.
2. Locate the following registry subkey:
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM
3. Right-click MSCRM, point to New, and then click DWORD Value to create a new DWORD value.

4. Rename the DWORD value to the following value: OLEDBTimeout. The default OLEDB 

    timeout value is 30 seconds
5. Right-click the DWORD value, and then click Modify. In the Edit DWORD Value dialog

    box, type 86400 in the Value data box, click Decimal in the Base option, and then click

    OK.

ExtendedTimeout:
1. Right-click MSCRM, point to New, and then click DWORD Value to create a new DWORD value.

2. Rename the DWORD value to the following value: ExtendedTimeout

3. Right-click the DWORD value, and then click Modify. In the Edit DWORD Value dialog 

   box, type 1000000 in the Value data box, and then click OK. You can type a value that is

   larger than 1,000,000. However, do not type a value that is larger than 2,147,483,647. 

   This is hexadecimal 0x7FFFFFFF.

If this key already exists, note down the current value. Once you are done with the task like import or the upgrade of Microsoft Dynamics CRM, revert the value to its original value or delete the key if it doesn't exist previously.

Restart IIS to take these values into effect.

MS CRM 4.0 Export To Excel - Export Records more than 10000

In real time scenario, there are situations where you would like to import huge date from MSCRM to Excel. But, there is a limitation in the export to excel feature that, it imports a maximum of 10,000 records by default. To import all the data we need to update the column value of 'MaxRecordsForExportToExcel' in the database. 


1. Open SQL Server Management Studio
2. Open your MS CRM 4.0 Organization Database (OrganizationName_MSCRM)
3. Check the current limit by using the below query
    Select MaxRecordsForExportToExcel From OrganizationBase
4. Update the MaxRecordsForExportToExcel column in OrganizationBase to the desired 
    value.
    Update OrganizationBase Set MaxRecordsForExportToExcel=50000
5. Reset the IIS Server

MS CRM - Hide or Collapse Form Assistant

1. Open the MS CRM Entity Form from Customizations
2. Go to Display Tab
3. Uncheck the below items based on your requirement
    Enable Form Assistant
    Expanded by Default

MS CRM Debugging Custom Plugin Code

Follow the steps to succesfully start debugging of your custom plugin code in MSCRM.

1. Place the DLL and PDB files in the CRM installed path. By default, it points to following path C:\Program Files\Microsoft Dynamics CRM\Server\bin\assembly
If you are unable to paste the files, then reset the IIS and CRMAsyncService and try.
2. Register the workflow to Disk instead of Database using PluginRegister tool
3. After sucessfully placing those files, open the code on the server using Visual Studio and put a break point and press Ctrl+Alt+P that opens 'Attach to process' window.
Check these 2 fields
      1. Show processes from all users
      2. Show processes in all sessions

4. Now, select w3wp.exe and click on Attach. If you are trying to debug a workflow, then attach it to
    CrmAsynService.exe
5. Open the crm window and run the functionality that triggers the custom plugin and see the debugger works for you.
6. Reset IIS if you are unable to debug and try again.

MS CRM Reports - Email Subscription and Scheduling

Once you are done with creating a report in ms crm, the next probable step is to subscribe the report inorder to automatically send the report to a particular emailaddress at particular intervals. We use the Report Manager Subscriptions for this automation purpose.

Inorder to create a subscription, follow the below steps:
1. Open the Report Manager from the browser using the below url - 
    http://reportingserver/reports
    here, ReportingServerName should be replaced by the name of your server on which  
    SSRS is installed and which is pointed by MS CRM during the installation process.
2. Open the folder where the name of the folder is same as your CRM Organization name
3. Open your specific report. If it is insisting for credentials then you need to update the 
    DataSource in the Properties tab to automatically supply the credentials.
4.In the Properties tab, under General, give appropriate name to the report which can be 
   displayed when this report has been sent as an attachment from the subscription process. 
   By default, this is having a GUID value.
5. Go to Data Sources. Best practice is to point to a Shared data source with the following 
    properties.
    Connection type: Microsoft SQL Server
    Connection string: Data Source=SQLServerName; Initial Catalog=DatabaseName
    Connect using:
      Credentials stored securely in the report server
      Provide the windows credentials that are having necessary privileges to run the report
      Check the Use as Windows credentials when connecting to the data source.
6. Apply it
7. Go to Subscriptions and click on New Subscription. Provide the necessary fields like To, 
    Reply-To, Subject..
8. Check Include Report and Select required Render Format
9. Select Schedule and click Ok

You can test the subscription by scheduling this for "Once" in Select Scehdule and verify if the report is getting delivered in the way it is required.
If you come across any error during the delivery, most of the times it happens with the credentials. The credentials you supplied do not have enough privileges to run the report. so, try with admin credentials to confirm this and provide the credentials with updated privileges.

MS CRM Debugging Custom Workflow code

Follow the steps to succesfully start debugging of your custom workflow code in MSCRM.

1. Place the DLL and PDB files in the CRM installed path. By default, it points to following path C:\Program Files\Microsoft Dynamics CRM\Server\bin\assembly
If you are unable to paste the files, then reset the IIS and  MicrosoftCRMAsynchronous service and try.
2. Register the workflow to Disk instead of Database using PluginRegister tool
3. After sucessfully placing those files, Reset MicrosoftCRMAsynchronous service from windows services
4. Open the code on the server using Visual Studio and put a break point and press Ctrl+Alt+P that opens 'Attach to process' window.
Check these 2 fields
      1. Show processes from all users
      2. Show processes in all sessions

5. Now, select CrmAsynService.exe and click on Attach. If you are trying to debug a plugin, then attach it with w3wp.exe
6. Open the crm window and run the functionality that triggers the custom workflow and see the debugger works for you.

ms sql query against null values - ansi_nulls is ON

We use the below query to check against null values
select * from table_name where column_name is null which returns the records whose values are null for that particular column.

But, the below query will not yield any result eventhough there are records which contains null values.select * from table_name where column_name = null 

In the same way
select * from table_name where column_name != null will also produce no results.

This is because, ansi_nulls is set to ON by default in SQL.To make the above query work use set ansi_nulls offNow we get the same results with the following queries  

select * from table_name where column_name = null and
select * from table_name where column_name != null

Unable to access ms crm after the Import of Organization

In general, when we access ms crm through web browser using  http://MyCrmServer:Port, it automatically points to the default organization as http://MyCrmServer:Port/DefaultOrganizationName/loader.aspx

If we import an organization in crm, then we need to consider the following cases.

Case-1: If the installed MS CRM is a Professional Edition

Here Professional addition supports only one organization. So, as soon as we import an organization the old organization gets deleted and new organization becomes the default organization. So if we access http://MyCrmServer:Port, it points to the new organization.

Case-2:  If the installed MS CRM is an Enterprise Edition

Enterprise edition supports for multiple organizations. So, if we import an organization, both the organizations are present and the new organization will not become the default organization. So, to access the new organization the url is

http://MyCrmServer:Port/NewOrganizationName

Even if we set the new organization as default it is unable to resolve.

If we no more need the old organization then simply by disabling  and deleteing it makes the new organization default and everything works fine with this url

http://MyCrmServer:Port

Get the Context instance of ms crm workflow and plugin

To get the context of ms crm is the first phase every MS CRM developer come across when going to develop a custom workflow or plugin. The context instance of workflow or plugin gives us the details of the entity on which entity record this workflow is running which helps us to fetch more details to lead the custom logic.

//Workflow
[CrmWorkflowActivity("My Test Workflow")]
public partial class MyTestWorkflow : SequenceActivity
{
        #region Designer generated code
        ---
        ---
        #endregion
      protected override ActivityExecutionStatus Execute(ActivityExecutionContext context)
      {
         IContextService contextService = (IContextService)context.GetService(typeof(IContextService));                                  
         IWorkflowContext workflowContext = contextService.Context;
         Guid entityID = (Guid)context.OutputParameters.Properties["id"];
      }
}

//Plugin
public class Plugin:IPlugin
{
    public void Execute(IPluginExecutionContext context)
    {
        DynamicEntity entityInstance;
        if (context.InputParameters.Properties.Contains("Target") && 
               context.InputParameters.Properties["Target"] is DynamicEntity)
        {
             entityInstance= (DynamicEntity)context.InputParameters.Properties["Target"];             
        }                        
    }     
}

MS CRM Retrieve and RetrieveMultiple methods do not work on Custom or Dynamic Entities

RetrieveMultiple do not work to retrieve data from custom entities and it ends with the runtime exception if we try to get the records using this method.
Exception: Error in XML document. The specified type was not recognized.

This is because RetrieveMultiple works on strongly typed entities only. When we make use of SDK's we return custom entities as dynamic entities which are not strongly typed. In order to retrieve the Dynamic Entity, we should use Execute method as shown in the below code.

RetrieveMultipleRequest req = new RetrieveMultipleRequest();
req.Query = queryExpression;
req.ReturnDynamicEntities = true;
RetrieveMultipleResponse resp = (RetrieveMultipleResponse)_service.Execute(req);
if (resp != null && resp.BusinessEntityCollection.BusinessEntities.Count > 0)
{
  Guid columnID=Guid.Empty;
  dynEntity = (DynamicEntity)resp.BusinessEntityCollection.BusinessEntities[0];
  if ( dynEntity.Properties.Contains("new_columnid"))
     columnID = ((Lookup)dynEntity.Properties[" new_columnid"]).Value;
}

If we use crm webservices instead of sdk, we can make use of RetrieveMultiple without any issues.

Dynamically update or assign the owner of an entity in MS CRM 4.0

In general we use the Update method to update the fields in MSCRM. But, we cannot update the owner of an entity using update method. To update the owner, we have to make use of AssignRequest.

AssignRequest req = new AssignRequest();

SecurityPrincipal assignee = new SecurityPrincipal();

assignee.Type = SecurityPrincipalType.User; //User or Queue

assignee.PrincipalId = ownerID; //crm userid to whom we assign 

// Request changes depending on the entity. For account, it is TargetOwnedAccount

TargetOwnedLead target = new TargetOwnedLead(); 

target.EntityId = leadID; //Id of the lead record whose owner has to be updated

req.Assignee = assignee;

req.Target = target;

_service.Execute(req); //_service is the crm service

Add new column in search criteria of MS CRM 4.0

Search in MS CRM is a handy option to easily search records of a particular entity. But, sometimes we cannot find the records if we search based on certain fields. For example, you would like to search the list of Opportunities whose Potential Customer is XYZ and entered it in the search text box in Opportunities view and ended with no  results even there are Opportunities for that Potential Customer. This is due to the fields that are being used by the search criteria to search Opps. You need to add the Potential Customer field in the Find columns of Search criteria.
In order to add new search column in search criteria, follow the below steps:

1. Open Entity from Customizations
2. Under Forms and Views, open Quick Find View
3. Add Find Columns
4. Check the fields that you would like to include in the search criteria, in this case check the Potential Customer field
5. Save and Close the view 
6. Publish the entity

MS CRM - Update manager for crm user

Once we are done with creating an MS CRM User, then the Manager field becomes read only. I n order to update the Manager field, Open the user and go to Actions menu and click on Change Manager...

Details on MSCRM Entities ObjectCodes and Relationships in CRM Metadata Browser

A simple post but useful at times if you are looking to find the relationship between 2 Entities or the object type code of a particular entity. MS CRM comes with a metadata browser which gives brief details on the metadata of all entities, their object type codes and their relationships with other entities.

CRM Metadata browser url: http://crmservername:port/OrganizationName/sdk/list.aspx

How to update MS CRM User logon name

There might be times where we happen to update the domain logon name of a crm user when there is a change in the name in the Active Directory. If we try to update the logon name in MSCRM, we end up with the below error:

"You are attempting to create a user with a domain logon that is already used by another user. Select another domain logon and try again"


To overcome this and to change the logon name successfully, create a dummy user in the active directory and make sure that doesn't exist in crm users. Now, open the user whose logon name should be changed. Point the logon name to the dummy account and save it. Now, re open the user and point to the updated account and save it.

Easy way to build FetchXml query using Advanced Find in MS CRM 4.0

CRM 2011 comes with advanced features and one important feature that saves time is building FetchXml query. It comes with the out of box feature which includes FetchXml button in the Advanced Find window. Whereas in CRM 4.0 we can get the fetch xml using the below technique:

Open Advanced Find and Write the query and click on Find to get the result

In the Result window, click F11 and enter javascript:alert(resultRender.FetchXml.value); which opens an alert box with the fetch xml query. Click on the alert box and press Ctrl+c to copy the xml and make  use of it for easy development.

Outlook client configuration issue in MS CRM 4.0 Unauthorized Error

I really feel happy to share this and save some time for someone else as I had some really bad experience while configuring MSCRM 4.0 Outlook client. My CRM Server is on a different domain and while configuring the outlook on my machine, it asks for the crm server url and after providing it, I simply came across the below errors:

"The configuration wizard cannot connect to the Microsoft CRM server. This might indicate that the server is down".
or
"The request failed with http status 401: UnAuthorized" 

The error directly says this is an authentication issue and even I know that this issue is related to domain as it requires crm user credentials to connect to crm server but it is not insisting for credentials. So, how to supply the credentials? 

After a few hours of debugging, I could able to supply the credentials with the below settings:
1. Make sure to login as administrator on the client machine where we install outlook client
2. Add the crm sever url in trusted sites zone in internet explorer from tools menu - Internet Options - Security - Trusted sites zone
3. open user accounts from control panel on the client machine where you install the outlook client
4. Click on the Advanced tab
5. Click Manage Passwords button
6. Click on Manage your credentials if it is Windows 7
6. Under Windows Credentials section, add the login credentials  required to login to CRM. 
7. Provide the fullname for crm server as servername.domain.com in Internet or network address field

Giving alias name or different name to Linked Server in MS SQL

Today I would like to put sometime on giving alias named to Linked Server. I got some request to update the linked server name from Server_ABC to Server_XYZ. It sounds pretty simple to update the name but I came to know the real depth in it when I tried to find the dependencies. There are many dependencies for this linked server and most of the cases I found it in Store Procedures. So, I have to update the linked server name at all the places to reflect the change. In Order to get rid of this, we can make use of alias concept where we keep the linked server name to be more generic and pointing the data source points to original server name.

Follow these settings to create an alias name for linked server:

Right click on the target linked server and Properties
General Tab -
Linked Server: generic name
Server Type: choose other data source
Provider: SQL Native Client
Product: sql_server
DataSource: original server name

Security Tab - choose the appropriate security context
If you are connecting to a remote server,  click Be made using this security context and enter sql login credentials to connect to the linked server.

Server Options -
Collation Compatible: false
Data Access - True
Rpc - True
Rpc Out - True
Use Remote Collation - True
Connection Timeout & Query Timeout are by default set to 0 which is unlimited. Any value specified greater than 0 is treated in seconds.

This alias name helps us more when we work on the linked servers in both dev and production environment. We can make use of this alias concept to maintain same linked server name on both the environments.


Hope this helps!

Entity field is read only in bulk edit in mscrm 4.0

We all are very much handy using the bulk edit feature in MSCRM 4.0 to bulk update data. We might come across some scenario where we tried to update a field during bulk edit and found that field is set as read only even though the field is not read only field. 
The reason for this is there is an OnChange script published for that field letting not the users to bulk edit that field. For time being, you can uncheck the load script button on the onchange event and publish the entity and you can now able to edit the field.