Database Locked?

[daddio2005]

Hi

I am running the following code to UPDATE a few rows in a table. The corresponding INSERT statement runs fine and many other UPDATE statements work fine so I know the permissions are OK.

Any Ideas? It doesnt throw any errors up, just times out!

strSQL = "select * from pages where page_id=" & ref2
adoRs.Open strSQL, adoCn
if not adoRs.EOF then
pos=adoRs("pos")
end if
adoRs.Close

if ref1="Before" then
new_pos = pos*1 - 0.5
else
new_pos = pos*1 + 0.5
end if

strSQL = "UPDATE pages set pos=" & new_pos & " WHERE page_id=" & current_id
adoCn.Execute strSQL

strSQL = "select * from pages where parent_page_id=" & parent_page_id & " AND pos<>0 order by pos ASC"

adoRs.Open strSQL, adoCn
n = 0

do while not adoRs.EOF
n = n+1
strSQL = "UPDATE pages set pos=" & n & " WHERE page_id=" & adoRs("page_id")
adoCn.Execute strSQL
adoRs.MoveNext
loop

adoRs.Close


IT APPEARS TO CRASH WITHIN THE DO WHILE LOOP ON THE LINE "adoCn.Execute strSQL".

Any ideas?
Thanks.

 

[mrdenny]

While it's running open query analyzer and see if the process is being blocked, what the wait reason is, etc by using the sp_who2 procedure.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[gmmastros]

You can probably implement the entire logic within a SQL Server stored procedure. Doing this will probably prevent the locking issues but will absolutely make this function faster.

Please explain in more detail what you are trying to accomplish. Posting some sample data (with expected results) would be helpful.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[druer]

Have you tried turning on the SQL Profiler to find out what is coming through to the server? I know I've had cases before where the loop didn't terminate properly for some reasons I can't recall.

 

[daddio2005]

So is there anyway I can un-lock the row? I dont understand why this section of code works on another page but not this one!

Would anyone allow me to e-mail them the peices of code and maybe you could have a look at it?

It really doesnt mae sense to me!

Thanks.

 

[mrdenny]

If the record is being locked, it's being locked for a reason.

Are you seeing blocking when you run the sp_who2 procedure?



Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[daddio2005]

Sorry but I have no idea how to run sp_who2 procedure in SQL Analyser!

Can anyone show me step by step how to do this? The Updates statments are running within a ASP webpage as described above.

Or if anyone would be willing to look at my code, it would much appreciated.

Thanks

 

[mrdenny]

Open Query Analyzer, connect to the server and put sp_who2 in the window and press the play button.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com