Query Results to Text Programatically

[grnzbra]

If I run a SELECT statement in T-SQL, I can choose to display the result in a grid, as text, or send it to a file by selecting the appropriate button on the SQL Editor tool bar. Is there any way to do this within the SQL statement itself?

 

[Olaf Doschke]

No, this isn't T-SQL specific, but SSMS specific. SSMS is not SQL Server, it is a client software connecting to (one or more) SQL Server instances. Using other client software or programming languages what you get in return pretty much depends on both driver used and client side handling of the drivers return "value", in PHP you may get an array, in C# a datatable or xml, etc. etc.

What happens on the lowest level is driven by the connector used (ODBC driver/OLEDB Provider, whatever). How that result comes over and is displayed is not in the hands of the SQL Server nor it's job, it only delivers to the ODBC/OLEDB layer and has no further action on the caller side.

It's a bit like asking whether you can choose the type of paper a printer will print on through the software and driver.

Bye, Olaf.

 

[grnzbra]

Thanks for the info. I'll just have to make a note in all caps after a PRINT statement telling the user to select the Results to Text button before running the script.

 

[Olaf Doschke]

SSMS is not for endusers, it's a developer tool. You might make that a provisorial interface, but should rather provide some type of query tool for the needs of users, if you want to have the full control about what type of output to get.

PRINT and some other T-SQL commands indeeed produce output messages not part of result sets.

See

https://msdn.microsoft.com/en-us/library/ms130812.aspx

Several other Transact-SQL statements return their data in messages rather than result sets. When the SQL Server Native Client ODBC driver receives these messages, it returns SQL_SUCCESS_WITH_INFO to let the application know that informational messages are available. The application can then call SQLGetDiagRec to retrieve these messages. The Transact-SQL statements that work this way are:

DBCC

SET SHOWPLAN (available with earlier versions of SQL Server)

SET STATISTICS

PRINT

RAISERROR

So in case of using the Native Client driver this tells you where to get at such PRINT messages not needing SSMS.

I never cared to develop clients accessing these output "channels" T-SQL also has, besides T-SQL RAISERROR coming back client side as error info. Scripts using these commands are therefore rather very SSMS specific and less reusable for any other client than SSMS.

Bye, Olaf.