RANK() and alternate Sub Query in T-SQL

Consider, we have data table called Orders_Header which hold order header details.

As you can see, PO number(PONo)  ‘0050426995’ is duplicating. Because this table containing PO amendments too. If you notice, DownloadData is in order, meaning first version of PO received on 2010-06-26 17:07:04.910, rest of them subsequently.
The requirement is we want to get following output.

Normally we can write a sub query for this and get the output.

SELECT PONo,

(

SELECT COUNT(PONo)+1 -- to omit zero

FROM ORDERS_Header WHERE PONo=oh.PONo AND DownloadDate< oh.DownloadDate

) AS Revision,

DownloadDate

FROM ORDERS_Header oh

ORDER BY PONO,DownloadDate

By running this query, you can see the following Actual Execution Plan.





From MS-SQL 2005 onwards this can be easily achieved by RANK() function. 

SElECT PONo, 

RANK() OVER(PARTITION BY PONo ORDER BY DownloadDate) AS Revision ,DownloadDate 

FROM dbo.ORDERS_Header

ORDER BY PONO,DownloadDate

Check its Actual Execution Plan and note the difference.

Movex Tech Training from Lawson

My current employer (Brandix Lanka) arranged a MoveX technical training from Lawson, our ERP vendor. It took 12 days and today is the last day.

Mr. Daniel Rodrigo Olea , EMEA Technical Project Manager of Lawson software is the presenter and delivered marvelous sessions to our team.

Very first day, we were totally blank of what he is talking about. But he guided us in a way that we were able to understand almost everything in Movex code.

         

 

 

Areas that we covered in the training,

1. MAK tool
2. Creating/modifying Movex Program
3. Creating/modifying View Definition
4. Creating/modifying M3 API
5. Managing Out Interface
6. Data tables, Data Structures, CL in M3
7. Lawson web services to interact with M3 programs and APIs.
8. and much more…

View Full Album

Windows Could Not Start Enterprise Single Sign-On Service

In Windows 7 development machine, suddenly I got following error  when starting the ESSO service.

Windows could not start the Enterprise Single Sign-On Service service on Local Computer Error 0x80131700: 0x80131700

If check the windows event viewer, we can see,

Could not create SSOSQL. To fix the problem, reinstall SSO or try 'regasm SSOSQL.dll' from a Visual Studio command prompt.
Error Code: 0x80131700

I ran “regasm” and it is worked.

Brandix B2B (EDI) Implementation

This project was totally new to me. I knew about EDI, but no experiences in the past. But I took the challenge and did our own researches over 2 months and implemented within 6 months. We used Microsoft BizTalk Server 2006 R2 to implement this.

Abstract of the project

Our(Brandix*) one of leading buyer(Mark And Spencer) wanted to implement Business-to-Business (B2B) application with us.So that they can raise their purchase orders and receive invoices electronically. Within 48 hours we will receive invoices status,E.g. Invoice Processing Successful, automatically. 

Architecture

• Download Purchase Orders

1. Buyer raises the purchase order through a trading partner.
2. Our EDI system subscribes that PO.
3. Process in BizTalk server and immediately send email notification to the relevant merchandiser.
   

• Upload Invoices

1. Invoices been processed from our logistic system and upload them to the EDI system (BizTalk server/SQL server)
2. Make the EDI file based on the given EDI specification inside the BizTalk server.
3. Upload to the trading partner.
   

Props & Cons

1. Implementation cost is bit higher
2. Reduce paper works
3. Minimal data entry, hence time saving 
4. Seamless business process

 

---

* Brandix is the FIRST Sri Lankan company using Ms-BizTalk server for B2B integration in Sri Lanka.

The status code returned from the server was 12029

Figure 1: Ajax exception

This error code coming from Microsoft Win32 Internet (WinInet) API, which provides abstracts the protocols into high level interface.

12029       ERROR_INTERNET_CANNOT_CONNECT
               The attempt to connect to the server failed.

Useful links:

