Intro
As we all are waiting in excitement for the final release of SQL Server “Denali” I’m going to look at the new TSQL functions it brings for developers. The first function is EOMONTH. EOMONTH stand for “End Of MONTH”.
MSDN Description
Returns the last day of the month that contains the specified date, with an optional offset.
Syntax Format: EOMONTH ( start_date [, month_to_add ] )
Start date specify the date for which to return the last day of the same month. Month to add is an optional parameter to add months to the specified start date.
TSQL Example
Basic example for EOMONTH function:

DECLARE @start_date DATETIME = '08/10/2011'; 
SELECT EOMONTH (@start_date) AS Result; --Result: 2011-08-31 00:00:00.000

As you can see when I specify the 10 August 2011 the last date of the month is 31 August 2011. You can specify the start date as DATETIME or as a VARCHAR value. EOMONTH will do a implicit conversion of the VARCHAR to DATETIME.

Example to get next month last date:

DECLARE @start_date DATETIME = '08/10/2011'; 
SELECT EOMONTH (@start_date, 1) AS Result; --Result: 2011-09-30 00:00:00.000

Now by adding the optional parameter of 1 to the EOMONTH function you ask for the next month last date. As the example shows the result is 30 September 2011 for the input of 10 August 2011. If I change it to 2 then I will get the second month after the specified date.

Example to get previous month last date:

DECLARE @start_date DATETIME = '08/10/2011'; 
SELECT EOMONTH (@start_date, -1) AS Result; --Result: 2011-07-31 00:00:00.000

This example show how to get the previous month last date from the specified input date. You simply make the optional parameter minus. The input was again 10 August 2011 and the result for EOMONTH function was 31 July 2011.

Practical Example

My practical examples are using the AdventureWorks2008R2 database for Denali which is available here. Let look at a practical example for EOMONTH function:

DECLARE @begin_date DATETIME = '01/01/2007',
        @end_date DATETIME;
SELECT  @end_date = EOMONTH(@begin_date, 3); --Result: 2007-04-30 00:00:00.000

SELECT SUM(TotalDue) AS 'First Quarter Purchase Total' --Result: 145291.7959 
  FROM [AdventureWorks2008R2].[Purchasing].[PurchaseOrderHeader]
 WHERE OrderDate > @begin_date
   AND OrderDate < @end_date

In this example I calculate the first quarter purchase total for the year 2007. I specify a start date and use the EOMONTH function to calculate the last date of the quarter. Now it is easy to just sum up the TotalDue column for records between the begin date and end date.

As you can see the EOMONTH function makes it allot easier to get the last day of a month and it helps to make your TSQL code smaller and more understandable. Let me know what you think about this function addition in SQL Server 2011 Denali or if you have any questions.

Cheerio!

I found the perfect solution for UI mock-ups namely PowerMockup.There is a whole bunch of offline and online tools to create UI mock-ups. Using these tools are great, but clients are not always happy with these tools. Clients usually consider its something new they have to learn.

With PowerMockup everything happens inside Microsoft PowerPoint. Client is happy because its a tool they know. You design inside PowerPoint and then just send the slides to clients and then they can view your designs. The clients can also move the elements around with or without having PowerMockup installed, but if they don’t have PowerMockup they cannot add new element.

The Toolset

Let me show you a couple of screenshots of PowerMockup. Firstly you need to go and download PowerMockup. You do get a trial period and the cost as of 27 August 2011 to buy is:

• 1 User = $39.95
• 5 Users = $119.95
• 10 Users = $199.90

When you install PowerMockup you will see a new menu option in PowerPoint.

The Show Stencil Library button you have to click to view all the stencil items you can use for your UI mock-ups. The stencil library will appear on your right hand side of PowerPoint.

The stencil library contains various items to use. The items are grouped as Custom Shapes, Containers, Graphics, Icons, Markup, Navigation and Text. You can create your own item and add it to the stencil library which you can export and import. PowerMockup also provides a nifty little search box at the top to quickly search for a specific item.

Here is an rough example design I did to show some of PowerMockup items.

If you have a change, take a look at this great add on. You will definitely enjoy it and I had clients that enjoyed the experience of viewing their UI designs inside PowerPoint.

Cheerio!

I found myself wondering around the Microsoft Research website the other day and found the Trinity project. Trinity is a graph database and computation platform over distributed memory cloud. Trinity currently have a release package of version 0.2 available for download. If you want to find out some more details visit the official site: http://research.microsoft.com/en-us/projects/trinity/default.aspx 

Key features of Trinity

• Use Hypergraph Data Model
• Distributed: Deployed to one more machines
• Memory-based graph store with rich database features.
• Highly Concurrent Online Query processing
• ACI transaction support
• Parallel graph processing system

The Interest

I never really took notice until I read through the documentation and understand the practical usage. Graph databases are used by all the big companies like Microsoft, Google and Facebook. A Graph database is a database that uses graph structures with nodes, edges and properties to represent and store information.

On Wikipedia you will find a whole list of Graph databases implementations: http://en.wikipedia.org/wiki/Graph_database

Where to know

You might ask yourself why do I write this entry about Trinity. Well, for starters all developers that use any Microsoft technology should take note of Trinity. Trinity can help to better structure information on your next project. I can see how Trinity can be used along side SharePoint and SQL Server. It would be interesting to see how you may be able to take existing SQL Server data to Trinity. I could also see how BI developers can take various data sources and structure the data inside Trinity to do their magic easier. I also believe that Trinity might be the solution service on Azure for graph database usage.

Since I’m a service integration person, I find it difficult to think how this will benefit any service integration project or be used along side BizTalk. I might be wrong?

I think Trinity has a bright future ahead. Hope Microsoft does not kill off this research project. Let me know what you think about graph databases and specifically the Trinity project.

 

Cheerio!

My latest presentation was at Microsoft Devs4Devs event. I discussed some of the new T-SQL functions that is available in SQL Server 2011 “Denali”. Firstly I want to thank Dave Russell from Microsoft for the opportunity. Also thanks must go to all the community members who attended the day and for your feedback.

My Slides is available here.

My T-SQL Code snippets here.

See you guys at the next community event.

Cheerio!

I had a very interesting experience try to setup Lync Server 2010 for my latest project. Using Lync Server 2010 with SharePoint 2010 environment. Very powerful combination for user collaboration.

Anyway, I got a very strange error in publishing my Lync Topology:

The existing topology identifies serverA.domain as the Central Management Store, but the topology that you are trying to publish identifies serverB.domain as the Central Management Store. The Central Management Stores must match before the topology can be published.

The white blotches on the images are server FQDN that I needed to hide.

The reason for this might be that you specified the wrong FQDN and try to publish the Lync topology before. Firstly make sure you use the correct FQDN for the server that is hosting the Central Management Store.

Open up Lync Server Management Shell and type the following command to get the currently registered Central Management Store location:

Get-CsConfigurationStoreLocation

To remove the registered Central Management Store location type in the following command:

Remove-CsConfigurationStoreLocation

After running these commands you can attempt to publish your Lync Topology again and see the success message.

Hope this help you with fixing Lync Topology publishing problems.

Cheerio!