SQL trigger Blank Title 85178

Yahoo Archive
1
BIG shout out of thanks to Vic for helping me on this one! Thanks Vic!



Rob Bucek

Manufacturing Engineer

PH: (715) 284-5376 ext 3111

FAX: (715)284-4084

<http://www.dsmfg.com/>

(Click the logo to view our site) <http://www.dsmfg.com/>





From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Rob Bucek
Sent: Tuesday, November 17, 2009 10:55 AM
To: vantage@yahoogroups.com
Subject: [Vantage] SQL trigger





Since Epicor hasn't been willing at this point to give us access to
process tables such as systask (via BAM or BPM) in order to monitor when
MRP is complete, I want to create an SQL trigger that will monitor the
task status field and when it is equal to complete and the task
description equals Process MRP, to change the CheckBox01 field in a UD
table to True (or 1), so my alert attachment can fire at that time. Any
mavericks out there creating home brewed triggers or stored procedures
in their Epicor SQL database? I could use a helpful nudge to get me
started here....

Unfortunately even in V9 data directives doesn't even allow access to
these either L

Rob Bucek

Manufacturing Engineer

PH: (715) 284-5376 ext 3111

FAX: (715)284-4084

<http://www.dsmfg.com/>

(Click the logo to view our site) <http://www.dsmfg.com/>

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]
2
Since Epicor hasn't been willing at this point to give us access to
process tables such as systask (via BAM or BPM) in order to monitor when
MRP is complete, I want to create an SQL trigger that will monitor the
task status field and when it is equal to complete and the task
description equals Process MRP, to change the CheckBox01 field in a UD
table to True (or 1), so my alert attachment can fire at that time. Any
mavericks out there creating home brewed triggers or stored procedures
in their Epicor SQL database? I could use a helpful nudge to get me
started here....



Unfortunately even in V9 data directives doesn't even allow access to
these either L

Rob Bucek

Manufacturing Engineer

PH: (715) 284-5376 ext 3111

FAX: (715)284-4084

<http://www.dsmfg.com/>

(Click the logo to view our site) <http://www.dsmfg.com/>







[Non-text portions of this message have been removed]
3
Rob,

See attached docs. It's a SQL query that nicely shows the most recent
500 tasks (active, completed and errored).

This is what I was chatting with you about at Perspectives.

I created a SQL view from this query. I hit the view with ASP and watch
the auto-refreshing ASP dashboard in Internet Explorer on one of my
monitors all day to monitor the system. (sample screenshot attached)

What I've done in the past is put an Update trigger on Systask.

If SysTask.TaskDescription = 'Process MRP' and the updated value for the
column Status = 'COMPLETE' then {{do your trigger}}. While MRP is
running TaskDescription will equal "ACTIVE".

Look at my query and screenshot and play with it. Look at some of the
existing triggers that Epicor put in SQL on the tables and you should be
able to piece together the syntax and logic.

Let me know if you need more help.


Vic


-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Rob Bucek
Sent: Tuesday, November 17, 2009 11:55 AM
To: vantage@yahoogroups.com
Subject: [Vantage] SQL trigger

Since Epicor hasn't been willing at this point to give us access to
process tables such as systask (via BAM or BPM) in order to monitor when
MRP is complete, I want to create an SQL trigger that will monitor the
task status field and when it is equal to complete and the task
description equals Process MRP, to change the CheckBox01 field in a UD
table to True (or 1), so my alert attachment can fire at that time. Any
mavericks out there creating home brewed triggers or stored procedures
in their Epicor SQL database? I could use a helpful nudge to get me
started here....



Unfortunately even in V9 data directives doesn't even allow access to
these either L

Rob Bucek

Manufacturing Engineer

PH: (715) 284-5376 ext 3111

FAX: (715)284-4084

<http://www.dsmfg.com/>

(Click the logo to view our site) <http://www.dsmfg.com/>







[Non-text portions of this message have been removed]



------------------------------------

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder
and Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links





[Non-text portions of this message have been removed]
4
Vic, You rock! I do remember (in spite of myself). Except for
attachments though (can't do them on yahoo groups)... could you send
that attachment to me offline?



Rob Bucek

Manufacturing Engineer

PH: (715) 284-5376 ext 3111

FAX: (715)284-4084

<http://www.dsmfg.com/>

(Click the logo to view our site) <http://www.dsmfg.com/>





From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Vic Drecchio
Sent: Tuesday, November 17, 2009 11:08 AM
To: vantage@yahoogroups.com
Cc: Rob Bucek
Subject: RE: [Vantage] SQL trigger





Rob,

See attached docs. It's a SQL query that nicely shows the most recent
500 tasks (active, completed and errored).

This is what I was chatting with you about at Perspectives.

I created a SQL view from this query. I hit the view with ASP and watch
the auto-refreshing ASP dashboard in Internet Explorer on one of my
monitors all day to monitor the system. (sample screenshot attached)

What I've done in the past is put an Update trigger on Systask.

If SysTask.TaskDescription = 'Process MRP' and the updated value for the
column Status = 'COMPLETE' then {{do your trigger}}. While MRP is
running TaskDescription will equal "ACTIVE".

Look at my query and screenshot and play with it. Look at some of the
existing triggers that Epicor put in SQL on the tables and you should be
able to piece together the syntax and logic.

Let me know if you need more help.

Vic

-----Original Message-----
From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf
Of Rob Bucek
Sent: Tuesday, November 17, 2009 11:55 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: [Vantage] SQL trigger

Since Epicor hasn't been willing at this point to give us access to
process tables such as systask (via BAM or BPM) in order to monitor when
MRP is complete, I want to create an SQL trigger that will monitor the
task status field and when it is equal to complete and the task
description equals Process MRP, to change the CheckBox01 field in a UD
table to True (or 1), so my alert attachment can fire at that time. Any
mavericks out there creating home brewed triggers or stored procedures
in their Epicor SQL database? I could use a helpful nudge to get me
started here....

Unfortunately even in V9 data directives doesn't even allow access to
these either L

Rob Bucek

Manufacturing Engineer

PH: (715) 284-5376 ext 3111

FAX: (715)284-4084

<http://www.dsmfg.com/>

(Click the logo to view our site) <http://www.dsmfg.com/>

[Non-text portions of this message have been removed]

------------------------------------

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder
and Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
<http://groups.yahoo.com/group/vantage/files/>
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]