Press "Enter" to skip to content

Category: SQL Server Management Studio

Generating Change Scripts

Published 2016-07-19 by Kevin Feasel

Steve Jones shows off a rather valuable feature in SQL Server Management Studio:

One of the really basic things I think everyone should understand is how to get scripts from Management Studio (SSMS) and saving them. In fact, I’ve written that everyone should use this button and really not ever execute their GUI changes. Capture the script, save that, and automate things.

However, that’s not what this post is about. This post is about how you get a script to look at changes, or better understand how SSMS might implement your changes.

The ability to script out your changes has a number of benefits, one of which is that you’ll get to learn the code you need to write to perform an action, which could make all the difference in a production-down situation.

Comments closed

SSMS And Azure SQL Data Warehouse

Published 2016-07-13 by Kevin Feasel

Rob Farley reports that we can now use Management Studio to connect to Azure SQL Data Warehouse:

One of the biggest frustrations that people find with SQL DW is that you need (or rather, needed) to use SSDT to connect to it. You couldn’t use SSMS. And let’s face it – while the ‘recommended’ approach may be to use SSDT for all database development, most people I come across tend to use SSMS.

But now with the July 2016 update of SSMS, you can finally connect to SQL DW using SQL Server Management Studio. Hurrah!

…except that it’s perhaps not quite that easy. There’s a few gotchas to be conscious of, plus a couple of things that caused me frustrations perhaps more than I’d’ve liked.

Yes, it’s never quite that easy…  Read the whole thing.

Comments closed

Watch Those Parentheses

Published 2016-06-28 by Kevin Feasel

Kenneth Fisher shows how to see open and close parenthetical locations:

You’ll notice that when I go over the parentheses the one I’ve selected and it’s pair turn yellow, unless there isn’t a pair of course. You can also use Ctrl-] to flip between the open and close parenthesis in a pair. This can be particularly useful to make sure that you remembered a close parenthesis at the end of a subquery. In this case that last close parenthesis doesn’t have a match. Now finding out that you are missing an open parenthesis doesn’t mean you know where it’s supposed to go. But you can track the different pairs, making sure that each time you open a parenthesis you close it in the correct place. In this case it belonged right at the beginning.

FYI yellow isn’t the default (it’s a light gray). I find the default hard to see (I’m getting old) so I changed it to yellow in the options under fonts and colors.

Read the whole thing.

Comments closed

Alternate Credentials

Published 2016-06-27 by Kevin Feasel

Daniel Hutmacher shows us various techniques for starting Management Studio under different Windows credentials:

The easy way to solve this is to just log on directly to the remote server using Remote Desktop and use Management Studio on that session, but this is not really desirable for several reasons: not only will your Remote Desktop session consume quite a bit of memory and server resources, but you’ll also lose all the customizations and scripts that you may have handy in your local SSMS configuration.

Your mileage may vary with these solutions, and I don’t have the requisite skills to elaborate on the finer points with regards to when one solution will work over another, so just give them a try and see what works for you.

I prefer Daniel’s second option, using runas.exe.

Comments closed

Negative Width And Height In SSMS

Published 2016-06-20 by Kevin Feasel

Manoj Pandey ran into an issue with Management Studio wanting to open a window with a negative size:

By checking the error its obvious that there is something wrong with Width or Height of SSMS Query-Editor window.

So, I went to REGEDIT (In RUN, type regedit.exe) and after navigating here n there got the location where to update this property.

Navigate to folder: HKEY_CURRENT_USER\SOFTWARE\Microsoft\SQL Server Management Studio\13.0\

Here check the MainWindow property value (image below), it was showing: 0 451 109 -120 876 1

Change it to a positive value considering the width of your SSMS editor window, I replaced -120 with 1400

I had no idea that the main window size details were kept in the Registry.

Comments closed

Undocked Query Windows

Published 2016-05-31 by Kevin Feasel

Michael Swart notes that undocked query windows now feel all grown up:

The March 2016 Refresh (13.0.13000.55 Changelog) updates SSMS to use the new Visual Studio 2015 shell. Part of that change means that undocked windows are now top-level windows.

Top level windows are windows without parents so the undocked window is not a child window of the main SSMS window (but it is part of the same process). And so it gets its own space in the task bar, and participates in alt+tab when you switch between windows.

Also these undocked windows can be a collection of query windows.

One reason I rarely used child windows is that I’d undock something, switch to a browser tab underneath, and then switch back and watch the undocked window pop over my browser tab.  This sounds like a good improvement.

Comments closed

Template Replacement

Published 2016-05-20 by Kevin Feasel

Andy Mallon shows SSMS template replacements:

In the above example, there’s not much value-add by using the template replacement. It’s probably easier to just use @variables and highlight-replace.

The template replacement really shines when you have examples where you’d otherwise need to use dynamic SQL. If you have object names or database names that need replacement, this is a great answer. If you work in a multi-tenant hosting environment, and a client name is part of the DB name, this can make your life a lot easier.

Templates work great with auto-replace (a feature several third-party toolkits include).  My favorite auto-replace that I’ve created is “die” which asks for an schema and procedure name and generates the DROP PROCEDURE script.  Naturally, I also have diet (table), diev (view), and dief (function).

Comments closed

Management Studio Trello Board

Published 2016-05-03 by Kevin Feasel

Aaron Nelson has set up a Trello board for Management Studio collaboration:

A couple weeks ago I mentioned that we are using Trello to help the community collaborate about what we want next in SQLPS before we submit Connect items to Microsoft.

That effort is going very well.  It’s going so well in fact that when the topic of getting some new improvements into SSMS was brought up, the SQL Tools team suggested that a Trello board to collaborate and prioritize what people want improved in SSMS would be very helpful to them.  Ultimately Microsoft needs Connect items filed but using Trello helps folks to debate and combine ideas.

The cynic in me says “this is what Connect is supposed to do” but Aaron and Chrissy LeMaire had a great deal of success working with the SQLPS team, so here’s hoping they get traction here as well.

Comments closed

Registered Servers

Published 2016-04-28 by Kevin Feasel

Erik Darling shows how to set up registered servers in Management Studio:

And if you hip and hop over to the Connection Properties tab, you can set all sorts of nifty stuff up. The biggest one for me was to give different types of servers different colored tabs that the bottom of SSMS is highlighted with. It’s the one you’re probably looking at now that’s a putrid yellow-ish color and tells you you’re connected and that your query has been executing for three hours. Reassuring. Anyway, I’d use this to differentiate dev from prod servers. Just make sure to choose light colors, because the black text doesn’t show up on dark colors too well.

In team environments, I’m more a fan of Central Management Servers.

Comments closed