1. http://forums.asp.net/t/1129622.aspx
2. http://support.microsoft.com/kb/193625

BizTalk Event Log error: The source was not found, but some or all event logs could not be searched.

My BizTalk development machine runs on Windows 7. In an orchestration I've wrote
System.Diagnostics.EventLog.WriteEntry("Payment Automation","Instantiated the Process Engine");

When it runs, i can see following error in the event log. But I wont get this in Windows 2003 R2 server.

Figure 1: Windows Event Log

Event Type: Error
Event Source: XLANG/s
Event Category: None
Event ID: 10034
Date:  8/26/2010
Time:  9:27:29 AM
User:  N/A
Computer: 
Description:
Uncaught exception (see the 'inner exception' below) has suspended an instance of service 'Brandix.EAI.Orchestration.PaymentAutomation.BrandixPaymentAutomation(7e9e7bc9-558e-8988-7478-6552f648a021)'.
The service instance will remain suspended until administratively resumed or terminated. 
If resumed the instance will continue from its last persisted state and may re-throw the same unexpected exception.
InstanceId: b4472198-88f8-458f-aee3-aaa272d327ee
Shape name: Event Log Entry
ShapeId: 33391213-fc37-4d3a-91d7-f5f7bd50b667
Exception thrown from: segment 3, progress 3
Inner exception: The source was not found, but some or all event logs could not be searched.  Inaccessible logs: Security.
        
Exception type: SecurityException
Source: System
Target Site: Microsoft.Win32.RegistryKey FindSourceRegistration(System.String, System.String, Boolean)
The following is a stack trace that identifies the location where the exception occured

To avoid this error we have to do following steps.

1. Run RegEdit.exe
2. Expand hives to the level of following
   Computer\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\EventLog\Application
3. Insert a new key to the Application hive, in this case "Payment Automation"
4. Insert a new String Value called "EventMessageFile" under the "Payment Automation"
5. Double click on "EventMessageFile" set Value Data as "C:\Windows\Microsoft.NET\Framework\v2.0.50727\EventLogMessages.dll"

Final windows registry editor will look like this,

Figure 2: Windows Registry Editor

Restoring Deferential backups in SQL Server (T-SQL)

In my previous post I demonstrated, how to restore deferential backups with SQL Management Studio. In this post, I'll show the same thing with T-SQL.

1. Select the Master database.
   USE Master
   GO
2. Restore the latest full back up with REPLACE and RECOVERY option
   RESTORE DATABASE RESTORE_COSTSHEET
   FROM DISK = 'e:\backup_201007282300.bak'
   WITH REPLACE,NORECOVERY;
3. Restore all deferential backups except last one
   RESTORE DATABASE RESTORE_COSTSHEET
   FROM DISK = 'e:\201006110800.incr'
   WITH NORECOVERY;
4. Restoration of Last back up file should be like this.
   RESTORE DATABASE RESTORE_COSTSHEET
   FROM DISK = 'e:\201006111200.incr'
   WITH RECOVERY;

Restoring Deferential backups in SQL Server

Did you have ever restored a SQL data base? If you are a developer probably not. With this post I'm going to show how to restore deferential backups step by step.

1. One important thing, to restore deferential backups, you need to have latest full backup and subsequent deferential backups.
   
   Go to the SQL Server Management Studio, and select "Restore Database..." option by right clicking on Databases node in the Object Explorer. Follow the steps as shown in following screen shot.



Figure 1

2. Select "Options" from the left of the dialog and select the following option.



Figure 2

3. Now, note your data base's status has been changed to "Restoring".



Figure 3

4. Restore the number of subsequent deferential backups, except the last one, only with "RESTORE WITH NORECOVERY" option on.
5. Restore last backup with default values of the option dialog box. That is with "RESTORE WITH RECOVERY" option. And also note the database status has changed back to normal.

Considerations:

• This demonstration was done with SQL Server 2005 with SP2
• Adhere necessary procedures with you company if you are going touch live data base.

Social Networks: The Other Aspect

