“Waiting for Resources” Error in MS CRM Bulk Delete Job

May 20th, 2010

I had an issue where a Bulk Delete job I had created was never executing and the status simply read “waiting for resources”.  I checked the Microsoft CRM Asynchronous Process Service and it was not started.  When I started it, I received an error:

“The Microsoft CRM Asynchronous Processing Service service on Local Computer started and then stopped.  Some services stop automatically if they are not in use by other services or programs.”

The service account was fine, so I checked the MS CRM logs for the service.  It was showing an error occurred in a method named “GetServerIdFromDatabase”.  I ran a SQL trace and discovered that the “NAME” column in table “SERVER” had the wrong value.  At one point the server was renamed, and apparently either a step was missed or it was done the wrong way – MS has a support article on moving CRM installations here: http://support.microsoft.com/kb/952934.

All I had to do was update the SERVER.NAME column with the correct value and the bulk delete job processed just fine.  Hopefully I won’t run into any other settings that may have been missed when things got moved, but thankfully there are logs and SQL traces!

Calculate Age

December 30th, 2009

A simple method to calculate age in years in C# and JavaScript.  Additional info on the number of days in a year can be found here.

C#
public static double CalculateAge( DateTime birthDate )
{
    double daysPerYear = 365.242199;
    TimeSpan span = DateTime.Now.Subtract( birthDate );
    double age = span.TotalDays / daysPerYear;
   

    return age;
}

JavaScript
function CalculateAge( birthDate )
{
   
var msPerDay = 1000 * 60 * 60 * 24;
   
var daysPerYear = 365.242199;
   
var age = ( new Date() – new Date(birthDate) ) / msPerDay / daysPerYear;
    return age;
}

Disabling SQL Server Constraints

November 17th, 2009

Adding and removing constraints from an existing SQL Server database table is performed using the ALTER TABLE statement. The article Using Microsoft SQL Server Constraints provides more information on constraints and some good examples and Books Online has specific details, if needed.

The following statement is an example of adding a primary key constraint to the Account table:

ALTER TABLE [Account] ADD CONSTRAINT [PK_Account] PRIMARY KEY( AccountId )

A common issue that I’ve run into is not adding/removing constraints, but rather needing to disable constraints when deleting data or importing data from related tables, including the primary keys. Disabling constraints is a two-step process because the constraints must also be re-enabled. For example, the following statement disables the ‘FK_Account’ Foreign Key constraint in the Account table.  The keyword “NOCHECK” disables the constraint and “CHECK” enables it.

ALTER TABLE [Account] NOCHECK CONSTRAINT [FK_Account]

Issuing a statement or two isn’t bad, but does require that one knows the name of the constraint to disable. What if you had to deal with five, ten, fifteen or more tables? I needed an automated way of identifying the constraints and creating the ALTER statements based on the constraint type.

Using the INFORMATION_SCHEMA.TABLE_CONSTRAINTS view I was able to automate the process of creating and executing the statements using a variation of the script below. This script will list ALL the Foreign Key constraints and the corresponding table for all tables in the current database. The script can be filtered as needed, but for my purposes, all tables were needed.

SELECT constraint_name, table_name
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE constraint_type = ‘FOREIGN KEY’
ORDER BY table_name

The above script was converted to the stored procedure below to disable/enable all the constraints of a specific type for all tables.

CREATE PROCEDURE [dbo].[ToggleConstraints]
(
@type    VARCHAR(128) = ‘FOREIGN KEY’
,@enable BIT = 1
)
AS
SET NOCOUNT ON

DECLARE @__CONSTRAINTS TABLE(Id INT IDENTITY(1,1), ConstraintName VARCHAR(128), TableName VARCHAR(128))
DECLARE @rows            INT
,@index          INT
,@sql            NVARCHAR(2000)
,@tableName      VARCHAR(128)
,@constraintName VARCHAR(128)

/* Retrieve the constraints using the INFORMATION SCHEMA View and place into a table
variable based on the specified type. Primary Key and Unique Key constraints are
excluded by default to prevent duplicate data from being inserted, but can also be
included by changing the constraint_type.  */

INSERT INTO @__CONSTRAINTS(ConstraintName, TableName)
SELECT constraint_name, table_name
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE constraint_type = @type
ORDER BY table_name

