CRM – Maximum number of records exported

Today, I encountered a limit when exporting records from CRM (versjon 4) to Excel. I only got the first 10,000 record in the Excel file and the customer wondered why. After some googling and help from collegues, I found a work-around. This approach worsk, but isn’t reccomended to do without contacting Microsoft through a support request. If the MaxRecordsForExportToExcel is changed without their approval (given in support request in most cases), you might loose support privileges in the future.

So, if you can wait for the processing of your support request, do that. If you can’t wait and absolutly need to export more than 10k records, this is the approach in CRM 4:

1. Login to the SQL Server where the <Organization_Name>_MSCRM database is stored
2. Open the OrganizationBase table
3. Find the Column:  MaxRecordsForExportToExcel
4. Change the value from 10,000 to the desired value
5. Committ the change to the database

Script:

Use <Organization_Name>_MSCRM ;
UPDATE OrganizationBase
SET MaxRecordsForExportToExcel = <new numer>
WHERE Name = <Organisation> ;

If you have just 1 row in table OrganizationBase, you don’t need the WHERE clause in the script above. In my case, I used 50000 as the new number, that allows me to export 50k record at the time to Excel.

UPDATED: I had to restart the SQL server and the CRM servers to get this working correctly.

Advertisements

IIS log parsing and performance tests

We had a stress test at work today and the test manager wanted some statistics on how the IIS performed during the test and what pages that had largest hit rates and so on. In addition, he wanted some overview of the error codes that had occurred during the test period.

We hadn’t much time after the test to create the basics for the report, so we did it after the KISS principle (Keep-It-Simple-Stupid). We decided to retrive a summerized hit rate grouped for each 10 minute, hit count for each page during the test and how many request was made grouped by IP-address.

I had to download and install the IIS LogParser application, as described in my earlier post, and create scripts to archive the result that the test manager wanted. Put the scripts into seperate files and run the command from the command prompt:

  1. Goto installation directory of LogParser
  2. Type command “LogParser.exe -i:W3C file:<script_filename.sql> -o:CSV

The following script will summerize the traffic statistics on the IIS for 17th June 2011.

SELECT 
    QUANTIZE(TO_LOCALTIME(TO_TIMESTAMP(date, time)), 600) AS Min10, 
    COUNT(*) AS Hits 
FROM C:\WINDOWS\system32\LogFiles\W3SVC1\* 
WHERE date >='2011-06-17' and date < '2011-06-18' 
Group By Min10

The following script will list the top 10 URLs with the most hits for 17th June 2011.

SELECT TOP 10  
    cs-uri-stem as Url,  
    COUNT(*) As Hits  
FROM C:\WINDOWS\system32\LogFiles\W3SVC1\*  
WHERE date >='2011-06-17' and date < '2011-06-18' 
GROUP BY cs-uri-stem  
ORDER By Hits DESC

The following script will list the IP and number of requests sent to the IIS for 17th June 2011.

SELECT c-ip As Machine,  
        COUNT(*) As Hits  
FROM C:\WINDOWS\system32\LogFiles\W3SVC1\* 
WHERE date >='2011-06-17' and date < '2011-06-18' 
GROUP BY Machine 
ORDER BY Hits DESC

Happy IIS debugging and performance testing.

Visio Forward Engineer Addin for Visio 2010

For those of you who like ER datamodelling i Visio, the Visio 2010 plug-in at forwardengineer.codeplex.com is a MUST. Visio doesn’t have forward engineering out-of-the-box, so this plug-in is just what I need for my ER modelling. This plug-in is developed by the people at http://www.sqlbi.com/. The source code for the plug-in can be downloaded from codeplex as well.

  1. Download and install plug-in installation package from codeplex.com
  2. Install package
  3. Start Visio 2010
  4. Open a Visio Database diagram and look for the Forward Engineer tab on the menu

I recommend to use int/bigint identity as PKs. Otherwise, there are some hickups in the scheme generations for the plug-in.

 

 

BizTalk Server 2010 RFID

I have always been facinated by RFID since I started working with tag based data in 1999. But I have never developed something useful with RFID techonlogy. This week, I was introduced to BizTalk Server 2010 RFID module. I got some help from a collegue to set up my local development environment. The client ‘BizTalk RFID Manager’ is used to configure the processes, device groups, reading devices og components bindings.

The component binding can be setup for different storage of tag events. In my case, I wants to store my tag events in a SQL database, called SQLServerSink. As default there are several other  available components you can add to you RFID installation:

  • SQLServerSink
  • RuleEnginePolicyExecutor
  • DuplicateEliminationFilter
  • VisibilityFilter
  • EpcFilter
  • EpicsEventForwarder
  • EventForwarder

I’m not going to describe each of these components for now. That has to be subject for future posts. I selected the SQLServerSink components. After configuration of the database, I got a a set of table that is used for the tag event collection. The main table where the tag events are stored is called ‘TagEvents’ and looks as follows:

