September 13, 2001 at 7:31 pm
When I update a column xx in table A the query hangs because of the FOR UPDATE trigger
This is the trigger:
CREATE TRIGGER aTrigger
ON A
FOR UPDATE
AS IF UPDATE(xx)
EXEC master..xp_cmdshell 'dtsrun /Sservername desk /Ndtspackagename /Uauserid /Papassword'
On its own the EXEC command runs okay with the userid and password, but when in a trigger it hangs. Does anyone know why ?
September 13, 2001 at 8:09 pm
Change xp_cmdshell to something trivial DIR *.* and do an update in Query Analyser. Once that work change xp_cmdshell to command.com/c dtsrun ???.
September 14, 2001 at 4:43 am
I think that when you EXEC it runs under the security context of the caller, not the trigger - not 100% sure of that, have to test.
Could you descibe briefly what you're running in the package? I ask because I like to see VERY lightweight code in triggers and executing DTS anything isnt very lightweight. So Im curious both to find out how your performance is once you get it running and how intense the package is.
Andy
September 14, 2001 at 10:29 am
I need to get a dump of some of the columns into a flat file on a server that cannot access the database when a column is updated. Like BULK INSERT is there any otherway to dump a table into a flat file, or must I run a DTS package ?
Also I checked, it does start writing to the file, but hangs at the record thats being updated which invokes the trigger.
September 14, 2001 at 11:11 am
Sounds like it might be waiting for a lock - sensible in my opinion as the data isn't actually in the database at that point.
If you can't read the data at any another time, could you code the trigger to write the data (or data to signify what's changed / updated) from the Inserted table into another table, then have a regular job to export the data from that one.
It is also possible use bcp to export data... but I wouldn't do it from a Trigger.
More background information would be good - I'm a little confused by what you want to do.
September 17, 2001 at 10:29 am
Looks like the trigger was not a good idea. Have got it working by calling the DTS package from another process after the update has been done and the transaction completed.
Thanks for all the info
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply