Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Posts
    6

    Unanswered: bcp operation within transaction

    hello,
    I have a problem where I am calling the BCP utility to write a table to a file. I then need to delete the rows of the table. but not all of them. This all works fine. I've been asked to place this into a transaction..incase a piece fails. When I do that...SQL server hangs. I must shutdown SQL Server.
    Any idea why that would happen. I am using the xp_cmdshell stored procedure to invoke bcp utility within a stored procdure. The procedure is executed every 15 minutes to provide files to an outboard system.

    Salik.

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    BCP operation is not affected by a transaction ....
    This might help...
    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=31640
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well....

    wouldn't you say that it's an implicit TRANS?

    I mean if the bcp fails in the middle (in it's own spid) then that rolls back..

    But as Enigma has said...if you do

    BEGIN TRAN

    master..xp_cmdshell 'bcp....

    ROLLBACK TRAN

    IF the bcp was successful, then you won't be able to roll it back...

    EDIT:

    And....

    When I do that...SQL server hangs
    I would say that's not true...do sp_lock

    I would say your spids are blocking each other...

    been there...done that.....
    Last edited by Brett Kaiser; 02-18-04 at 10:10.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Well ... its a thing to be left alone ...
    as you say ... been there .. done that (discussion)
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #5
    Join Date
    Feb 2004
    Posts
    6
    My main issue is that I am writing out data based on a 'events' table which tracks the changes users make to a product table. from the time I have built the strings and inserted them into a temporary table and then output the file, then removed the 'events' - a user could change a field in a product I am writing out - which would be missed by the system. Not good.

    The only thing I can think of is to lock the appropriate rows in the product table for the duration. WHich I'm not aware of the syntax.

    Any other strategies?

    Salik.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Data is fluid ny bature...what's the difference if it's updated during your export, or immediatley after...the data will still be changed...

    And is your issue a matter of concurrency? Why build a temp table?

    Also why not schedule a batch window? Grab the date from the system....use that as the window close date, grab all rows added or updated between the last time the window close and this windoes close...

    So if they update it during your export it wouldn't be part of this batch...

    How long is your transaction...and how many rows on average do you export?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Feb 2004
    Posts
    6
    The output data is '*' delimited for another system and requires padding of numbers, calculation etc.. that's why I build the strings into a temporary table and then ship the temp table out using bcp.

    We use triggers to track the events a user makes to a product. on certain fields a product event is created. and on others a price event. The price events are held util evening processing as they can only be sent out once a day. the product files are output every 15 minutes. There are four different files output at the same time, ecah containing slightly different data.

    The events table prevents duplicates ie: we don't append. if a existing price event exists we don't add another row.

    when I have finished the bcp the appropriate rows are removed from the events table.

    sooo - between the time the row for the product has been read and the time I delete the events row for that product. and event could happen...no duplicate would be added..and the new data not written out and the system thinks it has.

    I could snatch the rows from the events table into a temptable. delete them and if an error occurs add them back in to the events table...

Posting Permissions

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