Saving the Result of a query to a Text File in Sql Server

After being a part of the Sql Server MSDN forum for a quite a long time, One question I answered many time is “How to save the output of query to a text file in Sql Server?”
So thought of sharing a simple solution with you using BCP utility.

A simple query to save output of query to a text file is goes like this –

EXEC master..XP_CmdShell 'BCP "SELECT * FROM Database.dbo.TableName" queryout "c:\Mangal.txt" -c -T'

If you execute the above query and get the following error –

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

Then first you need to enable the xp_cmdshell procedure. To enable the xp_cmdshell execute -

sp_configure 'show advanced options',1
GO
RECONFIGURE
GO
sp_configure Xp_CmdShell,1
GO
RECONFIGURE WITH OVERRIDE
GO

Again back to BCP, Here
-c : Performs the operation using a character data type. This option does not prompt for each field; it uses char as the storage type, without prefixes and with \t (tab character) as the field separator and \n (new line character) as the row terminator.

-T : Specifies that the bcp utility connects to SQL Server with a trusted connection using integrated security. The security credentials of the network user, login_id, and password are not required. If –T is not specified, you need to specify –U and –P to successfully log in.

You can save the file to txt, xls, dbf, xml formats also.

There are so many other options available with BCP command.
e.g.
With –U and –P you can provide the username and password when you are not using the Windows Authentication (Trusted Connection).

For more on BCP utility you can read from Microsoft’s Books Online.
BCP Utility Books Online


Idea for this post was just to provide a simple solution on how to save the result of a query to a file?.

Well there is another way of directly sending the result of query to a file is by setting the “Result To Text” in management studio.
For that do –
Open the Management Studio.
In menu bar click to “Query” >> “Result to” and then select “Result To Text.”

Hopefully many of you find this post helpful.

- Mangal