Press "Enter" to skip to content

Category: SQL Server Management Studio

SSMS Autorecover

Published 2019-04-12 by Kevin Feasel

Michelle Haarhues takes us through auto-recovery in Management Studio:

Periodically there is a crash, power surge, or sudden reboot of your computer.  Of course, you had SQL Server Management Studio (SSMS) open and you were working on something important.  That seems to be the only time there is a crash/reboot.  You can lose work that that was open in SSMS but has not saved.  There is an AutoRecover feature in SSMS so all may not be lost.  

Read on to learn more about auto-recovery. It only restores on crashes, though there are third-party plugins you can get to restore after start. Azure Data Studio restores on restart as well, so kudos to the ADS team for that.

Comments closed

SSMS 18.0 RC1

Published 2019-03-29 by Kevin Feasel

Dinakar Nethi announces the first release candidate of SQL Server Management Studio 18.0:

As we get closer to the General Availability of SQL Server Management Studio (SSMS) 18, we have decided to have a quick release of the Release Candidate (RC) build.

You can download SSMS 18.0 RC1 today and for more details on what’s included, please see the Release Notes.

It’s been in preview for a while but things are now getting real. I’ll have to check later on if it fixes the bug I found with PolyBase on SQL Server.

Comments closed

Fun with Emoji in SSMS

Published 2019-03-27 by Kevin Feasel

Solomon Rutzky shares a method to generate any Unicode character in SQL Server Management Studio:

I used to go to the Emoticons (Emoji) 1F600—1F64F page of unicode-table.com to copy and paste characters, code points, or check the encoding chart at the bottom of each character page (the “hex” column of both “UTF-16BE” and “UTF-16LE” rows have proven most useful).
But not anymore. Now, I just hit:   Ctrl + 0.

When I do that, I get a list of 188,657 code points. Each row contains the official code point value (“U+HHHH”), the integer value, the hex value (“0xHHHH”), the character itself, the UTF-16 Little Endian byte sequence (how it is actually stored, and what you get if you convert an NVARCHAR value to VARBINARY), the surrogate pair values, the T-SQL notation (which does not require using an _SC or _140_ collation), the HTML notation (“&#xHHHH;”), and finally the C-style notation (“\xHHHH” ; used for C / C++ / C# / Java / etc). I can copy and paste any of those values and use them in queries, emails, blog posts, .NET code, and so on.

Click through to see how Solomon does this.

Comments closed

Capturing Queries With XEvent Profiler

Published 2019-03-01 by Kevin Feasel

Erin Stellato explains how to use the XEvent Profiler in SSMS 17.3 and later:

It’s worth pointing out that neither the Standard or TSQL session writes out to a file. In fact, there’s no target for either event session (if you didn’t know that you can create an event session without a target, now you know). If you want to save this data for further analysis, you need to do one of the following:

1. Stop the data feed and save the output to a file via the Extended Events menu (Export to | XEL File…)
2. Stop the data feed and save the output to a table in a database via the Extended Events menu (Export to | Table…)
3. Alter the event session and add the event_file as a target.

Read the whole thing.

Comments closed

Attempted To Read Or Write Protected Memory

Published 2019-02-20 by Kevin Feasel

Kenneth Fisher explains a nasty-looking error to us:

So, are you seeing this error?

Attempted to read or write protected memory. This is often an indication that other memory is corrupt.

If you read the error it might freak you out a bit. The key words memory and corrupt can be a bit .. concerning. Fortunately in this case they are also rather misleading.

Click through to understand what’s going on and how you can fix the problem if you see this error.

Comments closed

Formatting with RegEx in SQL Server

Published 2019-02-06 by Kevin Feasel

Shane O’Neill has a problem:

This is a contrived example but I was given a script that got the “Discipline”, “DocumentVersion”, “DocumentNumber”, “SectionNumber”, and “SectionName” out of the above.

And while it works, I hate that formatting. Everything is all squashed and shoved together.

No, thanks. Let’s see if we can make this more presentable.

Shane has a regular expression. Now Shane has two problems.

In all seriousness, regular expressions are extremely powerful in the right scenario. Shane mentions being okay with it not in the database engine and I’m usually alright with that, but there are cases when it’s really helpful like figuring out if a particular input is valid. One example I have on a project is finding legitimate codes (like ISBN) where you can solve the problem easily with a regex but my source data is abysmal. I can use the SQL# regular expression functions to drop into CLR and figure out whether that value is any good, something I would have a lot more trouble with in T-SQL alone.

Comments closed

The Value Of Central Management Server

Published 2019-01-29 by Kevin Feasel

Jeff Iannucci explains the benefits of Central Management Servers:

If you have more than a handful of instances, you really owe it to yourself to take a few minutes and set up your own CMS. The hard part is getting a list of all the instances, but you need to do that anyways, right?

Once you have that list pick ONE SQL Server instance to create your CMS. You want this to be something with solid up-time that is accessible to other team members, but not an instance that’s already spending CPU cycles on something critical. 

The lack of CMS compatibility is one of my larger pain points with Azure Data Studio and an issue which hopefully gets fixed this year.

Comments closed

Management Studio Query Shortcuts

Published 2019-01-17 by Kevin Feasel

Michelle Haarhues shows how you can use query shortcuts in SQL Server Management Studio:

Back in the day, with the introduction of programs like Word and Excel, I used keyboard shortcuts to make my job easier.  Then we started using a mouse and reduced the number of keyboard shortcuts I used.  It took me a long time to switch from the keyboard shortcuts to the mouse.  Now I am back to using shortcuts, especially in SQL Server Management Studio (SSMS).  Microsoft allows users to create shortcuts that, if you use them, could make your job easier.  Setting up the shortcuts in SSMS are pretty simple.

My shortcuts are all around running sp_whoisactive: Ctrl+F1 gets results back the way I want, Ctrl+3 shows only my sessions, and Ctrl+4 gives me more details (like execution plans) when I’m willing to wait the extra time to get them.

Comments closed

Navigating Execution Plans In Management Studio

Published 2019-01-03 by Kevin Feasel

Greg Low shares a few tips on working with graphical execution plans in SQL Server Management Studio:

So I can zoom in and out, set a custom zoom level, or zoom until the entire plan fits. Generally though, that would make the plan too small to read, as soon as you have a complicated plan.
But in one of the least discoverable UI features in SSMS, there is an option to pan around the plan.

Click through for the demos. My favorite way of navigating graphical execution plans in SSMS is to use SentryOne Plan Explorer instead.

Comments closed