473,287 Members | 1,793 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

timeout Expired when trying to do a 'where' or filter recordset

Hi all...
I've been stumped by this for days. Bit of ASP code: (IIS)

Set LocalConn = CreateObject("ADODB.Connection")
LocalConn.CursorLocation = adUseClient
LocalConn.CommandTimeout = 0
LocalConn.Open sConnStringSO (SQL Server 2000, irrelevant what it is,
see below).

LocalConn.Errors.Clear

CountDuplicatesSQL = "select * from dbo.tbl1 WHERE DESC LIKE 'aaa'"
oRss.Open
CountDuplicatesSQL,LocalConn,adOpenDynamic,adLockR eadOnly,acCmdText
this hangs and then results with "timeout expired".

the same SQL works flawlessly if tested on query analyzer, with the
same connection Login.

And I found, that If I remove the WHERE clause, it works without
problems..., both in ASP and Query analyzer.

I have another connection running in parallel to this one, Server
side, but to a different database on the same server. If I disable it,
the above code works. But, I need it open all the time...
if its the two connections conflicting, how come the code above works
with a simple Select but not with a WHERE criteria on it, under the
same two connection conditions?

~Thanks for insights.

Jul 23 '05 #1
9 3692
heruti (he****@lycos.com) writes:
I've been stumped by this for days. Bit of ASP code: (IIS)

Set LocalConn = CreateObject("ADODB.Connection")
LocalConn.CursorLocation = adUseClient
LocalConn.CommandTimeout = 0
LocalConn.Open sConnStringSO (SQL Server 2000, irrelevant what it is,
see below).

LocalConn.Errors.Clear

CountDuplicatesSQL = "select * from dbo.tbl1 WHERE DESC LIKE 'aaa'"

oRss.Open
CountDuplicatesSQL,LocalConn,adOpenDynamic,adLockR eadOnly,acCmdText

this hangs and then results with "timeout expired".

the same SQL works flawlessly if tested on query analyzer, with the
same connection Login.
It does? I get a syntax error from it:

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'DESC'.

Apart from that, I don't understand why you specify a dynamic cursor. Since
you use a client-side cursor, static is all you can get anyway. I would
somehow suspect that you get a dynamic server-side cursor anyway, and
this is part of your problems.
I have another connection running in parallel to this one, Server
side, but to a different database on the same server. If I disable it,
the above code works. But, I need it open all the time...
if its the two connections conflicting, how come the code above works
with a simple Select but not with a WHERE criteria on it, under the
same two connection conditions?


Apparently a blocking issue. And not knowing what your databases and
connections are up to, I cannot really say more. But you can use sp_who
to verify that you have a blocking issue. If you see a non-zero value
in the Blk column, that spid is blocking the spid on that row.

To get more detailed information about the blocking situation, my
aba_lockinfo can help you. It's on
http://www.sommarskog.se/sqlutil/aba_lockinfo.html.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2

Erland Sommarskog wrote:
LocalConn.Errors.Clear

CountDuplicatesSQL = "select * from dbo.tbl1 WHERE DESC LIKE 'aaa'"

oRss.Open
CountDuplicatesSQL,LocalConn,adOpenDynamic,adLockR eadOnly,acCmdText

this hangs and then results with "timeout expired".

the same SQL works flawlessly if tested on query analyzer, with the
same connection Login.
It does? I get a syntax error from it:

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'DESC'.

Apart from that, I don't understand why you specify a dynamic cursor.

Since you use a client-side cursor, static is all you can get anyway. I would somehow suspect that you get a dynamic server-side cursor anyway, and
this is part of your problems.


I have tried static, ForwardOnly and most of the other combinations
(out of despair..) but the problem apparently is not there but in the
other, locking, connection.
well perhaps you don't have the DESC field in your table... or the tbl1
table.
in Any case, here are more details on the connections:

the first connection:

sconString=
"Provider=SQLOLEDB; User ID=xxx;Password=yyy;Initial Catalog=eeee;Data
Source=aaa;"

Set oCon = CreateObject("ADODB.Connection")
oCon.Open sConString
oCon.Errors.Clear

oCon.BeginTrans

If oCon.State = adStateOpen Then OpenConn = True

the second connection:
function openSecondConn()

sConString2 =
"Provider=SQLOLEDB; User ID=xxx;Password=yyy;Initial Catalog=zzz;Data
Source=bbb;"

Set oConClientRs = CreateObject("ADODB.Connection")
oConClientRs.CursorLocation = adUseClient
oConClientRs.Open sConString2
oConClientRs.Errors.Clear

end function

And after this:

the ASP client runs several stored procedures on database aaa, with
this function:
Function ExecuteSP(sProc,arrParams)

Dim nCnt
Dim nParamMax
Dim nMax

On Error Resume Next

ExecuteSP = False

If Not oCon.State = adStateOpen Then
m_sErrorData = "No active connection"
RollbackTrans()
Exit Function
End If
nMax = UBound(arrParams)

