Press "Enter" to skip to content

Category: SQL Server Management Studio

SSMS 19.1 Changes and Roadmap

Published 2023-06-20 by Kevin Feasel

Erin Stellato has one more post for us:

In SQL Server 2022, support for backup to and restore from S3-compatible object storage was introduced, and in SSMS 19.1 we have added support for restoring from S3 for both Azure SQL Managed Instance and on-premises databases.  The Select backup devices dialog now includes S3 URL in the Backup media type drop down:

This is something I like. But do click through to learn more about SSMS (spoiler: it’s not going away and there will be an SSMS 20).

Comments closed

SSMS 19.1 Updates

Published 2023-06-13 by Kevin Feasel

Erin Stellato has a two-parter around SSMS 19.1. The first includes a small but important change:

In SSMS 19.1, the default value for Use system default web browser was changed to True.  This only applies to new installations; anyone upgrading from an earlier version of SSMS 19 will need to change the option to True manually.

This means that, for new installations, clicking on a link in SSMS will no longer open in the built-in SSMS browser, which doesn’t work for a lot of newer sites.

Part 2 picks up with some additional changes:

We have heard complaints, for months, about the startup time for SSMS.  Between 18.12.1 and 19.0 we made a few changes, and in 19.1 we delayed initializing the output window to also reduce startup time.  Some of you noticed!  Glenn Berry completed a round of testing and documented findings in his SSMS 19.1 Performance Improvements blog post.  We are hoping to also gain improvement when we get to SSMS 20, so know that our work is not yet done.

Click through for the full set of chnages.

2 Comments

SSMS 19.1 Startup Performance Improvements

Published 2023-05-31 by Kevin Feasel

Glenn Berry breaks out the stopwatch:

SSMS 18.X cold startup performance is a big issue for me, since I regularly have to start SSMS 18.X on various small VMs running on older, slower server-class processors. BTW, “cold startup” is the first time that a program is loaded into a process since the last reboot of the operating system, so nothing from that program is already loaded in memory.

The hero image alone convinced me to install 19.1.

Comments closed

Stopping an SSIS Package from the SSIS Catalog

Published 2023-04-12 by Kevin Feasel

Olivier Van Steenlandt hits the big red button:

I developed my package in Visual Studio 2019 and deployed my changes to my SSIS Catalog (SSISDB in my case). As soon as my SSIS Package was deployed I started the SSIS Package execution.

After a couple of minutes, I realised I made a mistake. I didn’t want to lose too much time, so I looked for a way to stop the SSIS Package Execution.

Since I was not executing the package from a SQL Server Agent Job, I couldn’t just stop the job. So I needed to find another way.

Click through for a way to do this via SQL Server Management Studio.

Comments closed

April Tools Day

Published 2023-04-03 by Kevin Feasel

Erin Stellato dispels some myths:

Myth #1 Azure Data Studio is the only standalone solution now that SSMS is deprecated.

SQL Server Management Studio (SSMS) is not deprecated.  We thought about writing that in ALL CAPS, but figured bold is sufficient.  SSMS has not been deprecated, and we are not planning on deprecating it.  You will see new functionality being added to Azure Data Studio, but we have a fair number of things lined up for SSMS, including migration to the Visual Studio 2022 shell, which brings 64-bit support.

Bold plus all caps might have been a bit too much, yeah.

Click through to see what’s happening in the world of SQL Server tooling from Microsoft.

Comments closed

Changing the Browser SSMS Uses

Published 2023-03-29 by Kevin Feasel

Meagan Longoria doesn’t want the built-in browser:

Did you know that you can change the browser used by SQL Server Management Studio to authenticate using Azure Active Directory to a SQL database in Azure?

I had been experiencing serious delays with the window that pops up to accept my credentials taking 30 seconds or more to populate. I also once got a warning that the browser I was using was old.

Click through to see how to resolve annoyances around SSMS’s built-in browser and change to the default you use for everything else.

Comments closed

Handling Optional T-SQL Code via SQLCMD

Published 2023-03-07 by Kevin Feasel

Louis Davidson shows off a bit of SQL Server Management Studio’s SQLCMD mode:

In the creating a database script, there are quite a few parameters you will want as part of your script. The database name, the settings, whether or not to drop the database or not. Built into SQL Server Management Studio is a cool tool called SQLCMD mode. Scripts using this mode get a few scripting tools that are really useful. These tools allow you to do things like insert other script files, set environment variables, and one I particularly like, make sure you don’t accidentally execute a script if no code is highlighted using EXIT to start your script ().

Where it is really lacking is in the area of control of flow language. In this blog I am going to share a few techniques I have used to get around this when building scripts that need to optionally execute different bits of code.

Click through to see how you can use it. Going one step further, you can directly script against sqlcmd.exe, which can be useful for automating deployments.

Comments closed

ORDER BY Clause in Subqueries and SSMS Warnings

Published 2023-02-20 by Kevin Feasel

Ronen Ariely explains a warning message:

Warning: The ORDER BY clause is used only to determine the rows that are returned by the TOP clause in the view definition. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself. Click CANCEL to discard your modifications. Click OK to save the view.

Read on for the full context of when you might see this warning message in SQL Server Management Studio.

Comments closed

SSMS 19.0 Released

Published 2023-01-27 by Kevin Feasel

Erin Stellato has a new version of Management Studio for us:

In addition, we received a lot of feedback about the dialog box, “Querying transaction count”, we introduced back in SSMS 18.11.   In 18.12, we added an option to cancel the check if it took more than two seconds, but user comments indicated that was not enough.  Therefore, in SSMS 19.0 you have the ability to disable to dialog box completely.  Within Tools > Options > Query Execution, simply uncheck the option “Check for open transactions before closing T-SQL query windows”.  

That alone might be worth it, especially if you work via VPN and have a bunch of SSMS tabs open.

Comments closed

SSMS and Additional Connection Parameters

Published 2023-01-17 by Kevin Feasel

Tom Zika gets spun around a bit:

The other day I managed to confuse myself. I was looking up some information from an Extended Events (XE) session, but my eyes were playing a trick on me. The database ids were off by one, and I couldn’t find some query hashes in the Query Store, even when they were supposed to be there. So my first thought was that I must be connected to a different server with a drift. But the information in the SSMS tab, status bar and even colour coding (courtesy of Redgate’s SQL Prompt) – all pointed to the correct server.

I don’t think I’ve ever run into this before but could see how it’d be really confusing.

Comments closed