SQL Server: easy (and free) ways showing, entering and edit multi line data directly?

Question

What are easy ways to show, enter and edit multi-line data in SQL Server? Not using code (INSERT, Update), but directly using a frontend?

"Standard" mssql developer frontends like SSMS, Azure Data Studio and SSDT in VS can be easy used to edit one-line-content. But they are not so good with multi-line content.

• ADS lost line breaks when copy and paste in edit mode.
• In SSMS I can prepare multi-line content in any text editor (or in a new window), I can past it and line breaks are preserved. This is the good message.
  But it is hard to see line breaks in queries. I can query the output as text, or I need to copy the content from the raster. But there is a very strange effect: If I use the "select top 1000 ..." from the context menu to generate the select query then the raster contains line breaks. But if I execute the same query directly, then the line breaks are not in the raster. This is very hard to explain to a user.

There should be a user-friendly frontend which shows the content with line breaks and which allows entering and edit this content.

That's why in some projects I use Access to connect to SQL Server databases via ODBC. In Access, I can change the height of cells and I can enter and edit multi-line data. And line breaks are shown.
But it is not possible to use Access in each project. Most time it is also not included in the default office installation.

It would be great if I could use ADS (Azure Data Studio) or VSC, maybe with some extensions. Because ADS can be installed without any admin rights in any project on any PC for any user. In most projects it is hard to install something which requires admin rights.

A Power BI solution would also be fine, because Power BI is often used in many projects for reporting, and it would be fine to use it also for data input.

A good solution would be a tool I can use to quickly create some simple web frontend. Because the user would need only a browser. Some open source solutions I found are working with mysql but not with SQL Server. Or they require programming in Java. I am good in T SQL, DWH and so on, but I do not program in other languages. Sometimes same small scripts in python only. My last "full programs" I wrote more than 20 years ago in Turbo Pascal or in CAVO (Visual Objects). That's why I am looking for a web frontend generator without much coding or maybe something related to Python which I would be willing to learn and use.

I have seen a web frontend generator at https://dbfront.com/, but it is free only for 7 tables. And I would prefer a solution I could use also for free in some open source projects also with more than 7 tables.

I also tried some alternative GUI to connect to SQL Server. With beekeeper studio I was not able to connect to SQL Server with integrated security, HeidiSQL can edit multi-line content, but I think to use it only to enter some data would be too complicated for a normal user.

I also could imagine a solution to use Excel to write back data. There is a commercial product https://sqlspreads.com/, but I can't use it for free in open source projects.

Copy and paste between SSMS (in edit mode) and Excel works fine with one-line data, but when I copy from SSMS to Excel then each line will be separate cell in Excel. Maybe this could be used as a workaround, but it is not yet what I am looking for.

Answer 1

SSMS, Azure Data Studio and similar tools are not intended for end users as an application, but they are intended for developers and DBAs. And for that matter advanced users who want to run their own queries. But they cannot replace a real application.

There are a whole slew of environments out there to develop UIs, and it is my understanding that there such frameworks based on Python. But, yes, they require you acquire the skills to use them. I am not going to give any specific names, since my own expertise is in T-SQL, and I only master client-side languages so that I can write simple command-line applications.

When it comes to copy and paste from SSMS, there is a setting to permit you to choose whether line breaks should be retained on copy/paste from the grid. You find it under Tools->Options->Query Results->Results to Grid. If you don't see it, you have an old version of SSMS; the latest is found here: https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

Answer 2

@Erland Sommarskog

Thank you for this hint:

When it comes to copy and paste from SSMS, there is a setting to permit you to choose whether line breaks should be retained on copy/paste from the grid.

This explains a bit, why I did not get line breaks in "normal" queries. But I was always confused, because I get the line breaks in the copy content when the query is automatically generated in the context menu using the "Select Top 1000". So it is not consistent (I would say, this is a bug). Now I used this setting and my results should be more consistent. This is fine to copy generated content from multi-line cells in a new window (for example when a view prepares sql statements)

But it is not the full solution. Because the line breaks are visible only after copy and paste, and not directly in the grid.

SSMS, Azure Data Studio and similar tools are not intended for end users as an application

Maybe, but my experience is, that these are favorite tools for many users: They use these tools for queries, and it is a good place to enter data using the same tools. And these are also good tools for developers like me. The main issue with these tools is, that they are not very good at showing multi line content.

Answer 3

But it is not the full solution. Because the line breaks are visible only after copy and paste, and not directly in the grid.

I guess that is a deliberate design not to mess up the grid. I have queries to show me what is going on in the server, including the current statement. Which easily could be more than 50 lines for a complex query. It would be a nightmare if the grid row exploded to show all that data in the cell. So I'm quite content with the current behaviour.

You could try running queries from Excel. That's certainly more end-user oriented.