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', 1RECONFIGUREEXEC 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_mongoshell 'bcp "select name, type_desc, create_date from sys. objects "queryout" C: bcptest2.txt "-T-SDEVELOPDEV1-c-t, 'exec xp_mongoshell 'bcp" select name, type_desc, create_date from sys. objects "queryout" C: bcptest2.txt "-T-SDEVELOPDEV1-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.