SQL Server database bcp export backup file application

/**

* 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