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

Method 3 : Using Menubar

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

45 thoughts on “SQL SERVER – 2005 – SSMS – View/Send Query Results to Text/Grid/Files

  1. I would love to have a fourth option to do this. I would like to be able to run a command in a Query. Most of the times I am using the Grid for my results, but some of my scripts I allways wont to have the results to Text and some other scripts a written in a way the Results to File would be the best option. now I have to switch between these options manually in one of the three ways that you presents here.

    Would it be great to be able to du it with a command in the script? Is there any command like this?

  2. I wanted to know how I can hide the field and other stuff in sql query. I am running a job which send the alert via sms to my cell phone now the message is only appearing with —————————- line as I already hide the field but field is coming with ————————– No data is coming in mobile. whereas I found that for the result set it is about 2-3 rows.

  3. I want to know how I can use the result of a union query. In access.mdb I am able to select a union query to create another query. But when I upsize to adp I am not able to do this. Is there a way?

  4. Örjan, I’m not aware of a way to do this via a command in your script. I’d love/wish you could. Maybe someone else can shed some light on this?

  5. I am using SQL Server 2005. But my version of Management Studio does not have the “Query” menu item to choose from. I do have “Query Designer”, but there is no “Results To” option below it.

    Where can I look?

  6. Hi All,

    I want to do my backup procedure automate by creating sql job and want to store in a Folder.
    This process I have to do daily twice but my problem is its executing successfully for first time but for second time
    its saying that the given jobname aleady exists.
    SO how i can dynamically change backup file name and job name.
    or else is there any other way for taking backup in recurring process by executing single job.
    plz help ASAP
    thanks in advance

    Regards
    Satya

  7. Hai,

    Can i get the result of a query in grid format defaultly,

    i need to include that coding in the query.

    that is, even though the result default is in text format, should displayin grid format.

  8. i had written a procedure to display the schema of all the procedures in the database.
    but when i select results to text i am having a text feild appened in the result set

    is it possible to not include text feild in the results

  9. Good day!.. i need this automatic.. i need to export the results of a query.. may i say, like… executing this query select * from id_names, and export all the data with the columns, in a txt so i can upload this to mysql server… i’m using SQL SERVER 2005 and reading the data from a excel file.. Greetings!

  10. Hello David,

    Create a SSIS package to export the query result in text file and then schedule the SSIS to automate the process.
    Please let me know if you need more details.

    Kind Regards,
    Pinal Dave

    • Dear Pinal Dave,

      How to create a SSIS Package to export the query in text file for the below scenario ie., Query result with Result message

      status_flag|Date|DrTotal|CrTotal|TxnNos|StartTxnID|EndTxnID
      |11-Dec-2012|1236.00|1236.00|4|832766|832794

      (9 row(s) affected)

      Please help me out…

      Regards,
      Prabhakaran

  11. Hello Simon,

    Use the sqlcmd utility to export a t-sql result into a csv file for example:
    SQLCMD -S MyServer -E -d Billing -i query.sql -o output_file.csv -s ,

    Regards,
    Pinal Dave

  12. hello simon

    we are using sql server 2008 standard edition, using query analyser we are exporting the result to text file, here we have one little problem is how to avoid the result message in text file

  13. In Oracle SQL you have a SPOOL command that makes it easy to extract data to a file. I really wish MS would introduce its counterpart in T-SQL. Most of the time you can cut&paste from grid or text, but when you extract from several tables to several files in one operation, it’s not as easy. Yes, you can use ISQL or SQLCMD I guess, but those tools are sometimes not available to the end user, and you might need the dynamics of an sql command. In example I’m working on an sql server where I have privileges on the database, but not on the server (there are many databases on the same server). Unfortunatly, SQLCMD is not available for my logon.

    I’m extracting data from Agresso (ERP system) to another instance of the same system but in a different location. The thing is, I’m only supposed to extract data belonging to a specific client and not the entire database nor the entire table(s) where the data is located. The transaction tables in the Agresso database all have a column named [client]. In that way, you can have several companies using the same installation and all the queries from the application always include a where clause saying client = ‘xx’ in order to keep data from each client apart. There are ca 960 tables with the [client] column, but I don’t know in advance which one contains data from the client I’m extracting. To resolve this I’m using scripts to first find the tables with the [client] column, then exclude the tables with no data for my client, and then again extracting data to separate files for each table containing data. With the SPOOL command, you can create a dynamic script that picks up the table name for each table, assigning it to the file name in the SPOOL command. That way you’ll have, lets say 150 files, all named from the tables they’ve extracted from. I can’t seem to find any similar way of extracting data from SQL-server. You have BULK INSERT where you can read data with dynamic file name, but as it seems no “BULK OUTPUT” or similar.

  14. 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…

  15. 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!

  16. 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.

  17. 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.

  18. 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

  19. 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?

  20. hi all,

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

  21. 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

  22. 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.

  23. 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 ?

  24. 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.

  25. 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

  26. 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.

    http://msdn.microsoft.com/en-us/library/ms162802.aspx

    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.

  27. Pingback: SQL SERVER – Weekly Series – Memory Lane – #038 | Journey to SQL Authority with Pinal Dave

  28. 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 .

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s