Find and Resolve the Issue of Trailing EOL in SQL Server Management Studio 20 Copying Results Grid
If you have recently upgraded to Microsoft SQL Server Management Studio (SSMS) version 20.0 and noticed that selecting lines with adjacent cells in the results grid and copying data pastes some weird characters, you are not alone. Many users have reported this issue, which is caused by trailing End-Of-Line (EOL) characters that get copied along with the data.
Understanding the Issue
When you execute a SELECT statement in SSMS, the results are displayed in a grid. If you want to copy the results to another application, such as Excel or Notepad, you can select the cells and press Ctrl+C or right-click and choose Copy. However, in SSMS version 20.0, you may notice that some extra characters get copied along with the data, which can cause issues when importing the data into another application. These characters are trailing EOL characters, which are invisible but can be seen if you paste the data into a text editor that shows EOL characters, such as Notepad++.
Resolving the Issue
To resolve the issue, you can use one of the following methods:
Use the Results to Text option instead of Results to Grid. To do this, go to the Query menu, choose Results To, and then select Results to Text. This will display the results in a text format, and when you copy the data, only the data will be copied, without any trailing EOL characters.
Use the SQL Server 2016 Tools option. If you have SQL Server 2016 installed, you can use the older version of SSMS tools to avoid the issue. To do this, go to the Tools menu, choose Options, and then go to Query Execution > SQL Server. Under General Options, uncheck the Use Microsoft SQL Server 2016 Tools option. This will revert to the older version of SSMS tools and resolve the issue.
Use a REGEX expression to remove the trailing EOL characters. If you prefer to use the Results to Grid option, you can use a REGEX expression to remove the trailing EOL characters. To do this, go to the Tools menu, choose Options, and then go to Query Results > SQL Server > Results to Grid. Under Grid Options, check the Include column headers when copying or saving the results option. Then, under Additional grid options, enter the following REGEX expression:
^(.*\r? )?([\s\S]*?)$\s*$This expression will remove any trailing EOL characters from the copied data.
The issue of trailing EOL characters in SSMS version 20.0 can be frustrating, but there are several ways to resolve it. By using the Results to Text option, the SQL Server 2016 Tools option, or a REGEX expression, you can copy data from the results grid without any trailing EOL characters.