Removing duplicate rows in SQL Server

If you are using SQL Server 2005 or greater (we need the CTE (Common Table Expression) functionality) you can in one statement delete duplicate rows from a table. You can look at all columns in the table or you can look at a subset of the columns. The example below uses just one column (EmailAddress), but you can replace that one column with a comma separated list of the columns you want to consider.

Before we go off and start running delete scripts on our data, it is always a good idea to make sure you have a backup of the table so you can check you work after or restore if something goes wrong.

Let's first look at the data

-- see what rows have duplicates and which ones don't
SELECT  EmailAddress
      , row_number() OVER ( PARTITION BY EmailAddress ORDER BY ID) AS NumInstances
FROM    Person

-- show only the rows that are duplicates
with myTable as (
SELECT  EmailAddress
      , row_number() OVER ( PARTITION BY EmailAddress ORDER BY ID) AS NumInstances
FROM    Person
)
select * FROM myTable
    WHERE   NumInstances > 1

The actual delete statement

-- do the actual deleting of the duplicate rows
with myTable as (
SELECT  EmailAddress
      , row_number() OVER ( PARTITION BY EmailAddress ORDER BY ID) AS NumInstances
FROM    Person
)
DELETE  FROM myTable
    WHERE   NumInstances > 1

To modify the code to fit your situation you will typically just need to replace the items in red with the comma separated list of columns you want to consider. Next change the table name in blue to the table you are trying to remove the duplicates from. Finally, change the column in orange to be one or more columns (separated by comma). In this case, I have a primary key so, I used it. You could use all the columns you are considering as well.

For more info on how it works and pre-SQL 2005 solutions see here.

GEOTAB review

I recently had the pleasure of reviewing GEOTAB GPS System. They have given a lot of thought into making their platform accessible via their API and their Architecture is very nice. 

Recommendation / Summary

After reviewing their website in detail and attending a demo for GEOTAB, I would technically recommend this product.  The ability to integrate with their product is strong, scalable, and well designed. Their proprietary hardware has one of the lowest failure rates in the industry (or so they say) and provides additional information that adds value to the data. To get the best features, you should use their proprietary hardware. The system is extensible from a hardware and software perspective. You can readily integrate with their GEOTAB. 

Hardware

GEOTAB has proprietary hardware, but also works with third party hardware. Their proprietary hardware appears to be superior to the competition because it uses accelerometers as well as GPS. This allows it to accurately be able to identify drivers that hard brake at low speeds, identify drivers that are distracted, etc. It has a high compatibility with most every vehicle. Installation is very easy (3 minutes they say) to install the device by plugging it into the data port of the vehicle. The hardware can be extended to interface with additional sensors, driver identification via RFID, and Garmin devices. With the Garmin devices two communication be done.

Reports/Monitoring/Dashboards

They have 30 reports, dashboards and pride themselves on productivity monitoring / reporting. Most vendors appear to record data at a periodic interval, but they do not. The reason is that some key data can be missed. This is actually a big benefit in recording the accuracy of the data.

API

The API is available in C# and JavaScript, but can also be called by any language that can make a HTTP GET or POST request via HTTPS (and most any language can). The API appears to be robust and they have a web farm so it should be scalable as well. The API is well documented and has many examples. There is a data integration Windows application they provide the source code for that extracts data from GEOTAB. It could easily be extended to import the data into your databases. You should be able to use this API to integrate with GateHouse GPS Portal if you want to or store the data in a database or file system. You can programmatically add users, devices, etc via the API as well.

GEOTAB Software Extensibility

Their web application UI can be extended using JavaScript to add calls to your own systems, or tweak their pages. The pages are unique urls so linking to their pages should be relatively easy.

Email notifications can easily be customized by users, but notifications can also be sent as texts. Their UI can also be embedded in your systems as well.

MADP changes from 2013 to 2014 according to Gartner

In the world of MADP (Mobile Application Develop) things change very fast. In fact, in April 2012 Gartner said, "Designers have to make complex trade-offs between native, hybrid and Web-oriented mobile architectures. From 2003 through 2009, Gartner observed that the majority of high-value mobile applications were written as native, but that began to change around 2009 as techniques for wrapping Web technologies emerged. These techniques create hybrid mobile applications (wrapped applications, where the container is native code, but the experience leverages the WebView capability of the OS) or as mobile Web applications. In addition, some Web-oriented applications began to offer HTML5 features, such as advanced rendering and local storage, that previously were only available within the native style.

