Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Aug 2002
    Location
    Pittsburgh, PA
    Posts
    10

    Unanswered: BCP within a Trigger Freezes

    Hey all,

    Has anybody run into an issue where a call to BCP within a trigger freezes Query Analyzer? When I try to stop the execution of the update statement, the program then crashes.

    I have no problem running BCP from within Query Analyzer itself.

    Here is the code I am using
    -------------
    CREATE TRIGGER tr_ack ON [dbo].[Track_tb]
    FOR UPDATE
    AS
    if update(AckStatus)
    insert into ins_hold
    select e.dc + e.po, e.emp,
    case AckStatus
    when 2 then 'Acknowledged'
    when 6 then 'Overdue'
    end
    from eack as e, document_tb as d, inserted
    where (inserted.DocumentKey = d.DocumentKey) and
    (d.DocumentName = e.DocNum)

    exec master..xp_cmdshell 'bcp ins_hold out c:\temp\testfile.txt -c -T -STestServer'

    drop table ins_hold
    -----------------

    I have also tried this with global temp tables rather than a permanent one, doing a select query and using queryout from BCP and receive the same type of crash.

    I can do a select * from ins_hold within the trigger and the results display correctly. I can do bcp out from within Query Analyzer using the data from the ins_hold table within the trigger and it works correctly.

    The update statement I'm using within Query Analyzer to test is:
    ----------
    update Track_tb
    Set AckStatus = 2
    where DocumentKey = (
    select DocumentKey
    from Document_tb
    where DocumentName = '0018830')
    ---------

    Thanks for any help you can provide, I'm greatly appreciative.

    -Greg

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    when issuing the command:

    exec master..xp_cmdshell 'bcp ins_hold out c:\temp\testfile.txt -c -T -STestServer'

    you need to use a three part naming convention for the table <DB NAME>.<OWNER NAME>.<TABLE NAME>.

    What version of SQL Server are you using?
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Aug 2002
    Location
    Pittsburgh, PA
    Posts
    10
    Using SQL 2000 for this (sorry I didn't include that originally)

    I did try doing what you suggested before and ran into the same problem.

    Thanks for the reply.

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Okay, go back to using a three part name, BCP will not work otherwise.

    xp_cmdshell runs under the SQL Server Agent account. HAve you created that account on your SQL server?

    I just tried issueing a BCP command via xp_cmdshell (Cut & past of your code) and everything worked.

    have you tried xp_cmdshell'dir c:\temp\*.*'? I tried it on my server and get a nasty access denied message.

    This might be a bugger to track down so hang in there!
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Aug 2002
    Location
    Pittsburgh, PA
    Posts
    10
    Hey Paul,

    Went back to the 3 part naming convention. The Server Agent account is set up on the system. I also tried doing the xp_cmdshell 'dir c:\temp' from within the trigger and it returned the results without any hitches.

    When you say you tried the BCP command of my code, do you mean that you tried that within a trigger or just from Query Analyzer? It seems to work fine for me within Query Analyzer, just gets hitched up when it tries to run from within a trigger.

    I'm going to run it again today and put all the traces on to see if I can come up with something in there. I tried this before, but didn't see anything indicative of why it may be freezing up.

    For the time being, I've been using a simple VBScript program on a 2 hour schedule to accomplish the same task, but I'd much rather have it running within the trigger so the results are instantaneous rather than having a potential 2 hour wait.

    Thanks again for the help,

  6. #6
    Join Date
    Aug 2002
    Location
    Pittsburgh, PA
    Posts
    10
    Okay, did some more testing.

    I also tried to do an out from an ISQL command.

    It worked fine from Query Analyzer, but when I tried to have it fire from within the trigger, it again hung and eventually froze.

    That command was:
    ------------
    declare @str
    set @str = 'isql -Q"select * from ins_hold" -E -oc:\temp\testfile.txt'
    exec master..xp_cmdshell @str
    ------------

    So I was wondering if perhaps it isn't configured for write access through sa, so I then did a:
    ------------
    exec master..xp_cmdshell 'dir c:\temp > c:\temp\testfile.txt'
    ------------
    within the trigger and it executed correctly and didn't freeze.

    Doing more research for the time being to see if I can find anything on the 'net related to this (haven't had any luck so far).

    Thanks,

    Greg

  7. #7
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    No, I did not try the BCP via a trigger. I do think you hit apon the problem though.

    What happens if you add th "-o" parameter to your BCP command? My WAG is that if any results are returned form xp_cmdshell the trigger will hang. The "-o" will redirect output from BCP to a file eliminating any data returned from xp_cmdshell. Worth a shot!
    Paul Young
    (Knowledge is power! Get some!)

  8. #8
    Join Date
    Aug 2002
    Location
    Pittsburgh, PA
    Posts
    10
    Okay, tried using the -o option to see if I can get something returned from BCP, but nothing gets generated (in either the output or log file). When I run from within Query Analyzer, it posts the return text, so it's again limited to being an issue with the Trigger itself.

    So back to the drawing board (or Internet if you prefer).

    Regardless of whether I get this worked out or not, thanks Paul--you've been a tremendous help thus far.

    -Greg

  9. #9
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Okay, I couldn't find anything that said you couldn't do this so I tried setting up a table and trigger to simulate the problem...

    Code:
    ------------------------------------------------------------------------------------------------------------------------------------------
    exec master..xp_cmdshell 'dir f:\MSSQL2k'
    go
    create table TestTable(f1 int)
    go
    create trigger Tigger on dbo.TestTable
    for INSERT, UPDATE, DELETE
    AS
    exec master..xp_cmdshell 'bcp dba.dbo.ProcedureUsage out f:\MSSQL2k\temp.txt -c -T -S hgw2db17 -o f:\MSSQL2k\temp.out', no_output
    go
    insert into TestTable values(1)
    go
    exec master..xp_cmdshell 'dir f:\MSSQL2k'
    go
    ------------------------------------------------------------------------------------------------------------------------------------------

    Results:
    ------------------------------------------------------------------------------------------------------------------------------------------
    output
    --------------------------------------------------------------------------------------------
    Volume in drive F is New Volume
    Volume Serial Number is 183C-AD6B
    NULL
    Directory of f:\MSSQL2k
    NULL
    08/21/2002 11:54a <DIR> .
    08/21/2002 11:54a <DIR> ..
    02/22/2002 03:23p <DIR> MSSQL
    08/21/2002 12:02p 108 temp.out
    08/21/2002 12:02p 4,319 temp.txt
    2 File(s) 4,427 bytes
    3 Dir(s) 12,744,605,696 bytes free
    NULL

    output
    --------------------------------------------------------------------------------------------
    Volume in drive F is New Volume
    Volume Serial Number is 183C-AD6B
    NULL
    Directory of f:\MSSQL2k
    NULL
    08/21/2002 11:54a <DIR> .
    08/21/2002 11:54a <DIR> ..
    02/22/2002 03:23p <DIR> MSSQL
    08/21/2002 12:02p 108 temp.out
    08/21/2002 12:02p 4,319 temp.txt
    2 File(s) 4,427 bytes
    3 Dir(s) 12,744,605,696 bytes free
    NULL
    ------------------------------------------------------------------------------------------------------------------------------------------
    Any chance you could try the above code on your server? 'F:\MSSQL2K' is the "root" for my SQL Server install. Also, I remembered that you can add the "no_output" parameter to xp_cmdshell, maybe that would help.
    Last edited by Paul Young; 08-21-02 at 14:06.
    Paul Young
    (Knowledge is power! Get some!)

  10. #10
    Join Date
    Aug 2002
    Location
    Pittsburgh, PA
    Posts
    10
    Okay, ran through the code which you had there (couple of changes to reflect my setup obviously). Worked perfectly--within the trigger and everything.

    So that being said, I did the same thing with the table I had created in the previous trigger and it worked fine as well.

    So now I'm wondering where in my old code I'm getting hitched up at since it is not the BCP actually freezing up (though I was able to do selects from within the trigger previously and display the data I was attempting to write out via BCP).

    I also tried having two triggers:
    One on track_Tb off of updates which inserts the values into ins_hold
    One on ins_hold off of inserts which bcp's itself out to a data file.

    Sames results with those.

    Both processes seem to work correctly independent of one another correctly, but freeze up whenever you attempt to use them together.

    Thanks again,

    Greg
    Last edited by gregorybritt; 08-21-02 at 15:27.

  11. #11
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Glad to hear you got everything to work. Sometimes the bloody forest blocks the view of the trees! My advice, press on to your final solution!
    Paul Young
    (Knowledge is power! Get some!)

  12. #12
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    I don't know if this TRIGGER is used in production and is being fired by an event within a client application. If it is you may want to rethink executing BCP from within a TRIGGER. The event that fired the TRIGGER will hold locks on the records until the TRIGGER has completed. Which means that locks are held until the BCP has finished. This could and most likely will degrade performance and cause potential deadlocks.
    MCDBA

  13. #13
    Join Date
    Aug 2002
    Location
    Pittsburgh, PA
    Posts
    10
    Originally posted by achorozy
    I don't know if this TRIGGER is used in production and is being fired by an event within a client application. If it is you may want to rethink executing BCP from within a TRIGGER. The event that fired the TRIGGER will hold locks on the records until the TRIGGER has completed. Which means that locks are held until the BCP has finished. This could and most likely will degrade performance and cause potential deadlocks.
    It is in fact going to be used in a production environment, with the table being updated from a client application. However, the table being updated is Track_tb, and I am inserting data into ins_hold and running BCP off of the ins_hold table. Will this table be locked as well?

    Also, when the insert is performed on the ins_hold table from within the trigger, will the trigger hold this lock until the end of the execution of it? If so, that would explain why the system seems to hang up when I try to do a BCP with that data.

    Thanks for the advice and help,

    Greg

  14. #14
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    What about using DTS and a scheduled job to create the text file every 5 minutes or so? You could even go a bit further and use the trigger to record the last time a change was made to the table and the job could check to see if the DTS was even needed!

    OR

    Set up a DTS package to export the data to a test file. Set up a job to launch the DTS package. Create a trigger on the ins_hold table to launch the job two or so minutes in the future. This will allow you to get in and out of your trigger quickly, keep your text file current, and prevent excessive data io when frequent updates are performed. I would probably add a table to record the last time the job ran so I could guarantee a text file write every ten minutes or so during peek times.
    Last edited by Paul Young; 08-22-02 at 09:25.
    Paul Young
    (Knowledge is power! Get some!)

  15. #15
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    To answer your question - yes. When an SQL statement (INSERT/UPDATE/DELETE) causes a trigger to fire and that trigger performs a database operation that in turn fires another trigger, etc., etc.. This become one big, long transaction and all locks are held.

    You can have the trigger insert data into ins_hold table, but you'll need to create a scheduled job that will peform the BCP on a time interval. You can use DTS as Paul has stated or you can modify the current code that does the BCP into a stored procedure and schedule it with SQL Server Agent. Either way is fine.
    MCDBA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •