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

[heruti]

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

Set LocalConn = CreateObject("A DODB.Connection ")
LocalConn.Curso rLocation = adUseClient
LocalConn.Comma ndTimeout = 0
LocalConn.Open sConnStringSO (SQL Server 2000, irrelevant what it is,
see below).

LocalConn.Error s.Clear

CountDuplicates SQL = "select * from dbo.tbl1 WHERE DESC LIKE 'aaa'"
oRss.Open
CountDuplicates SQL,LocalConn,a dOpenDynamic,ad LockReadOnly,ac CmdText
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.

[Erland Sommarskog]

heruti (he****@lycos.c om) writes:

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

Set LocalConn = CreateObject("A DODB.Connection ")
LocalConn.Curso rLocation = adUseClient
LocalConn.Comma ndTimeout = 0
LocalConn.Open sConnStringSO (SQL Server 2000, irrelevant what it is,
see below).

LocalConn.Error s.Clear

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

oRss.Open
CountDuplicates SQL,LocalConn,a dOpenDynamic,ad LockReadOnly,ac CmdText

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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[heruti]

Erland Sommarskog wrote:

LocalConn.Error s.Clear

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

oRss.Open
CountDuplicates SQL,LocalConn,a dOpenDynamic,ad LockReadOnly,ac CmdText

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=SQLOL EDB; User ID=xxx;Password =yyy;Initial Catalog=eeee;Da ta
Source=aaa;"

Set oCon = CreateObject("A DODB.Connection ")
oCon.Open sConString
oCon.Errors.Cle ar

oCon.BeginTrans

If oCon.State = adStateOpen Then OpenConn = True

the second connection:
function openSecondConn( )

sConString2 =
"Provider=SQLOL EDB; User ID=xxx;Password =yyy;Initial Catalog=zzz;Dat a
Source=bbb;"

Set oConClientRs = CreateObject("A DODB.Connection ")
oConClientRs.Cu rsorLocation = adUseClient
oConClientRs.Op en sConString2
oConClientRs.Er rors.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(arrParam s)

If Not IsObject(oCom) Then

Set oCom = CreateObject("A DODB.Command")

With oCom
..ActiveConnect ion = oCon
..CommandType = adCmdStoredProc
End With

End If

With oCom

..CommandText = sProc

..Parameters.Re fresh

nParamMax = .Parameters.Cou nt - 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.It em(nCnt+1).valu e = 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("A DODB.Recordset" )

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

oRss.Open
CountDuplicates SQL,oConClientR s,adOpenStatic, adLockReadOnly, 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?...

[heruti]

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

[heruti]

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...

[Erland Sommarskog]

heruti (he****@lycos.c om) 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.Ref resh!
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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[heruti]

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.Ref resh,
it was there before I came to the project. does it have any special
consequences?

[Erland Sommarskog]

heruti (he****@lycos.c om) 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.Ref resh,
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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[heruti]

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...

[Erland Sommarskog]

heruti (he****@lycos.c om) 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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp