萬盛學電腦網

 萬盛學電腦網 >> 數據庫 >> mssql數據庫 >> SQLServer數據庫bcp導出備份文件應用

SQLServer數據庫bcp導出備份文件應用

   /**

  * 授權

  */

  EXEC sp_configure 'show advanced options',1;

  go

  reconfigure;

  go

  exec sp_configure 'xp_cmdshell',1;

  go

  reconfigure;

  go

  /**導入指定表的文本文件*/

  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參數說明

SQLServer數據庫bcp導出備份文件應用 三聯

  下面是我自己寫的一個存儲過程,可以直接拿去使用

  第一步,先要授權。上面有授權的SQL代碼

  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) /*導出後文件存放的路徑*/

  as

  declare @exec_sql varchar(1000);

  declare @file_name varchar(200); /*文件名稱,時間格式,主要是用於記錄數據是什麼時候導出備份的*/

  declare @table_name varchar(100); /*要導出數據的表名*/

  declare @sql varchar(1000); /*執行業務數據查詢的sql語句*/

  /*要備份數據的業務表名*/

  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 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';

  end;

  print '111111';

  set @file_name = @table_name+'_'+CONVERT(varchar(100), GETDATE(), 112)+'.xls';

  set @file_path = @file_path + @file_name; /*文件路徑*/

  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;

  -- 導出數據到本地文件

  exec master..xp_cmdshell @exec_sql;

  fetch next from cur_tables into @table_name;

  end;

  close cur_tables; -- 關閉游標

  deallocate cur_tables;-- 釋放游標

  end try

  begin catch

  close cur_tables; -- 關閉游標

  deallocate cur_tables;-- 釋放游標

  end catch;

  go

  -- 執行存儲過程,進行測試

  exec sp_export_posm_data '';

  注意事項:

  1、查詢語句的語法 select * from [數據庫名]..[表名];

  如果運行過程中出現了SQLState = S1000, NativeError=0這個錯誤,這表示是你的數據庫名或表名寫錯了

  2、bcp 'sql語句' queryout -c -S'IP數據庫服務實例' -U'數據庫登錄用戶名' -P'數據庫登錄密碼'

  如果運行過程中出現了SQLState = S0002, NativeError=208這個錯誤,則表示是你的 -S服務名寫錯了,

  一般常寫錯是因為 沒有加 數據庫服務實例,這個可以參考你數據庫的連接,照著數據庫連接寫就可以。

  下圖是我本地的數據庫連接,所以我在寫 -S的時候,可以兩種寫法:-S'127.0.0.1SQLEXPRESS' 或者 -S'PED-VICKY-251SQLEXPRESS'

  3、導出文件中文亂碼,解決方法

  bcp 'sql語句' queryout -c -S'IP數據庫服務實例' -U'數據庫登錄用戶名' -P'數據庫登錄密碼' 改成

  bcp 'sql語句' queryout -w -S'IP數據庫服務實例' -U'數據庫登錄用戶名' -P'數據庫登錄密碼'

  即 -c 改成 -w 就行

  4、導出後的文件存放目錄,一定要是SQL Server數據庫安裝的目錄,不然會出錯

copyright © 萬盛學電腦網 all rights reserved