Doing a migration of a CRM 4.0 system from one environment to another, I ran into this problem:
The target environment was a test environment and the owner of the environment assigned ‘only’ 2 GB of RAM.

CPU 100%
Immediately after I imported the organization using the Deployment Manager, the CrmAsyncService spiked the CPU and the memory load up to nearly 100% constantly…

Solution
Because we migrated from a machine that had enough RAM to a machine with 2GB (on a 64 bit machine…), the solution was to change the values of the fields ‘IntColumn’ in the Table: ‘DeploymentProperties’ of the ‘MSCRM_CONFIG’ database where the ColumName equals ‘AsyncInMemoryHigh’ and ‘AsyncInMemoryLow’.

crm_asyncsettings
Fig 1: DeploymentProperties table

The values that worked for us are:

ColumnName IntColumn
AsyncInMemoryHigh 50
AsyncInMemoryLow 20

These values are a lot higher in a default Crm implementation, something like 5000 and 2000, so the values I use here are only in case of very low memory.

After a restart of the CrmAsyncService (kill process and manually restart service, or wait for it to come up again), the problem was gone and Performance Monitor showed that the workflows (which caused the problem) where processed again.

What these settings do is they dictate the CrmAsyncService to take the amount specified in the AsyncItemsInMemoryHigh IntColumn of tasks to process in memory (and no more than is specified).
The service will process these tasks one at a time, untill it reaches the amount specified in the AsyncItemsInMemoryLow IntColumn, it than takes the amount of tasks in memory, that are specified in the AsyncItemsInMemoryHigh IntColumn again, until there are no more tasks to process.

Henry Cordes
My thoughts exactly…


 Also take a look at: >> Part 2

Multiple search-criteria searching is a often encountered requirement that is not always straight forward to implement. These days we have NHibernate, Entity Framework, LBLGen, LINQ to SQL and many more.
I find that the choice for an ORM is hard, simply because there is so much to choose from.
I will not go in the choice for one or the other, but will use LINQ to SQL in this example, in my opinion if you only need a one to one mapping of your database tables to domain objects and your database does not contain a lot of tables LINQ to SQL is the way to go. LINQ to SQL is intuitive, the querying of the DataContext works as you would expect it to work and the performance is better than a lot of stored procs and T-SQL I have seen running in production at some of my clients :-).
So in this post I am going to show how to implement a search using multiple search criteria with LINQ to SQL.

The table I use in my example is a simple table called Post, it only has a few fields (see picture 1).

Pic 1: The SQL Server design of the Post table
Pic 1: SQL server design of Table ‘Post’

T-SQL
So how do we search
with T-SQL, if we want the query to return all records of the ‘Post’ table where the Title field contains the text “mvc”.
So in T-SQL, that would be:

   1:  SELECT PostId, Title, … FROM Post WHERE Title LIKE ‘%mvc%’

Listing 1: T-SQL example LIKE query

Right? In T-SQL we have to place wildcards (the ‘%’) around the search criterion we use with the LIKE operator to get the behavior that records where the Title field is “asp.net mvc framework” will be returned also, not only the records where the title field has the exact value: “mvc”.

When we execute the sql directly in the SQL Server Manager the result shows 2 rows (see picture 2).

Pic 2: Execute T-SQL in SQL Server directly
Pic 2: Execute T-SQL in SQL Server directly

LINQ to SQL model
I use LINQ to SQL to create a model (or a DataContext). The model shows the ‘entity’ you see in picture 3. As I mentioned earlier it is a one to one mapping with the SQL table from picture 1.

Pic 3: The LINQ to SQL designer shows the Post entity
Pic 3: Post entity in LINQ to SQL model

Unittest
To easily proof how many rows will be returned
I create a unittest, that instanciates the MvcBlogDataContext and does a search in the Post table. The LINQ query also looks if the Title field of the Post table contains the text “mvc”. In the test the ‘Contains’ operator is used. ‘Contains’ in LINQ to SQL works exactly the same as the LIKE with the ‘%’ wildcards in listing 1. 

   1:  [TestMethod]
   2:  public void Search_For_mvc_in_Title_Test()
   3:  {
   4:    MvcBlogDataContext repository = new MvcBlogDataContext("Data Source=.;Initial Catalog=MvcBlog;Persist Security Info=True;");
   5:    List<Post> posts = repository.Posts.Where(p => p.Title.Contains("mvc")).ToList();
   6:   
   7:    Assert.IsNotNull(posts, "DataContext did not return posts when searching for 'mvc' in title");
   8:    Assert.AreEqual(posts.Count, 2, "DataContext did not return 2 posts when searching for 'mvc' in title");
   9:  }

