Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004
    Location
    Quebec, Canada
    Posts
    7

    Unanswered: DTS Execute SQL Task freezes

    Hi all !

    Hi have a package with 3 steps :
    1 - import data from flat file into a temp table
    2 - copy selected data from the temp table to permanent tables
    3 - truncate temp table

    The task #2 is a SQL statement

    Code:
    INSERT INTO MODELS (MODEL_ID)
    SELECT DISTINCT MATNR
    	 FROM STANDARD
     WHERE MATNR NOT IN (SELECT MODEL_ID FROM MODELS)
    where STANDARD is the temp table and MODELS a permanent one. My problem is this simple task freezes all the time. It stalls on 'Starting' and stays in this state forever when I execute the package.

    Any idea why ?

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Questions:
    1. How large is the MODELS table?
    2. How large is the STANDARD table?
    3. Is the MODEL_ID column in the MODELS table a primary or alternate key?
    4. If you start this job, open a QA session, and execute sp_who2, is your table load being blocked by another process?

    If the MODELS table is not overly large, you could execute a nolock count on MODELS.MODEL_ID to see if rows are being added to the MODELS table without throwing any additional locks onto the table.

    If you have a large quantity of inserts, maybe you want to break them up into smaller batches of about 1K - 5K so that the tran log doesn't have to auto expand.

    Without more information on rowcounts, table sizes, table indexes, etc. even a simple request for help is difficult to answer.

  3. #3
    Join Date
    Jul 2004
    Location
    Quebec, Canada
    Posts
    7
    MODELS HAVE ONLY 1 field MODEL_ID varchar(15) wich is the PK. It has 1706 records.

    STANDARD has 9 fields with no PK (because there's some duplicates). It has about 23000 records

    There's no other process locking the DB

    It seems to happen because there's no row to be updated, no new MODEL_ID in the standard table. The inner query returns no row

  4. #4
    Join Date
    Jul 2004
    Location
    Quebec, Canada
    Posts
    7
    I'm stupid !!!

    The problem was very simple, in the workflow properties of the first import task, I was closing the connection on completion.....

Posting Permissions

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