Sam Jones CRM Corner

T-SQL Date and Time Functions

Introduction

Date and time values are highly used in database applications. They involve sales, time sheets, taxes, overtime work, etc. Based on this usefulness, their operations are supported by various libraries you will be using when developing your application. Without being the most elaborate on this issue, Transact-SQL provides its own level of support for date and time values.

Before using a date or a time value in a calculation, remember that you must first get it one way or another. You can define a date or a time constant in your application. An example would be '1992/10/28'. You can declare a DateTime or a SmallDateTime variable and initialize it as you see fit. You may get a date or a time from another function. As the last alternative, you may get a date or time from another application or from a user. Once you have an appropriate date, you can use it.

The Current System Date and/or Time

One of the ways you can assist the user with date and time is to get the current date or the current time. For example, if you create a time sheet, when the user starts using it, it would be convenient to fill part of the time sheet with such predictable values.

To get the current date and the current time of the computer that a user is using, you can use the GETDATE() function of Transact-SQL. Its syntax is:

GETDATE()

This function simply returns the current date and time of the operating system.

Date/Time Addition

One of the primary operations you may want to perform on a date or a time value would consist of adding a value to it. To support this operation, Transact-SQL provides the DATEADD() function. Its syntax is:

DATEADD(TypeOfValue, ValueToAdd, DateOrTimeReferenced)

The third argument to this function is the value of a date or a time on which the operation will be performed. It can be a constant value in the form of 'year/month/day' for a date or 'hour:minutes AM/PM' for a time.

The second argument is the value that will be added. It should be a constant integer, such as 8, or a floating point value, such as 4.06.

When calling this function, you must first specify the type of value that you want to add. This type is passed as the first argument. It is used as follows:

• If you want to add a number of years to a date, specify the TypeOfValue as Year or yy, or yyyy (remember that SQL is case-insensitive). Here is an example:

  DECLARE @Anniversary As DateTime;
  SET @Anniversary = '2002/10/02';
  SELECT DATEADD(yy, 4, @Anniversary) AS Anniversary;
  GO

  

• If you want to add a number of quarters of a year to a date, specify the TypeOfValue as Quarter or d, or qq. Here is an example:

  DECLARE @NextVacation As DateTime;
  SET @NextVacation = '2002/10/02';
  SELECT DATEADD(Quarter, 2, @NextVacation) AS [Next Vacation];
  GO

  

• If you want to add a number of months to a date, specify the TypeOfValue as Month or m, or mm. The following example adds 5 months to its date:

  DECLARE @SchoolStart As DateTime;
  SET @SchoolStart = '2004/05/12';
  SELECT DATEADD(m, 5, @SchoolStart) AS [School Start];
  GO

  

In the same way, you can add values as follows:

 

Type of Value Abbreviation As a result Year yy A number of years will be added to the date value yyyy quarter q A number of quarters of a year will be added to the date value qq Month m A number of months will be added to the date value mm dayofyear y A number of days of a year will be added to the date value dy Day d A number of days will be added to the date value dd Week wk A number of weeks will be added to the date value ww Hour hh A number of hours will be added to the time value minute n A number of minutes will be added to the time value mi second s A number of seconds will be added to the time value ss millisecond ms A number of milliseconds will be added to the time value

 

Date/Time Subtraction

Another regular operation performed on a date or a time value consists of getting the number of units that has elapsed in the range of two dates or two time values. To support this operation, Transact-SQL provides the DATEDIFF() function. Its syntax is:

DATEDIFF(TypeOfValue, StartDate, EndDate)

This function takes three arguments. The second argument is the starting date or the starting time of the range to be considered. The third argument is the end or last date or time of the considered range. You use the first argument to specify the type of value you want the function to produce. This argument uses the same value as those of the DATEADD() function:

 

Here is an example that calculates the number of years that an employees has been with the company:

DECLARE @DateHired As DateTime,;
	@CurrentDate As DateTime;
SET @DateHired = '1996/10/04';
SET @CurrentDate  = GETDATE();
SELECT DATEDIFF(year, @DateHired, @CurrentDate)
       AS [Current Experience];
