A while ago I’ve had a blogpost about how I suffer working with unformatted SQLs.
Sometimes ago I found the tool the suits me the most – ApexSQL Refactor . It has a lot of settings, much more than all competitors I’ve tried before. I’ve managed to configure it to produce results that annoy me as little as possible. My settings can be downloaded and imported. It still has some shortcomings and hopefully they will add more features in future versions.
There are a few gotchas here though. On my current project I have to use SQL Server 2008 R2 and I kept installing this exactly version just to ensure we’re not using features from neweer versions. Actually we a lucky, as before we had to support SQL Server 2005 which had even more limitations.
But recently ApexSQL Refactor dropped support for the SQL Server Mangement Studio older than 2012, and I was not able to download the latest version that still supports SSMS 2008. I’ve written to the tech support team and they were kind enough to send me the link for the version that works with SSMS 2008
Recently I’ve scripted and reformatted every single object from the database. Then we discovered some strange behavior in the system we are working on. After some investigation I found that actually the formatter caused it! Reformatter broke the logic.
So the problem is the following
Let’s reformat the following script
If you do that you’ll see that
IF (@@ROWCOUNT = 0) -- Username not found RETURN SELECT p.PageSettings FROM dbo.aspnet_PersonalizationPerUser p WHERE p.PathId = @PathId AND p.UserId = @UserId
is formatted as
IF (@@ROWCOUNT = 0) -- Username not found BEGIN RETURN SELECT p.PageSettings FROM dbo.aspnet_PersonalizationPerUser p WHERE p.PathId = @PathId AND p.UserId = @UserId; END;
So SELECT moved from outside IF block inside it, which actually causes the problem.
But the code itself is very easy to fix, just move SELECTs out of IFs. It won’t break it again on the next reformatting because of the BEGIN/END blocks.
I’ve already written an email tech support team and they said they will reply to me shortly.
But even with this bug, I am in love with the tool!