SQL SERVER – 2005 – SSMS – View/Send Query Results to Text/Grid/Files

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

SQL SERVER - 2005 - SSMS - View/Send Query Results to Text/Grid/Files sqlserverresults1

Method 3 : Using Menubar

SQL SERVER - 2005 - SSMS - View/Send Query Results to Text/Grid/Files sqlserverresults2

Reference : Pinal Dave (https://blog.sqlauthority.com)

, , , , ,
Previous Post
SQL SERVER – SPACE Function Example
Next Post
SQL SERVER – Fix : Error Msg 4214 – Error Msg 3013 – BACKUP LOG cannot be performed because there is no current database backup

Related Posts

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…

    Reply
  • 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!

    Reply
    • Press Control+D to make it as Grid view in Query Analyser

      Reply
      • 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.

    Reply
  • 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.

    Reply
  • 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

    Reply
  • 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?

    Reply
  • hi all,

    please give any idea to send sms from sqlserver automatically based on time in the table values before 3 hours…..

    Reply
  • 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

    Reply
  • 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.

    Reply
  • Thank you

    Reply
  • Thank you it’s very help full for me i resolve my issue

    Reply
  • 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 ?

    Reply
  • 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?

    Reply
    • 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.

      Reply
  • 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

    Reply
  • 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.

    Reply
  • 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 .

    Reply
  • 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

    Reply
  • 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?

    Reply

Leave a Reply

Menu