* ** 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.