Listing 2: Unittest that proofs 2 records are in database

The test does an Assert on the posts object not being Null and on the number of posts in the posts object (List<Post>) being two. When we execute the test, will the test pass, or fail?

Pic 4: The unittest to proof we have 2 rows containing 'mvc' in the Title field of the Post table passed succesfully
Pic 4: Unittest to proof we have 2 rows containing 'mvc' in the Title field has passed

The test passed, the result returns two rows, so we now know two rows in the Post table contain a Title field that contains "mvc".

Make a search with multiple search criteria
Now when we have one search criterion, the LINQ to SQL query is straight forward. But in real life the requirements are in a lot of cases that you have a lot of search criteria, which can be used alone, or in combinations. So how do we go about in making a search with multiple search criteria?

   1:  [TestMethod]
   2:  public void Search_For_mvc_in_Title_And_Use_Paging_Test()
   3:  {
   4:    MvcBlogDataContext repository = new MvcBlogDataContext("Data Source=.;Initial Catalog=MvcBlog;Persist Security Info=True;");
   5:   
   6:    var postsQuery = from p in repository.Posts
   7:                     select p;
   8:   
   9:    postsQuery = GetPostsQuery(postsQuery, "mvc", "", "", null);
  10:    postsQuery = GetPostsPagingQuery(postsQuery, 0, 5);
  11:   
  12:    List<Post> posts = postsQuery.ToList();
  13:   
  14:    Assert.IsNotNull(posts, "DataContext did not return posts when searching for 'mvc' in title");
  15:    Assert.AreEqual(posts.Count, 2, "DataContext did not return 2 posts when searching for 'mvc' in title");
  16:  }

Listing 3: Unittest search with multiple search criteria and paging

Listing 3 shows a unittest that uses the MvcBlogDataContext, the by LINQ to SQL generated datacontext. Om line 6 the postsQuery object inferes it’s type (IQueryable<Post>) from the LINQ Query that selects all posts in the datacontext.
Line 9 in listing 3 calls a method GetPostsQuery that expects an IQueryable<Post> object and some other parameters (our search criteria). We pass the postQuery that selects all posts from the datacontext.

Line 10 in listing 3 does almost the same trick, but than with other parameters, the GetPostsPagingQuery extends the query with Paging criteria and returns the extended query again as an IQueryable<Post> object.

Line 12 in listing 3 executes the query by calling ToList().

Line 14 in listing 3 does an Assert on the posts object not being Null and the next line asserts the number of posts in the posts object (List<Post>) being two.

Multi search criteria LINQ to SQL query
The following listing (4) shows the method GetPostsQuery, the method that does the actual work of building the LINQ Query according the values of the search criteria. The method’s first parameter is of type IQueryable<Post>, it returns an IQueryable<Post> also. When we use IQueryable<T> it is possible to pass LINQ queries around and extend it. 
The method in listing 4 does check for every criteria (parameter) if it has a valid value. If it doesn’t, nothing happens and we move on. If a parameter (search criterion) does contain a valid value the method will extend the query by adding the criterion to the query. For nvarchar fields the Contains operator is used. Again ‘'Contains’ works exactly the same as the LIKE with the ‘%’ wildcards from listing 1.

   1:  /// <summary>
   2:  /// Gets a LINQ to SQL Query according the provided parameters
   3:  /// </summary>
   4:  /// <param name="postsQuery"></param>
   5:  /// <param name="title"></param>
   6:  /// <param name="tags"></param>
   7:  /// <param name="createdOn"></param>
   8:  /// <param name="bodyText"></param>
   9:  /// <returns></returns>
  10:  private IQueryable<Post> GetPostsQuery(IQueryable<Post> postsQuery, 
  11:                                         string title, 
  12:                                         string tags, 
  13:                                         string bodyText,
  14:                                         DateTime? createdOn)
  15:  {
  16:      if (!string.IsNullOrEmpty(title))
  17:          postsQuery = postsQuery.Where(p => p.Title.Contains(title));
  18:   
  19:      if (!string.IsNullOrEmpty(tags))
  20:          postsQuery = postsQuery.Where(p => p.Tags.Contains(tags));
  21:   
  22:      if (!string.IsNullOrEmpty(bodyText))
  23:          postsQuery = postsQuery.Where(p => p.Body.Contains(bodyText));
  24:   
  25:      if (createdOn.HasValue && createdOn.Value > DateTime.MinValue)
  26:          postsQuery = postsQuery.Where(p => p.CreatedOn.Value.Date == createdOn.Value.Date);
  27:   
  28:      return postsQuery;
  29:  }

