Voice of the DBA

Tag: Redgate

SQL Prompt Gets Even Better

SQL Prompt v9 came out recently, and just when you thought they couldn’t make it better, they found a way. If you click the menu for SQL Prompt, you’ll see a couple new items.

Code Analysis!!! Finally, a first step towards some sort of better analysis of code. These are a set of rules from our SQL Code Guard acquisition and we’ve integrated these into Prompt. Now you’ll get some green squiggly’s to alert you to potential issues.

For example, I get two in this short, poorly written code. The first item is a TOP without an ORDER BY, which usually isn’t a good thing.

The second item is one of those that I would like to put in all CAPs for developers. No asterisks.

Right now there are a set of static rules, but we are working to add more and allow you to customize these items. We’re also looking for feedback on how you might like to apply or surface these rules to your staff. Please, if you have comments or questions, ask at the Redgate Hub.

For now, you can disable analysis, or disable selected rules. Click the Manage link, and you’ll see a list of rules, which you can turn off as needed.

Static analysis of SQL code has a long way to go, but I am looking forward to seeing more improvements appear in SQL Prompt and our other products over time.

December 5, 2017
SQL in the City 2017
It’s almost time for SQL in the City 2017, coming Dec 13, live from Cambridge, UK. I’ll be traveling over to England for the event, and you can stream it to your office live. Once again, we’ll be presenting the conference twice, 11am and 6pm (both GMT), and you can pick the times that work best for you.

We’ve got a variety of sessions scheduled, and you can tune in to ask questions live, which we’ll try to answer. The full schedule is on the site, but here’s a few sessions you may want to fit in:
- Keynote  – Data Privacy and DevOps
- Why code standards are important and how code analysis can help
- From DBA to DevOps hero – rapid database provisioning with SQL Clone
- What SQL Server performance metrics should you monitor?
- Extend DevOps to the database by automating your deployments
We’ve been working hard at Redgate to improve our tools and build new ones that will need the needs that many organizations will have as GDPR takes effect next year. And, as always, we’re trying to save developer time with all our tools.

Take a training day to learn a bit about privacy, security, DevOps, code analysis and more. We’ll show how Redgate can help solve some of your issues along with plenty of good general information.

Hope to see you online, Dec 13.
November 30, 2017
Watch Out for Automap in SQL Compare

I wrote a piece on SQL Compare and customizing the Automap feature. This was handy for me in a small project where I encountered drift and needed to fix the production side.

In the article, I don’t map one of the columns in the target. In this case, I’d actually be dropping the target column in SQL Compare. After all, the idea of SQL Compare is that we want to synchronize the databases.

If that’s not correct, then I’d need to do one of two things: add the column in development or add a filter. Filters are a great way to avoid issues in production when development items are in flight or uncertain (I’ve written about those as well).

In this case, if I really needed to keep this column in production, I wouldn’t want to add a filter. Instead, I’d really want to add the column in development. In fact, I’d want to add it in the same place it is (column order) in production, so in Development I’d rebuild the table with the new column, in that order, and then run the Compare again.

Automap can be a great feature, but it can’t know what your intentions are. It can only guess, like a new developer that is working on the system for the first time. SQL Compare needs guidance to do the job properly.

SQL Compare is here to help you, and if you haven’t used it, give it a try. If you’re on a version older than 12, I’d say you should upgrade to 13 immediately. If you work with SQL 2017, then move from SQL Compare 12 to 13 and ensure all your comparisons work with the new features.

November 27, 2017
Adding a Format SQL Button to the Redgate Toolbar

I’ve gotten used to CTRL+K,Y to format SQL with SQL Prompt, but a customer wanted a button on the toolbar. It’s fairly easy to do, but I thought I should document the process for others.

First, if you click the small area on the right of a toolbar in SSMS, you’ll get an “Add or Remove Buttons” menu, as shown here.

If you click that, you’ll see this dialog.

In this case, I’m happy with the buttons, I want to customize my toolbar. Click that option. This opens up the dialog below, and I’ll want to click the “add Command” to add a menu item as a button.

From here, I get a list of all SSMS menus. In this case, I’ve clicked the Dimension item, and I can see all the possible menu items on the right.

However, I want a SQL Prompt item, so I need to scroll down the left to SQL Prompt. Once I click that, I see the commands on the right,

Now scroll the right to find Format SQL.

Click OK and then your new button appears in the list.

And on the toolbar.

Now you GUI clickers can reformat SQL quickly.

November 14, 2017