應用資料庫代理程式於組織工作警訊之設計與實作

(1)

ᔈҔၗ਑৤ж౛ำԄܭಔᙃπբ᝾ૻϐ೛ीᆶჴբ

ഋࣂ౺ ৪ᔮШ

୯ҥ໦݅ࣽמεᏢၗૻᆅ౛س

g9323211@yuntech.edu.tw

ǵhsujs@mis.yuntech.edu.tw

ᄔा

ගଯಔᙃΓ঩ޑπբਏ౗ᆶቚ຾Γ঩ϐॄೢ ҺᄊࡋǴ΋ޔࢂᆅ౛ޣ܌ᜢϪޑ᝼ᚒǶऩࢬำύޑ ΢Πෞ೽ߐ໔πբ຾ՉคݤӵයӦ໩ճሒௗǴ೽ߐ ໔ޑݾ֐Ϸ௢ڕೢҺޑ௃ඳஒόᘐӦख़ፄ΢ᄽǶฅ ԶᏤΕ΋঺ཥၗૻس಍ٰᆅ౛πբࢬำ٣୍ǴჹҾ ཰ಔᙃٰᖱǴஒࢂ΋฽ᚳεޑ຤ҔЍрǶӢԜǴҁ ࣴز๱౳ܭҾ཰ಔᙃԋҁ຤ҔޑԵໆǴаόᚐѦቚ уၗૻس಍຤Ҕϐচ߾ΠǴճҔၗ਑৤ж౛ำԄϷ SQL Mail ޑ୍ܺ೛ۓǴٰჴբπբၻਔϐ᝾ૻ೯ ޕǶԜ΋೛ीૈԖਏ่ӝಔᙃϣ೽ޑ౜Ԗၗૻس ಍Ǵගٮ೽ߐΓ঩πբ຾ࡋပࡕਔԾ୏᝾ૻᆶ຾Չ ࡕុଓᙫޑ୏բǶ ᜢᗖຒǺπբࢬำǵၗ਑৤ж౛ำԄǵSQL Mailǵ Ծ୏᝾ૻǶ

Abstract

It has been the key issue of the manager to improve the work efficiency and responsibility of staff in organization all the time. If the interdepartmental work from upstream to downstream in the workflow can't be linked up smoothly on schedule, the situation of argument and shirking duty between each department will be occurred constantly. In organization, however, it would be a huge expense to introduce a new information system to manage the workflow affair.

The main consideration of this research focuses on the expenses of enterprises, without increasing the expenses of information system. We utilize database agent and SQL Mail service to implement alert notice that the work has not been finished on schedule. This design will combine with the existing information system effectively and provide automatic alert on work progress delay and go on follow-up tracking.

Keywords: Workflow, Database Agent, SQL Mail,

Automatic Alert.

1. ߻ق

ࣁΑߥ࡭ಔᙃᝡݾΚǴ೚ӭҾ཰࿶த೸ၸ௦ᖼ ཥၗૻس಍܈ϣ೽ԾՉ໒วཥس಍ޑБԄǴٰᅈى ಔᙃϐ҅தᔼၮբ཰Ǵаයၲډ࿯࣪ΓΚǵ࿯ऊԋ ҁǵගܹਏ౗ޑҞ኱Ƕ೭٤εࠠޑၗૻس಍܌ගٮ ޑኳಔфૈǴ΋૓ࢂ٩ಔᙃϣޑπբࢬำٰฝϩځ ٬Ҕޑ೽ߐϷᏹբޑΓ঩Ƕ೭٤٬Ҕس಍ޑ೽ߐ۶ Ԝ໔ΨԖ࣬྽ำࡋޑπբᜢᖄǴ೽ߐ໔ޑϕ୏Ϸྎ ೯ҭሥࣁஏϪǶᖐٯٰᇥǴ䩤೽ߐޑπբҺ୍ࢂ܍ ុΌ೽ߐޑπբԶٰǴΌ೽ߐޑπբҺ୍ࢂ܍ុЧ ೽ߐޑπբԶٰǶΨ൩ࢂᇥǴ䩤೽ߐΓ঩ϐπբा ຎΌ೽ߐΓ঩ϐπբ຾ࡋԶۓǴΌ೽ߐΓ঩ϐπբ ाຎЧ೽ߐΓ঩ϐπբ຾ࡋԶۓǴ٩Ԝᜪ௢Ƕऩࢂ πբࢬำޑ΢ෞ೽ߐ຾ࡋั༾ۯᒨǴஒೱ஥ቹៜࡕ ុπբࢬำޑΠෞ೽ߐπբ຾ՉǶӵՖૈዴߞ঩π ӵයֹԋځᔈ଺ޑ٣ǻჹᆅ౛ޣԶقǴࢂཱུࣁख़ा ޑǴ೭ΨࢂҾ཰ಔᙃ܈ࡹ۬ൂՏයࣰၲԋޑҞ኱Ƕ ӢԜӧಔᙃύǴคό׆ఈ೸ၸ΋ঁس಍ϯޑᆅ ౛ᐒڋǴ҅Ԅޑຑ՗঩ππբޑᕮਏǶ೭٤ᆅ౛ᐒ ڋޑ೛ҥǴҞޑόӧೀᆦ঩πǴԶࢂाૈ፾ਔౢғ ᝾Ңаගᒬ঩πᔈᅰޑက୍ᆶೢҺǶπբᆅ౛س಍ ёаҔٰև౜ԖᜢܭΓ঩πբᕮਏᆶӣ㎸ၗૻ [1]Ǵаၲډ׳ӳޑྎ೯ਏ౗Ϸ׳זೲޑϸᔈǴ٠а Ԝڐշᆅ౛ޣڗளᕮਏׯ๓ϐࡌ᝼ᆶᑽໆಔᙃΓ ঩ϐว৖ǶࣁԜ೚ӭҾ཰Ѹ໪׫ΕᚐѦޑ຤ҔٰᏤ Εᆶᆅ౛πբࢬำ࣬ᜢޑၗૻس಍ǴႽࢂ Lotus Notes฻ޕӜ೬ᡏس಍Ƕՠ೭٤س಍۳۳ᐱҥၮբǴ คݤԖਏ᏾ӝಔᙃ౜Չၮբύޑځдၗૻس಍ǴႽ ࢂ ERP(Enterprise Resource Planning) س ಍ ǵ

