Post reply

Can we use Cursor in DTS packages

  • February 5, 2008 at 2:43 pm

    #120246

    Hey Guys

    i have written a cursor to pull some info, it is working fine when run through Query Analyzer, if i put the same script in my DTS Package it is giving only one row why it is so any body have ideas

    thanks in advance

  • February 5, 2008 at 9:18 pm

    #775413

    Are you talking about Query Analyzer in SQL 2000? We do use cursors in DTS without any issue.

  • February 5, 2008 at 11:07 pm

    #775427

    Can you post the code in question? It may be that it can be done without a cursor at all.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 7, 2008 at 5:36 pm

    #776235

    I experienced the same issue this week and found that the resolution was simply to use SET NOCOUNT ON in the stored procedure I was calling in the DTS package 'Execute SQL Task'. As you have experienced code was working fine when executed in Query Anayzer where it is able to return the informational result set (e.g. 15 row(s) affected). However when called from the DTS package the informational result confuses the task and the cursor was halting after the first loop.

    Rob Stocks

  • February 8, 2008 at 8:55 am

    #776474

    You could also script an ADO recordset and move through each row, applying what you need to record by record.

Viewing 5 posts - 1 through 4 (of 4 total)

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