This caused a rapid shift of focus for many enterprises toward Web-oriented techniques — so much so that, based on our surveys conducted in 2011, 40% of enterprise application developers were still targeting native first for a variety of reasons (such as performance and disconnected mode). This migration will continue for two to three more years, and we predict that, by 2015, 80% of all mobile applications developed will be hybrid or mobile-Web-oriented."

After reading about MADPs, I decided it might be useful to visually see how the players have changed from April 2012 to August 2013. So, I took the Magic Quadrant charts from 2012 and 2013 and superimposed them into one chart, added arrows from 2012 to 2013 and consolidated the labels to produce the diagram below.

NOTE: The ones in red are new in 2013. The ones in gray disappeared in 2013 from the rankings.
Some observations:

Antenna - didn't see much change in score, but is a pretty strong Leader.
Kony and Adobe - are neck and neck and both moved from a strong Visionary to Leader.
Appcelerator - They have the strongest vision out of all the companies. They need a little work on executing before they will be a Leader though.
Salesforce.com - was Niche and has moved into a strong Challenger; nearly a leader with a tad bit more vision.
jQuery - moved from fairly weak Visionary to a Leader because of their improved Execution. They still seem to be a bit light on the vision though.
IBM - moved from Niche player to a strong Leader. They lead in second on both execution and vision. They could be a good choice overall.
SAP - stayed in the Leader circle, but has gained some execution, but at the expense of losing some vision.
Google - stayed in the Niche player square, but has significantly increased their vision.
Blackberry - stayed in Niche player square, and really just lost its ability to execute. Not much hope on this one I think.
Microsoft - stayed in the Niche player square, and gained a little vision, but at the expense of execution.
Apple - stayed in the Niche player square, and gained noticeable amount of vision, but again at the cost of execution.

I am very curious what the 2014 Gartner report will bring.

Thank you to all for your support

Just geeks has reached over 1 million hits since I started tracking hits. This is very exciting! Thank you all for visiting my blog.

Moving a SharePoint site (not site collection), list, or library from DEV to Production

If you want to know how to move a SharePoint site (not site collection) then continue reading. You can also use it to move a list or library.

NOTE: You can use this technique to change the path of a site on the same server or move it to an entirely new server and different path. 

If you are trying to move an entire site collection click here for details on how to do that.

1. First we need to open up SharePoint 2010 Management Shell (in Start Menu )
2. Type something like the following to export your site.
   PS> Export-SPWeb https://mysp/abc -Path "c:\temp\abc.cmp" -IncludeUserSecurity -IncludeVersions all
   This will create a file at c:\temp\abc.cmp
3. Move the file to the new server. For example you can move it from DEV to Production.
4. (Optional) Go to new server and open up SharePoint 2010 Management Shell (in Start Menu)
5. Create a subsite at the desired destination url. You MUST use the SAME template that the source site uses. This is the url where you will replace with what you previously backed up. If you try to restore without an existing url it will give you an error.
6. Type something like the following to import your site to the path specified.
7. PS> Import-SPWeb http://mysp2/abc2 -Path "c:\temp\abc.cmp" -UpdateVersions Overwrite -IncludeUserSecurity
   This will replace the site at the url http://mysp2/abc2 with the site that we backed up earlier to c:\temp\abc.cmp

The nice thing about this technique is that it keeps the last modified dates as well. 

Permission issue with subsite when using FBA with SharePoint

I recently ran into an issue with my SharePoint 2010 (though it appears to be an issue in 2013 as well) site that is using FBA (Forms Based Authentication). Here is the scenario. I have a SharePoint site that is root in FBA Web Application. I don't want to have to do all the configuration for each site I add so I want to create subsites for each new site I want to add. I want to have the root site not accessible to average users. I will give the users of each subsite the direct url to their site. They will not know the root site exists unless they hack the url in the browser. I am using the SharePoint 2010 FBA Pack, but I don't think that really matters (though I have not tried without it being installed). Regardless, the problem I am having is that when I add a user to one of the subsites it doesn't allow the user to log in and instead tells them access denied.

The problem is that the Master Page Gallery for the site collection needs to have the permissions changed. You can verify that this is the problem by