PDM(Product Data Management)س಍Ǵҭό಄ӝ೽

ߐ໔ჴሞٯՉ܄πբҺ୍ϐሡ؃ǴᏤठӚ೽ߐΓ঩ Ѹ໪ӧ౜Չޑᔼၮس಍ϷཥᏤΕޑπբࢬำᆅ౛ س಍ύᒡΕख़ᙟޑၗ਑Ǵό໻ੁ຤ΓΚπਔǴჹܭ ཥᏤΕޑπբࢬำᆅ౛س಍ѝ௦Ҕ೽ϩޑфૈǴӵ Ԝ׳ࢂੁ຤ၗૻس಍ޑሽॶϷᏤΕ຤ҔǶ ᆕӝа΢܌قǴҁࣴزЬाаόᚐѦቚуၗૻ س಍຤Ҕࣁ߻ගǴޔௗ่ӝಔᙃϣ೽ᔼၮύޑၗૻ س಍Ǵஒၗ਑ၻਔ҂ೀ౛ϐղᘐᡄᒠቪԋႣӸำ ׇǴ٠ճҔၗ਑৤ж౛ำԄ୍ܺۓਔҗ SQL Mail ஒࢗ၌่݀วଌډႝηແҹ՛ܺᏔǴനࡕ໺ଌ೯ޕ ߞҹډ࣬ᜢ೽ߐΓ঩ޑႝηແҹߞጃǴаၲډВத πբ຾ࡋပࡕਔԾ୏᝾ૻගᒬᆶଓᙫϐфਏǶ

2. ࣬ᜢמೌϷЎ᝘௖૸

ҁക࿯ଞჹᆶҁࣴز࣬ᜢޑמೌϷЎ᝘຾Չ ௖૸᏾౛ǴЬाϩࣁѤε೽ϩǶಃ΋೽ϩࣁπբࢬ ำϐۓကǴಃΒ೽ϩࣁ SQL Server AgentǴಃΟ೽ ϩࣁ Stored ProcedureǴಃѤ೽ϩࣁ SQL MailǶ

(2)

