473,240 Members | 1,737 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,240 software developers and data experts.

xp_cmdshell from within a trigger on SQL 2000 SP4

I have a trigger that should be execute on each row insert and only if
appcode = 'I' and datasent = 0. It should execute a DTS package. The
DTS package by itself runs about 6 seconds. Trigger was created
successfuly. When I try to insert a row my db hangs. I can see that
SPID on my db is hang by SPID from master db. It doesn't complete
until I kill that SPID. Why is that?
This is the trigger that I am trying to execute:
CREATE TRIGGER myExport
ON ruExport

FOR INSERT
AS
declare @appcode varchar (10)
select @appcode = appcode from ruexport where appcode = 'I' and
datesent = 0

if @appcode = 'I'
exec master..xp_cmdshell 'DTSRun /S "KOCL384017SQL" /N "Sys_Export" /G
"{17D112A8-413E-420F-A624-3790BDFBED9F}" /W "0" /E'

go

Any suggestions?

Jan 19 '07 #1
2 4234
tolcis,

There are a couple of considerations you might want to think about here.

1. Triggers should be designed as set operations. A trigger only fires once
for each batch that is being updated. If the batch updates multiple rows,
then your trigger should account for it. As it is written, your trigger
assumes only one row is getting updated.

2. You are already selecting a set where the appcode = 'I', why the if
@appcode = 'I' comparison later in the trigger?

3. Personal architectural philosophy: I would not execute a DTS package
directly from a trigger. There are many external kinds of issues that can
cause a DTS package to fail. I would make the interaction with the DTS
package more loosely coupled with the application. An alternative would be
to have the DTS package set up a job to run once a minute ( or hour, etc.).
First job step could check to see if any rows have to be processed ( appcode
= 'I' and datesent = 0). If none need to be processed then exit the job
with success, otherwise execute the DTS package.

-- Bill

"tolcis" <ny********@gmail.comwrote in message
news:11*********************@a75g2000cwd.googlegro ups.com...
>I have a trigger that should be execute on each row insert and only if
appcode = 'I' and datasent = 0. It should execute a DTS package. The
DTS package by itself runs about 6 seconds. Trigger was created
successfuly. When I try to insert a row my db hangs. I can see that
SPID on my db is hang by SPID from master db. It doesn't complete
until I kill that SPID. Why is that?
This is the trigger that I am trying to execute:
CREATE TRIGGER myExport
ON ruExport

FOR INSERT
AS
declare @appcode varchar (10)
select @appcode = appcode from ruexport where appcode = 'I' and
datesent = 0

if @appcode = 'I'
exec master..xp_cmdshell 'DTSRun /S "KOCL384017SQL" /N "Sys_Export" /G
"{17D112A8-413E-420F-A624-3790BDFBED9F}" /W "0" /E'

go

Any suggestions?

Jan 19 '07 #2
To add to AlterEgo's response, note that the DTS package is an external
process and not running in the trigger transaction context. If the DTS
package accesses uncommitted data from the INSERT statement, it will be
blocked until the INSERT completes. However, the INSERT can't complete
until the DTS package executed from the trigger completes. This is a
deadlock that requires you to KILL the spid.

A better application design is to have the trigger insert into a queue table
like AlterEgo suggested and have a separate perform the process
asynchronously.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"tolcis" <ny********@gmail.comwrote in message
news:11*********************@a75g2000cwd.googlegro ups.com...
>I have a trigger that should be execute on each row insert and only if
appcode = 'I' and datasent = 0. It should execute a DTS package. The
DTS package by itself runs about 6 seconds. Trigger was created
successfuly. When I try to insert a row my db hangs. I can see that
SPID on my db is hang by SPID from master db. It doesn't complete
until I kill that SPID. Why is that?
This is the trigger that I am trying to execute:
CREATE TRIGGER myExport
ON ruExport

FOR INSERT
AS
declare @appcode varchar (10)
select @appcode = appcode from ruexport where appcode = 'I' and
datesent = 0

if @appcode = 'I'
exec master..xp_cmdshell 'DTSRun /S "KOCL384017SQL" /N "Sys_Export" /G
"{17D112A8-413E-420F-A624-3790BDFBED9F}" /W "0" /E'

go

Any suggestions?
Jan 20 '07 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: dananrg | last post by:
I've created a small company database where the tables reside in a SQL Server database. I'm using Access 2000 forms for a front end. I've got a System DSN set-up to SQL Server and am using links...
3
by: Gustavo Randich | last post by:
The following seems to be a bug. The execution returns rows 1,2. It should return 1,1. In fact, if I run the code within a stored procedure alone (not in a trigger), the loop doesn't overwrite the...
1
by: ligi168 | last post by:
Hi,can anybody help me? One question has confused me for a long time.That is we can use api within excel 2000 by delare syntax,but some dll file cannot be referred and an error appeared like "no...
0
by: Freddy | last post by:
In my Access db (which I have created to help me learn Greek) I am using text box controls on a form. Some of these controls require English input and some require Greek input. Currently I have...
0
by: ismailc | last post by:
hi, i need help please! i have a macro within an excel file(c:\test.xls) called macro1, how do i get it to execute? xl_app.run macro1 (the area of concern, this does not work) ...
1
by: akshaysk | last post by:
HI, I am new to Visual Basic. For my Assignment i am supposed to write a visual basic program using excel. I have tried many scripts, but receive errors. Can anyone tell me of any website that...
1
by: shark | last post by:
hi, does anybody know how to enable/disable xp_cmdshell in sql server 2000. i our server this sp has been disbaled for security puropse.but i need to use it in a job.so please can any body...
0
by: fpwilliams71 | last post by:
I have tried to write functions within SQL 2000 that would mimic the DSum funcion found in MS Access without any luck. This I could achieve withing Vb 6.0. I would rather have it done within MS SQL...
1
by: AngieMP | last post by:
Hi Guys I have inherited an Access DB in a very poorly state! I have offered to make some changes to it for a local charity but have got stuck on this error it seems to be creating for all new...
4
by: Wrighttrak | last post by:
In VBA behind a Command Button I want to copy a directory to a new directory. Ive tried FileCopy but I dont know how to use *.* in that command
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.