I keep getting the error  has not been registered" when I try to use the Import Data functionality in SQL Server Management Studio (SSMS) with a .xlsx Excel file as the data source.

The OLE DB provider Microsoft.ACE.OLEDB.12.0 has not been registered.

Here is my environment:
Windows 7 (64-bit)
SQL Server 2008 R2 (64-bit)
MS Access 2010 (64-bit)
MS Office Excel 2010 (64-bit)

I do NOT get the above error when I use the import wizard in MS Access 2010 to import my Excel (.xlsx). It only happens with SSMS. I tried installing the different Data Connectivity Components. Some would say it was already installed, other would complain that it wouldn't work with the 64-bit software I was using. In the end the only one that would install that helped was the following:

2007 Office System Driver: Data Connectivity Components

So frustrating that I had to waste time on something that should just work after installing SQL Server 2008 in my opinion.

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.