2.1 πբࢬำϐۓက πբࢬำࢂࡰ΋ԖಔᙃޑπբҺ୍ԏ໣ǴҔٰ ֹԋҾ཰ޑࢌ٤ࢬำ[5]Ƕ΋૓ٰᇥǴ΋໨πբҺ୍ ޑֹԋёаࢂ΋ঁ܈ӭঁ೬ᡏس಍ǵൂ΋ঁΓ܈΋ ဂΓǴ܈ࢂ೭٤೬ᡏس಍کΓޑ่ӝ܌ӅӕֹԋǶ ӢԜǴπբࢬำࢂҗӚᅿٰྍᆅၰӝٳၗૻǴΨ൩ ࢂӧ٬Ҕޣᆶس಍໔ᙯ౽ၗૻ[4]Ƕڂࠠޑπբࢬำ ᔈҔำԄхࡴ߄ൂޑ໺᎙/೚ёǵЎҹޑᔠ᎙/วթ ϷୢᚒଓᙫǶӧҾ཰ಔᙃύǴӚ೽ߐ໔ޑπբ܄፦ தத཮Ԗπբࢬำ΢ޑӃࡕᜢ߯Ǵಔᙃࢬำύޑ΢ ෞ೽ߐπբֹԋࡕǴௗុځࡕޑ೽ߐωૈ৖໒πբ Һ୍ޑ຾ՉǶҗԜىаᡉҢрπբࢬำޑ໩ᄣǴѸ ૈߦ຾Ҿ཰ϣ೽ၗૻޑࢬ೯Ǵ຾ԶၲډගϲҾ཰ғ ౢΚϷᝡݾΚǶ ӧჴሞޑҾ཰ಔᙃύǴπբೀ౛ޑၸำ཮࿶җ ΋ঁԖԛׇ܄ޑࢬำٰၲԋ΋໨πբҞ኱ǶӧԜ΋ ࢬำ຾ՉਔǴπբҺ୍ύ܌х֖ޑၗ਑ᆶၗૻஒ໩ ๱٣Ӄۓကӳޑࢬำ໩ׇǴ٩ԛ໺ଌԿΠ΋ࢬำޑ ୖᆶޣٰ୺ՉπբǶᙖҗႝတၗૻس಍ϐำԄ೛ ीǴёஒࢬำୖᆶޣ໔ޑၗ਑ǵЎҹǵၗૻϷπբ ำׇϒа೛ीೕჄǴٰၲԋࢬำԾ୏ϯϐҞ኱Ƕ 2.2 SQL Server Agent SQL Server Agent(ж౛ำԄ)Ьाॄೢ೛ۓ SQL Server΢ޑຼයࢲ୏௨ำǴૈஒ՛ܺᏔςวғ ޑୢᚒ֋ޕس಍ᆅ౛঩ǶSQL Server Agent Ьाޑ фૈࣁբ཰௨ำǵ୺Չբ཰(Job)ǵౢғ᝾Ң(Alert)ǵ ٣ҹวғ೯ޕᆅ౛Γ঩܈ᏹբ঩(Operator)Ƕჴբ೭ ٤фૈޑ SQL Server ж౛ำԄϡҹ[3]ࣁǺ 1.Operator(ᏹբ঩)Ǻ೛ۓёှ،՛ܺᏔୢᚒޑ ᆅ౛Γ঩Ϸځ೯ૻБԄǶ྽՛ܺᏔวғୢᚒ ਔǴ೭٤ᏹբ঩ёа೸ၸႝηແҹǵڥћ Ꮤǵnet send ᆛၡڮзԶԋࣁ᝾ҢޑҞ኱Ƕ 2.Job(բ཰)Ǻҗ΋܈ӭঁట୺Չϐ؁ᡯ܌ᄬԋ ޑςۓကނҹǴ೭٤؁ᡯࢂё୺Չޑ T-SQL ഋॊԄǶբ཰ёа೛ۓ௨ำǴ୺Չ௨ۓܭ੝ ۓਔ໔܈ຼය୔໔܌วғޑ SQL Server π բǶբ཰ё೛ۓԋԾ୏ϷЋ୏ٿᅿБԄǶ 3.Alert(᝾Ң)Ǻଞჹวғ੝ۓރݩਔाวߞ๏ ᏹբ঩܌௦ڗޑ୏բǶځ೛ۓޑБԄёଞჹ ࢌ੝ۓ٣ҹǴٯӵ੝ۓޑᒱᇤǵၗ਑৤ډၲ ςۓကޑёҔޜ໔ज़ڋ฻Ǵ܈ࢂࡰۓ٣ҹޑ ᝄख़܄Ǵٯӵࢌᅿᝄख़܄ޑᒱᇤǶ྽᝾Ң೏ ᝻วਔǴځ܌ा௦ڗޑ୏բёҗᆅ౛ޣ٣Ӄ ܌ۓကǴٯӵ໺ଌႝηແҹǵڥћᏹբ঩܈ ୺Չբ཰ٰှ،ୢᚒǶ SQL Server ж౛ำԄѸ໪٬Ҕى୼៾ज़ޑ Windowsᆛୱ஦ဦٰ௴୏ǴԶߚ٬Ҕ SQL Server س ಍஦ဦ[9]ǴӵԜ SQL Server ж౛ำԄޑϡҹωૈ҅ தၮբǶӧ Microsoft SQL ύǴբ཰ǵ᝾ҢаϷᏹ բ঩ёа೸ၸ SQL Server Enterprise ManagerǵSQL

ϩ ණ Ԅ ᆅ ౛ ނ ҹ (SQL-DMO) ᔈ Ҕ ำ Ԅ ǵ Transact-SQLᔈҔำԄ฻БԄٰࡰۓǶ೭٤ۓကΨ ஒᓯӸܭ SQL Server ޑ msdb س಍ၗ਑৤ύǴа฻ ࡑ SQLServerAgent ୍ܺ௴୏ਔǴёܭԜࢗ၌Ϸ،ۓ ܌ा௴Ҕޑբ཰ک᝾ҢǶ྽ၗ਑৤ Server Ԗރݩว ғਔǴஒ཮໺ሀ٣ҹ๏ж౛ำԄǴж౛ำԄΨஒ཮ ٩բ཰௨ำޑਔ໔ٰ୺Չբ཰܈ࢂ୺Չ᝾Ңǵวଌ

SQL Mailा؃๏ SQL ServerǴаϷ໺ଌ net send ᆛ

ၡڮз๏ Windows ܈ࢂᙖշႝηແҹٰ֋ޕᏹբ ঩Ƕଞჹ΢ॊޑբ཰ǵ᝾ҢаϷᏹբ঩ϐۓကᆶ୏ բ܌٬ҔޑЬाϡҹǴёҗკ 1 ٰև౜Ƕ კ 1 բ཰ǵ᝾ҢаϷᏹբ঩ϐۓကᆶ୏բ܌ ٬ҔޑЬाϡҹ ၗ਑ٰྍǺMicrosoft SQL Server ጕ΢ᇥܴ 2.3 Stored Procedure Stored Procedure(ႣӸำׇ)ࢂ΋ಔࣁΑֹԋ੝ ۓфૈޑ T-SQL(Transact-SQL)௓ڋڮзᇟݤǴஒ΋ ٤ ቪ ӳ ޑ T-SQL ڮ з ᇟ ݤ х း ԋ ΋ ঁ ำ ׇ (Procedure)ᓯӸӧၗ਑৤ύǴ΋૓ךॺёаஒ࿶த ሡाೀ౛ޑ΋٤୏բቪԋႣӸำׇǴаБߡ۳ࡕϐ ୺Չ[2]ǶҗܭቪӳޑႣӸำׇࢂᓯӸӧ Database Server ΢ǴஒёуזӸڗၗ਑৤ޑਏ౗аϷБߡВ ࡕᆅ౛Ƕ΋૓ٰᇥǴႣӸำׇڀԖаΠᓬᗺǺ(1) ෧ϿϷᗉխᔈҔำԄϐঅׯϷख़ཥ೽࿿ǵ(2)ჴ౜ၨ זޑ୺Չೲࡋǵ(3)෧Ͽᆛၡࢬໆǵ(4)բࣁ΋ᅿӼӄ ᐒڋٰкϩճҔǶ ӵ߄ 1 ܌ҢǴջࢂֹ᏾ޑႣӸำׇࡌҥᇟݤ [6]ǴӧԜᙁܰᇥܴӵࡕǶࡌҥႣӸำׇޑ௶ॊڮз

ࢂа CREATE PROCEDURE ܈ CREATE PROC ໒ ۈ[7]Ǵځࡕௗ๱ႣӸำׇޑӜᆀ,ҭёӧԜำׇӜᆀ ϐ߻ࡰۓځᏱԖޣ(Owner)Ƕᒧ᏷܄᏾ኧॶ[;number] ёҔٰࡌҥ΋ಔႣӸำׇǴٯӵǴspList;1ǵspList;2Ƕ ќѦΨёࣁႣӸำׇࡰۓୖኧǴхࡴᒡΕୖኧکᒡ рୖኧǴ೭٤ࡰۓୖኧޑӜᆀӚձࢂа@ࣁ߻Ꮴӷ ϡǴ؂ঁୖኧޑࡕय़ሡᆙௗ๱ୖኧޑၗ਑ࠠᄊǴऩ

(3)

၀ୖኧࢂٮ໺ӣॶҔਔǴёܭځࡕу΢ OUTPUT ߄ҢǶ٬Ҕ WITH ENCRYPTION ૈჹႣӸำׇำ ԄዸуஏǴ٬Ҕ WITH RECOMPILE ߾؂ԛ٬ҔႣ Ӹำׇਔ཮மॐख़ཥጓ᝿ǶќѦӧၗ਑৤຾Չፄቪ

(Replication)ਔǴ٬Ҕ FOR REPLICATION ٰೀ౛ᑔ

ᒧǶനࡕӧ AS ᜢᗖӷࡕय़ޑϣ৒߾ࢂ၀ႣӸำׇ ޑЬᡏ೽ϩǴࡰۓำׇ܌ा୺Չޑ୏բǴхࡴӧำ ׇύޑҺՖ T-SQL ഋॊԄޑኧҞ܈ၗ਑ࠠᄊǶ

߄ 1 ֹ᏾ႣӸำׇࡌҥᇟݤ

CREATE PROC[ EDURE ] procedure_name [ ; number ]

[

{ @parameter data_type }

[ VARYING ] [ = default ] [ OUTPUT ] ] [ ,...n ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] [ FOR REPLICATION ] AS sql_statement [ ...n ] 2.4 SQL Mail SQL MailගٮΑ΋ᅿவ SQL Server วଌک᎙ ᠐ႝηແҹޑᙁൂБݤǴᙖҗႝηແҹҔЊᆄٰԏ วႝηແҹǶҗܭ SQL Mail ࢂ΋ঁ MAPI(Mail

Application Programming Interface)ᔈҔำԄǴӢԜ

՛ܺᏔ΢Ѹ໪Ӹӧ MAPI ηس಍[8]Ǵٯӵ Microsoft

Outlookϐᜪޑ MAPI ࠼ЊᆄǶόӕܭ SQL Server Agent ແҹфૈ(SQLAgentMail)ѝૈวߞ೯ޕس಍ ᏹբ঩ǴSQL Mail ନΑёаวଌႝηແҹǴҭёа ӧ໺ଌύх֖΋ঁ่݀໣(Result Set)Ǵஒϣ֖ࢗ၌ ڮзޑႝηແҹ໺ଌ๏ SQL ServerǴҗ SQL Server ஒڮзೀ౛ၸࡕӆ೸ၸ SQL Mail аႝηແҹ໺ӣ ๏٬Ҕޣ[2]Ƕҗ΢܌ॊǴSQL Mail ᡣ SQL Server ёаᆶແҹ՛ܺᏔࡌҥҔЊᆄೱጕǴٰ໺ଌᆶௗԏ ႝηແҹǴЪ೯த܌٬Ҕޑແҹ՛ܺᏔԖ Microsoft

Exchange ServerǵMicrosoft Windows NT ແҹ܈ Post Office Protocol 3 (POP3)฻Ƕ

3. س಍ࢎᄬϷфૈჴբ

ӵკ 2 ϐࢎᄬკ܌ҢǴҁࣴز௦Ҕ Microsoft SQL Server 2000բࣁၗ਑৤՛ܺᏔ೬ᡏǴଛӝ SQL Mail୍ܺϐᔈҔǴ٠೛ۓ᝾ૻೀ౛ೕ߾Ǵӆᙖҗж ౛ำԄ೛ۓբ཰௨ำਔ໔ǴԾ୏୺Չ᝾ૻೀ౛୏ բǴനࡕஒ၀୏բೀ౛่݀วଌډແҹ՛ܺᏔǴа ၲډ᝾ૻ೯ޕޑфૈǶ კ 2 س಍ࢎᄬკ 3.1 س಍੝Յ ҁࣴزϐ᝾ૻᐒڋࢂࢎᄬӧ Microsoft SQL

