Workaround for the SharePoint Asset Inventory Tool ';' error
I was preparing for the upcoming conference and wanted to demo some of the new tools coming for SharePoint. Often I have heard the customer's IT department talk about finding rogue SharePoint installations and getting better metrics on how these servers were being used, so I thought I would spend some time with the SharePoint Asset Inventory Tool. This tool is currently in Beta, but is publicly available through Microsoft's Connect site. It is basically a scanning solution that looks for SharePoint servers and dumps metrics about them into a SQL store with some nice SQL Reports. I installed the tool, only to get an error half-way through the scan. The error was something about a malformed SQL statement near a ';' and an ELSE statement. Unfortunately, I had nothing in the repository to even show as part of the demo. So the hunt began...
Looking through log files, I found the offending stored procedure to be dbo.SP_INSUPD_DEVICES_PORT_NUMBER. This proc was in the SATAssets database on my SQL server. If you modify this proc, you will notice that it builds a dynamic SQL string. In this string, one of the variables is not covered by two single quptes ''. So ...
IF('' + PORT_NUMBER + '' IS NOT NULL)
notice that I added the 2 ''. This proc and the SATAssets DB seem to be recreated each time you run a new scan after the initialization screen. Start the wizard, wait for the initialize prompt to go away, modify the proc, and then it works.
I also am a big fan of host headers in my dev environments. I noticed I also had to make sure that I had a web application that resolved the the server name on port 80 (or one of the ports it was looking for) to get my results.
I've also posted this workaround in the forums so hopefully you'll have some luck before the next drop happens on the Connect beta site.