If Not IsObject(oCom) Then

Set oCom = CreateObject("ADODB.Command")

With oCom
..ActiveConnection = oCon
..CommandType = adCmdStoredProc
End With

End If

With oCom

..CommandText = sProc

..Parameters.Refresh

nParamMax = .Parameters.Count - 1 'minus one for return param

'check to see if we have passed the correct number of parameters
If Not nParamMax = nMax + 1 Then
m_sErrorData = "Incorrect number of parameters. Passed: " & nMax +
1 & " Required: " & nParamMax
RollbackTrans()
Exit Function
End If

For nCnt = 0 To nMax

..Parameters.Item(nCnt+1).value = arrParams(nCnt)

Next

..Execute

nRet = .Parameters(0)

End with
' error handling code snipped off, irrelevant
ExecuteSP = True

End Function

then, I'm trying to run the code that gets stuck:

openSecondConn(sConnString2)
Set oRss = CreateObject("ADODB.Recordset")

CountDuplicatesSQL = "select count (*) as cnt from dbo.tbl1 WHERE Desc
LIKE 'qqqq'"

oRss.Open
CountDuplicatesSQL,oConClientRs,adOpenStatic,adLoc kReadOnly,acCmdText

hangs...

but why, if its the other connection locking it up, does it still work
when I remove the 'where' criteria?
can it at least fail thoroughly, is it too much to ask?...

Jul 23 '05 #3
Also forgot to mention,

