SQL Server database bcp export backup file application

* ** Authorize * EXECsp_configureshowadvancedoptions, 1; goreconfigure; goexecsp_configurexp_cmdshell, 1; goreconfigure; go ** to import the text file * EXECmaster of the specified table .. xp_mongoshellbcpdbname .. tablenameind: DT.txt-c-Sservername-Usa

/*** Authorize */EXEC sp_configure 'show advanced options', 1; go reconfigure; go exec sp_configure 'xp _ ALLOW shell', 1; go reconfigure; go/** import the text file of the specified table */EXEC master .. xp_mongoshell 'bcp dbname .. tablename in d: DT.txt-c-Sservername-Usa

/**

* Authorization

*/

EXEC sp_configure 'show advanced options', 1;

Go

Reconfigure;

Go

Exec sp_configure 'xp _ Your shell', 1;

Go

Reconfigure;

Go

/** Import the text file of the specified table */

EXEC master .. xp_mongoshell 'bcp dbname .. tablename in d: DT.txt-c-Sservername-Usa-ppassword'

Exec master .. xp_mongoshell 'bcp "select * from dbname .. tablename" queryout "D: 20140528.xls"-c-Sservername-Uuser-ppassword'

Xp_cmdshell parameter description

The following is a stored procedure written by myself. You can use it directly.

Step 1: authorize first. There is an authorized SQL code

If exists (select * from sysobjects where type = 'p' and) begin

Drop procedure sp_export_posm_data;

End;

Go

Create procedure sp_export_posm_data

@ File_path varchar (200)/* path of the exported file */

As

Declare @ exec_ SQL varchar (1000 );

Declare @ file_name varchar (200);/* file name, time format, mainly used to record when data is exported */

Declare @ table_name varchar (100);/* Name of the table to export data */

Declare @ SQL varchar (1000);/* SQL statement used to query business data */

/* Name of the business table for data backup */

Declare cur_tables cursor

Select name from sysobjects where 1 = 1 and type = 'U'

And name like 'wm _ ORDER % 'or name like 'wm _ PICKING %' or name like 'rp _ %'

Begin try

Open cur_tables;

Fetch next from cur_tables into @ table_name;

While @ FETCH_STATUS = 0 begin

Set @ file_name = '';

Set @ file_path = '';

Set @ SQL = 'select * from DHL_POSM_WS... '+ @ table_name;

Set @ SQL + = 'where 1 = 1 and DATEDIFF (MONTH, MODIFY_TIME, GETDATE ()> 10 ';

Print @ SQL;

Set @ exec_ SQL = 'bcp "'+ @ SQL +'" queryout ';

If ''= @ file_path begin

Set @ file_path = 'd: Program Files (x86) Microsoft SQL Server ';

End;

Print '20140901 ';

Set @ file_name = @ table_name + '_' + CONVERT (varchar (100), GETDATE (), 1_1_1_'.xls ';

Set @ file_path = @ file_path + @ file_name;/* file path */

Print '20140901 ';

Set @ exec_ SQL = @ exec_ SQL + '"' + @ file_path + '"';

Set @ exec_ SQL = @ exec_ SQL + '-c-S "127.0.0.1SQLEXPRESS"-U "DHL_POSM_WS"-P "DHLposm "';

Print @ exec_ SQL;

-- Export data to a local file

Exec master.. xp_mongoshell @ exec_ SQL;

Fetch next from cur_tables into @ table_name;

End;

Close cur_tables; -- close the cursor

Deallocate cur_tables; -- release the cursor

End try

Begin catch

Close cur_tables; -- close the cursor

Deallocate cur_tables; -- release the cursor

End catch;

Go

-- Execute the stored procedure for testing

Exec sp_export_posm_data '';

Note:

1. query statement syntax select * from [database name]... [Table name];

If the SQLState = S1000 and NativeError = 0 error occurs during running, it indicates that your database name or table name is incorrect.

2. bcp 'SQL statement 'queryyout-c-S' IP database service instance'-U' database logon Username '-p' database logon password'

If the SQLState = S0002 and NativeError = 208 error occurs during the running process, it indicates that your-S service name is incorrect,

Generally, the error is caused by the absence of database service instances. You can refer to the connection of your database and write it according to the database connection.

It's my local database connection, so I can write-S in two ways:-S '2017. 0.0.1SQLEXPRESS 'or-S' PED-VICKY-251SQLEXPRESS'

3. Chinese garbled characters of exported files. Solution

Bcp 'SQL statement 'queryyout-c-S' IP database service instance'-U' database logon Username '-p' database logon password' is changed

Bcp 'SQL statement 'queryyout-w-S' IP database service instance'-U' database logon Username '-p' database logon password'

Change-c to-w.

4. the exported file storage directory must be the directory installed in the SQL Server database. Otherwise, an error will occur.