SQL Server database bcp export backup file application Example _mssql

/**
* Authorized
/EXEC sp_configure ' show advanced options ', 1;
Go
reconfigure;
Go
exec sp_configure ' xp_cmdshell ', 1;
Go
reconfigure;
Go

/** Import the 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 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_dat
A
End
Go CREATE PROCEDURE sp_export_posm_data @file_path varchar (200)/* exported file stored path/AS declare @exec_sql varchar (1000); DECLARE @file_name varchar (200); /* file name, time format, is mainly used to record when the data is exported back to the Declare @table_name varchar (100); /* Table name to export data/declare @sql varchar (1000); /* SQL statement to execute Business data query//////////* Business table name to back up data/* Declare cur_tables cursor FOR select name from sysobjects where 1=1 and type= ' U ' and nam
E 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\ ';
Endprint ' 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.1\sqlexpress '-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; --Closes the cursor deallocate cur_tables;--releases the cursor end try-begin catch Close cur_tables;
--Close cursor deallocate cur_tables;--release cursor end catch; Go-Execute stored procedure, test exec sp_export_posm_data ';

Precautions:

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.1\sqlexpress ' or-s ' ped-vicky-251\sqlexpress '

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