Shulou(Shulou.com)05/31 Report--

How to use bcp to export backup files in SQL Server database? I believe many inexperienced people don't know what to do about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

/ * authorization * / EXEC sp_configure 'show advanced options',1;goreconfigure;goexec sp_configure' xp_cmdshell',1;goreconfigure;go/** imports the text file of 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

The following is a stored procedure I wrote myself, which can be used directly as the first step, which requires authorization first. There is an authorized SQL code on it.

If exists (select * from sysobjects where type='p' and name='sp_export_posm_data') begindrop procedure sp_export_posm_data;end;gocreate procedure sp_export_posm_data @ file_path varchar / * the path to the file after export * / asdeclare @ 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 / * the table name of the data to be exported * / declare @ sql varchar (1000); / * the sql statement to execute the business data query * / / * the business table name of the data to be backed up * / declare cur_tables cursor forselect name from sysobjects where 1, and type='u'and name like 'WM_ORDER%' or name like' WM_PICKING%' or name like 'RP_%'begin tryopen cur_tables;fetch next from cur_tables into @ table_name;while @ @ FETCH_STATUS = 0 beginset @ file_name ='' Set @ file_path =''; set @ sql = 'select * from DHL_POSM_WS..'+@table_name;set @ sql+ =' where 1mm 1 and DATEDIFF (MONTH,MODIFY_TIME,GETDATE ()) > 10potential and DATEDIFF @ sql;set @ exec_sql = 'bcp "' + @ sql+'" queryout'; if'= @ file_path beginset @ file_path ='D:\ Program Files (x86)\ Microsoft SQL Server\'; end;print '1111' Set @ file_name = @ table_name+'_'+CONVERT (varchar (100), GETDATE (), 112) + '.xls'; set @ file_path = @ file_path+ @ file_name; / * File path * / print '222222 transaction 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 the 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 trybegin catchclose cur_tables;-- close cursor deallocate cur_tables;-- release cursor end catch;go-- execute stored procedure, test exec sp_export_posm_data''

Note:

1. The syntax of the query statement select * from [database name].. [table name] If the error SQLState = S1000, NativeError=0 occurs during the operation, it means that your database name or table name is misspelled 2. Bcp 'sql statement' queryout-c-S'IP\ database service instance'- U 'database login user name'-P 'database login password' if the error SQLState = S0002, NativeError=208 occurs during the operation, it means that your-S service name is misspelled Generally speaking, the reason for miswriting is that there is no database service instance added. You can refer to your database connection and write according to the database connection. The following figure shows my local database connection, so when I write-S, I can write it in two ways:-S'PED-VICKY-251 127.0.0.1\ SQLEXPRESS' or-S'PED-VICKY-251\ SQLEXPRESS'.

3. Export documents with garbled codes in Chinese Solution: bcp 'sql statement' queryout-c-S'IP\ database service instance'- U 'database login user name'-P 'database login password' change to bcp 'sql statement' queryout-w-S'IP\ database service instance'- U 'database login user name'-P 'database login password' that is,-c to-w line 4. Exported file storage directory Be sure to be the directory where the SQL Server database is installed, or there will be an error

After reading the above, have you mastered how to use bcp to export backup files in SQL Server database? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.