Post reply

Hanging-up of the Execute SQL Task

  • June 16, 2010 at 6:17 am

    #222869

    SQL Server 2005 (9.0.4053)

    I have "Delete" query like

    Delete dbo.MyTable

    From dbo.MyTable t1

    Inner join SecondDb.dbo.MyTable t2 on t1.Id>t2.Id

    Execution time of this query in SQL Management Studio is less than 1 second.

    When I put this query into Execute SQL Task (Direct Input SQLStatement) then this task can't be completed (take infinite amount of time)

    - so I need to stop the execution process.

    What can be the reason for that?

  • June 16, 2010 at 7:19 am

    #1180950

    [font="Comic Sans MS"]

    Are you sure the connection being resolved properly and the statement is being executed at all? You can see the progress of the particular component and get an idea of the problem.

    [/font]

    [font="Comic Sans MS"]--
    Sabya[/font]

  • June 16, 2010 at 7:26 am

    #1180959

    When I disable this component all work fine.

    And when I looking to the Profiler Trace I see the same query that when I execute it from SSMS

  • June 16, 2010 at 7:33 am

    #1180965

    [font="Comic Sans MS"]

    I meant to say - when you don't disable the component, does this sql statement ever gets executed at the first place? You can see the progress bar on how the component being executed. My gut says - it might be an issue with the connection manager and the sql statement is not being fired at all.

    If this is not the case and the statement being fired fine - you can use sql profiler to see what is actually causing the bottleneck.

    [/font]

    [font="Comic Sans MS"]--
    Sabya[/font]

  • June 16, 2010 at 7:37 am

    #1180969

    Yes, I understand you.

    I'm looked to the Profiler and Activity Monitor (in SSMS) and I see only one statement which is executing while I'm not stop it.

  • June 16, 2010 at 8:14 am

    #1180997

    [font="Comic Sans MS"]

    Can you replace the sql statement to something like 'select getdate()' and see if this still hangs?

    [/font]

    [font="Comic Sans MS"]--
    Sabya[/font]

  • June 16, 2010 at 8:27 am

    #1181019

    Hm...interesting tip.

    I did it.

    When I replaced statement to something simple (like select getdate()) - component executed immediately.

    When I return original delete statement - component again executed infinite.

  • June 16, 2010 at 8:54 am

    #1181068

    I'm found solution, but I still don't understand the reasons. 🙂

    I'm changed the SQLStatement to next

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    DELETE...

    GO

    Thanks for the participation.

Viewing 8 posts - 1 through 7 (of 7 total)

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