Server 2000Ϸ Microsoft Exchange 2000 Server ΢Ǵ

ԜΒ೽՛ܺᏔբ཰س಍ࣣ௦Ҕ Microsoft Windows

2000 ServerǶ೸ၸԜΒ೽՛ܺᏔٰ೛ीϷჴբҁࣴ

زϐπբ᝾ૻ೯ޕǴЬाҗ SQL Server ϐж౛ำԄ ؂Вమఃٰ᝻วԾुޑႣӸำԄǴଞჹၗ਑৤ύၻ ය҂ೀ౛ϐၗ਑຾Չၸᘠᆶ༼ᕴࡕǴӆஒ܌ౢғޑ ่݀೸ၸ SQL Mail ໺ଌډ Exchange ServerǶԜ΋ ၮբኳԄӵკ 3 ܌ҢǴჴ౜Αаಔᙃ౜Ԗᔼၮύޑ س಍ٰԾ୏วଌπբ᝾ૻ೯ޕޑфૈǶ

კ 3 ᝾ૻၮբҢཀკ

3.2 SQL Mail೛ۓ

Microsoft SQL ServerගٮΑ SQL Mail ୍ܺǴ

ࣁΑ٬Ҕ SQL MailǴ२Ӄӧჴբޑ՛ܺᏔ΢Ѹ໪ा Ԗ SMTP(Simple Mail Transfer Protocol)୍ܺǶҗܭ

Microsoft Windows 2000όගٮ MAPI ηس಍ǴӢ

ԜǴךॺӃՉӼးΑ Microsoft Outlook բࣁ MAPI ࠼ЊᆄǶԜѦǴSQL Mail ሡाԖແֽೱጕǵແҹӸ ܫ(ߞጃ)ǵҔЊᆄແҹ೛ۓᔞ(Mail Profile)ᆶҔٰฦ Ε SQL Server ୺Չঁᡏ(Instance)ޑ Windows ᆛୱ

Database SQL Mail ೛ۓ/௴୏ ᝾ૻೕ߾೛ۓ ж౛ำԄ բ཰௨ำ SQL Server Mail Server วଌૻ৲ ແҹૻ৲ ೀ౛ Exchange 2000 Server ႚಬᤞಛၡٙ ޢֲ堚ඣ ച۩ඈ࿓܂ᄐ SQL Server 2000 ᤞಛփ୲๠෻ ܂ᄐඈ࿓๻ࡳ

(4)

٬Ҕޣ஦ЊǶӧӼးک೛ۓӳ࠼Њᆄແҹ೛ۓᔞ ࡕǴࣁ٬ SQL Mail ૈڗள࿶җႝηແҹ܌໺ଌϐ ࢗ၌ा؃Ǵۘ໪ࡰۓ SQL Mail ಔᄊ೛ۓᔞǴӵკ 4 ܌ҢǶ კ 4 ࡰۓ SQL Mail ಔᄊ೛ۓ 3.3 ᝾ૻ೯ޕೕ߾೛ी ךॺޑҞ኱ࢂाᙖշ SQL Mail ஌рࢗ၌ڮз ๏ SQL ServerǴᡣ SQL Server ೀ౛ֹڮзࡕᒿջஒ ࢗ၌่݀஌๏࣬ᜢޑ೽ߐΓ঩Ǵаගᒬ࣬ᜢΓ঩π բೀ౛ϐ຾ࡋǶӢԜѸ໪Ӄ๏ۓ᝾ૻࢗ၌ޑᡄᒠೕ ߾Ǵஒჴሞೀ౛ၗ਑ࢗ၌ޑ SQL ᇟݤቪԋႣӸำ ׇǴٮัࡕ SQL Mail วଌࢗ၌ڮзਔ٬ҔǴау ז୺ՉೲࡋϷБߡࡕុೕ߾অׯǶ᝾ૻ೯ޕޑࢗ၌ ೕ߾௶ॊӵΠǺ 1.೛ۓ᝾ૻ೯ޕ܌ೕጄޑπբၻයϺኧ(аୖ ኧ@Term ٰࡰۓ)Ǵӕਔೕጄ೯ޕ Team Leader(аୖኧ@ CTLTermٰࡰۓ)аϷଯቫ Ьᆅ(аୖኧ@AdminTermٰࡰۓ)ϐၻයϺ ኧǴӵ߄ 2 ܌ҢǶ 2.வҾ཰ಔᙃ౜Ԗޑᔼၮس಍ၗ਑৤ύǴפр ၻය҂ೀ౛ޑၗ਑(ஒࢗ၌ೕ߾ќቪԋႣӸ ำׇٰೀ౛)Ƕ 3.פрπբၻය҂ೀ౛ޑΓ঩Ӝൂ(аୖኧ @Developer ٰ૶ᒵ)Ϸځႝηແҹ(аୖኧ @MyReceiverٰ૶ᒵ)Ƕ 4.פрπբၻය҂ೀޑΓ঩܌ჹᔈϐ Team Leaderႝηແҹ(аୖኧ@Mycopy_recipients ٰ૶ᒵ)Ƕ 5.ଌрႝηແҹा؃SQL Server୺Չၗ਑ࢗ၌ ༼ᕴǴ٠ஒࢗ၌่݀Ծ୏ౢғߕᔞ೯ޕ࣬ᜢ Γ঩Ƕ ߄ 2 ᝾ૻ೯ޕ೛ीϐ೽ϩୖኧ೛ۓ /* ࠹֋ୖኧ */

