Many times I have been asked how to change the result window from Text to Grid and vice versa. There are three different ways to do it.
Method 1 : Key-Board Short Cut
Results to Text – CTRL + T
Results to Grid – CTRL + D
Results to File – CTRL + SHIFT + F
Method 2 : Using Toolbar
Method 3 : Using Menubar
Reference : Pinal Dave (https://blog.sqlauthority.com)
Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future.
Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.
46 Comments. Leave new
hello sir i am chirag
sir my problem is tht i am searching tht how to send sms through sql..but the problem is tht no 1 is ready to help neither any1 knows about it..i had a search on net there was 1 way & tht was something ozeki..but i dont think so it wroks bcoz there r no quey given for tht …sir its my request to u tht plz..help me out in my problem ..i want to knw to the syntax…or query..or any procedure u knw ..plz..tell me ..sir how to send sms using sql…
Somehow I have reset default to send results to text, however, 95% of the time I prefer results to grid. Having trouble changing it back and very frustrated. Please help!
Press Control+D to make it as Grid view in Query Analyser
Madhivanan,
Thanks for the quick response, not quite the answer I was looking for. I know how to change it on an individual basis. It seems I have reset the default to automatically display to text; How do I set it so that it defaults to Grid?
Thanks,
Goto Tools–>Options–>Query results. In that change default destination for results to “Results to grid”
Thanks a lot, that saved me a fair bit of time. How long have you spent ninja-ing SQL Server? You so are an online legend at it!
Thanks again, Den.
Need to export result set from a query to xml format file. Similarly like text file. I tried with changing xetension in end of the file name, but not working,
Can u help me out.
Hi Pinal:
I could do it manually via many steps to save the results to .csv file by changing the query option about the result, output format (comma delimited).
sqlcmd is another option via a job to run daily, but how to dbMail the file to a user as part of the job.
How could I dbMail out a query result with .CSV format?
Thanks for your help!
David
When I run results with multiple grids, such as using sp_MSForEachDB or sp_MSForEachTable, the suggestion of “Results to Text” ONLY do whatever one of the hundreds of grids in the result set happen to be selected. How do you select ALL (Ctrl-A doesn’t work) of what is in the lower pane?
hi all,
please give any idea to send sms from sqlserver automatically based on time in the table values before 3 hours…..
Hi,
Is there any option to display the grid column results in fixed length? Because my table has lot of columns and it will take lot of time to copy to excel and format or align the columns to read the data together.
Thanks in advance
Siva
Hi Pinal, will sending the result to file has more burden on the server? I am running a lot of stuff and sending the result to the file as you mentioned..But does this approach uses more resources than the regular one? Thanks in advance.
Thank you
Thank you it’s very help full for me i resolve my issue
How can i do Text Formatting for the output of my Sql Query by Query Text, i mean in sql query not by given options in Query tab ?
HI there, i am having an issue, i am querying a remote server on SSMS 2012, when i export the data for any type of file CSV, TXT or copy paste to excel there is allways a few rows that are with data on the wrong collumns, this is a big issue because it is preventing me to import the data, anyn sugestion to resolve this?
Daniel: You don’t say much about how your initial query is designed or what your output format\results looks like, but here’s a cople of suggestions:
A common problem when extracting data to CSV, is that the separator may occur within the extracted data itself. In example, if you use comma as the field seporator, you’ll have problems with text strings containing comma. I.e. the text string “Clinton, Ann” will be written to the CSV-file as two separate fields, even thou the data is extracted from one column in the table. This is because the text contains the same character you used as a field separator (comma). As opposed to “Ann Clinton” which will be written as one field because there is no comma in the text. Typpiclal problem issues will be text fields containg user generated text or descriptions. The users are evil :-) They don’t restrain them selves to a certain format …The same goes for number columns with thousand seperators i.e.
A cople of work arounds:
Use a really uncommon character as seperator instead of the more common comma or semi colon.
Use a replace function in the SQL to remove commas from the extracted data.
Or quite simply use a different type of format all together. I.e. write a format where each field is refered to a spesific position in the file. This can be done by padding the fields in the SQL with blanks, to ensure every field has the same length and is written to the same position every time, regardless of the data.
It’s more work designing a position orientated output/input, but you’ll gave less error situations once it is in place.
See below a bit of the SQL code used i am using code that is returned from a reporting tool in SSMS 2012
I have made a lot of changes as you said but nothing seems to work…
SET NOCOUNT ON
DECLARE @COMPLEXTEMPTABLE TABLE(
[complex@Aging:Aging]
[complex@Area:Geography] (40),
INSERT INTO @COMPLEXTEMPTABLE
SELECT DA132.AgeBucketName AS ‘Aging’,DA144.AreaDesc AS ‘Area’,
FROM vw_Acco…..
Join [rpt_Sun on Pay……….
WHERE
GROUP BY
SET NOCOUNT OFF
SELECT
[complex@Aging:Aging] AS ‘Aging’,
[complex@Area:Geography] AS ‘Area’,
GROUP BY [complex@Aging:Aging],
[complex@Area:Geography]
e code as example
First: I suggest you don’t use Excel as data storrage between databases. Excel tends to mess up by adding formats.
Secondly: I’ll sugges you’ll take a look at the bcp utility to see if it fits your purpose. Perhaps in combination with the methods below.
https://docs.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-2017
It’s difficult to fully understand your problem without also having the same dataset (I’ll still guess the problem is in your data), but here is an example to demonstrate different output formats. and handeling unvanted characters in the data (comma, semicolon i.e.) The thing is to exctract to one column instead of many, and then read the data from a text file/data file.
— First create the temp-table #tmp_1
CREATE TABLE #tmp_1
([Names] varchar(25) not null default ”,
[Comment] varchar(50) not null default ”,
[Revenue] decimal(28,2) not null default 0.00
)
GO
— Inserting three rows in the tmp table.
— Note that the columns in the first and third row have
— .. different use of comma and semi colon. and the second row have
— ..no comma.
— PS. The insert statement uses the SQL-server 2008
— Row constructor method.
INSERT INTO #tmp_1
Values (‘Clinton, Ann’, ‘Sold; Books, CD, SQL-guidance’, 1170.96),
(‘Martin King’, ‘Consulting’, 350.00),
(‘David Tudor’, ‘Travel Expence’, -430.10)
GO
— Two examples of extracting data into one, controlled, text string:
— In the first example I remove comma and semikolon from the selected data
— by using REPLACE.
DECLARE @separator varchar(1)
SET @separator = ‘;’ — semikolon is used to separate the fields.
SELECT
replace(replace([Names],’,’,”) ,’;’,”) + @separator +
replace(replace([Comment],’,’,”) ,’;’,”) + @separator +
replace(replace([Revenue],’,’,”) ,’;’,”) — no separator at the end of the rows
AS SemiColonSepartedString
FROM #tmp_1
GO
— In the second example I pad the fields to a fixed length by using REPLICATE.
— It might look faulty in Excel, but you can use notepad or the “text to column”
— function in Excel to separate the fields (but really: don’t use Excel).
SELECT
ltrim([Names]) + replicate(‘ ‘, 25 – len(ltrim([Names])) ) +
ltrim([Comment]) + replicate(‘ ‘, 50 – len(ltrim([Comment])) ) +
CASE when [Revenue]<0.00 then '-' else ' 'end +
REPLICATE('0', 15 – len(replace(replace(convert(char(15),Revenue),'.',''),'-','') ))+
replace(replace(convert(char(15),revenue),'.',''),'-','')
AS PositionOrientatedString
FROM #tmp_1
— The case and replace functions at the end is a way to convert numbers into a text string with leading zero. It may or may not be your prefered method for displaying numbers.
Can you please let me know if we can write a script to copy the result messages(which are displayed in results pane) and write it to a file .
This is very helpful for me. I spent a couple of hours for this. But as I came to this blog. Its done in a second
Can SQL Server display Multiple Result tabs in the Grid instead of replacing the Results tab each time a Query is ran ? Sometimes I am comparing and don’t want to have to copy and paste into Excel before I finish my SQL logic. TOAD will display up to 5 I think different results so you can refer to previous results set. Can SQL Server do this ? If so how do I set it up?