Voice of the DBA

Tag: Redgate

Installing the SQL Clone Agent and the VC++ Runtime

I was trying to get SQL Clone working recently and had an issue with the Agent. If you haven’t looked at SQL Clone, it’s a really cool product from Redgate that’s in beta right now. It will help you build and provision those large dev, test, customer service, etc. environments with full size copies of data without using the space (or time) for the actual files.

In any case, when I downloaded the agent and ran the installer, I saw this:

That struck me as funny because I have all sorts of VC++ runtimes on my machine. In fact, in debugging, I saw all these:

After the download completed, I got an error.

This was strange, since I actually had a 14.3 version, but apparently SQL Clone doesn’t realize this is the case. This should be fixed soon, but for now, here’s what to do.

First, run regedt32.exe. The setup program is checking for an installation in a key. I had the keys in the HKLM\SOFTWARE\Microsoft\VisualStudio\14.0\VC\Runtimes\x64, but not in the Wow6432 node.

Browse to HKLM\Software\Wow6432\Microsoft\VisualStudio\14.0\VC. Here’s what I had:

Here’s my x64 folder structure:

In here I have the sub-keys and the “Installed” value.

Since I know I have a later VC++ runtime, I did this. First, create the “Runtimes” key under VC, then add a “x64” key below that. Inside of x64, add an Installed value as a Reg_DWord with a value of 1. You should see this:

Now run the SQL Clone Agent installer:

Everything works.

If you’d like to learn more about SQL Clone, check out this short video:

January 13, 2017
More #SQLintheCity–Behind the Scenes

I posted this, but here it is again. A a shot from our engineer, behind the scenes of my live talk today.

Tom Austin and @way0utwest live at #sqlinthecity – a behind the scenes look! pic.twitter.com/ADsWhc2tEg

— Robert Chipperfield (@rmc47) December 15, 2016

//platform.twitter.com/widgets.js

When we decided to stream SQL in the City this year, I wondered how things would go. We’ve debated about a studio at the Redgate office but never built one. However, we did take over a room for this event, one on the top (2nd) floor, in a corner of the building away from most desks.

We didn’t have a red “on the air” light, but our engineers did put a whiteboard in the hall outside.

Inside, we had a backdrop and podium for presenters, tape on the floor for our marks, and multiple cameras. Our setup allowed for a few microphones, multiple video feeds, and a few monitors on the wall.

My view from the podium shows a few desks and the monitors on the back wall. One had the live stream, and the other was hooked up to a laptop where Annabel would type questions that we could see (or other notes).

]

The event kicked off with Annabel providing an opening each day. Here is the opening segment from Wednesday morning. I watched most of the first day in the room, and then did what most presenters did the second day; I’d work at my desk and then pop in 10 minutes or so before my talk.

There were lots of wires, and while most were taped down to the floor, we had to be careful about where we walked. I suspect next year we’ll have a better setup. At least, I hope we do, and hope that we actually do more streamed content throughout the year.

I think this was a success, and certainly an easier way for me to present content. It was strange to not have an audience, but it helped to have a few people in the room that were watching and paying attention.

While I really do enjoy live events and audiences, and I am pushing for a live SQL in the City 2017 in the UK, I also know that streaming and video broadcast is changing for all sorts of media. I’d like to experiment and do more here, and might do some more from home in 2017, but I do hope we’ll have more streaming from Redgate, perhaps even a Redgate TV show running on a regular basis.

January 11, 2017
Quick Prompt Tips–Custom Procedure Templates

One of the things that I often do is create stored procedures. The syntax for doing so is simple, but it has a number of items that need to be included. SQL Prompt makes this much quicker with the “cp” snippet. When I type “cp”, I get this:

I can hit Tab and I have a snippet, but it has a lot of things I don’t like in it. Plus, I want to save time coding, not have to remove some commented out items.

Let’s make this more efficient. I can go to the Snippet Manager under the SQL Prompt menu and select it. When it opens, the snippets are highlighted, so I type “cp” to get to the Create Procedure snippet.

I click edit and see the code, which I highlight before deleting this.

Then I paste in the code that makes more sense to me. Notice that in my case, I have two placeholders, not one (as shown above).

The code I use has a header in the procedure, and the procedure name is used both for the definition and a GRANT EXECUTE. I include the begin..end structure for the procedure with the cursor starting in the spot where I’d put code. I also have a placeholder for a role name. It looks like this.

CREATE PROCEDURE $procedure_name$

/*
Description:

Changes:
Date Who Notes
———- — —————————————————
*/
AS
BEGIN
$CURSOR$
END
GO

GRANT EXECUTE ON $procedure_name$ TO $role_name$

In practice, when I type “cp” and hit Tab, I get the code with the procedure highlighted. I can enter a name here. Note what I typed is also placed in the GRANT statement at the bottom.

Once I am done and hit Tab, my cursor jumps to the next placeholder, in this case, the role name. Notice that SQL Prompt knows this is a role and gives me a list of roles and users to choose from.

When I finish and hit Tab again, the cursor jumps to the point between the BEGIN and End where I will enter my code. Now my job begins.

This little customization gives all my procedures some standard look as well as ensuring that I can quickly build procedures without a lot of mundane, tedious typing.

Try out this quick SQL Prompt tip and see how much smoother your coding goes. And if you’re not a SQL Prompt user, download an evaluation today and see how much more efficient you can be when writing T-SQL code.