DECLARE @AdminReceiver varchar(100) --ଯቫ

Ьᆅԏҹޣ

DECLARE @Pno int

DECLARE @Developer varchar(400) --πբၻය҂

ೀ౛Γ঩

DECLARE @MyReceiver varchar(255) --ԏҹޣ DECLARE @Mycopy_recipients varchar(255) --ୋ

ҁԏҹޣ

DECLARE @MySubject varchar(255) --ແҹЬԑ /* ࡰۓୖኧॶ */

SET @Computetime =getdate() SET @Term=3 --೛ۓπբၻයϺኧ

SET @CTLTerm=9 --೛ۓ൳Ϻࡕ๏ Team Leader SET @AdminTerm=12 --೛ۓ൳Ϻࡕ๏ଯቫЬᆅ ೸ၸ΢ॊೕ߾೛ۓࡕǴѝाӧҾ཰ಔᙃВதޑ ᔼၮس಍ύǴπբၻය҂ೀ౛ϐ࣬ᜢΓ঩Ǵ؂Ϻஒ ཮ԏډҗ SQL Server ܌วрޑ᝾ૻ೯ޕǴޔԿ၀Γ ঩ӧᔼၮس಍ύஒځॄೢϐπբҺ୍ֹԋࣁЗǶࣁ ᗉխ၀᝾ૻ೯ޕၸܭᓎᕷǴӕ΋πբҺ୍ϐॄೢΓ ؂Ϻԏډ࣬ӕޑૻ৲Ǵό໻೷ԋΓ঩௃ᆣեပǵᓸ ΚᡂεǴҭόΓ܄ǶӢԜӧ᝾ૻ೯ޕೕ߾ύǴቚу Ȩ؂႖൳Ϻගᒬ΋ԛȩޑ೛ۓǶऩȨҁԛ᝾ૻวғ ϐВයȩ೛ࣁ AlertDateǴȨ၀πբ΢ԛϐଓᙫ୷ྗ Вයȩ೛ࣁ TraceDateǴȨ᝾ૻ೯ޕຼයȩ೛ࣁ CycleǴ ߾Ԝཥቚޑೕ߾ёаϦԄ߄ҢࣁǺ

(AlertDate - TraceDate) % Cycle = 1

...ϦԄ(1)

ӧϦԄ(1)ύޑ%߄Ңٿኧӷ࣬ନࡕڗᎩኧǶԖ ΑԜϦԄǴךॺߡёаࡐ৒ܰीᆉрҁԛ᝾ૻวғ ਔǴᔈ၀ஒٗ٤҂ֹԋޑၻයၗ਑ӈΕ᝾ૻ೯ޕǶ ଷ೛ҁԛ᝾ૻวғϐВය(AlertDate)ࣁ 2005/6/6Ǵ᝾ ૻ೯ޕຼය(Cycle)೛ࣁ 3 ϺਔǴ঺ҔϦԄ(1)ёளډ (2005/6/6 - TraceDate) % 3 = 1 ܌аǴTraceDate = {2005/6/5, 2005/6/2, 2005/5/30, 2005/5/27, 2005/5/24, …} Ԝ่݀ࡰрǴӧᔼၮس಍ύϩձឦܭ 2005/6/5ǵ 2005/6/2ǵ2005/5/30ǵ2005/5/27ǵ2005/5/24ǵ…฻ ВයޑπբǴ೭٤ၻය҂ೀ౛ֹ౥ϐၗ਑Ѹ໪ӈΕ ᝾ૻ೯ޕǶඤقϐǴऩᔼၮس಍ύǴࢌ΋πբԾவ ΢ԛଓᙫ୷ྗВයख़೛ࡕǴ΋ޔ҂೏ॄೢΓೀ౛ֹ ౥Ǵ߾аԜଓᙫ୷ྗВය۳ࡕᆉޑಃ 1ǵ4ǵ7ǵ 10ǵ...(೏ Cycle ନǴᎩኧ֡ࣁ 1)Ϻޑၗ਑ஒ༼ᕴࡕ ౢғ᝾ૻߕᔞǴԶЪߕᔞύ೭٤ၗ਑ޑॄೢΓΨஒ ԋࣁ᝾ૻ೯ޕޑჹຝǴ೭٤ΓޑႝηແҹӦ֟ஒ཮ ೴΋೏ࡰۓډແҹԏҹΓύǴԾ୏ԏډπբۯᒨ᝾ ૻ೯ޕޑႝηແҹǶ᝾ૻ೯ޕޑ೽ϩᡄᒠೕ߾೛ी ӵ߄ 3 ܌ҢǶ

(5)

߄ 3 ᝾ૻ೯ޕ೛ीϐ೽ϩᡄᒠೕ߾

/* פрπբၻය҂ೀ౛ΓӜӷϷځႝηແҹ */ Select @Pno=@pno+1, @Developer= @Developer + convert(varchar(2),@Pno)+'.'+( d.dvlptmp1) +' ', @MyReceiver = @MyReceiver + u.mailalias + ';' From colorspec s join pccdvlp d on

