"Transaction deadlocked" problem

Greetings

I have a script that establishes a connection to an SQL database and creates severa
recordset objects via Select statements. I only want to extract data -- no updatin
needed. But I'm experiencing the following error condition

Transaction deadlocked on lock resources with another process and has been chose
as the deadlock victim. (Code: 80004005

I tried the following syntax on my recordset Opens
objRec.Open strSQL, objConn, adOpenForwardOnly, adLockReadOnl

But this gave me a syntax error
Arguments of the wrong type, out of acceptable range, or in conflict with one another

Then I tried eliminating the ad* parameters entirely, which according to a VB manua
should default to a forward-only, read-only recordset. But I still get the "deadlock
condition

Can anyone offer any help on this? Would appreciate greatly

Thanks
To

Steven
Mon Apr 26 12:41:39 CDT 2004

Try changing;

objRec.Open strSQL, objConn, adOpenForwardOnly, adLockReadOnly

to

objRec.Open chr(34) & strSQL & chr(34), objConn, adOpenForwardOnly,
adLockReadOnly

--

Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!

Personal favourites

Practically Nerded - http://mvps.org/PracticallyNerded/
Bugs, Glitches n stuff - http://mvps.org/inetexplorer/Darnit.htm
Calendar of Updates - http://www.dozleng.com/updates/index.php?&act=calendar


"Tom Glasser" <anonymous@discussions.microsoft.com> wrote in message
news:5734DD96-75CF-46FB-A47D-40F68756148A@microsoft.com...
> Greetings!
>
> I have a script that establishes a connection to an SQL database and
creates several
> recordset objects via Select statements. I only want to extract data --
no updating
> needed. But I'm experiencing the following error condition:
>
> Transaction deadlocked on lock resources with another process and has been
chosen
> as the deadlock victim. (Code: 80004005)
>
> I tried the following syntax on my recordset Opens:
> objRec.Open strSQL, objConn, adOpenForwardOnly, adLockReadOnly
>
> But this gave me a syntax error:
> Arguments of the wrong type, out of acceptable range, or in conflict with
one another.
>
> Then I tried eliminating the ad* parameters entirely, which according to a
VB manual
> should default to a forward-only, read-only recordset. But I still get
the "deadlock"
> condition.
>
> Can anyone offer any help on this? Would appreciate greatly!
>
> Thanks,
> Tom
>

Bob
Mon Apr 26 12:53:46 CDT 2004

Steven Burn wrote:
> Try changing;
>
> objRec.Open strSQL, objConn, adOpenForwardOnly, adLockReadOnly
>
> to
>
> objRec.Open chr(34) & strSQL & chr(34), objConn, adOpenForwardOnly,
> adLockReadOnly
>
Why?
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Steven
Mon Apr 26 12:58:51 CDT 2004

I've found problems when not encasing the string in quotes...... just
figured it was worth a try in this case too.

--

Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!

Personal favourites

Practically Nerded - http://mvps.org/PracticallyNerded/
Bugs, Glitches n stuff - http://mvps.org/inetexplorer/Darnit.htm
Calendar of Updates - http://www.dozleng.com/updates/index.php?&act=calendar


"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:eqxpzd7KEHA.3852@TK2MSFTNGP10.phx.gbl...
> Steven Burn wrote:
> > Try changing;
> >
> > objRec.Open strSQL, objConn, adOpenForwardOnly, adLockReadOnly
> >
> > to
> >
> > objRec.Open chr(34) & strSQL & chr(34), objConn, adOpenForwardOnly,
> > adLockReadOnly
> >
> Why?
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

Bob
Mon Apr 26 12:59:55 CDT 2004

Tom Glasser wrote:
> Greetings!
>
> I have a script that establishes a connection to an SQL database and
> creates several recordset objects via Select statements.

From the same table?

> I only want
> to extract data -- no updating needed. But I'm experiencing the
> following error condition:
>
> Transaction deadlocked on lock resources with another process and has
> been chosen
> as the deadlock victim. (Code: 80004005)
>
> I tried the following syntax on my recordset Opens:
> objRec.Open strSQL, objConn, adOpenForwardOnly, adLockReadOnly
>
> But this gave me a syntax error:
> Arguments of the wrong type, out of acceptable range, or in conflict
> with one another.

>
> Then I tried eliminating the ad* parameters entirely, which according
> to a VB manual should default to a forward-only, read-only recordset.
> But I still get the "deadlock" condition.
>
> Can anyone offer any help on this? Would appreciate greatly!
>
> Thanks,
> Tom

Suggestion 1: Use a single recordset - use GetRows to populate arrays with
the various sets of data from each SELECT statement in turn:

rs.open sql1
ar1=rs.getrows
rs.close
rs.open sql2
ar2 = rs.getrows
etc.

Suggestion 2: Use the WITH (NOLOCK) hint in your select statements:

SELECT ... FROM tablename WITH (NOLOCK) ...


HTH,
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Bob
Mon Apr 26 13:03:02 CDT 2004

If you could find one, I'd appreciate a "f'r instance" ...

Bob Barrows
Steven Burn wrote:
> I've found problems when not encasing the string in quotes...... just
> figured it was worth a try in this case too.
>
>
> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:eqxpzd7KEHA.3852@TK2MSFTNGP10.phx.gbl...
>> Steven Burn wrote:
>>> Try changing;
>>>
>>> objRec.Open strSQL, objConn, adOpenForwardOnly, adLockReadOnly
>>>
>>> to
>>>
>>> objRec.Open chr(34) & strSQL & chr(34), objConn, adOpenForwardOnly,
>>> adLockReadOnly
>>>
>> Why?
>> --
>> Microsoft MVP -- ASP/ASP.NET
>> Please reply to the newsgroup. The email account listed in my From
>> header is my spam trap, so I don't check it very often. You will get
>> a quicker response by posting to the newsgroup.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.