CREATE TABLE [dbo].[TagEvents]
(
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[DeviceName] [dbo].[RfidName] NOT NULL,
	[TagId] [dbo].[RfidTagId] NOT NULL,
	[TagType] [int] NULL,
	[TagTypeDescription] [dbo].[RfidName] NULL,
	[TagSource] [dbo].[RfidName] NULL,
	[TagTime] [datetime] NOT NULL,
	[TagData] [image] NULL,
	[SinkTime] [datetime] NOT NULL,
	[ProcessName] [dbo].[RfidName] NOT NULL,
	[ExtData] [ntext] NULL,
	[LogicalDeviceName] [dbo].[RfidName] NOT NULL,
	[TagIdAsHex]  AS ([dbo].[LocalVarbinaryToHexString]([TagId])),
	[TagDataAsHex]  AS ([dbo].[LocalVarbinaryToHexString]([TagData]))
)

The column TagEvent.DeviceName correspond to the device you have configured in the BizTalk RFID Manager. The column TagEvents.TagId is the identificator for a tag. There are two timestamps for an event – TagTime and SinkTime. The difference between these two timestamps should not be large, and is the time used by the component binding to perform the event filtering.

By using the BizTalk RFID Manager, I created three devices – reader1, reader2 and reader3. These readers can be representing RFID readers in seperate rooms in a building. Each RFID tag can represent employee access cards. In my testing enviroment, I will create a database where I can define the access rights for each employee to the rooms represented by the reader 1..3.

This is enought for the time beeing, but as Arnold said: “I’ll be back!”

Creating CRM4 web service using proxy class

After my post earlier today, I got a question on how to create the proxy class for accessing a CRM4 installation. When I started my project for CRM import I was recommended to create a web service to implement access for against CRM4. I tended to use WCF to enhance my knowlegde on that techonlogy, but our CRM expert recommended standard web service.

Create CRM proxy class(es)

  • Login on the CRM4 server
  • Download Install the CRM4 SDK from microsoft.com
  • Go to <CRM4SDK>\microsoft.xrm\tools
  • Type the following command at the DOS prompt:
crmsvcutil /server:http://localhost/MyCompany
           /namespace:"CRM4.Xrm"
           /dataContextPrefix:CRM4 /out:Xrm.cs

MyCompany in the /server link, is the same as OrganisationName used for the CRM4 installation.

Now, we have the foundation for accessing CRM4 using LINQ.   I’m not going to describe how to create a web service, but here is some sted-by-step instructions at microsoft.com.  But when you have created the foundation for the web service, you need to add the appropriate DLLs as references from the CRM4SDK in your solution. These DLLs are as follow:

  • Crm4Dsk\bin\microsoft.crm.sdk.dll
  • Crm4Dsk\bin\microsoft.crm.sdktypeproxy.dll
  • Crm4Sdk\bin\microsoft.crm.sdktypeproxy.xmlserializers.dll
  • Crm4Sdk\microsoft.xrm\bin\microsoft.xrm.client.dll
  • Crm4Sdk\microsoft.xrm\bin\microsoft.xrm.portal.dll
  • System.Data.Services.dll
  • System.Data.Services.Client.dll

In addition, I always add logging using Log4Net in my development project.

When this is done, you might be adding DataContract classes for the CRM4 objects that should be used to pass values for creating and updating CRM data. The only important thing is to make sure the data contract is serializable. Example:

    [Serializable]
    public class AccountContract
    {
        public string AccountNumber { get; set; }
        public string Name { get; set; }
    }

Now, you can use this contract to create account objects and pass them to you web service methods.

Happy coding…

CRM4 and LINQ example

I have been working with CRM import lately. This import is implemented using CRM proxy classes for the account entity that enables use of LINQ statements when I’m going to perform lookups, insert and updates against the CRM4 object model. Here is a link for several LINQ examples.

var crm = new CRMDataContext(CrmConnection.Parse(ConnectionStringCrmService));
var acc = (from c in crm.accounts
           where c.accountnumber == accountNumber
           select c).FirstOrDefault();

The from…where…select statement gives my the CRM account object that has equal the account number accountNumber given as parameter to my method. The ConnectionStringCrmService property retrieves the connection string to the CRM4 web service that is used to construct the CRM data context object.

When you have found the CRM account, this can be modiefied by setting the properties for the appropriate values and calling the UpdateObject method, as shown below.

acc.name = "xxx";
crm.UpdateObject(acc);
crm.SaveChanges();

To add a new account to CRM4, you will create a new account object, assign the propper values through the account properties, call the AddToaccount method and finally call the SaveChanges method on the CRM4 data context object.

var tmp = new account();
tmp.accountnumber = "1234";
tmp.name = "MyComapny";
crm.AddToaccounts(tmp);
crm.SaveChanges();

Happy CRM and LINQ coding! 🙂