GO

This would produce:

What is the difference between .Net 2.0 and .Net 3.5

Recently I was asked by a colleague to investigate and report on the differences between .Net 2.0 and .Net 3.5 so this seemed like an excellent opportunity to explore this subject for the community as a whole. While most consultants working within the CRM community don’t really have a tremendous amount to do with .Net programming, less than say a professional programmer, it is still useful to understand the differences between the frameworks and the new features that have been added to the latest release .Net 3.5. So, for you edification, here is an article summarising the differences.

Some History (.Net 1.1)

.Net 1.1 and .Net 2.0 frameworks focused on allowing different languages to communicate with a common set of libraries translated through the Common Language Runtime (CLR). Essentially, the CLR provides a managed and language agnostic environment for executing applications designed for the .Net Framework. The managed runtime environment provides a number of services to the code, these services include compilation, code safety verification, code execution, automatic memory management and other system services. The applications designed to run under the CLR are known as managed applications because they enjoy the benefit o f services offered by the managed execution environment provided by the CLR.

.Net Framework 2.0

As .Net Framework 1.1 focused on consolidating and improving the CLR, .Net Framework 2.0 brought some new additions to the table. Such as :

· Virtual Machine : Support for generics

· Class Library : Generic Classes, including nullable types

· C# Features : Generics, partial classes, anonymous methods etc

Further, ASP.NET 2.0 introduced a number of new data Web controls, including the GridView, DetailsView, and FormView controls. The GridView displays a list of records in a boxy, grid-like table. The DetailsView and FormView each display one record at a time, the DetailsView in a grid-like output and the FormView in a more fluid layout using templates. The ListView Control was an update to the old DataList and Repeater controls, displaying mulitple records and providing functionality like the GridView, but allowing for a more flexible layout through the use of templates. The DataPager control operated as a sort of free-standing paging interface.

The new API gave a fine grain control on the behaviour of the runtime with regards to multithreading, memory allocation, assembly loading and more. Full 64-bit support for both the x64 and the IA64 hardware platforms was introduced along with the new personalisation features for ASP.NET, such as support for themes, skins and webparts as well as a .NET Micro Framework.

.Net Framework 3.0

The .Net Framework 3.0 was not a mere improvement to .Net 2.0, it in fact introduced 4 completely new foundation technologies although it continued to use the .Net 2.0 framework as a basis. Also called WinFX, it included a new set of managed code APIs that are an integral part of Windows Vista and Windows Server 2008 operating systems.

· Windows Presentation Foundation (WPF) AKA Avalon

provided the foundation for building applications and high fidelity experiences in Windows Vista, blending together application UI, documents, and media content. The functionality extends to the support for Tablet and other forms of input, a more modern imaging and printing pipeline, accessibility and UI automation infrastructure, data driven UI and visualization, as well as the integration points for weaving the application experience into the Windows shell.

· Windows Communication Foundation (WCF) AKA Indigo

is the Microsoft messaging platform for building service-oriented applications. Clients communicate with services by exchanging messages that are serialized on the wire, and de-serialized into common language runtime (CLR) types at each end. In the simplest scenario, client and service developers only work with objects, and all the serialization magic happens somewhere down below in the plumbing. Windows Communication Foundation (WCF) provides this plumbing and is similar in many ways to Web Services.

· Windows Workflow Foundation (WWF)

Windows Workflow Foundation is a Microsoft technology for defining, executing, and managing workflows

· Windows CardSpace (WCS) AKA InfoCard

CardSpace allows users to create personal (also known as self-issued) Information Cards, which can contain one or more of 14 fields of telephone book-quality identity information (that is to say, labelled and categorized information, such as full name, address, etc., though most fields are optional). Other transactions may require a managed InfoCard; these are issued by a third party identity provider that makes the claims on the person's behalf, such as a bank, employer, or a government agency. In short, it is a software component which securely stores a person's digital identities and provides a unified interface for choosing the identity for a particular transaction, such as logging in to a website

Many developers do not generally view this as a real framework in its own right. The real comparison is therefore between .Net 2.0 and .Net 3.5