Listing 4: IQueryable<Post> GetPostsQuery, extends and returns the IQueryable<Post> query

Listing 5 contains the method GetpostsPagingQuery. This method also extends and returns the query. LINQ to SQL provides paging with the Skip (skip all rows untill) and Take (take n number of rows) methods. And this method uses these methods to extends the query with paging capabilities.

   1:  private IQueryable<Post> GetPostsPagingQuery(IQueryable<Post> postsQuery,
   2:                                         int? startRow,
   3:                                         int? rowCount)
   4:  {
   5:      if ((startRow.HasValue) && (rowCount.HasValue && rowCount.Value > 0))
   6:          postsQuery = postsQuery.Skip((int)startRow).Take((int)rowCount);
   7:   
   8:      return postsQuery;
   9:  }

Listing 5: Adds paging to query, using Skip and Take methods

We have everything wired up, all we have to do is execute the TestMethod in listing 3. In this test we only check for the title field to contain the text “mvc”. I think the idea is quite obvious, but in this post i do not really test it. So when I run the test, it passes, the test returned the same two rows like before:

 
Pic 5: The unittest that executed the programatically created LINQ Query according the search criteria passed succesfull

With LINQ to SQL it is possible to code queries that have to search using one or more search criteria in a structured and type safe way. It is not necessary to use string concatenation using wildcards. With LINQ to SQL we can solve this problem in a more elegant way. The fact that the SQL generated by LINQ to SQL performs really well is the icing on the cake. Or are you the type of developer that likes to write stored procs that 90 % of the time are the same but only the field and tablenames vary…

Henry Cordes
My thoughts exactly…


While creating and editing a database in SQL server 2008 I soon ran into a brick wall, or a 'by design' feature of the Database Management Studio.
When you design a table in a database and then try to make a change to a table structure that requires the table to be recreated, the Database Management Studio will not allow you to save the changes.
This is caused by a configuration setting that default results in the following dialog:

Error dialog

This dialog states: "Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created."

You can only react clicking the Cancel button.
And than Management Studio shows this dialog:

 

So what you want to do to configure the setting with the value you want (IMHO should be the default setting!).
Go to: Tools > Options

Tools menu choose Options

Select the tab Designers and choose Tables and designers

And uncheck the option: "Prevent saving changes that require table re-creation".
Once you now save changes it will work.

Henry Cordes
My thoughts exactly...


SQL Server 2008 install error

Published 11/2/2008 by Henry in SQL
Tags:

I wanted to start using SQL server 2008 on my dev machine.

Microsoft SQL Server 2008 logo

I removed all sql server 2005 installations and Visual Studio 2005 (BI Tools).
I am running vista 64 bit, so I install SQL Server 2008 64 bit.
I needed to install sql server 2008 on another drive than the default suggested 'C:\Program Files\Microsoft Sql Server\'. The drive and path I wanted to use was 'D:\Program Files\Microsoft Sql Server\'.
During setup (when selecting features), I received the following error : "The INSTANCESHAREDWOWDIR command line value was not specified. This value must be specified when the INSTANCESHAREDDIR value is specified."
Yeah, I know this is really helpfull, don't you agree...
It appears that this error only occurs if I select an install dir different from the default one (C:\Program Files\Microsoft Sql Server).


I found a work-around:

  1. Run the setup, select all the features you want to install. DO NOT change the install folder, press next 
  2. When you see the screen displaying the configuration summary (just continue the setup untill you can copy the ConfigurationFile.ini path)
  3. Copy/paste the  ConfigurationFile.ini file on the drive/folder you want to use for installation
  4. Edit this file to set correct values for INSTALLSHAREDDIR & INSTALLSHAREDWOWDIR (for my case I set D:\Program Files\Microsoft Sql Server and D:\Program Files\Microsoft Sql Server (x86))
  5. This step was neccesary because I want to use mixed mode authentication, if you also want to use mixed mode authentication you have to add the sa password to the ini file:
     If you have: SECURITYMODE="SQL"
     Add this entry to the file:
     SAPWD="<Password>"
  6. Run setup from the command line : setup.exe /q /action=Install /configurationfile=<Path to your custom .ini>
     In my case, I first did a 'G:' and than an '<ENTER>' in the command prompt to navigate to the SQL Server 2008 installation Drive, than I entered:
     setup.exe /q /action=Install /configurationfile="D:\Program Files\Microsoft Sql Server\ConfigurationFile.ini"
     <ENTER>
     Note : the /q stands for silent mode. I didn't try without this switch.