As we know, social networks increasingly becoming new communication tool among us. Surprisingly the Public adopt such tools more quickly even without training. According to the Forrester Research two third of internet users (total internet users 625 million¹ ) have joined a social network.

Why this becomes so popular? We can find very old friends, probably haven’t been meet them after high school, which is very good thing . Also social networking can use as mechanism of promoting your business. We have to pay less than TV or printed media advertisements.

Well, did you think about its other side?

1. This will be affected to the country's productivity.
   Most of the companies in the world now blocking them in working hours. Some countries completely blocked such tools.
2. People losing their privacy.
   It is found that more than 85% of users has crossed their line and suffering or will suffer consequence. This is a great place for being Identity theft. Also, I read some news someone had been robbed, because he or she put everything in the network including days he/she out of the town.
   Had several cases ended up with death.
3. Law Enforcement
   Who will be the responsible for cases such as defamatory, obscene work? We can clearly see rules and regulations completely differ from country to country. Hence no guarantee for protecting such cases.
4. Protection of Data
   The most important thing is, do these social networks can protect or have compliances on their users’ data. Very recently, one social network had information flaw and it’s been there since first release.

Ok. What we can do then? Should be completely dined?

1. Don’t expose your too much information.
2. Don not trusts anybody in the cyberspace.
3. Avoid giving personal data.
4. Limit the use of them and don’t be addicted.

---

^{1. It is expected that internet users will increase by 45% by 2013. (IT NOW Jan 2010)}

New Look

It took me only 5 minutes to make complete change of my blog's look and feel. Blogger has cool feature "Pick Template" which makes our life easier.

Thanks for the blogger and guys who creating templates.

Skinput - Turns your body into an interface

Microsoft and Carnegie Mellon University, doing research for Skinput and showing how it can turn your body into an interface.   This product is still only POC. Check this video.

---

Sources:
http://www.bcs.org/server.php?show=conWebDoc.34927
http://www.msnbc.msn.com/id/35708587

Inside Microsoft Chicago data center

Microsoft opened its Chicago Data Center late last year. In its first phase, the ground floor of the facility is designed to hold up to 56 containers, each filled with anywhere from 1,800 to 2,500 servers.

Further said , building the data center required 2,400 tons of copper, 3,400 tons of steel, 26,000 cubic yards of concrete, and 190 miles of conduit.

More...

Calculating Elapsed Time Accurately (C#)

To calculate elapsed time in our code, we normally do ,

DateTime stTime = DateTime.Now;
.....
// Do some work here
....
DateTime etTime = DateTime.Now;
TimeSpan ts = etTime – stTime;

But, this method won’t always give accurate value. I found Stopwatch class in System.Diagnostics namespace to calculate accurate elapsed time. Should look into it.

using System;
using System.Diagnostics;
using System.Threading;
class Program
{
    static void Main(string[] args)
    {
        Stopwatch stopWatch = new Stopwatch();
        stopWatch.Start();
        Thread.Sleep(10000);
        stopWatch.Stop();
        // Get the elapsed time as a TimeSpan value.
        TimeSpan ts = stopWatch.Elapsed;

        // Format and display the TimeSpan value.
        string elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
            ts.Hours, ts.Minutes, ts.Seconds,
            ts.Milliseconds / 10);
        Console.WriteLine(elapsedTime, "RunTime");
    }
}

Above example directly taken from MSDN. For more information check this link, http://msdn.microsoft.com/en-us/library/system.diagnostics.stopwatch.aspx

Did you Know?

A fantastic video found in YouTube, should check this out.

Challenge your world: Day Final

The sessions that I attended today, I can categorized into three.

1. Technical (Most of the time)
ASP.net 4.0 by Seok Chern.
• He has showed us new features of ASP.net. Like background task, async file uploading, email verification etc.
• AppFabric- without setting up webfarms we can distribute load across multiple servers. 
• CSS Sprites- If you have 4 images to show in the page, typically asp.net will call 4 server request to get those image files from the folder. But using Sprites technique asp.net runtime will get those 4 images as 1 image from the folder and show it in the page.  
• SmartyRoute – eg: we can omit file extension in the browser address bar but still load the resource. You can type http://www.foo.com/category instead of http://www.foo.com/category.aspx .
  SmatyRoute enables load dynamic pages based on the parameters given in the URL.
