Output data from SQL Server to a text file

The first thing you need to do is to make sure that xp_cmdshell is available. You can choose one of the following two methods to achieve this.

1. You can use sp_configure and execute the following script.

EXEC master.dbo.sp_configure 'show advanced options', 1 RECONFIGUREEXEC master.dbo.sp_configure 'xp_cmdshell', 1RECONFIGURE

2. You can use the Surface Area Configuration tool.

Select the Surface Area Configuration in the feature and check the Enable xp_cmdshell check bar.

Now that you have made xp_cmdshell available, you can prepare to output your file.

The following example shows how to export the result of the system file table to bcptest.txt, a comma-separated file. You only need to copy and paste it into a query window and execute this query.

EXEC xp_cmdshell 'bcp "SELECT * FROM sysfiles" queryout "C:\bcptest.txt" -T -c -t,'

Note: BCP is a command line utility. xp_cmdshell must be used only in one SQL batch.

This output is obtained when the above command is executed in the "master" database.

The following parameters are used:

L The Queryout option allows you to specify a query for output. This can be as simple as the query we use or as complicated as you need. You can also create a view and select data from the view.

L file name stores the results. It is located after the queryout option.

The l-T parameter specifies that the bcp utility uses a trusted connection to connect to SQL Server through integrated security. If you want to log on with an SQL Server, you can use-P (password) and-U (user ).

L-c specifies the character data type that will be used for each field.

The l-t parameter allows you to specify a field delimiter. The characters After-t are used to separate data fields. If-t is deleted, the tab is used as the default delimiter.

The other parameter you may need is-S. You can use it to specify the server name. If you have a specified instance, you need to use this parameter. The following is an example of connecting to the server "DEVELOP" and instance "DEV1.

EXEC xp_cmdshell 'bcp "select name, type_desc, create_date from sys.objects" queryout "C:\bcptest2.txt" -T -SDEVELOP\DEV1 -c -t,'

You must also know that the instance using the-t parameter and-T parameter are different, so make sure that you use the correct parameter in the instance.

1. Video: SQL Server 2008 New features Overview
2. Download video tutorial: security improvement of SQL Server 2008
3. Video: SQL Server 2008 New features Overview