1. Navigate to your Master Page Gallery (http://yourSPhostHere/_catalogs/masterpage/Forms/AllItems.aspx). 
2. Once there you can get permissions on that library. Next Click the Check Permissions button and enter the user that is being denied access. If you see that they don't have access then this is likely your problem. 

The solution is simple. From the permissions for the Master Page Gallery do the following:

1. Click the Grant Permissions button. 
2. For Users/Groups textfield enter: All Authenticated Users
3. For Grant Permissions  select Add users to a SharePoint group (recommended)
4. Select Style Resource Readers from the drop down list.
5. Click the OK button.
6. Try to login again. This time it should work.

ASP.NET Code Generators Reviewed for the novice or Business Analyst

Requirement

Advanced enough for a ASP.NET developer to do whatever they need to do, but simple enough that a business analyst could create a simple application based on a database schema (done by a DBA or developer). Ideally embrace standard and newer technologies such as Linq, Entity Framework, JSON, WCF. It would be nice to have the ability to create a SharePoint application.

Xomega 3/5 stars

Website: http://www.xomega.net
Xomega uses a Visual Studio Plug-in instead of a full proprietary IDE. Leverages the entity framework and WCF. Generates end-to-end web and desktop applications. Uses Webforms or Silverlight or WPF to implement UI. They have created a new type of VS2013 solution type. Separate projects / assemblies are created in Visual Studio for each layer (ClientObjects, Client.Web, Entities, Model, Services). Configurations are done via XML files using Visual Studios standard tools and navigation. All generation is based on what is in the xml files. Can rename generated files to prevent changes from being overwritten. Partial classes are also used in some cases to implement additional functionality. The generated UI is clean. No coding needed to generate basic UI, but requires a bit of configuration with the XML. Appears to not have a date picker built in. This tool is definitely for someone that is comfortable with Visual Studio and would not be suitable for someone like a Business Analyst. Its strength is that it embraces new Microsoft technologies. Overall I like this generation tool for the more technically proficient developer, but maybe not a beginner that doesn't really understand the different layers and how they fit together.

radarc 3/5 stars

Website: http://www.radarc.net

radarc sounds very promising for MVC4 code generation (a rare feature) and multiple mobile phone app generation (Android, iPhone, Windows Phone 8) also. When doing phone app generation it creates the backend REST services as well. It just came out of beta this week. The demo video looks great. I had some issues with basic functionality of the generator working. Based on that, I can't say that this is ready for real production use and should still be in beta. However, I am very excited by the quality of code that it generates. It doesn't use stored procedures and instead its own model and includes a model to do Model first design. I appears to use LINQ or something modeled after it (I need to look closer at that). I get the impression you are locked into the application it generates and that it is a generate-once kind of design. I don't see that many options as far as options / configuration, but I'm sure that will improve as the product matures. I can't give this a excellent status yet, but I hope in time it will mature into a great product. The biggest issue I see is that there is a lack of documentation, I can't change the views that are generated (as far as I can tell) without affecting the ability to regenerate the pages. It suggests that there are extension points for changing views, but I have not figured out where that is. This is a great start once you have your model figured out, and are okay with a generate-once application that you can then customize. 

SoftFluent 2/5 stars

Website: http://softfluent.com

The model is continuously generated from the model (CodeFluent Entities). Supports new technologies such as WCF, JSON, It is not an end-to-end code generation tool. It does do the plumbing for you. Think of it as doing the data access layer and the business layer, but not the presentation layer. It is model-first code generator. It does support many types of applications (ASP.NET MVC, WebForms, SharePoint, Silverlight, Windows Phone, WPF, etc. However, they are not full CRUD applications, they are instead empty applications with the model already to go. Has a concept of Producer for each type of application it creates and allows you to extend it as well. The idea is that it leaves you to do important things like the UI since you have essentially created an API to connect to your UI. I don't really see the cost-benefit of using this since this can more or less be done with the entity framework and domain services which are standard .NET components. 

ASPRunner.NET 3/5 stars

Website: http://www.xlinesoft.com

This is a pretty nice end-to-end code generation tool. It seems more like you configure it how you want and then generate once. The WYSIWYG editor is pretty limited and I'm not super impressed. It has many nice themes (colors) and gives you options to change the color on top of that. It doesn't create drop downs for foreign key columns with friendly name, but instead showed numbers. Maybe that was somewhere in the config, but it wasn't automatically done. It generates Microsoft MVC code using Razor. The project is using .NET 4.0. I don't see master-detail layouts by default, but I do see they can be added relatively easy. The data Model appears to be non-existent. There is a Data Access layer that handles most any query you can through at it, but it doesn't use the MS Entity framework or LINQ. Each Controller is VERY heavy because there is code for every option that you MAY want to use on the page. So, the code is MVC, it doesn't really follow the best practice of having a fairly lightweight controller. I'm not sure how, but the UI for the generated apps feel very PHP like. In general it feels a bit clunky. I was hoping for more AJAX /HTML 5 interactions.I want to like this because it is only MVC end-to-end generator I have found, but I just don't like the UI it generates and the controllers are so heavy.

Code4Green 1/5 stars

Website: http://www.code4green.com
Code4Green is a web based code generation tool. It is actually pretty nice if you like the way they do things. You must manually type in your table definitions and it then generates a stored procedures, .aspx, code-behind, data objects, data mapper, etc. You must then copy and paste each of these individually into Visual Studio (after you create the files in Visual Studio). It doesn't use LINQ or Entity Framework or really any of the new UI objects. It generates ASP.NET 1.1 style code. While it isn't bad if you have nothing else, it isn't what I am looking for due to not being able to point it at a database and it generating an application. I also want it to generate more modern code and UI. It reminds me of some simple snippet tools. It does generate some interesting SharePoint code that may be worth looking at further.

LayerCake Generator 2/5 stars

Website: http://www.layercake-generator.net/

While the architecture looks good for this application it is a new effort. It has only been around since Feb 2013 and the web site is a bit light on content, details, and in general just a polished look that is well thought out.. I tried to run the Cooker application and couldn't get past the first screen. I found it very strange that I have to specify a database that does NOT exist yet. I decide to instead just look through the step by step tutorials. What I see from the tutorial is the following. The primary keys and foreign keys must follow a very specific naming convention. The column names must contain the table name then an underscore then the actual column name. I don't like that at all. I like that it creates contracts and WCF Services. It uses specific table format to handle translations. I think using a table instead of a resource file could have serious performance implications. I like that it support unit tests well. It doesn't appear based on the tutorial that any UI is generated. This looks like an interesting tool, but I think falls short in the end-to-end generator category due to strict database naming rules and no UI generation.

OutSystems Platform 3.5/5 stars

Website: http://www.outsystems.com

This is a very nice piece of software. I know it must cost a lot because there is no price on the site and you can't get a trial edition without giving them your contact info and then they want you to talk to their sales team. Despite that, it is not so much a code generator as it it is a RAD IDE. It does as they say generate well architected code for ASP.NET or Java, but that could mean anything, so I can't really comment on  it. The IDE is VERY polished. I like the idea of it. Pages and applications look like they can be created quickly using default created when you drag tables onto the screen. You can also do more of a manual page creation. It seems to be quite intelligent and would allow for faster development and easier understanding of application flow because much of the tooling is graphical in nature. Being able to do Java as well is a plus. I have to subtract some stars because it doesn't appear to just generate the full application, and I don't know how easy it is to do a lot of things in the tooling. I don't have all the details I need to properly evaluate the tool, but I have a feeling it is a nice tool that will do what I require AND the price will be way too high. It also suffers from a complete new IDE and paradigm and doesn't use Visual Studio. I see these as pretty major drawbacks if you are looking to keep .NET developers happy.

CodeOnTime 3.5/5 stars

Website: http://www.codeontime.com

It generates ASP.NET Web Forms application. I think this is a pretty good tool overall for end-to-end application generation and I like the SharePoint-like UI that it generates. However, it just doesn't feel natural to work in this tool. For instance, I don't understand why they have a tab with EVERY field in the database, a tab for All the comands, all fields, all controllers, and all pages. Then when I open any one of these items it is just predefined questions about generation. It appears you can do custom layouts, but it is through a little editor window and just doesn't really make sense to me why I would want to work in that manner. The code it generates looks clean enough, but everything is abstracted away so much I find it difficult to find my concrete classes. For instance, if I have a table called Person and I want to find the methods, pages, etc related to that I can't easily find it. I took stars away from this one because in general I just feel like I get lost in all the pages of code generation options when what I want is to modify fields, pages, controllers, etc.

Microsoft Lightswitch 4/5 stars

Website: http://msdn.microsoft.com/en-us/vstudio/ff796201.aspx

Lightswitch generates Silverlight applications which are very nice visually, and are very customizable and extensible. Lightswitch is built into Visual Studio 2013. It uses new technologies like WCF, LINQ, Entity Framework, and good design patterns. Granted you are pretty much locked into their architecture, but that is the case with all these code generators. There is a bit of a learning curve for Lightswitch if you want to extend it heavily. Lightswitch was actually my first choice except that it is based on Silverlight. I was very excited when Microsoft came out with a HTML version. Unfortunately, the HTML version is really heavily geared towards mobile access and really isn't good for a full browser experience. 

Microsoft Dynamic Data 4/5 stars

Website: http://msdn.microsoft.com/en-us/library/ee845452.ASPX

Microsoft Dynamic Data is a project type built into Visual Studio 2010 and newer that is actually very customizable and generates decent UIs. It can use lots of different technologies, but most importantly it can use LINQ and Entity Framework. The cool thing is that it is all template driven at runtime so you can customize it to whatever you want. You can add any custom pages you want, or have a particular table or page for a table be completely custom. There is really not that much code that is generated compared to some code generators. I have successfully extended the base application into very nice applications, so I know it is extensible. That is not to say that it is quicker than standard ASP.NET to develop, but it does allow you to have all your basic CRUD screens right there and create custom pages for the stuff that doesn't fit that mold. It is worth a look and considering it as a real solution. This is a great platform if you want a starting point for making a company standard for all applications and don't mind working a bit to make it just how you want it. Word of warning: you can spend an enormous amount of time extending (more than just customizing) Dynamic Data Applications. For example, to add security to it and add support for only showing certain fields on a page can take a lot of work.

CodeSmith 3.5/5 stars

Website: http://www.codesmithtools.com

Out of the box, CodeSmith is really about allowing you to create your own templates to generate your own code. However there are hundreds of templates to help speed up development. One example that allows the generation of an entire application is  .netTiers. Based on a database schema it creates separate Logical layers for entity, dal, business, web, forms, web service. It uses stored procedures or parameterized sql for dal. The UI is reasonable, but a bit dated in look and feel. The good news is this is 100% customizable. CodeSmith would be a good option if you want to completely customize the kind of application that gets generated. It is a reasonable option if you combine it with .netTiers for generating an application with the click of a button

NOTE: There is a open source product called TaHoGen that executes CodeSmith templates, but has no UI.

MVC Scaffolding using Razor and Entity Framework 2.5/5 stars

While scaffolding in ASP.NET MVC projects in Visual Studio 2012 is very cool and extensible (yes you can customize the generated scaffolding). However, there is no ability to maintain the changes and have to be done manually or regenerate the scaffolding. It is also far from point and click to generate the application. For an experiences MVC developer and a polished database, a basic application could be done fairly quickly, but in days and weeks and longer to develop something similar to a polished application. The upside is that the code is very clean and the html pages sent to the browser are generally very light because they are MVC, not WebForms. While I can't recommend this scenario for a business analyst, I would recommend it for developers. Hence, my poor rating for it. Please not I love MVC, but it doesn't fit these requirements. As a tool for a developer I give it 4.5/5.

Iron Speed 4.5/5 stars  THE WINNER

Website: http://www.ironspeed.com

Iron Speed specializes in end-to-end generation of ASP.NET WebForms based applications using its own data, business, and presentation layers, and stored procedures or inline SQL. It excels at picking up changes to the database and maintaining your application. It is not a generate once kind of generation tool. It gives you the tools to edit your application. In general it has an IDE that gives you everything you need to maintain the application. However, there are thimes when you will want to use Visual Studio such as to get Intellisense support if you are writing lots of code. You do not need Visual Studio, but you can use it. However, do NOT edit the .aspx pages. Instead you must use the Excel like editor to manipulate the page. Alternatively, you can make changes via code. There are event handlers for everything, you can override methods, etc It has a concept of formula that work like Excel and are great for doing calculations, manipulations, and in general most customizations you will need to do. It has great transaction support. For example, if you have a master-detail page the all the details rows can be edited at the same time AND the master (parent) is editable all without even saving. This allows for Excel like editing in grids. Filters are trivial to add. Moving columns and layouts is all graphical and trivial. The UI is very polished looking. You can create your own ASP.NET theme to change the look and feel of the application. There are many different kinds of layouts. Training is readily available through videos or through live training classes which include Q&A. Security is trivial as well. Just define if you FBA, Windows authentication, FBA with Active Directory as backend, etc and then set permissions on each page and even field if yo u prefer using the graphical UI. It does NOT use Entity Framework or LINQ, but it does use well written stored procedures and has a data layer. The data layer basically maintains itself. The business layer does too for the most part. This IDE is suitable for a techie Business Analyst or beginning developer. It deploys to SharePoint 2007 and 2010, but not SharePoint 2013. If Iron Speed had support for Entity Framework and LINQ and MVC I would give it a 5/5.

NOTE: I have been using this tool for a few months now and support on the tool is also good. I have not found anything I can't do with it or anything functionality I can't implement in it. I have a deep understanding of ASP.NET, but it is by no means necessary to use this tool. In my mind, this is the best choice for my needs. Depending on your needs you may choose differently. There are lots of good choices and cheaper solutions.