s.devlopid=d.devlopid and s.purpid=d.purpid join v_specusr u on d.dvlper=u.usracct

Where ( ( d.clrdel='C' ) and ( d.status<>'C' ) and ( s.usecheck<>'' ) and ( s.colorcfm is null ) and ( DATEDIFF(D, s.trace , @Computetime ) > @Term ) and (( DATEDIFF(D, s.trace , @Computetime ) % @Term ) = 1) ) Group By d.dvlptmp1, u.mailalias

/* פрπբၻය҂ೀ౛Γځ܌ჹᔈޑ Leader */ Select @Mycopy_recipients =

@Mycopy_recipients + ';' + t.ctlemail From colorspec s join pccdvlp d on

s.devlopid=d.devlopid and s.purpid=d.purpid join v_specusr u on d.dvlper=u.usracct join team t on d.dvlpgrp=t.dvlpgrp

Where ( ( d.clrdel='C' ) and ( d.status<>'C' ) and ( s.usecheck<>'' ) and ( s.colorcfm is null ) and ( DATEDIFF(D, s.trace , @Computetime ) > @CTLTerm ) and (( DATEDIFF(D, s.trace , @Computetime ) % @Term ) = 1) )

Group By d.dvlpgrp, t.ctlemail

/* פрҁԛπբၻය೯ޕ᝾ૻϐӜᆀ */ Select @MySubject = JobName

From JobList

Where JobId = 'ClrunCFM' /* ଌрႝηແҹ๏ SQL Server */

Exec master.dbo.xp_sendmail @recipients = @MyReceiver , @copy_recipients =

@Mycopy_recipients , @message = @MyContent, @query = 'sp_ClrunCFM', @subject = @MySubject, @attach_results='true',@attachments='unCFM.xls', @no_header='false',@width=2000,@separator=' ' 3.4 ж౛ำԄ೛ۓ ֹԋΑ΢ॊޑ೛ी୏բǴךॺᗋሡा຾Չж౛ ำԄޑ೛ۓωૈ೸ၸ௨ำфૈᡣSQL ServerԾ୏ۓ යೀ౛੝ۓޑբ཰Ƕךॺஒ٩ׇࡌҥբ཰ޑۓကǴ ೛ۓ୺Չޑ؁ᡯǴ೛ۓբ཰ޑ௨ำǴඓ௓բ཰ֹ ԋǵԋфаϷѨ௳ਔޑ೯ޕૻ৲Ƕӧკ 5 ܌Ңޑж ౛ำԄբ཰(Job)ύǴሡࡰۓբ཰ޑ؁ᡯӜᆀǵڮз ᅿᜪǵሡाೀ౛ޑၗ਑৤аϷགྷा୺ՉޑำԄ(ӧԜ ᒡΕ exec sp_mailClrunCFMǴٰ୺Չךॺ܌Ծुޑ ႣӸำׇ)Ƕനࡕӆཥቚբ཰௨ำǴࡰۓբ཰୺Չޑ ਔ໔Ϸᓎ౗(ӧԜ೛ۓ؂Ϻ΢ϱ 6 ᗺ୺Չբ཰)Ǵ٠ ௴ҔԜ௨ำǴֹԋϐ೛ۓӵკ 6 ܌ҢǶ྽ฅǴࣁዴ ߥբ཰୺ՉѨ௳ਔૈஒૻ৲֋ޕس಍ᆅ౛঩аբ ᆙ࡚ೀ౛ǴךॺΨёӧკ 7 ޑᒧ໨ύҭ຾Չ࣬ᜢ೛ ۓ୏բǶ კ 5 ཥቚբ཰؁ᡯ კ 6 ௨ۓբ཰୺Չਔ໔Ϸᓎ౗ კ 7 բ཰୺Չࡕ֋ޕૻ৲೛ۓ

4. س಍фૈჴٯ

ࣁΑᡍ᛾Ҿ཰ಔᙃϣ೽ᔼၮس಍ޑၗ਑৤ၗ ਑Ǵૈ୼җж౛ำԄԾ୏ۓය᝻ว SQL Mail วଌ ࢗ၌ڮз๏ SQL ServerǴஒၻය҂ೀ౛ޑၗ਑Ǵ٩ Ᏽ๏ۓޑೕ߾చҹٰᑔᒧрٰ٠஌рႝηແҹ᝾ ૻ೯ޕ࣬ᜢޑΓ঩ǴҁࣴزаҾ཰ϣ೽Ҟ߻ჴሞᔼ ၮޑس಍ࣁٯٰ຾ՉჴբǶԜس಍ޑၗ਑৤ύԖ΢ ԭ࿤฽ၗ਑ǴځύԖ೚ӭԐςຬၸځᔈ၀೏ॄೢΓ

(6)

ೀ౛Զϝ҂ೀ౛ޑၗ਑ǴΨ҅ӢࣁԖ೭٤ჴሞޑၗ ਑Ǵᡣҁჴᡍޑၮբ่݀ڙډ೽ߐϣ୷ቫΓ঩ޑख़ ຎᆶ᎜ᡋǴ׳ڙډଯቫЬᆅޑᜢϪᆶЍ࡭Ƕᢀჸჴ ሞวଌрٰޑ᝾ૻ೯ޕແҹύ(ӵკ 8 ܌Ң)ǴϣЎ ύዴჴѝஒπբ҂ӵයֹԋޑॄೢΓӜൂӈрǴ٠ ஒ೭٤ΓޑႝηແҹӦ֟ࡰۓډԏҹޣޑӦБǴዴ ߥдॺૈԏډ᝾ૻ೯ޕǴ٠Ъёа໒௴ߕᔞϐ Excel(ӵკ 9 ܌Ң)ٰᔠຎၻය҂ֹԋޑπբϣ৒Ƕ კ 8 ᝾ૻ೯ޕແҹ კ 9 ၻය҂ֹԋޑπբϣ৒

5. ่ፕᆶ҂ٰࣴزБӛ

ҁࣴزޑଅ᝘ЬाࣁԖਏ่ӝಔᙃϣ೽ϐᔼ ၮس಍ޑၗ਑৤ၗ਑Ǵ٠ᔈҔၗ਑৤ж౛ำԄܺ ୍Ǵམଛ SQL Mail ޑ೛ۓǴٰჴբπբၻਔϐ᝾ ૻ೯ޕǶԜ΋Γ঩πբۯᒨ᝾ૻ೯ޕޑфૈ೛ीǴ ёᗉխቚуҾ཰ಔᙃᏤΕཥၗૻس಍ޑ຤ҔаϷ Γ঩ख़ፄᒡΕၗ਑ϐπբॄ಻Ǵ٠ගٮкҽё᎞ޑ ၻයၗ਑ϣ৒ٰ᛾ܴ᝾ૻ೯ޕޑ҅ዴ܄Ƕҗܭ҂Ꮴ ΕԜ΋фૈϐ߻Ǵ೽ߐᆶ೽ߐ໔ਔதӢࣁ΢ෞ೽ߐ πբ౧۹҂ࡪਔֹԋǴᏤठΠෞ೽ߐπբคݤ҅த ሒௗԶᎁڙଯቫЬᆅޑೢഢǴ׳ёૈ೷ԋౢࠔۯᒨ ډᣁ൑௢рԶ٬Ҿ཰ᕇճཞѨǶҁЎ܌ჴբϐфૈ Ҟ߻ς࿶ӧҾ཰ಔᙃϣ໩ճၮբǴ٠ԋࣁ΢ભЬᆅ Եਡ೽ឦπբᕮਏϐୖԵǴΨቚ຾Ҿ཰ಔᙃڐፓӝ բϷၗྍճҔޑਏҔǶ ନΑ MS SQL Server ϐѦǴځдޑၗ਑৤ᆅ౛ س಍ΨԖж౛ำԄޑᐒڋǴٯӵӧ Oracle Enterprise

Manager(OEM)ࢎᄬύǴ྽ Intelligent Agent ௗԏډ

җ Management Server ܌໺ଌԶٰޑڮз܈ Job ਔǴߡ཮ѐ୺Չ೭٤ڮзǴஒ୺Չ่݀ӣ໺๏

Management ServerǴ٠ё೸ၸ Email ฻БԄ஌р่

݀೯ޕǶҁࣴزගрΑԜ΋ၮբᐒڋޑ೛ीϐࡕǴ ҂ٰය೚ૈஒԜᐒڋ೴ᅌᘉ৖ԿځдޑಔᙃϷሦ ୱ΢Ǵᡣၗ਑৤ޑж౛ำԄόӆѝࢂଞჹ՛ܺᏔ౦ தਔޑૻ৲֋ޕǴ܈ࢂၗ਑৤ύբ཰୺Չֹԋޑӣ ൔ୏բԶςǴ׳ाஒځ፟ϒཥޑ٬ڮٰബ೷р׳Ԗ ሽॶޑᔈҔǶ

ୖԵЎ᝘

[1]݅ࡌྦྷጓ᝿Ƕ2002Ƕ౜жᆅ౛ᏢǶ๮ੀЎϯ٣཰ ϦљǶѠчǶ [2]ࡼ࠶ሎࣴز࠻Ƕ2001ǶSQL Server 2000 ᆅ౛ჴ ୍Ƕᄡ኱рހިҽԖज़ϦљǶѠчǶ [3]ഋߪྍǶ2001ǶSQL Server س಍ᆅ౛ჴ୍Ƕޕࠤ ኧՏࣽמǶѠчǶ

[4]C. Frye, “Move to Workflow Provokes Business Process Scrutiny,” Software Magazine, April, 1994.

[5]D. Georgakopoulos, M. Hornick and A. Aheth, "An Overview of Workflow Management:From Process Modeling to Workflow Automation Infrastructure," Distributed and Parallel Databases, Vol. 3, No. 2, pp. 119-153, 1995.

[6]Dejan Sunderic, Tom WoodheadǶֆܿ፣ ᝿Ƕ 2001ǶSQL Server 2000 ႣӸำׇำԄ೛ीǶഝ

਱ᛥ׆ᅟǶѠчǶ

[7]Ken Henderson. 2002. SQL Server Stored Procedure Basics: Creating a Stored Procedure.

TU

http://www.informit.com/articles/article.asp?p=25 288&seqNum=4UT

[8]Mark Linsenbardt, Shane StiglerǶ೚ඵහ ฻᝿Ƕ 2001ǶSQL Server 2000 س಍ᆅ౛-ᄇۭࣴزǶഝ

਱ᛥ׆ᅟǶѠчǶ

[9]SQL Server Reference Guide: SQL Mail.

TU

http://www.informit.com/guides/content.asp?g=sql server&seqNum=31&rl=1UT