Status Bar shows "Executing Query" but the result is viewed through other query windo

Question

Status Bar shows "Executing Query" but the result is viewed through other query windo

karthik babu

Hall of Fame

Points: 3335
More actions
December 3, 2013 at 4:25 am

#301039

I have peculiar question...
The query windows shows "Executing Query..." but when the result is viewed though another window, I found its been updated as if the query is successfully completed.
Please explain..
______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂

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

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

Grant Fritchey SSC Guru Points: 398694 More actions · Answer 1

Missing a commit statement? You used dirty reads (read uncommitted)? Something along these lines could easily explain it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

karthik babu Hall of Fame Points: 3335 More actions · Answer 2

May be.. but I didn't use begin transaction to use commit.. should I include begin tran... end tran with commit..?

Else that query will continue running indefinitely? :w00t:

______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂

Lowell SSC Guru Points: 323504 More actions · Answer 3

karthik babu (12/3/2013)
May be.. but I didn't use begin transaction to use commit.. should I include begin tran... end tran with commit..?
Else that query will continue running indefinitely? :w00t:

is the update in a (gasp) cursor so, you can see the row by row results in the other window, and it's no obvious which row is currently being executed int eh main update command?

what is the exact command running, and how are you viewing the results in the other window? with nolock, like Grant was asking?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

karthik babu Hall of Fame Points: 3335 More actions · Answer 4

thanks for your interest...

Please check the below code..

set rowcount 40000

Update T1 SET sName = t2.sName

from dbo.t_table1 T1 INNER JOIN dbo.t_table2 T2 ON t1.tID = t2.tID

while @@rowcount>0

begin

set rowcount 40000

Update T1 SET sName = t2.sName

from dbo.t_table1 T1 INNER JOIN dbo.t_table2 T2 ON t1.tID = t2.tID

END

set rowcount 0

Here I am updating 40k rows per batch since its a pretty large table. After couple of hours I am seeing the updated records through another window however the query hasn't stopped running..

______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂

Lowell SSC Guru Points: 323504 More actions · Answer 5

karthik babu (12/3/2013)
thanks for your interest...
Please check the below code..
set rowcount 40000
Update T1 SET sName = t2.sName
from dbo.t_table1 T1 INNER JOIN dbo.t_table2 T2 ON t1.tID = t2.tID
while @@rowcount>0
begin
set rowcount 40000
Update T1 SET sName = t2.sName
from dbo.t_table1 T1 INNER JOIN dbo.t_table2 T2 ON t1.tID = t2.tID
END
set rowcount 0
Here I am updating 40k rows per batch since its a pretty large table. After couple of hours I am seeing the updated records through another window however the query hasn't stopped running..

you don't have a WHERE statement, so it will keep updating the same 40K rows forever.

your command probably need something like this to exclude already updated rows:

UPDATE T1

SET T1.sName = t2.sName

FROM dbo.t_table1 T1

INNER JOIN dbo.t_table2 T2

ON t1.tID = t2.tID

WHERE sName <> t2.sName --< this is the important part!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Grant Fritchey SSC Guru Points: 398694 More actions · Answer 6

Ah, infinite loop. Yeah, that'll keep executing.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

karthik babu Hall of Fame Points: 3335 More actions · Answer 7

Oh! I couldn't believe as I thought that the condition "@@rowcount > 0" will take care of it.. however I will try using a condition..

Thank you all for your contribution 🙂

______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