Tag Archives: SSMS

My SSMS Settings: A Visual Guide

As a database professional, I’ve optimized my SQL Server Management Studio (SSMS) settings for maximum efficiency. Here’s a visual breakdown of my key configurations:

 

1. Editor Tab and Status Bar

  • Status bar shows execution time, database name, login, row count, and server
  • Pink for group connections, khaki for single server
  • Tab text includes only the file name

 

2. Advanced Execution Settings

  • SET CONCAT_NULL_YIELDS_NULL and SET ARITHABORT enabled
  • Transaction isolation: READ COMMITTED
  • Suppressed provider message headers
  • Completion time shown after query execution

 

3. Query Results to Grid

  • Max characters for non-XML: 65535
  • XML data: Unlimited

 

4. Query Results to Text

  • Custom delimiter: | (vertical bar)
  • Include column headers
  • Scroll as results are received
  • Max characters per column: 8192

 

5. SQL Server Object Explorer Options

  • Audit Log: Top 1000 records
  • Drag-and-drop: Prepend schema, surround with brackets
  • Prompt for schema object renaming
  • Edit Top: 200 rows
  • Select Top: 1000 rows

 

6. Query Output Formatting

  • Custom delimiter: | (pipe character)
  • Include column headers
  • Scroll as results are received
  • Max characters per column: 8192

These settings streamline my SSMS workflow. Adjust them to fit your needs and boost your productivity!

SQL Server Management Studio – automatically get the latest version

When working with SQL Server, I prefer to have a jump host with my tools already installed.

You may also have them on your main laptop/workstation, but I would also recommend to have a VM in the cloud or in your datacenter.

Everyone has a different list of tools they use, but most of the time you’ll find these apps on those lists:

  • command line tools to interact with various cloud providers
  • putty
  • winscp
  • chocolatey
  • SQL Server Management Studio
  • Azure Data Studio
  • git
  • vscode
  • vim
  • various PowerShell modules

In this post I will focus on SQL Server Management Studio.

Since version 18.7, the Azure Data Studio is also included in the installer.

By default Azure Data Studio is installed along with SSMS, but this can be excluded by using:

Eventually, this will not be a valid option, as SSMS will require dependencies provided by Azure Data Studio.

This is a good time to update the script I’m using to automatically install the latest version of SSMS.

I could not find an official manifest with the SSMS versions that I could query, so the script is using the GitHub page where Microsoft publishes the SSMS release notes.

If SSMS is already installed, the script will compare the existing version with the latest one available and it will notify the user.

You can also reference this script directly from my GitHub account.

Feel free to grab it and share it you you find it useful.

DTExec: The package execution returned DTSER_FAILURE (1)

What to do when a SQL backup job created with SQL server Maintenance Plans fails with this error?

Investigate! No useful information in the logs.
Previously that day I dropped a few unneeded databases so maybe the Maintenance Plan was still referencing them. Everything looked good when the Maintenance Plan was opened in SSMS (it was configured to backup “SELECTED” databases), I could not find any reference to the deleted databases. Under the hood, the Maintenance Plan was still trying to backup those deleted databases because when I clicked OK and then hit Save, that Maintenance Plan was refreshed and everything worked just fine.

So always try to find the root cause.