Post reply

Help Please:dts package acting wierd

  • September 5, 2008 at 12:31 pm

    #123351

    Hi,

    I'm having a problem with one of the dts package. The dts package consist of only one step which will import data from a view on one database to table on the other database on the same server.

    This package used to succeed in around 2 hours but now it has failed to succeed for about 15 days. The reason it's very hard to troubleshoot is that it just keep on running for even 5 days without any error.

    I used SP_WHO2 active, I don't see any blocking any where. I used DBCC OPENTRAN , but doesn't see any open transaction. It seems like something is blocking the operation somewhere.

    I would appreciate any help regarding this.

    Thanks,

    :w00t:

  • September 5, 2008 at 12:47 pm

    #868001

    Hi,

    I just figured out that I missed something on the question. Actually, the destination table in this case is a Heap table. Just felt like it will help to troubleshoot faster.

    Thanks in advance

  • September 5, 2008 at 4:07 pm

    #868068

    Seems almost exactly the same problem as the one I posted about this afternoon

    http://www.sqlservercentral.com/Forums/Topic564531-65-1.aspx

    Can you track if anything is happening? is p.e. the rowcount of the destination table going up?

    My suggestion would be

    1. stop the DTS

    2. issue update statistics statements for all tables that are referenced by the view you're copying from

    3. restart the DTS

    Can you also please post if you recently install SP4 of SQL-Server 2000 or not? The answer could be helpful in solving my problem.

    Hope it works for you as it did for me

    Marc

  • September 8, 2008 at 10:44 am

    #868598

    Hi Marc,

    Thanks for your response. Actually, I didn't include some of the information in my previous post so that we can concentrate only in the task with problem. I have some other tasks on the same package. Thanks to Marc that updating statistics made other tasks faster than before. But I'm still having problem for this particular task. I updated the stats for the table referenced by the view in question. Task is still running for more than 4 hours now.

    My version of SQL 2000 is "Standard Edition" with SP3.

    Thanks In Advance.

  • September 8, 2008 at 11:05 am

    #868608

    Hi Marc,

    A new puzzle in this package kind of surprised me. DTS package now failed with the error quoting destination table as Invalid object. However, when I check the database, destination table is still there and I just updated the statistics while ago and owner is "dbo" .

    I guess this is better than the last time. At least, I've an error to play with.

    Thanks in Advance.

  • September 8, 2008 at 3:23 pm

    #868754

    Just replying to make others clear that issue has been solved after running update statistics as told by Marc. My sincere thanks goes to Marc.

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

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