Hanging-up of the Execute SQL Task

Question

Hanging-up of the Execute SQL Task

AlexDomashev

SSCarpal Tunnel

Points: 4968
More actions
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?

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

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

sabyasm SSCarpal Tunnel Points: 4324 More actions · Answer 1

[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]

AlexDomashev SSCarpal Tunnel Points: 4968 More actions · Answer 2

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

sabyasm SSCarpal Tunnel Points: 4324 More actions · Answer 3

[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]

AlexDomashev SSCarpal Tunnel Points: 4968 More actions · Answer 4

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.

sabyasm SSCarpal Tunnel Points: 4324 More actions · Answer 5

[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]

AlexDomashev SSCarpal Tunnel Points: 4968 More actions · Answer 6

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.

AlexDomashev SSCarpal Tunnel Points: 4968 More actions · Answer 7

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.