You can see a complete list of SQL Prompt tips at Redgate.

January 4, 2017
New Formatting in #SQLPrompt
I love SQL Prompt. This might be my favorite tool from Redgate Software and just when I thought it couldn’t be any better, a new formatting engine has been released.

Reading through existing source code and understanding the intent is an important part of any software developer’s life. Often, we find ourselves reading code written by other people, which can be difficult to comprehend when the structure and layout is unfamiliar. This is one reason why many companies have had coding standards that not only enforce items such as naming, but also can prescribe whether tabs or spaces are used, indentation prescriptions, and even line breaks. While our industry has some conventions, they are not as widespread as we might hope.

One of the features I find most useful about SQL Prompt is the ability to quickly reformat code in an easy to read fashion. For example, I’ll get code like this from a forum post or a co worker:
```
SELECT  * FROM
  ( SELECT 'Month' = DATEPART(mm, NewsDate), 'Count' = COUNT(*)     FROM News GROUP BY DATEPART(mm, newsdate)  ) a
```
With a quick CTRL+K, CTRL+Y, and then I see this:
```
SELECT  *
 FROM
    (
        SELECT  'Month' = DATEPART(mm, NewsDate)
              , 'Count' = COUNT(*)
         FROM   dbo.News
         GROUP BY
            DATEPART(mm, NewsDate)
    ) a
;
```
That’s much easier for me to read, and thus easier to understand, debug, and improve. Over the years I’ve become used to indentations in certain places, and different types of alignment that can help me understand how a query is structured quicker. I also find certain layout, such as commas first, easier when I try to alter and debug code.

While SQL Prompts formatting engine has worked well, it’s lacked some features that I’ve wanted. For example, I’ve written a lot of code with the Window functions in T-SQL and prior to SQL Prompt v7.3, the formatting would place then entire OVER() clause on a single line. The same thing used to happen with various DDL features as well, meaning that I’d be able to clean up some code, but I’d then go back and add various line breaks to help the code fit on one line and be easier to read.

The Way0utwest Style

That’s changed. I’ve been beta testing a new formatting engine for a few months as a part of the Experimental Features in SQL Prompt, and I love it. In fact, I’ve even added my own style, as you can see below:

This is my custom style, which I’m still working on. On a regular basis, I’ll edit the style, changing some of settings as I find new code that doesn’t quite look right to me. I’m amazed by the incredible array of options, and I find myself impressed with the SQL Prompt team once again.

This hasn’t been an effort for me. I don’t usually go poking too far into the settings or make a concerted effort to get things right the first time. Like many things with Prompt, I just want it to work in the background, correcting code. When it doesn’t format in an easy to read way, I’ll go alter a setting. For example, the first time I got some CTE code, the default style will format things like this:
```
WITH myTally (n)
AS (SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM sys.sysobjects
   )
SELECT *
FROM myTally;
```
However, that isn’t quite what I want. I quickly opened the SQL Prompt menu, and I see my formatting options at the bottom:

I have also been able to access this from the Experimental Features menu (you’ll want to keep an eye on this area in the future).

Once in the style editor, I can click the colon after my style name and then edit.

There are a number of options, but CTE is one of these.

Inside this area, I can choose to do a few things. I can choose when to break to new lines, when to add spaces, and where to indent. This is truly a flexible and highly configurable formatting engine.

As I make changes, I can see the new format below with a sample query, or I can check a box to see how my current query will be reformatted. Once I’m happy, I can save the changes and move on to what I need to do: write code.

There are far too many options to work with, and it’s one reason that I don’t bother trying to set all the various settings. In some cases, I’m not even sure how I want the code formatted until I realize that there’s an issue understanding a complex layout. It’s at that point that I’ll go ahead and change some settings to adapt SQL Prompt to my current query. I can even create a new style that inherits the settings from a previous style if I’m experimenting, a topic for another blog.

Formatting for Friends

In my format list there’s a second style I’ve labeled “Grant”. This is a separate style that formats differently based on Grant’s preferences. I asked Grant for his .sqlpromptstylev2 file and added to my system. The default path is %USER_PROFILE%\AppData\Local\Red Gate\SQL Prompt 7\Stylesv2. Once I have his style, I can reformat code the way he wants it if I send it over.

Why? Certainly Grant can reformat code the way he wants it with his own SQL Prompt. It’s a nice touch fo rme to be able to work with code the way that’s easier for me and then reformat it in a way that’s easy to understand if I send a short segment over email, Slack, or some other communication means.

I’ve also worked in situations where code needed to be formatted a certain way. Various clients and employers have dictated that code is strictly laid out in a certain style for code reviews and committed in a VCS in a particular way. By having the option to quickly switch styles in Prompt, I can reformat code for others without too much effort.

SQL Prompt has been one of the more useful and helpful tools I’ve worked with in SQL Server, and just when I thought it might be mature, I get this fantastic formatting engine. It’s been fun to play with across the last few months and now it’s live as a default feature in SQL Prompt 7.3. You can now start playing with your own formatting style when you upgrade.

Or download a copy of my style and see how I prefer to read code: Way0utwest’s Style.sqlpromptstylev2

If you’re not a SQL Prompt customer, you don’t know what you’re missing. This is a vast improvement over the native intellisense and I’d say you should download an evaluation and give SQL Prompt a try today.
December 27, 2016