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
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?
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.
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?
But are there some code in sql server to save in a file the results?
Ö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?
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?
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
You dont need to change the job name
Create a job that has this code
and schedule it to run twice
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.
It is not possible to set it via code
Use keyboard shortcut CONTROL+D
This is really a good one
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
Ctrl+T for text results
Ctrl+D for Grid Results
CTRL+R to show/hide result pane
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!
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
i am unable to set default format for the Result pane in Grid Format
Hello Shahid,
Which method are you using to change the output format and what error or issue you are facing?
Regards,
Pinal Dave
Can I export results to a CSV file using T-SQL or do I have to write an application to do this?
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
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
You can use bcp to avoid this
or
you can use set nocount on at the start of the query
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.
I found this article that uses the xp_cmdshell and bcp to create an output file:
Would you recommend SQLCMD over this procedure?