Archive
SQL Server Timeout Error – Importing CSV file
Other day I got a SQL Timeout error while importing Article’s using a CSV file which is of 75 KB size.
SQL Timeout Error – Data Import
Below are my troubleshooting steps and Fix .
Troubleshoot steps
- Verified both SQL Server and CRM App server’s event viewer.
- On CRM App server there was only one warning with an ‘Insert’ query. However there was no error information in the log, it’s just that Query was not completely written in the log.
CRM Warning – Insert Query
- On SQL Server machine there were no Error or Warning event logs. But there was an Information log around same time which had useful information.
Log – Autogrow of file
Reason
- In the SQL Server event log, there was an event with below details
“Autogrow of file ‘mscrm’ in database ‘XXXX_MSCRM’ was cancelled by user or timed out after 30824 milliseconds” information.
- So, the issue was with my CRM DB (i.e.XXXX_MSCRM) setting’s “AutoGrowth” property which has higher ‘File Growth’ value.
- It is recommended to keep 1024 MB for data files and 256 MB for log files.
Fix
- Open ‘XXXX_MSCRM’ AutoGrowth setting by following below steps
- Right click on the database.
- Go to Properties.
- Then go to the Files section.
- Click on either the data or log autogrowth column (Click the …)
- Provide the recommended ‘File Growth’ setting
Auto Growth Setting
Refer MSDN AutoGrowth Settings article for more details
🙂
Both ‘amount’ and ‘percentage’ cannot be set – CRM Data Import Error
I got below error when I tried to import OOB ‘ContractDetail’ record using Data Import.
Data import error
Reason:
- My Data Import template had ‘Discount’ and ‘Discount (%)’ fields and I gave 0 in both fields
Contract Detail Data Template
- Since ‘Discount (%)’ is computational field which would auto calculated by system, it given error while import.
Fix:
- You don’t need to input ‘Discount (%)’. Remove ‘Discount (%)’ field from your data file and import the file.
🙂
Things to be considered while using duplicate detection feature in CRM 2011
In CRM you can create “Duplicate detection rules” to avoid duplicate records in to the system.
Below are the scenarios I observed with duplicate detection in CRM 2011
Enabling duplicate rule while creating/Updating records using CRM service
- By default, when you execute a Create or Update request using the CRM Service, the record will be created/updated without checking for duplicates
- We can force the platform to check for duplicates by setting the “SuppressDuplicateDetection” optional parameter to “false”
- You get “A record was not created or updated because a duplicate of the current record already exists.” exception it matching record already exists
CreateRequest req = new CreateRequest();
Account myAcc = new Account(){
Name = “Rajeev”,
};
req.Target = myAcc;
req.Parameters[“SuppressDuplicateDetection“] = false;
try{
service.Execute(req);
}
catch (FaultException<OrganizationServiceFault> ex){
if (ex.Detail.ErrorCode == -2147220685){
// duplicate detected: Handle it here
}
else{
throw;
}
}
Enable/Disable duplicate detection rules while data import
Using “Duplicate Detection Settings” we can turn the duplicate detection rules on/off while data import. It’s a system level setting applicable to all imports
Duplicate Detection Settings
You can also use the “Allow Duplicates” option, right at the time of Import to turn off the duplicate detection rules
Duplicate Detection Data Import
The “Allow Duplicates” option works based on the duplicate detection settings and duplicate detection rules defined.
If you choose
- Yes
- It wont fire the duplicate detection rules
- No
- It will fire the duplicate detection rules
Duplicate Detection Rules Automatically get Unpublished
- By design, whenever any entity metadata is changed all duplicate rules associated with that entity are unpublished
- Whenever you import a solution you release, if the solution has customization change for an entity, say for ‘Account’ entity then the duplicate detection rule for ‘Account’ would turn into unpublished state.
- So whenever you imported a solution make sure your duplicate detection rules are in published state
🙂
Bulk Updating or Inserting records from Excel using CRM Data Import
Hi,
In CRM, you may come across scenarios where you need to Update/Insert a very large number of records very quickly. In these cases, opening each record’s form to make the change can be time-consuming.
We can handle this better by using CRM Data Import feature by which we can make bulk Update/Insert very quickly.
Below are the steps to achieve this. I am using ‘Contact’ entity for this article.
Steps :-
- Navigate to ‘Contact’ entity
- Click on “Export to Excel” button in the Ribbon menu
- In the “Export Data to Excel” dialog box which comes up, Select “Static worksheet with records” and check the “Make this data available…” check box.
"Export Data to Excel" Dialog Box
- Save the file
- Open the Excel file and it looks as below
Records in imported file
Important Points :-
* This Excel file has some unique characteristics which simplifies the data entry and re-import process.
* As you click on each cell, you will notice a pop-up that tells you format of the data and whether the field is required or not.
* Required fields are not enforced in Excel (i.e., Excel won’t validate even if you miss the value in required cell).
* If you miss a value in required field cell, the record wont be updated in the import process.
* You can enter “Lookup” values also, but the values must match with parent record.
For example, In this Excel “Parent Customer” cell is a lookup to the “Account” entity. So i need to give “Account” full name in the cell.
* If any wrong data entry in the lookup cell, the record will not be updated on import
- In the imported Excel (Above screen), If you observe, I don’t have “Middle Name” for any of my contacts. So I am giving middle name as “Updated” (Below screen)
Updating "Middle Name" field
- Next, I want to insert a new record “Rajeev Pentyala” to my contacts.
Important Point :-
– We can create new records by entering them in the bottom of Excel. (***Be sure to fill all required fields ***).
- So, In my last row of Excel, I enter “First Name”,”Last Name” ,”Email”,”Parent Customer” (i.e.,Valid full name of existing Account)
Adding new record in the last row
- Save the Excel file. (Ignore the warning and continue saving)
Ignore warning and save
- In the CRM, Click on “Import Data” button in the Ribbon
- In the dialog window browse the saved file
Import process
- Click “Next” button
- In the next window, click on “Submit” button
Import process - submit
Import Porcess - Finish
- Refresh the CRM application
- Now you can see the newly inserted contact “Rajeev Pentyala” with the given values in Excel
Import - New Contact
- You can also verify the remaining contacts with the updated “Middle Name”
- This is how we handle “Data import” process
Tip :-
- To verify your import finished successfully or any problems with it.
- Navigate to “Settings -> Data Management -> Imports”
Data Import - Settings
Imported File - Success & Failures
This article provides you a basic knowledge about Data import from Excel.
Hope it helps 🙂
Rajeev Pentyala - Microsoft Power Platform 