Azure SQL作業

c:\script\deleteLog.sql 這個script為需要執行的SQL語法，例如刪除表等等；

• -U Azure SQL登錄賬戶
• -d 資料庫名稱
• -P Azure SQL登錄密碼
• - i 執行的script路徑
• -o 執行結果輸出文件

 

設置輸出檔：

 

新增排程如下：

類型為「重複執行」

發生於「每日」

重複頻率「1」天

 

作業活動監視器可以查看所有作業的執行狀態，如果有錯誤，可以到上一步-o指定的文件查看。

 

 

deleteLog.sql內容如下：

DELETE FROM TKBLSTS WHERE JOB_NO IN (SELECT JOB_NO FROM TKBL WHERE (GROUP_ID<>‘EG‘ AND GROUP_ID<>‘PL‘) AND CREATE_DATE < DATEADD(MONTH,-4,getdate()));
GO
DELETE FROM TKBLPTY WHERE JOB_NO IN (SELECT JOB_NO FROM TKBL WHERE (GROUP_ID<>‘EG‘ AND GROUP_ID<>‘PL‘) AND CREATE_DATE < DATEADD(MONTH,-4,getdate()));
GO
DELETE FROM TKBLCNTR WHERE JOB_NO IN (SELECT JOB_NO FROM TKBL WHERE (GROUP_ID<>‘EG‘ AND GROUP_ID<>‘PL‘) AND CREATE_DATE < DATEADD(MONTH,-4,getdate()));
GO
DELETE FROM TKBLFLNO WHERE JOB_NO IN (SELECT JOB_NO FROM TKBL WHERE (GROUP_ID<>‘EG‘ AND GROUP_ID<>‘PL‘) AND CREATE_DATE < DATEADD(MONTH,-4,getdate()));
GO
DELETE FROM TKBLPO WHERE JOB_NO IN (SELECT JOB_NO FROM TKBL WHERE (GROUP_ID<>‘EG‘ AND GROUP_ID<>‘PL‘) AND CREATE_DATE < DATEADD(MONTH,-4,getdate()));
GO
DELETE FROM TKBL WHERE (GROUP_ID<>‘EG‘ AND GROUP_ID<>‘PL‘) AND CREATE_DATE < DATEADD(MONTH,-4,getdate());
GO
DELETE FROM OTH_LOG1 WHERE CREATE_DATE <  DATEADD(day,-10,getdate());

　　

 

Azure SQL作業

标签：local   http   add   azure   xxx   cal   lcm   sqlcmd   _id