2. Promotional
• Microsoft Dynamics introduction were held by Krishnan Muralidharan.
• Lisa Tay showed us xRM as a rapid application development freamwork with some case studies.
3. Tactical
• Madhusudan Kohil & Rahul Pahwa delivered a practical topic these days Green IT. We also learnt that we still have opportunities to save Green in terms of IT. They showed us how Microsoft has adopt this strategy and how far success them.
• Freddy Tan, security advisor for Microsoft Asia, has delivered yet another good session to the gathering about IT security risks.

Today is the final day of Tech Ed 2010 Sri Lanka. I am truly thanks full to all the speakers and Microsoft with other sponsors to make this happen in Sri Lanka.

Challenge your world: Day Two

Bruce Schneier, The security guru – The Economist , delivered a speech under topic Re conceptualizing Security. In that one hour session, he convince us that he is a real security guru. And he discussed what security models passed down with our generations and how it think in that way to apply security concepts in IT. Though it is not technical session but quite interesting one which change the way thinking of security.


Its took my attention what he explained about Feelings, Model and Reality in terms of security.

In the next session, Prasanna Amirthalingam has presented Tips & Tricks with WCF 4.0. Some of the interesting things are,

• Missing address discovery using UDPDiscoveryService
• Service request routing
• Validating inputs (Parameter Inspectors) in WCF pipeline which will enable to plug or unplug without modifying the current WCF service.
• Caching
• Implementing REST principle

Pinal Dave showed us the other side of SQL Server index which we already know but use it wrong way. It is also interesting session that attracted everyone’s’ attention. He proved us worst cases when using index without knowing it. The conclusion of this session is,

1. Drop unused indexes
2. Always have clustered index
3. Use filtered index( new to Ms-SQL 2008)
4. Use User Defined Functions(UDF) wisely.

I have learnt lot of things today and as a totally I’m happy for the day with tech ed.

Challenge your world: Day One

Microsoft Tec Ed 2010 Sri Lanka is now happening in Colombo. This time its theme becames

Challenge your world.

 It is quit thrilling experience to Sri Lankan IT professionals. It will be last 3 days, today is the day first. I am one of attendee on behalf of my company(Brandix Lanka).

Here is the summary of sessions which I attended to,

Sliverlight 4.0 (Beta) (Speaker : Risman Adnan) ,Some of the new cool features were introduced, among them ,

• Drag’N Drop- you can drag files from your local hard drive into the Silverlight application.
• Printing Capabilities.
• Right Click facility which enables to add context menu to the application.
• Integrating web cam with Silverlight applications.
• Feature that I’ve thrilled is Out of Browser enhancement.

Darren Sim has explained to us about VS2010 and its new features and ASP.net 4 .0 and AJAX CDN(Content Delivery Network).
ASP.net 4.0 comes with,

• Lots of Charts including financial and statistics. Now creating a chart as easy as binding data to a Gird control.
• AJAX Toolkit – now totally supported by Microsoft.
• MVC 2.0
• URL rewriting
• SEO optimization
• And we can create packages to deploy ASP.net applications with VS2010.

In addition to that there were following sessions too,

1. What's new in Exchange 2010 by Madhusudan Kohli & Aviraj Ajgekar
2. Optimize your Windows 7 Deployments with System Center by Charlie Tan
3. New Features in SQL Server 2008 R2 by Rushab Metha
4. Managing Exchange Server with System Center by Charlie Tan
5. Data Protection Manager 2010 by David Foo
6. The history of the Log: Change Data Capture (CDC) by Pinal Dave
7. Active Directory Federation Services v2 by Brian Puhl
8. SharePoint Server 2010 by Joy Rathnayake
9. Introducing the Microsoft Web Platform by EdQuek