.Net Framework 3.5

.Net Framework 3.5 builds incrementally in the new features added in .Net Framework 3.0. For example, feature sets in WWF, WCF, WPF and WCS. In addition, .Net Framework 3.5 contains a number of new features in several technology areas which have been added as new assemblies to avoid breaking changes. They include the following :

Deep integration of Language Integrated Query (LINQ) and data awareness. This new feature lets the developer write code written in LINQ enabled languages to filter, enumerate and create projections of several types of SQL data, collections, XML and DataSets by using the same syntax.

It should also be noted that .Net Framework 3.5 has added support for ASP.Net AJAX and Silverlight. Further enhancements that can be considered are :

1) CLR enhancements : general

2) Compiler enhancements : New VB.Net 9.0 compiler and support for changes to C# 3.0 like expression trees, lambda methods, extension methods, static reference for anonymous types etc.

3) LINQ : LINQ to XML, LINQ to SQL, LINQ to Objects and LINQ to datasets. Along with functional programming, LINQ is an outlook change to programming in C#.

4) Performance Improvements : ADO.Net gets paging support as well as synchronisation from caches at local level and server datastores. Also performance improvements for multi-code CPU’s.

5) Networking Changes : Peer to peer networking stack, including managed PNRP revolver.

6) Windows Information APIs : new wrapper for WMI and Active Directory Services. WMI 3.0 gets a managed provider

7) ASP.Net : New implementation of Client Application services as well as 3 new ASP.Net controls. Also AJAX programming for ASP.Net is easier and performs better.

8) WCF : Now works with POX and JSON data

9) WPF : Newer plugin model for creating AddIns. Silverlight CLR is also part of the new .Net Framework 3.5

10) Misc : The C/C++ get a standard template library (STL) so that these languages can share .Net libraries. Javascript intellisense and Javascript debugging, a CSS properties windows and a Managed Styles tools window as well as a new System.CodeDom namespace.

Conclusion

We’ve come along way from Framework 1.1 to Framework 3.5. For me, the most exciting developments are with LINQ, Silverlight and AJAX. I can foresee that these will be very big in the future and will enable the developers of the future to create some truly interesting and useful CRM additions.

Sprint Dashboard

Like the Carling advert, this is probably the best dashboard in the world....

Ever wanted to know what the current world population is, or how many babies are being born this minute? This is the dashboard to end all dashboards. I love it.

Sprint Dashboard Here

Exam Retirement Alert

For all those people out there pursuing a certification path, you should be aware that Microsoft are retiring a whole bunch of core exams on the 31st March 2009. Specifically, the following :

•Exam 70-300: Analyzing Requirements and Defining Microsoft .NET Solution Architectures

•Exam 70-301: Managing, Organizing, and Delivering IT Projects by Using Microsoft Solutions Framework 3.0

•Exam 70-305: Developing and Implementing Web Applications with Microsoft Visual Basic .NET and Microsoft Visual Studio .NET

•Exam 70-306: Developing and Implementing Windows-Based Applications with Microsoft Visual Basic .NET and Microsoft Visual Studio .NET

•Exam 70-310: Developing XML Web Services and Server Components with Microsoft Visual Basic .NET and the Microsoft .NET Framework

•Exam 70-315: Developing and Implementing Web Applications with Microsoft Visual C# .NET and Microsoft Visual Studio .NET

•Exam 70-316: Developing and Implementing Windows-Based Applications with Microsoft Visual C# .NET and Microsoft Visual Studio .NET

•Exam 70-320: Developing XML Web Services and Server Components with Microsoft Visual C# and the Microsoft .NET Framework

•Exam 70-330: Implementing Security for Applications with Microsoft Visual Basic .NET

•Exam 70-340: Implementing Security for Applications with Microsoft Visual C# .NET

Microsoft SQL Server exams that retire on March 31, 2009

•Exam 70-228: Installing, Configuring, and Administering Microsoft SQL Server 2000 Enterprise Edition

•Exam 70-229: Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition

This might present something of a problem for those of us who are going for Microsoft Certified Business Management Solutions Professional Developer for MSCRM qualifications as at least two of the retired exams are requirements of this qualification! As yet, there doesn't appear to be any alternatives to those exams which achieve the same result, so I suggest that we all get our skates on and sit the required exams before the 31st March 2009. 

Best of luck!

Validating CRM 4.0 Customizations Files

Do you get this screen whenever you change the ISV file?

Annoying isnt it, especially if you don't know where you've gone wrong.

Well, there is an easy way to validate your XML before you attempt to import it into the CRM.

1) Open XMLNotepad

2) View --> Schemas

3) Add the following three files which you can find in your CRM installation folder.

a) wwroot\_Resources\customizations.xsd

b) wwwroot\_Resources\isv.config.xsd

c) wwwroot\_root\sitemaptype.xsd

 

Once you have these three files installed, any invalid XML will give a warning in the error listing on the main screen.

WebException (HTTP 401) thrown in 4.0 plug-ins

Last week I identified a need for an auto-incrementing numbering system and so I hunted around for something to use. At first, I used the old stand by of a basic UTC date conversion but it quickly became apparent that this was inadequate.

So, I came across a free auto-increment counter on this site which uses a plugin. Yay!

I installed the plugin on my development environment and it worked a treat. After some further testing, I then moved it to my Production environment which is where the problems started. No matter what I did, I kept getting a 401 Unauthorised Error. Round and round I went playing with IIS, changing permissions, re-installing the plugin, rebooting until I finally gave up and emailed the writers of the plugin.

Long story short, it seems that there is a bug within CRM 4.0 that causes the plugin to execute permissions using the Organization Friendly Name rather than the Organization Unique Name. Since, like most companies, our friendly name was just that, friendly, complete with spaces, I could see why the plugin was throwing an exception. The exact answer is reproduced below for you interest and also for future reference since this is rather a fundamental bug which could see you throw yourself out of the window in frustration.

 

Problem

The cause of the problem is that the wrong version of the organization name is being passed to the plug-in by the platform. The organization's friendly name is being used instead of the unique name. A friendly name can contain spaces while the unique name cannot. The platform expects an organization's unique name when doing authentication. The organization name is used internally by the CreateCrmService and CreateMetadataService methods of the IPluginExecutionContext class to authenticate the user in Microsoft Dynamics CRM. This problem only occurs if an organization name containing one or more blank spaces was specified when Microsoft Dynamics CRM 4.0 was installed.

Workaround

(Option 1) Use Deployment Manager to change the name of the organization to remove any blank spaces in the organization's display name. You must first disable the organization in Deployment Manager before you can edit the display name in the Edit Organization Wizard. An easy approach to guarantee using a correct organization name is to simply copy the value in the Name field of the wizard and paste it into the Display name field. After making the change in Deployment Manager wizard and clicking the Apply button, enable the organization and reset IIS. This option fixes the problem without requiring any plug-in code changes and does not change plug-in performance.

(Option 2) Do not use the CreateCrmService or CreateMetadataService methods in your plug-in to obtain an ICrmService or IMetadataService instance. Instead, create an instance of the Web service proxy using the CrmService or MetadataService class just as you would if you were calling Web services outside of a plug-in. When setting the CrmAuthenticationTokenValue property, remember to strip out any spaces in the organization name that you have obtained from the IPluginExecutionContext.OrganizationName property which is passed to the plug-in's Execute method. When a hotfix is made available for this bug, you should change your code back to using the CreateCrmService or CreateMetadataService methods for improved plug-in performance. This option will reduce the performance of your plug-in.

The following sample code is taken from the Walkthroughs\Plugin\CS\AccountCreate SDK sample and shows the affected lines of code.

// Create the task on the Microsoft CRM server.

ICrmService service = context.CreateCrmService(true);

service.Create(followup);

Replace the above code with the following code to implement option 2 for the sample plug-in.

// Workaround to bug #33458.

CrmAuthenticationToken token = new CrmAuthenticationToken();

token.AuthenticationType = AuthenticationType.AD;

token.CallerId = context.UserId;

// Remove any blank spaces in the organization's name.