The setup build I used was:  10.00.1600.22.

In the end I succesfully installed SQL server 2008 on my dev machine, but it took me a lot longer than initially was the plan.

Henry Cordes
My thoughts exactly...


SQL Server and Dynamic SQL

Published 8/20/2005 by Henry in SQL
Tags:
HTML Source EditorWord wrap

'EXEC()', 'sp_executesql' and 'EXEC('EXEC sp_executesql ...')'

There are two ways to invoke dynamic SQL in MS SQL Server, 'EXEC()' and 'sp_executesql'. And you can use a combination of the two 'EXEC('EXEC sp_executesql ...')'.
EXEC() is the most simple, as illustrated by this example:

   1:  SELECT @table = 'sales' EXEC('SELECT * FROM ' + @table) 

The permissions of the current user always apply, even if the statement appears in a stored procedure. 'EXEC(''')' is similar to the EXEC of a stored procedure. But rather than calling a stored procedure, you invoke a batch of single SQL statements.

sp_executesql has the advantage that it allows you to pass parameters to the dynamic query, both for input and output.
Here is an example with an output parameter:

   1:  DECLARE @sql nvarchar(4000) -- nvarchar(MAX) on SQL 2005. DECLARE @col sysname 
   2:  DECLARE @min varchar(20) 
   3:  SELECT @col = N'au_fname' 
   4:  SELECT @sql = N'SELECT @min = convert(varchar(20), MIN(' + @col + N')) FROM authors' 
   5:  EXEC sp_executesql @sql, N'@min varchar(20) OUTPUT', @min OUTPUT SELECT @min 

Thus, you can much easier get a value from your dynamic SQL statement into a local variable than with EXEC().

These points apply to EXEC() and sp_executesql as well:

  • The SQL code is it in own scope, and you have no access to the variables in the calling stored procedure;
  • The permissions of the actual user apply;
  • Any USE statement will not affect the calling stored procedure;
  • Temp tables created in the SQL batch will not be available to the calling procedure;
  • SET statements only affects the SQL batch, not the caller;
  • Termination of the batch executed by sp_executesql terminates the calling procedure too;
  • @@error reports the status of the last statement in the dynamic SQL code;
  • According to Books Online the return value from sp_executesql is either 0 for success or 1 for failure. However it appears that the return value is the final value of @@error, at least in SQL 2000.

EXEC('EXEC sp_executesql ....
By using 'EXEC('EXEC sp_executesql ....' it is possible to use statement parameters with the dynamic query string. The sql query must be in unicode string , in nvarchar the MAX characters is 4000. By using EXEC('EXEC sp_executesql ...., multiple parameters can be concatenated, which results in the possibility to use more than 4000 characters in the dynamic query (the point with dynamic ofcourse is that you can not be sure what length the query will be).

Example:

   1:  DECLARE @query nvarchar(4000)
   2:  DECLARE @@queryDynamic nvarchar(4000)
   3:  DECLARE @l_Startdate datetimeDECLARE @l_Enddate datetimeDECLARE @dbName varchar(50)
   4:  DECLARE @dbName2 varchar(50)
   5:   
   6:  SET @dbName = '[SQLServer_instance].[Databasename]'
   7:  SET @dbName2 = '[SQLServer_instance2].[Databasename2]' 
   8:  SET @query = 'SELECT * FROM ' + @dbName + '.dbo.tblTable ' + 'WHERE startDate >= @pl_Startdate AND Enddate < @pl_Enddate '
   9:  SET @@queryDynamic = 'UNION ' + 'SELECT * FROM ' + @dbName2 + '.dbo.tblTable ' + 'WHERE startDate >= @pl_Startdate AND Enddate < @pl_Enddate ' 
  10:   
  11:  EXEC('EXEC sp_executesql N''' + @query + @queryDynamic + ''', N''@pl_Startdate datetime ,
 12:                              @pl_Enddate datetime'', @pl_Startdate = ''' + @l_Startdate + ''', @pl_Enddate = ''' + @l_Enddate + '''')


Henry Cordes
My thoughts exactly...