@echo offrem -- =============================================rem -- DESCRIPTION: <自動導出バッチ>rem -- AUTHOR: <>rem -- CREATE DATE: <2010/12/27>rem -- =============================================
rem -------------------------------------------------------------rem ファイルサーバ__usernamerem -------------------------------------------------------------@SET file_server_username=xxx
rem -------------------------------------------------------------rem ファイルサーバ__passwordrem -------------------------------------------------------------@SET file_server_password=xxx
rem --------------------------------------------------------------rem ファイルサーバ_dir(copy to)rem --------------------------------------------------------------@SET file_server_dir=//192.168.0.218/aaa
rem --------------------------------------------------------------rem コピー元タイプ_(default: txt)rem --------------------------------------------------------------@SET file_type1=.txt
rem -------------------------------------------------------------rem データベース__namerem -------------------------------------------------------------@SET database_name=[project_guest].[dbo]
rem -------------------------------------------------------------rem テーブル__namerem -------------------------------------------------------------@SET table_name=.[m_unit_price]
rem -------------------------------------------------------------rem SQLrem -------------------------------------------------------------@SET select_query="select replace(replace(replace(CONVERT(nvarchar,GETDATE(),120),'-',''),':',''),' ','')"
rem -------------------------------------------------------------rem 仮ファイル名rem -------------------------------------------------------------@SET tmp_file_1=now_time.dat
rem -------------------------------------------------------------rem CONFIGファイル名rem -------------------------------------------------------------@SET config_file_name=CONFIG.TXT
rem -------------------------------------------------------------rem システム日付を取得rem -------------------------------------------------------------@FOR /F "eol=# tokens=1,2,3* delims=, " %%i IN (%config_file_name%) DO (BCP %select_query% queryout %tmp_file_1% -S%%i -U%%j -P%%k -cIF errorlevel 1 GOTO error @FOR /f "delims=," %%x IN (%tmp_file_1%) DO ( @set now_time=%%~nx ))@DEL %tmp_file_1%
rem -------------------------------------------rem SQLサーバ 導出成功rem -------------------------------------------@FOR /F "eol=# tokens=1,2,3* delims=, " %%i IN (%config_file_name%) DO (BCP %database_name%%table_name% out %database_name%%table_name%_%now_time%%file_type1% -S%%i -U%%j -P%%k -cIF errorlevel 1 GOTO error)
@echo -- =============================================@echo 導出成功しました。@echo -- =============================================
rem -------------------------------------------rem ファイルサーバを接続rem -------------------------------------------@NET USE %file_server_dir% %file_server_password% /u:%file_server_username%IF errorlevel 1 GOTO error
rem -------------------------------------------rem ファイルサーバへコピーrem -------------------------------------------@XCOPY %database_name%%table_name%_%now_time%%file_type1% %file_server_dir% /YIF errorlevel 1 GOTO error@echo -- ===========================================================@echo %file_server_dir%へコピーしました。@echo コピー完了しました。@echo -- ===========================================================
@GOTO end@:error
@echo -- =============================================@echo 導出失敗しました。@echo -- =============================================@GOTO end@:endrem -------------------------------------------rem コピー元を削除rem -------------------------------------------@IF EXIST %database_name%%table_name%_%now_time%%file_type1% (DEL %database_name%%table_name%_%now_time%%file_type1%)
----------------------------------------
----------------------------------------
config.txt
192.168.0.218,sa,password
