OK now I understand your full story. -deep_blue(BLUE) @芝加哥华人论坛:芝加哥枫下论坛 The Rolia Forum of Chicago

OK now I understand your full story.

deep_blue(BLUE)

Generally speaking, most data processes starts and are controlled by applications (desktop, web, or mobile programs). In those cases, it’s better to put follow control in application (program) layer.

However, there are also many data processes not involving any program, e.g. scheduled database jobs. There are also some data transferring between systems and it’s hard to get middle of it like your case.

Anyway, to different stories there are should be different solutions.

If you want to get real-time data, the better way is still in database trigger.

Put an AFTER UPDATE TRIGGER to the table. And use master.. xp_cmdshell to call outside. However, you cannot dirrectly call you .NET program. You should create DOS batch file like

Cd c:\YourProgramFolder
YourProgram

Then in the trigger, call the bat file.

You should put your program in database’s machine. Otherwise, you will have permission inssues.

(#3873819@0)
Last Updated: 2007-8-16
This post has been archived. It cannot be replied.

Yes -mcsdocp(MCTIS); 2007-8-15 (#3870523@0)

谢谢，怎么实现呢？我不是很熟悉SQL DataBase，在网上找没有找到，先谢谢！ -radish(radish); 2007-8-15 (#3870691@0)

Use OLE Automation. You can create VB object and call method inside Stored Proc -whereismyid(到处找ID); 2007-8-15 (#3871636@0)

触发两个字用得好-因为问题的答案就是触发器or trigger。Try Google database trigger and you'll find the answer -renjl0810(相信网络,相信Google); 2007-8-15 (#3871811@0)

看了各位的建议，查了一下资料，先试了一下trigger，从0->1时能触发，但执行exec master..xp_cmdshell后，SQL就死了，必须关机重起。什么原因呢？ -radish(radish); 2007-8-16 (#3872563@0)

good try.. :) try to avoid trigger as much as possible.. especially this type of external program which takes too long to finish. -sunday8(sunday8); 2007-8-16 {254} (#3872670@0)
you may try:
1. create a job schedule in SQL server that will scan the 0->1, where you may need another field if the upload finish, to trigger the VB program with master..xp_cmdshell
2. have your vb program to scan the database and trigger the upload.
just my 2 cents. you might need to make your trigger handler to make a call in asychronous way, opposed to the synchronous one. -tonixlaw(bigt); 2007-9-9 {215} (#3921024@0)
that means your trigger is just to give a signal to the external program and then will get back to finish the trigger handling. Otherwise, imagine that if your external program hangs, your trigger handler hangs too.

It can be implemented technically, but it would be a very, very bad design. The action should be taken in business layer, not data access layer. If you do need it, consider invoking external stored procedure from trigger. -liquid(avaya); 2007-8-15 (#3872289@0)

The better way is to implement it in other layer. -deep_blue(BLUE); 2007-8-16 {263} (#3872510@0)

Although it’s possible to implement it in trigger and use xp_cmdshell to call external executable, many problems come out, e.g. permission of calling .NET program, how to determine specific variables, and so on. Therefore the better way is put it in other layer.

你说对了，用trigger遇到问题, but不太理解other layer,这是什么意思？ -radish(radish); 2007-8-16 (#3872576@0)

I’m very busy now, I will explain it late. -deep_blue(BLUE); 2007-8-16 (#3872643@0)

thanks -radish(radish); 2007-8-16 (#3872986@0)

OK now I understand your full story. -deep_blue(BLUE); 2007-8-16 {937} (#3873819@0)
Generally speaking, most data processes starts and are controlled by applications (desktop, web, or mobile programs). In those cases, it’s better to put follow control in application (program) layer.

However, there are also many data processes not involving any program, e.g. scheduled database jobs. There are also some data transferring between systems and it’s hard to get middle of it like your case.

Anyway, to different stories there are should be different solutions.

If you want to get real-time data, the better way is still in database trigger.

Put an AFTER UPDATE TRIGGER to the table. And use master.. xp_cmdshell to call outside. However, you cannot dirrectly call you .NET program. You should create DOS batch file like

Cd c:\YourProgramFolder
YourProgram

Then in the trigger, call the bat file.

You should put your program in database’s machine. Otherwise, you will have permission inssues.

thanks again, now I understand. I will try your solution tomorrow -radish(radish); 2007-8-16 (#3874382@0)

具体是这样的，０－》１表示一个订单生产完成(由生产过程)，完成后把这个订单的相关数据传给ERP SERVER，已经用vb.net的程序实现数据传给ERP SERVER，但如何调用这个vb.net程序并把订单号传给程序？ -radish(radish); 2007-8-16 (#3872572@0)

Create a timer or job to check the flag and invoke your program in right status. The data layer and app layer should decoupled, that is what the point is. -sunstar123(sunstar123); 2007-8-16 (#3872651@0)

要是我也会这么做。 -newkid(newkid); 2007-8-16 (#3872736@0)
我已经有２个vb.net程序每２小时运行一次，本来这个查０－》１也是写在其中一个程序里的，但客户想生产一结束数据就要传到ERP SERVER,所以只能看看SQL里有没有可能实现 -radish(radish); 2007-8-16 (#3872740@0)

除了数据库之外，还有哪个程序知道“生产结束”的，就做在那里。 -newkid(newkid); 2007-8-16 (#3872776@0)
为什么不放在把 0 改成 1 的那个步骤里？ -bdbs(不多不少); 2007-8-16 (#3872812@0)

０改成１在生产用的AB PLC Program, 他没有与外部互动的程序（可能有OPC，但不是免费的），我们用PLC 自带的RSSQL，把想要的数据通过RSSQL存到SQL里，然后再处理。 -radish(radish); 2007-8-16 {115} (#3872840@0)
后台有vb程序每２小时自动运行，主要是报告原材料的消耗，但这个生产结束后可能２小时后报告生产数据到ERP，客户说说太迟了

不能缩短运行周期吗？比方说10分钟。如果怕开销太大就把这个任务分离出来。通过索引可以很快找出变动过的数据。 -newkid(newkid); 2007-8-16 (#3872853@0)

SORRY, I am not a SQL expert, do you mean "INDEX"? what is it? -radish(radish); 2007-8-16 (#3872963@0)

就是INDEX啦。假如你这个FLAG是0(未生产) 1(已生产) 2(已生产但未传输)，那么建立 INDEX 之后，要扫描 FLAG=2 就会很快。传输过的数据要改成FLAG=1。你可能有其他办法例如保存传输过的最大ID，那也是可以的。关键是要利用索引找出需要传输的数据。 -newkid(newkid); 2007-8-16 (#3872987@0)

if ２小时 is too long, how long they can ok? -win(秋天的菠菜); 2007-8-16 (#3872868@0)

客户想立刻了，因为只有报告了生产结束ERP才能包装，计算库存，发货什么的，我也想把这个任务分离出来，另写一个VB程序２０－３０分执行一次，就是怕开销太大，但SQL实现不了的话，也只能这样了 -radish(radish); 2007-8-16 (#3872961@0)

just checking a flag file or something like that, the overhead should be minimum. much better than put such a thing in a trigger. -win(秋天的菠菜); 2007-8-16 (#3872967@0)

看样子是最好不用Trigger了 -radish(radish); 2007-8-16 (#3872985@0)

and sometimes 客户 say 立刻, they might not mean 立刻 in a technical sense. maybe 10 minutes is 立刻 for them too. -win(秋天的菠菜); 2007-8-16 (#3872971@0)

good idea, I will talk to them. 2 hours is too long, how about 1 hours? still to long? Ok, 30 minute. That is it. This program will run at server that is very busy. -radish(radish); 2007-8-16 (#3873010@0)

为了解决查询间隔过长的问题，在订单传送程序中把查询新订单(QueryNewOrder）、传送新订单(TransferORder)的功能分开，中间设置一个待传送新订单队列(OrderQueue). -digitworm(digitworm); 2007-8-18 {177} (#3877866@0)
QueryNewOrder 负责查询新订单，把新订单放入OrderQueue和通知ransferORder. TransferORder接到通知后从OrderQueue取出订单, 传送，直到OrderQueue空为止。
这样传送程序不依赖于查询间隔。

RSSQL到SQL是如何实现的？能否在这个步骤检测？ -bdbs(不多不少); 2007-8-16 (#3872871@0)

对我们来说RSSQL就是个界面，在RSSQL里选出需要跟踪的数据然后连上相应的表，底层的东西看不见 -radish(radish); 2007-8-16 (#3872980@0)

Not sure if you've read this, I post it here anyway. -bdbs(不多不少); 2007-8-16 (#3873511@0)

看了问题，我激动万分，可一看答案，立刻回复原形。我们用的是SQL 2000，工业自动化产品一旦稳定，更新很慢的，为了写这个程序，我重新装的SQL 2000 & VS.NET 2003.BUT THANKS -radish(radish); 2007-8-16 (#3874375@0)

Notification might not meet your need..notification could be a smtp email notification.. -sunday8(sunday8); 2007-8-16 {138} (#3874562@0)
which means you have to do extra work before it fits into your application.
I have not used notification yet.. correct me if i am wrong..

如果是我, 我会这么做: 另建一个表, 专门存变成1时需处理的数据. 主表建立trigger, 每到变1时, 把要处理的数据insert到新表中. 用DTS或外部程序定时扫描新表, 通知客户, 同时删除已处理过的数据. 这样由于新表很小, 可以数分钟扫描一次. -poohbear(毛毛熊); 2007-8-16 (#3874637@0)

你这还是定时扫描法，完全不需要新表，只要加一种状态就好了（0,1变成0,1,2) -newkid(newkid); 2007-8-16 (#3874711@0)

如果原表大, 又外接设备, 锁来锁去的话, 几分钟扫描一次不太现实, 加一种状态改变了原始数据, 也不安全 -poohbear(毛毛熊); 2007-8-17 (#3876330@0)

表很大：有FLAG索引只需扫描少量数据；锁：只需行锁未发送的数据。如果实在是无法更新FLAG,用增量扫描ID的办法（假设ID都是递增的）也比新表好。 -newkid(newkid); 2007-8-17 (#3876636@0)

I wonder if you have HMI like RsviewSE in this manufactory. If they do, then you can set 1 event to trigger an external exe program. -yello(改用samsung了); 2007-9-17 (#3936270@0)

Using Query Notifications -tjhong(我检讨); 2007-9-21 (#3947263@0)

@Chicago

OK now I understand your full story.

Replies, comments and Discussions:

More Topics