PoorSQL Formatter in SSMS

Formatting T-SQL code is very important.  There are many tools that we can use to help us.  One of my favorites is the PoorSQL Formatter.  There is a website that you can use, www.poorsql.com.  I also did a blog post a while ago on the PoorSQL extension for Azure Data Studio, you can find it here.  Now I have found out that there is also a plug in for SQL Server Management Studio.  This apparently has been around for a while. but I just became aware of it recently. The version of PoorSQL Formatter we are going to talk about in this post will work with SSMS 2014 or newer.

So what is PoorSQL Formatter?  Well, as you probably know, formatting is very important when writing T-SQL code.  While we try to format our code as we write it, sometimes the code we need to review was written by someone else and maybe doesn’t follow formatting standards. This is where PoorSQL Formatter will come in handy.  It will help us take the poorly written code and format it in a manner that is more in line with T-SQL formatting best practices.  The best part, it will do the formatting in seconds.

In order to install this extension, we will need to download it first.  To do so, go to PoorSQL,com.  Once you are there, find the link that says “SSMS/Visual Studio plugin, this will be toward the top of the page.  As in the image below.

Once you download the MSI file, double click it and install it.  Then open SSMS.  When you do, under the Tools menu, you will find two new items.  “Format T-SQL Code” and “T-SQL Formatting Options…”.  Of course the first one is what you will use to format your code.  While the second item is where the rules that the tool will follow when formatting code.

Let’s take a look at the options first. When you click the menu item, a window similar to the image below will appear.

As you can see there are a number of options.  I usually keep the defaults, which is what you see above.  I think they are pretty good default settings.

Once you have opened your poorly formatted code in SSMS, you can now use PoorSQL Formatter to fix some of the issues.  To do this, now click on the first menu item, “Format T-SQL Code”.  Once you click it, it will now format your code based on the preferences you have define.  Again, I think the defaults are pretty good.  Well, they work for me.

The more I use this tool, the more I like it.  I even have choices in what rules for formatted and what development tool I can use.  I can use it in both Azure Data Studio and SQL Server Management Studio.

Thanks for visiting my blog!!

Azure Data Studio – SSMS Keymap

One of the key board shortcuts I use quite often is ctrl + shift + u, which changes all the selected letters to upper case.  However, when I tried this in Azure Data Studio it did nothing. At first I was disappointed but then was very quickly relieved when I found the SSMS Keymap for Azure Data Studio Extension.  This extension will bring many of the keyboard short cuts into ADS, just as they are in SQL Server Management Studio.

In case you haven’t worked with Azure Data Studio and extensions, they are kind of like a snap in for ADS.  Once you have ADS open, to install it you will need to open the extension window.  You can do this either by clicking Extension under the view menu or click the button that is highlighted below.

When you finally arrive at the extension window, you will need to look for the SSMS Keymark extension.  It will looks similar to below.  If you look carefully you will see that this is a third party extension and not written by Microsoft.  This was developed by Kevin Cunnane.  When look at the GitHub to download the file, it looks like Kevin may work for Microsoft.  Although Microsoft’s name is not on this extension.

Click the install button and you will be taken to the GitHub location where you can download the .vsix file.  Place this in a location that it will not get deleted or overwritten.  Open Azure Data Studio, under the File menu you can click on the “Install Extension from VSIX package” item and navigate to the location you place the file.

When the install begins you may see this:

Click Yes and the install will begin.  This extension installs very fast so not much waiting.  Unlike some extensions, this one does not need a restart of Azure Data Studio to work properly.

This is a short post, but I plan on doing more posts on some of the different extensions.

Thanks for visiting my blog.

 

 

 

 

 

Azure SQL Database Connections work in SSMS but not with Azure Data Factory Connection

Over the past month or so, I have attended a number of sessions on Azure Data Factories.  Both presenters were amazing and I learned a ton.  However, as I usually do, I took the information and went off on my own to play with this new tool I hear a lot of people talking about. During both sessions I was able to connect to my Azure SQL Database with SQL Server Management Studio, but not with the connection in the Azure Data Factory. While I really didn’t get much of a chance to ask the presenter about my problem, I was finally able to.  He was able to give me the answer I was looking for.

Before getting to the solution, let’s go over what the problem actually was.  I have an Azure SQL Database that I have connected to many times via SSMS.  However, during both sessions I attempted to create a connection in ADF and the test of the connection failed every time.

So what did I check? Just to confirm, I again connected via SSMS as I had done many times in the past.  Again, no issues. Now I was convinced that the issue was not the database or the username\password. The next thing I thought of was the firewall rules.  I asked myself the following question: Could there be an issue with the firewall and the IP addresses?  Again, all appeared to be working just fine.  Then I decided to do one more check using a third technology, I connected to the database via the Azure SQL Editor, which requires a username and password to connect.  Again, all appeared to be fine.

At this point, I knew more about what was not causing the problem than what was causing the problem.  Finally I had a moment with one of the presenters and he was able to give me the cause and the solution. As is usually the case, it was a single setting that was causing this and a very simple fix.  That setting was the an Azure SQLDB setting, specifically the “Allow Access to Azure Services” setting.

To get to the setting, navigate to the settings of the Azure SQL DB and click on Overview, the orange box below.  Then you will see the “Set server Firewall” option, click that.

Once you do, you will see something like the image below.  Simply move the On\Off slider to On for the “Allow access to Azure service” setting.  Then click the Save button.

Once you do that, then you should be able to connect to your Azure SQL Database using the Azure Data Factory connection.  This made perfect sense, SSMS is not an Azure service, while Azure Data Factory is. Also keep in mind there are many reason that could create a similar situation, this was the cause of the issue I was seeing.  So make be prepared to continue to troubleshoot this issue if this does not work.

Thanks for visiting my blog!!!