My new favorite SQL toy – ApexSQL Refactor

Hi folks

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!

Stay tuned!

Advertisements

About mnaoumov

Senior .NET Developer in Readify
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s