/**
* Authorization
*/
EXEC sp_configure ' show advanced options ', 1;
Go
Reconfigure
Go
exec sp_configure ' xp_cmdshell ', 1;
Go
Reconfigure
Go
/** Import a text file for the specified table */
EXEC Master. xp_cmdshell ' bcp dbname. TableName in D:\DT.txt-c-sservername-usa-ppassword '
EXEC master. xp_cmdshell ' bcp ' select * from dbname. TableName "Queryout" D:\20140528.xls "-c-sservername-uuser-ppassword"
xp_cmdshell Parameter Description
Here is a stored procedure that I wrote myself, which can be taken directly to use
The first step is to authorize it first. There's an authorized SQL code on it.
if exists (select * from sysobjects where type= ' P ' and name= ' sp_export_posm_data ') begin
drop procedure Sp_export_posm_data;
End
Go
CREATE PROCEDURE Sp_export_posm_data
@file_path varchar (200)/* The path of file storage after export */
As
DECLARE @exec_sql varchar (1000);
DECLARE @file_name varchar (200); /* file name, time format, mainly used to record when the data is exported/*/
DECLARE @table_name varchar (100); /* The name of the table to export data for */
DECLARE @sql varchar (1000); /* SQL statement to execute Business data query */
/* Business table name to back up the data */
DECLARE cur_tables cursor FOR
Select name from sysobjects where 1=1 and type= ' u '
and name like ' wm_order% ' or the name like ' wm_picking% ' or the name like ' rp_% '
Begin try
Open cur_tables;
FETCH NEXT from Cur_tables to @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 "[Email Protected]_path begin
Set @file_path = ' D:\Program Files (x86) \microsoft SQL Server\ ';
End
print ' 111111 ';
Set @file_name = @table_name + ' _ ' +convert (varchar (+), GETDATE (), ()) + '. xls ';
Set @file_path = @file_path + @file_name; /* File path */
print ' 2222222 ';
Set @exec_sql = @exec_sql + ' "' + @file_path + '";
Set @exec_sql = @exec_sql + '-c-s ' 127.0.0.1\sqlexpress "-u" dhl_posm_ws "-P" dhlposm "';
Print @exec_sql;
--Export data to a local file
EXEC master. xp_cmdshell @exec_sql;
FETCH NEXT from Cur_tables to @table_name;
End
Close Cur_tables; --Close cursor
deallocate cur_tables;--Release Cursors
End Try
Begin Catch
Close Cur_tables; --Close cursor
deallocate cur_tables;--Release Cursors
End catch;
Go
--Execute the stored procedure for testing
exec sp_export_posm_data ';
Precautions
:
1. Syntax for query statement SELECT * FROM [database name]. [table name];
If SQLState = S1000 is present during the run, nativeerror=0 this error, which means that your database name or table name is incorrectly written
2. bcp ' SQL statement ' queryout-c-S ' ip\ database service instance '-U ' database login username '-P ' database login password '
If SQLState = S0002 is present during the run, nativeerror=208 this error means that your-s service name is incorrectly written,
Generally write wrong is because there is no database service instance, this can refer to your database connection, according to the database connection to write.
is my local database connection, so I write-S, can be written in two ways:-S ' 127.0.0.1\sqlexpress ' or-s ' ped-vicky-251\sqlexpress '
3, the export file Chinese garbled, the solution
BCP ' SQL statement ' queryout- C-S ' ip\ database service instance '-U ' database login user name '-P ' database login password ' changed to
BCP ' SQL statement ' queryout- W-S ' ip\ database service instance '-U ' database login user name '-P ' database login password '
That is,-C is changed to-W.
4, the exported file directory, must be the SQL Server database installed directory, or error
