Post reply

BCP cmds BUILDING up in cursor

  • May 4, 2012 at 2:39 am

    #273575

    I have a cursor which with each loop selects a BLOB and outputs it to a file

    using EXEC master..xp_cmdshell @bcpCommand, no_output

    trouble is instead of outputing a file with each loop of the cursor it outputs them all at the end.

    I know why this happens due to SQL needing to be able to roll back. But is there any way to commit the BCP cmd with out breaking the cursor?

  • May 4, 2012 at 8:37 am

    #1483603

    From my limited testing you can use the following code:

    SET CURSOR_CLOSE_ON_COMMIT OFF

    DECLARE @ID INT

    DECLARE test CURSOR FOR

    SELECT productID FROM product ORDER BY productID

    OPEN test

    FETCH NEXT FROM test INTO @ID

    WHILE @@FETCH_STATUS = 0

    BEGIN

    BEGIN TRAN

    DELETE FROM product WHERE ProductID = @ID

    COMMIT TRAN

    FETCH NEXT FROM test INTO @ID

    END

    It seems to work when i run it but have a look at the link on CURSOR_CLOSE_ON_COMMIT

    http://msdn.microsoft.com/en-us/library/ms184746.aspx

    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply