S**H
发帖数: 1256 | 1 requirement is straight: when table A has Insert/Update --> do some string
manipulation-->then output the result to a .txt file.
current plan: use trigger on table A, --> trigger then execute one stored
procedure to do the string manipulation and output the result to a .txt file.
problem is : the stored procedure could only be executed successully
manually and the *.txt is created.
if it is called though the trigger. The application
hangs there...
the stored procedure syntax is like this:
exec xp_cmdshell
'bcp " select * ........." query out " tmp.txt
exec xp_cmdshell 'type tmp.txt >>text.txt'
....bcp is called 3 times in here for string manipulations
Thanks thanks . |
j*****n
发帖数: 1781 | 2 xp_cmdshell requires CONTROL SERVER permission so the risk is high hence I
was mentioning my doubt in your previous post...
my question is, why the requirement is so mean that having all actions
logged into text file instead of table? who the hell wants to look into
individual text files? dude, he must out of his mind!
why don't do the log into table via trigger? it is easy to implement and
easy to search to. even it has to have into text files, why don't just
periodically dump out once a while, say, daily? |
g***l
发帖数: 18555 | 3 是的，这个用TRIGGER的人基本不懂数据库，文件操作很慢而且对读写权限有要求，何况你还要创建文件，数据库是用来存数据的，弄些TEXT FILE不是自找麻烦吗？
【在 j*****n 的大作中提到】
: xp_cmdshell requires CONTROL SERVER permission so the risk is high hence I
: was mentioning my doubt in your previous post...
: my question is, why the requirement is so mean that having all actions
: logged into text file instead of table? who the hell wants to look into
: individual text files? dude, he must out of his mind!
: why don't do the log into table via trigger? it is easy to implement and
: easy to search to. even it has to have into text files, why don't just
: periodically dump out once a while, say, daily?
|
S**H
发帖数: 1256 | 4 thanks all !!!
1. but the *.txt file need to be delivered to a third party/application.
don't want them to check the database.
Any Idea ? thanks a lot again. thanks!!!
so I select the message into a table, then SSIS to .txt file.
but they want Real Time......
2. another syntax help here: kind of realize that the trigger hang the
process is because of the table DeadLock.
But I can't query the INSERTED table in BCP command. even tried to use
variable instead.
BCP "SELECT * from INSERTED" queryout....
it will complain can't find the INSERTED
thanks a lot |
g***l
发帖数: 18555 | 5 搞个LOG TABLE，写到TABLE做成个，然后用EMAIL或者SSIS送出去不就得了，搞什么文
件 |
j*****n
发帖数: 1781 | 6 i won't like to see thousands emails come in every day... haha
【在 g***l 的大作中提到】
: 搞个LOG TABLE，写到TABLE做成个，然后用EMAIL或者SSIS送出去不就得了，搞什么文
: 件
|
g***l
发帖数: 18555 | 7 一般是有问题的时候才发EMAIL。正常的TRANS TRIGGER了发什么EMAIL，比如有不清楚
的PROCESS删除了记录
【在 j*****n 的大作中提到】
: i won't like to see thousands emails come in every day... haha
|
S**H
发帖数: 1256 | 8 Thanks you all for the wise direction. Finally convince them to drop the
trigger solution.
Another question is:
if 1. stored all the info in a Table. ( will use a stored procedure to
constantly update the table)
2. then use SSIS to retrieve the data from that table for sending out.
would that cause the deadlock?
I am really a rookie. Thanks again. |
B*****g
发帖数: 34098 | 9 让公司雇人吧
【在 S**H 的大作中提到】
: Thanks you all for the wise direction. Finally convince them to drop the
: trigger solution.
: Another question is:
: if 1. stored all the info in a Table. ( will use a stored procedure to
: constantly update the table)
: 2. then use SSIS to retrieve the data from that table for sending out.
: would that cause the deadlock?
: I am really a rookie. Thanks again.
|
g***l
发帖数: 18555 | 10 为什么一写就要给人送TEXT啊，如果是连续不断地写当然是会BLOCK了，你目的是什么
，是怕人用你的TABLE吗还是不知道有谁在写你的TABLE？多久写一次，如果一个小时写
一次，EMAIL是可以的。如果一秒一次，EMAIL都能把SERVER弄DOWN了。你不是ROOKIE，
你是个菜鸟。LOL |
|
|
B*****g
发帖数: 34098 | 11 yao hou dao
【在 g***l 的大作中提到】
: 为什么一写就要给人送TEXT啊，如果是连续不断地写当然是会BLOCK了，你目的是什么
: ，是怕人用你的TABLE吗还是不知道有谁在写你的TABLE？多久写一次，如果一个小时写
: 一次，EMAIL是可以的。如果一秒一次，EMAIL都能把SERVER弄DOWN了。你不是ROOKIE，
: 你是个菜鸟。LOL
|
g***l
发帖数: 18555 | 12 要想找到一个好的SOLUTION就要把问题说清楚，否则就是乱来。
【在 B*****g 的大作中提到】
: yao hou dao
|
S**H
发帖数: 1256 | 13 LOL.
谢谢大家的耐心哈！
是两个不同的application. users 通过 application A 的interface 修改了数据 （
backend 用的是 SQL server ). 希望 application B 可以 Real Time 收到 Update。
（ backend 用的是一个没听过的database.）
两个application 是不同公司的产品。
之前一直在用VB scripts 在两个applicatons 之间鸡毛信。用*.txt delivered. 隔一
阵子传一次。
公司能让我做，我就敢上来问问题。大家见笑了。
thanks again ！ |
g***l
发帖数: 18555 | 14 捕捉CHANGE是在数据库中进行的，而不是APPLICATION之间，只要在TABLE中记录是谁改
的什么时间就可以了。还是那句话，改的频率是SOLUTION的关键，一天改一次还是一秒
改一次差别大多了。
【在 S**H 的大作中提到】
: LOL.
: 谢谢大家的耐心哈！
: 是两个不同的application. users 通过 application A 的interface 修改了数据 （
: backend 用的是 SQL server ). 希望 application B 可以 Real Time 收到 Update。
: （ backend 用的是一个没听过的database.）
: 两个application 是不同公司的产品。
: 之前一直在用VB scripts 在两个applicatons 之间鸡毛信。用*.txt delivered. 隔一
: 阵子传一次。
: 公司能让我做，我就敢上来问问题。大家见笑了。
: thanks again ！
|
a9
发帖数: 21638 | 15 没听过的数据库你是怎么往里导.txt的？频率是多少？如何保证实时呢？
【在 S**H 的大作中提到】
: LOL.
: 谢谢大家的耐心哈！
: 是两个不同的application. users 通过 application A 的interface 修改了数据 （
: backend 用的是 SQL server ). 希望 application B 可以 Real Time 收到 Update。
: （ backend 用的是一个没听过的database.）
: 两个application 是不同公司的产品。
: 之前一直在用VB scripts 在两个applicatons 之间鸡毛信。用*.txt delivered. 隔一
: 阵子传一次。
: 公司能让我做，我就敢上来问问题。大家见笑了。
: thanks again ！
|
S**H
发帖数: 1256 | 16 现在我做的就是在original Table 放了trigger,跑stored procedure ,照大家说的最
后能个message Table, 根据每个transaction 放做好的message.
现在就是怎么把data 送出去.
以前用的Application B 那边也要跑个scipts 来load 送过来的 *.txt.很久一次. 他
们要是要我写个支持 publish/subscribe 的,直接 "杀"了我好了.
1. 没算正经用过SSIS, Export/Import wizard 折腾过. 是不是不管application B 用
的谁家database, 都可以把data 直接export到destination Database 里 呢?
2. 如果不能直接export, 他们好像又不许applicationB 直接Access Application A
的SQL server. 那是不是我再写
个 perl scripts 去读 message Table, 每个transaction, 产生一个单独的
*.txt . 然后用windows job agent run the perl ??
3. how Real time ? I don't know. Application A 就是一些 不多的users
在该.
临时被拉来颠被做这个,水平凹. 尽力吧... |
j*****n
发帖数: 1781 | 17 alright, it was close to the solution I thought. Well, let's go this way...
1. a log table with Identity column as the primary key and it is the
clustered index.
2. this log table was inserted via trigger from the table is monitored.
3. a SSIS or BCP job that runs periodically, say, once a minute.
3.1 step 1: select max ID from logTable, if thisID > lastSavedID, then
3.2 step 2: select * from logTable with (NOLOCK) where ID between
lastSavedID + 1 and thisID
3.3 lastSaveID = thisID for next job occurrence.
Note: 3.1 is read committed and 3.2 is read uncommitted that why you see the
NOLOCK hint present.
dude, where is my Baozi?
【在 S**H 的大作中提到】
: Thanks you all for the wise direction. Finally convince them to drop the
: trigger solution.
: Another question is:
: if 1. stored all the info in a Table. ( will use a stored procedure to
: constantly update the table)
: 2. then use SSIS to retrieve the data from that table for sending out.
: would that cause the deadlock?
: I am really a rookie. Thanks again.
|
S**H
发帖数: 1256 | |
g***l
发帖数: 18555 | 19 其实你弄个HISTORY TABLE好了，UPDATE INSERT都记录下来，避免不同的APP之间互相
扯皮，谁动我的蛋糕？找起来也好找，骂街也有证据。 |
S**H
发帖数: 1256 | 20 谢谢提醒. original Table--> historoy Table--> message Table.
什么是包子?
大家 开心平安 2012 ! |
j*****n
发帖数: 1781 | 21 //sigh...
if this is not your MAJIA then you don't have enough 伪币 for baozi...
anyway, thanks for your kindly greetings.
【在 S**H 的大作中提到】
: 谢谢提醒. original Table--> historoy Table--> message Table.
: 什么是包子?
: 大家 开心平安 2012 !
|