the first connection (server side) runs with transactions, where it
begintrans'es in the start of a session and commits only at the end. in
between I want to run the above open operation with the second
connection, without transactions (preferably but I have tried both
ways, didn't work), on a static (or keyset) client side cursor,
Thanks

Jul 23 '05 #4

heruti wrote:

And a final note:

due to my stupidity probably, I didn't realise that the stored
procedure called just before my hanging function manipulates the same
table.
But it is finished, so isn't it possible to nest transactions?

my situation is complicated by the fact that, all stored procedures in
the project are stored on a different database than the one holding the
data tables (hence the two different connections). Its too complicated
to change that (there are over 50 storedprocs).
But, does a transaction Begintrans / commit also work when you are,
within it, calling stored procedures on one database that manipulate
data on another database (same server though).??
if not, the first connection doesn't really benefit from the
Transaction processing and I can remove the begintrans/commit...

Jul 23 '05 #5
heruti (he****@lycos.com) writes:
And a final note:

due to my stupidity probably, I didn't realise that the stored
procedure called just before my hanging function manipulates the same
table.
But it is finished, so isn't it possible to nest transactions?
Not sure what you mean, but if you say:

BEGIN TRANSACTION

BEGIN TRANSACTION

COMMIT TRANSACTION

COMMIT TRANSACTION

nothing really gets committed until the outermost COMMIT statement is
executed. Until that COMMIT only decrements the transaction count. This
means that all locks acquired during the transaction are still there.

So if that connection to bbb excutes stored procedures that operates on
data in aaa, and that connection never commits until it exists, I am not
surprised that you get blocking problems.
my situation is complicated by the fact that, all stored procedures in
the project are stored on a different database than the one holding the
data tables (hence the two different connections). Its too complicated
to change that (there are over 50 storedprocs).
So instead you run into to complications that you don't even understand?
Sounds to me that a cleaning-up would be a good thing to do!

In any case that is not reason for two different connections. You can
be in one database and call stored procedures in another. Just prefix
with database name: aaa.dbo.some_sp. Ah, you are using .Parameters.Refresh!
This makes life simpler, but it's not the most effecient way to call
a stored procedure anyway.

If you must use .Refresh, you should definitely move those stored
procedures.
But, does a transaction Begintrans / commit also work when you are,
within it, calling stored procedures on one database that manipulate
data on another database (same server though).??


Not sure what you really mean, but a transaction spans databases.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #6
thank you for your response...

I have solved the problem by most awkwardly way of temporarily storing
in an array the data to be written in the blocked function, until later
when the first transaction is commited and then executing this
function. .

But still, this seems a most roundabout and illogical way to do things
in the 21st century (I seem to remember somewhere in the Renessance
period, databases were better! ask Leonardo...)

Am I really to believe, that if you open a server side connection and
transaction to database A, in which you execute with ADO some stored
procedures on it that manipulate data on database B, you must end this
transaction before you can open any other client side connection to the
database B?
(I need another connection, because I have to use ADO to write records,
which only works with client side recordset. The first transaction uses
server side).

Like this:
OPEN CONNECTION 1 TO DB. A (SERVER SIDE)
BEGIN TRANSACTION (A)
..
..
[execute stored procs on database A, which manipulate
data on DB. B, tables A1, A2, A3...]

BEGIN TRANSACTION (B)
OPEN CONNECTION 2 TO DB. B

[ read/write records with ADO directly Tables A1, A2, A3..
LOCKED....!!!]
COMMIT TRANSACTION B

COMMIT TRANSACTION A

and regarding the .Parameters.Refresh,
it was there before I came to the project. does it have any special
consequences?

Jul 23 '05 #7
heruti (he****@lycos.com) writes:
I have solved the problem by most awkwardly way of temporarily storing
in an array the data to be written in the blocked function, until later
when the first transaction is commited and then executing this
function. .

But still, this seems a most roundabout and illogical way to do things
in the 21st century (I seem to remember somewhere in the Renessance
period, databases were better! ask Leonardo...)
Yes, this an akward and complicated way of doing things. But it appears
that this is because you've taken akward way to start with. Maybe you
should go back and re-design the original setup with two connections.

If you have to transactions that access the same data, and one has a
transaction that does not commit, then they will interfer with each
other.

I can't really say much more, because there is so much that I don't
know about your system, since you have not been very good at sharing
information. It is possible that you can keep the current scheme with
proper indexing, and knowing what you update etc. But I don't really
like to guess what you are doing.

I should add that you can actually share a transaction between two
connection. This is something I have not dug into myself, but I believe
the system procedure sp_bindsession is part of the plot. However, this
would just add one more kludge to your system, and you seem to have
too many already.
(I need another connection, because I have to use ADO to write records,
which only works with client side recordset. The first transaction uses
server side).
I don't know why you use server-side cursors at all. Client-side
recordsets usually perform better, and causes less confusion.
and regarding the .Parameters.Refresh,
it was there before I came to the project. does it have any special
consequences?


That's a roundtrip to the server to get the parameter profile.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #8

I usually don't think it appropriate to burden readers, who are
gracious enough to help, with all of the jumbalaya of my code.

I thought perhaps it was something basic, like maybe one cannot have a
server side SQL process and a client side process on two different
connections to the same table (if this is impossible I am not sure of
till now anyway)

Thank you for your efforts.

I haven't really understood how a lock on a table can so partial as to
allow a SELECT but time our on a SELECT WHERE, but perhaps its a bug in
the Locking system...

Jul 23 '05 #9
heruti (he****@lycos.com) writes:
I usually don't think it appropriate to burden readers, who are
gracious enough to help, with all of the jumbalaya of my code.

I thought perhaps it was something basic, like maybe one cannot have a
server side SQL process and a client side process on two different
connections to the same table (if this is impossible I am not sure of
till now anyway)

Thank you for your efforts.

I haven't really understood how a lock on a table can so partial as to
allow a SELECT but time our on a SELECT WHERE, but perhaps its a bug in
the Locking system...


You still seem to be prone to give SQL Server the blame for your problems.
Believe me, your problem is in the overall architecture of your application.

I cannot answer why you first got the problem when you added the WHERE
clause. But now I have a strong feeling that I don't even know half of
your story, I don't even find it a mystery. I could think of a number of
possible reasons, but I have too little knowledge to find it worthwhile
to present any speculations.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: David Morgan | last post by:
Hello In my ASP page I am trying to ascertain whether a randomly generated ID starts with certain characters that are not allowed or has been used before. When the SQL statement for latter is...
4
by: Paul | last post by:
I sometimes get a timeout error when populating my datagrid, the code is WizardConnection.Open() UpdateCommand.CommandText = "EXECUTE sp_assign_user '" & PhysOffice.SelectedValue & "', '" &...
2
by: Chris Langston | last post by:
I have a Web Server running IIS 5 or 6 on Windows 2K and Windows 2003 Server that is experiencing strange shutdown problems. We are using ASP.NET v1.1 and our application is written in VB.NET ...
4
by: Nevyn Twyll | last post by:
I've been working on an asp.net application and everything's been great. But suddenly, whether I'm tyring to use a database on my own machine, or on my server, I'm getting a timeout when trying to...
0
by: gak999 | last post by:
I have list box showing list of worksheet with double click event: Dim rs3 As Object Set rs3 = Me.Recordset.Clone rs3.FindFirst " = " & Str(Me!) Me.Bookmark = rs3.Bookmark Dim appttype As...
4
by: VB Programmer | last post by:
When I run my ASP.NET 2.0 web app on my dev machine it works perfect. When I precomile it to my web deployment project and then copy the debug files to my web server I get this problem when trying...
1
by: Ron | last post by:
Hi, I had a stored procedure on SQL 2000 server to run calculation with large amount of data. When I called this stored procedure via System.Data.SqlClient.SqlCommand on production, i got error...
3
by: Sems | last post by:
Hi I'm using the Session_End event in the global.asax to detect if a users sessions has ended. Is there any way to tell if the session end is due to it being expired and not abandoned? I'm...
1
by: Scorpion657 | last post by:
Hey I really need help. I have a Website coded using ASP.NET and VB and for some reason, i'm getting the following error when I try to upload or access a large file which is stored in the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.