Data is being lost from SQL Server to a text file

The first thing you need to do is make sure 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 reconfigure exec master.dbo.sp_configure ' xp_ Cmdshell ', 1 reconfigure

2. You can use the Surface Area Configuration tool.

Select the surface area Configuration in the feature and check the Enable xp_cmdshell inspection bar.

Figure I

Now that you have made xp_cmdshell available, you can prepare to export your files.

The following is an example command that prints the results of the System file table to a comma-separated file bcptest.txt. Just copy and paste it into a query window and execute the query.

EXEC xp_cmdshell ' bcp ' select * from Sysfiles ' queryout ' c:bcptest.txt '-t-c-T, '

Note: bcp is a command-line utility that xp_cmdshell requirements can only be used within one SQL batch.

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

Figure II

The parameters used are:

The l queryout option allows you to specify a query to output. This can be as simple as the query we use or as complex as you need it to be. You can also create a view and select data from the view.

L file name is the result that is stored after the queryout option.

The L-T parameter specifies that the bcp utility connects to SQL Server through integrated security using a trusted connection. If you want to log in using a SQL Server, you can use the-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. Characters after-T are used to separate data fields. If-T is deleted, the tab will be used for the default delimiter.

Another parameter you may need to use is-s, which you can use to specify the server name. If you have a specified instance, then you need to use this parameter. The following is an example of connecting to the server "develop" and the instance "DEV1".

EXEC xp_cmdshell ' bcp ' select name, Type_desc, create_date from sys.objects "Queryout" C:bcptest2.txt -T, ' EXEC xp_cmdshell ' bcp ' select name, Type_desc, create_date from sys.objects queryout ' C:bcptest2.txt '-t-sdevelopde V1-c-T, '

You must also know that instances of parameters-T and-T are not the same, so make sure you use the correct arguments in the instance.