token.OrganizationName = context.OrganizationName.Replace(" ", "");

// Instantiate the CrmService Web service. 

CrmService service = new CrmService();

service.UseDefaultCredentials = true;

service.CrmAuthenticationTokenValue = token;

// Obtain the service Url from the registry.

service.Url = (string)(Registry.LocalMachine.OpenSubKey("Software\\Microsoft\\MSCRM").GetValue("ServerUrl"));

service.Url += "/2007/crmservice.asmx";

// Create the task on the Microsoft CRM server.

service.Create(followup);

You will also need to add a using statement (using Microsoft.Win32) to the sample plug-in.

This same technique applies to any plug-in that is affected by this particular bug.

 

Personally, I favour Option 1 which is to change the Organization Friendly Name to match the Organization Unique Name and hope that Microsoft bring out a hotfix shortly. This is exactly what I did and hey, it works. Option two is something to be aware of if you are developing your own plugin, but for the purposes of my exercise, option 1 would suffice.

The method for implementing Option 1 is the following :

1. Launch Deployment Manager on the Web server

2. Select the Organization, Right Click ->

Disable 3. RightClick Again and Click Edit Organization

4. Change the Organization Name and FriendlyName to be the same

5. Click Next and Finish.

You can view the Knowledge Base article on this issue HERE

Developer Ramp Up Kit for Microsoft Dynamics CRM 4.0

Ok, so I've been pottering around for the last couple of weeks exploring the CRM interface. I understand that it is very powerful in it's 'out of the box' incarnation, but there is one huge problem with it. Whilst CRM is very customisable and intuitive, it is just not possible to tailor the program to suit every customer without delving into the code, or at least overlaying your own code on top. This is about where I am up to. I want to develop a widget that will use the power of CRM but be able to present data in the way that I want it displayed. CRM is a wonderful interface, but it just doesn't handle queues properly, nor does it do nifty things like flow analysis or trend analysis. I want my widget to do both. Luckily for me, CRM is about as close to 'open source' as Microsoft is willing to get and so there is allot of scope for third party development. To help developers with this, Microsoft have released a number of tools and most of us are familiar with the SDK, but there are also a number of video tutorials out there that I am sure will prove equally useful. To take a look for yourself, visit the link below.

Get you videos here....

CWR Mobile CRM

 

CWR have released their new mobile version of CRM.

Request your free trial here

Personally, I'm very excited about this development. I can see how this would be incredibly useful to a sales force out on the road all the time. When I worked for sales, many moons ago, the only time that the outdoor sales team could collect their upcoming appointments was when they came into the office, now, they can sync this from anywhere. Couple this with a Google Map plugin and you could actually track your sales staff on the move....eek, what a thought! To think that 20 years ago, the only time the backoffice knew what a rep was up to was when s/he rang in!

One of the minor things that annoy me is the idea of having to use my mobile phone address book to store all of my business contacts. Quite apart from not having that much space anyway, I would rather have my business contacts all in one place along with all the activities and information about them and this little mobile application offers just that functionality. Of course, like most things Microsoft, you are going to have to upgrade your hardware to accommodate this new software, but hey, this one might actually be worth it.

Microsoft Resource Centre now available

Microsoft has published their resource centre online. Previously the resource centre was only available through Dynamics itself but now you can access it yourself directly from the web.

Online Resource Centre

Resource centre for on Premise and Service Provider editions

Visibone Crib Cards

OK. I just have to bring this to your attention.

I've known about this particular resource for a number of years and have them all myself, but I just have to publicise it here on my shiny new blog.

These cards are fantastic and if you buy any new resource in the next few months, this is what you will need. The cards are crib sheets for Hex colours, fonts, HTML, CSS, Javascript and the DOM. These are not exhaustive explanations but just quick look ups for the most common usages. If you are like me, a head like a sieve then this is the resource for you.

Just lately, a new set of cards have been released and these cover PHP and MySQL. I use my cards all the time and they are always to hand on my desk. In fact, I've had to rescue them from envious colleagues over the years.

Get your crib cards by clicking on the image.