Tag Archives: SSMS

Articles on SQL Server SSMS

#0407 – SQL Server – Clearing out the list of servers in SSMS

Today, I will talk about a very common question that I see in the forums. When you work with a lot of SQL Server instances, the list of servers seen on the login screen in the SQL Server Management Studio (SSMS) becomes quite long, raising the question:

How to clear out the list of SQL Server instance names in SSMS?

SQL Server 2014 and above

Clearing out servers that no longer exist or to which you no longer need to connect to is quite simple in SQL Server 2014 and above. All you need to do is:

Open SSMS
In the login window, expand the list of available SQL Server instances
Use the keyboard’s down arrow or use the mouse to scroll down to the instance that needs to be deleted
Once the required instance is selected in the list, just press “Delete” on the keyboard

Just select the appropriate SQL Server instance and press “Delete” to remove it from the SSMS login history

If you are still using an older version of SSMS due to various reasons, there is a manual workaround to this as shown below.

SSMS for SQL Server 2012 and below

Close all open instances of SSMS on your workstation
Depending upon your version of the SSMS client tools, navigate to and delete the files as shown in the table below:
Launch SSMS
It might take a little while to launch, as it recreates the “SqlStudio.bin”/”mru.dat”
- Once launched, you will see that the entire SSMS history is gone

SSMS Client Tools Version	Path	File to Delete
SQL 2012	%USERPROFILE%AppDataRoamingMicrosoftSQL Server Management Studio11.0	SqlStudio.bin
SQL 2008	%USERPROFILE%AppDataRoamingMicrosoftMicrosoft SQL Server100ToolsShell	SqlStudio.bin
SQL 2005	%USERPROFILE%AppDataRoamingMicrosoftMicrosoft SQL Server90ToolsShell	mru.dat

Until we meet next time,

Be courteous. Drive responsibly.

#0382 – SQL Server – SSMS – Debugging a T-SQL DML Trigger

Leave a reply

I often receive questions related to debugging code using SSMS. Based on these questions, I have written a lot of blog posts in the past, however I was recently asked the question:

How can I debug a DML trigger in SSMS?

I realized that while I had encountered the same question and researched it in my initial days (when I worked on SQL Server 2000), I had never written about it. So, here goes.

In order to demonstrate how to debug a trigger, all we need to do is debug the statement that initiates the trigger.

For this demonstration, I will fire an update on the [Sales].[SalesOrderHeader] table of the AdventureWorks database.

USE AdventureWorks2012;
GO
UPDATE soh
SET soh.SubTotal += 2
FROM Sales.SalesOrderHeader AS soh
WHERE soh.SalesOrderNumber = 'SO43697';

When we debug this query (using F10), the SSMS debugger launches and we can step through the code using the same key combinations as we have in Visual Studio.

Demonstrating how to debug DML triggers using SSMS

Debugging T-SQL script using SSMS

Executing the update should fire the DML trigger [uSalesOrderHeader] which can be debugged like any other T-SQL code by stepping into the trigger (press F11 when executing the update).

Stepping into a DML trigger during a SSMS T-SQL Debugging session

That’s it. Debugging a trigger is no different than debugging a stored procedure or any other T-SQL script.

In case you would like to learn more about debugging in SSMS, please do refer my previous posts (links below). If there is anything specific that you would like to know more about, do drop in a line.

#0366 – SQL Server – SSMS – Simulating a TCP/IP connection on the same machine as the server

Leave a reply

Most development environments that I have worked in have a developer edition of Microsoft SQL Server on the local machine of the developer. Hence, both the client (SSMS) and the database server are often on the same machine.

What this means is that the default connection mechanism used for all connections will be “Shared Memory”. However, when working on features like auditing, the ability to simulate a TCP/IP connection becomes important. Today, I document a mechanism that you can use to initiate a TCP/IP connection even when both server and client (SSMS) are the on the same machine, provided the server has been configured to allow TCP/IP connections in the Configuration Manager.

SQLTwins, Post #0366 - Ensuring that TCP/IP connections to the server are enabled via the SQL Server Configuration Manager

SQL Server Configuration Manager – ensuring that TCP/IP connections to the server are enabled

When initiating a new connection using the SQL Server Management Studio, simply click on the “Options” button in the “Connect to Database Engine” window, and navigate to the “Connection properties” tab.

SQL Server Management Studio (SSMS) – The “Connect to Database Engine” window

SQL Server Management Studio (SSMS) – Using the Connection Properties tab to change the Network protocol

In the “Network” group, locate the “Network Protocol” drop-down. Change the value from “<default>” to “TCP/IP“.

Finally, verify the credentials in the “Login” tab and click “Connect“. That’s it!

Use the CONNECTIONPROPERTY() function described in my previous post to confirm that you are indeed connected via TCP/IP.

#0349 – SQL Server – SSMS Usability – Unable to Show XML, Unexpected End of File when parsing

The solution

The solution is actually indicated in the error message itself – increase the number of characters being retrieved from the server for XML data. To do this:

Go to Tools –> Options
Expand the “Query Results” node
Expand SQL Server –> Results to Grid options
Under “Maximum Characters Retrieved”, select the appropriate value for “XML Data”
Click “OK” and close out of the Options window
Attempt to open the XML again