/**
* Authorized
*/
EXEC sp_configure ' show advanced options ', 1;
Go
Reconfigure;
Go
exec sp_configure ' xp_cmdshell ', 1;
Go
Reconfigure;
Go
/** Import a text file for a 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 I wrote myself, which I can use directly to
The first step is to authorize the first. The authorized SQL code above
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 after the export file is deposited.
As
DECLARE @exec_sql varchar (1000);
DECLARE @file_name varchar (200); /* file name, time format, mainly used to record when the data is exported for backup
DECLARE @table_name varchar (100); /* Name of the table to export data to
DECLARE @sql varchar (1000); /* SQL statement to execute business data query * *
/* The 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 ' name like ' wm_picking% ' or ' 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 ' 111111 ';
Set @file_name = @table_name + ' _ ' +convert (varchar), GETDATE (), 112) + '. 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.1SQLEXPRESS '-u ' dhl_posm_ws '-P ' dhlposm ';
Print @exec_sql;
--Export data to local file
EXEC master.. xp_cmdshell @exec_sql;
FETCH NEXT from cur_tables into @table_name;
End
Close Cur_tables; --Close cursor
DEALLOCATE cur_tables;--Release cursor
End Try
Begin Catch
Close Cur_tables; --Close cursor
DEALLOCATE cur_tables;--Release cursor
End catch;
Go
--Execute a stored procedure and test
exec sp_export_posm_data ';
Note:
1. The syntax of the query statement select * FROM [database name]. [table name];
If a 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 there is a sqlstate = S0002 in the running process, nativeerror=208 This error, it means that your-s service name has been incorrectly written,
Usually write wrong because there is no database service instance, this can refer to your database connection, according to the database connection write.
The following figure is my local database connection, so I write-S, can be written in two ways:-S ' 127.0.0.1SQLEXPRESS ' or-s ' ped-vicky-251sqlexpress '
3, the export file Chinese garbled, solve the method
BCP ' SQL statement ' queryout-c-S ' IP database service instance '-U ' database login username '-P ' database login password ' changed to
BCP ' SQL statement ' queryout-w-S ' IP database service instance '-U ' database login username '-P ' database login password '
That is, C-W
4, after the export of file storage directory, must be a SQL Server database installed directory, or you will be wrong