/* Set defaults */
SELECT
@rows = @@ROWCOUNT, @index = 1

/* Generate and execute the sql script */
WHILE( @index <= @rows )
BEGIN
SELECT @tableName = TableName, @constraintName = ConstraintName
FROM @__CONSTRAINTS
WHERE Id = @index

/* Toggle the constraint based on the @enable parameter */
SET @sql = ‘ALTER TABLE [' + @tableName + '] ‘ + CASE WHEN @enable = 0 THEN ‘NOCHECK’ ELSE ‘CHECK’ END + ‘ CONSTRAINT [' + @constraintName + ']‘

PRINT @sql
/* EXEC sp_executesql @sql */

SET @index = @index + 1
END

SET
NOCOUNT OFF

Simply uncomment the EXEC line if you want to actually run the generated statements.

Pinning Visual Studio Projects to the Taskbar in Windows 7

November 13th, 2009

I love the ability to have recent files listed and various shortcuts for documents of an application pinned to a program’s taskbar icon in Windows 7.  I had shortcuts to Visual Studio 2008 and Visual Studio 2005 pinned to the taskbar, but unfortunately, when you right click the taskbar icons you don’t get access to the most recently used solutions and projects – you only get the most recently used files.  You can’t even manually pin solutions or projects to them.  There is, however, a workaround!

Find your Visual Studio “VSLauncher.exe” application.  For me, it was located in: “C:\Program Files (x86)\Common Files\microsoft shared\MSEnv\VSLauncher.exe”.  Pin that to the taskbar.  You now have access to your most recently used items and you can also pin shortcuts to solutions and projects to that icon.

Here’s a screen shot (with some project & solution names partially blocked out).  Enjoy!

Waking A Computer From Sleep Mode Remotely

September 15th, 2009

Has your computer ever gone into sleep mode and you can’t remote desktop to it?  There’s a way to wake it up by sending what’s called a “magic packet”.  Before the “how”, here are some things to consider:

  1. You will need to know some info about the computer you want to wake up that you can only get from that computer (see below), so if you can’t remote to the computer in question now or physically get to it, this won’t help you until you can get the info.  I’d recommend you get the info and keep it with you so you have it when you need it.
  2. Depending on how your network is set up, your IP address may change.  They usually don’t change very often, but they can.
  3. If you are trying to reach a computer on your company’s network, you will need to be VPN’ed in.
  4. Your computer’s BIOS may require that you set enable a “Wake on LAN” setting.  The actual setting name may vary from machine to machine.
  5. There is one parameter that is outlined on the Depicus page listed below: the port number.  You can try without a port number – it will default to 7.  If port 7 doesn’t work, ask you network administrator.

Now, on to the “how”.  Here’s the info you will need to get from the computer you want to wake remotely:

  1. Your computer’s mac address (open a command prompt, type “ipconfig /all”, locate your network card and the “physical address” is your mac address)
  2. Your computers IP address (use the ipconfig /all to find it as well).
  3. Your subnet mask (use the ipconfig /all to find it)

Download the utility “wake-on-lan” command line utility – the link is at the bottom of the page.  http://www.depicus.com/wake-on-lan/wake-on-lan-cmd.aspx

After you download, save and extract the file, open a command prompt and browse to the location you extracted the file to.  Enter the following:

wolcmd [mac address] [ip address] [subnet mask]

replacing the values in brackets with the values for the computer you want to wake up.  That should wake up your computer remotely by sending a “magic packet”.

Creating Separate DLLs for App_Code Folders

September 4th, 2009

Starting with ASP.NET 2.0, Web Site projects were introduced and allowed for the compilation of the code-behind file for each page and usercontrol into single page assemblies/DLLs (see Figure 1 below). This option works well for the individual pages and usercontrols, however, by default all classes in the App_Code directory are compiled into a single DLL, even if App_Code contains separate sub-directories.

Figure 1. Publish Web Site Options

This normally is not an issue, however, with a recent Web Site that only contain three pages (Medical, Dental and Life), we did not want the classes (the logical tiers) in the App_Code directory combined into one DLL because each page was independent and we could be delivering changes for one page while working on enhancements for the others…we needed separate DLLs for the tiers as well as the pages and usercontrols.  The simplest solution would have been to deploy the source code files, but that was not an option.  Another solution was to create separate Business, Model (custom business objects) and Data projects for each page, but that seemed like overkill.

ASP.NET does support the use of classes in multiple languages within the same project, and those are compiled into separate DLLs by creating codeSubDirectories under App_Code, but you must also specify that you are using code sub-directories in the Web.Config file, <compilation> element (see Web.Config snippet below).

In our case though, we are using the same language (C#), but the concept is the same. We created the sub-directories, updated the Web.config file compilation section to include the appropriate codeSubDirectories, re-compiled the site and a separate DLL was generated for each sub-directory (see Bin Folder contents below).

Note: According to the above link “The build order is inferred from the top-down order of the codeSubDirectories collection. The App_Code directory is built last. However, there are no build order dependencies, therefore that the build order is not significant.”  In practice, if you place the Business folder before the Data folder in the Web.Config file, classes in the Business folder won’t be able to reference classes in the Data folder.  It seems the build order does matter.

Sample Project Solution

Web.Config

<compilation debug=false>

<!–Code SubDirectories added in order to have separate DLLs
for each product/page to simplify future maintenance and deployment
–>
<
codeSubDirectories>

<add directoryName=Common/>
<add directoryName=Model/>
<add directoryName=MedicalData/>
<add directoryName=MedicalBusiness/>
<add directoryName=DentalData/>
<add directoryName=DentalBusiness/>
<add directoryName=LifeData/>
<add directoryName=LifeBusiness/>
</codeSubDirectories>

</compilation>

Bin Folder

App_SubCode_Common.dll
App_SubCode_DentalBusiness.dll
App_SubCode_DentalData.dll
App_SubCode_LifeBusiness.dll
App_SubCode_LifeData.dll
App_SubCode_MedicalBusiness.dll
App_SubCode_MedicalData.dll
App_SubCode_Model.dll
App_Web_dental.aspx.f9b0821e.dll
App_Web_life.aspx.f9b0821e.dll
App_Web_medical.aspx.f9b0821e.dll

LINQ to SQL Connection Strings

August 17th, 2009

Like many developers, after reading several articles on the wonders of LINQ to SQL, I decided to incorporate it into the next project I did.  During the development phase, everything went very well.  I separated each layer of my solution into its own project and when it came time to define my business objects, I created a new .dbml file, opened a connection to my database and dragged the tables I wanted to use onto the designer screen as seen below.

dbml_file

I also added a connection string to the web.config file of my web project, but after the initial setup, I did not give a second thought to how it was that my data layer was connecting to the database.  In all the LINQ to SQL tutorials that I read, all that was necessary to connect to the database was to define a new data context and then use it like so.

public static tbl_page Page_GetByPageID(int pageID)

{

PageDataContext db = new PageDataContext();

var page = from p in db.tbl_pages

where p.PageID == pageID

select p;

What I did not realize, was that Visual Studio had taken the connection string directly from my server explorer and saved it into a file called “app.config” in my Object Model project.  When the solution was compiled, that connection string information was stored directly in the .dll and could not be changed.  As long as I was still working in development and querying the same database this was not an issue, however, when the first deployment was made to a remote production server, all of a sudden the project could not connect to the database specified in the web.config file of my web project.

To compound the problem, the error generated by SQL Server did not seem to point to an incorrect connection string, but instead indicated that there was some issue with the SQL Server installation itself.  The exact error was:

An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)

Once the project was installed on the remote server, I actually spent a good deal of time trying to troubleshoot issues with SQL Server settings rather than looking at the source of my connection string.  When I finally did realize that my project was still trying to connect to the local database on my laptop, I spent another couple of hours trying to figure out what to do with LINQ to SQL since it, from what I could tell, had stored the local connection string in the code without any intervention from me.

The answer proved to be quite simple in the end.  I read a couple of different blog posts like this one from Rick Strahl and this one from Graham O’Neale.  Both of these posts recommended removing the connection string from the properties of the .dbml file as well as setting the “Application Settings” property to false shown here:

PreviewPane_file

This step is necessary to get the connection string out of your .dll’s and into the config file where it belongs, but their other recommendations dealt with making manual code changes to the datacontext constructor or the designer class of the .dbml file.  While both of those suggestions work, from what I can see, any such manual changes would be overwritten the next time you have to make a database change during development, which would require you to recode the manual changes each time the .dbml file changes.

Not wanting to have to have to recode any such changes every time the .dbml file changed, I kept looking for another solution and I found my answer by mistake.  After removing the connection string and setting “Application Settings” to false, I tried to compile my project and found that the data layer was giving me an error for each data context constructor that I tried to initialize.  The error said:

PageDataContext(string Connection)

Error:

‘PageDataContext’ does not contain a constructor that takes ‘0’ arguments

Clearly, now that no connection was hard coded into the .dbml file by the designer, it was wanting a connection string to be passed in when the data context was initialized.  I thought that would certainly be easy enough to do, so I defined a public variable at the top of each data layer class like so:

public static string conn = ConfigurationManager.ConnectionStrings["myConnectionString"].ToString();

which of course points to the connection string in the web.config file of my web project and I changed each initialization of the data context object to use the new variable as follows:

PageDataContext db = new PageDataContext(conn);

Once that was done, everything compiled and worked like a charm both locally and on the remote server.  What I like about this also, is that I didn’t need to make any changes to base constructors or designer files of any of my object model classes.  (NOTE: In fairness to Rick, he mentioned doing this in the comments section of his post.  In my frustration, I just didn’t catch it the first time).

This is the type of thing that you only need to figure out once, but if you are trying to implement a LINQ to SQL project for the first time, it can be a very perplexing issue.  Most of the LINQ to SQL tutorials I read understandably focus on getting things up and running in your development environment, without a lot said about deployment.  If you are experiencing this issue, I hope this post saves you some and frustration.

Find Items In A Generic List

July 9th, 2009

Ken Getz wrote a good article titled Predicates and Actions about the advantage of the new generic features in .NET 2.0 Framework.  One issue I ran into using predicates was passing an argument to use as the filter.  For example, in the callback method Match() below, the predicate searches the states generic list for those that start with “C”, but the string to search for is hardcoded because the predicate signature only allows for one argument that is of the same type as the items in the generic list.

Using a Lambda Expression removes the need for a callback method (anonymous methods as well), so the filter is inline or it could be encapsulated in the method that takes the filter as an argument.

The following code snippet lists 3 different ways to find an item in a generic list.

static void Main( string[] args )
{
    // Create an abbreviated subset of states
    List<string> states = new List<string> {
            "AL", "AZ", "CA", "CO", "MA",
            "MD", "NJ", "NV", "TX", "TN" };

    //----------------------------------------------------------
    // You do not need to create the delegate explicitly
    // http://msdn.microsoft.com/en-us/library/bfcke1bz.aspx
    //----------------------------------------------------------
    //
    // Using an concrete method that matches the predicate 
    // signature to find the matching states
    List<string> methoddel = states.FindAll( Match );

    // Using an Anonymous Method to find the matching states
    List<string> anonymous = states.FindAll(
        delegate( string s )
        {
            return s.StartsWith( "C" );
        } );

    // Using a Lambda Expression to find the matching states
    //(new w/ C# 3.0)
    List<string> lambdaexp =
        states.FindAll( s => s.StartsWith( "C" ) );

    // Display the output from lambdaexp
    lambdaexp.ForEach( Print );

    Console.ReadLine();
}

// Finds the matching states
static bool Match( string s )
{
    return s.StartsWith( "C" );
}

// Display the contents
static void Print( string s )
{
    Console.WriteLine( s );
}

Create and Initialize List of Strings

February 22nd, 2009

In .NET you can create and initialize an Array of strings or a generic List<string> in C# using a comma-delimited list.  However, the only way I’ve found so far to do the same thing with a generic List(Of String) in VB.NET is to use an Array of strings as a parameter for the List like this.

VB.NET

Dim states As List(Of String) = New List(Of String) _
    (New String() {"AL", "AZ", "CA", "CO", "NV", "OK"})


C#

List<string> states =
     new List<string>() { "AL", "AZ", "CA", "CO", "NV", "OK" };

Current ASPX Page Name

February 21st, 2009

Get the name of the current ASP.NET page.

string path = System.Web.HttpContext.Current.Request.Url.AbsolutePath;
string name = System.IO.Path.GetFileName( path );