Command.Execute hangs forever

[Rob MacFadyen]

Hey all,

I've got a very odd case. I have a command object that occasionally will
"hang" forever.

Roughly speaking the code looks like:

dim cmd as ADODB.Command
cmd.CommandText = "blah blah"
set cmd.ActiveConnection = cn
cmd.Parameters.Append ... half dozen parameters ...
cmd.Execute ' <--- never ever completes or error's out

What is really odd about this is the doing a SQL trace/profile on the
database server does not show the command being sent to the server. It is
also odd that the particular bit of code is well used on a daily basis by
lots installations, and even many times by the installation where the problem
is occurring.

The windows event log (application, system, security) do not have any
applicable entries, the SQL Server error log is clean.

Is there any setting I can enable to get any sort of debug information out
of ADODB?

Any suggestions on stuff I should be looking at (I ran component checker on
the server and it's fine... mdac 2.8 sp2 on windows server 2003 sp2)?

[Bob Barrows]

If you have On Error Resume Next in your code, comment it out so errors
are not masked.
--
HTH,
Bob Barrows

[Bob Barrows]

Rob MacFadyen wrote:
> Hey all,
>
> I've got a very odd case. I have a command object that occasionally
> will "hang" forever.
>
> Roughly speaking the code looks like:
>
> dim cmd as ADODB.Command

Is this VB or VBA code?

> cmd.CommandText = "blah blah"
> set cmd.ActiveConnection = cn
> cmd.Parameters.Append ... half dozen parameters ...
> cmd.Execute ' <--- never ever completes or error's out

You're sure the code is reaching this point? You said it occurs
"occasionally" ... have you been able to see the behavior while stepping
through the code?

>
> What is really odd about this is the doing a SQL trace/profile on the
> database server does not show the command being sent to the server.

Can you see the connection being made (audit login event)?

> It is also odd that the particular bit of code is well used on a
> daily basis by lots installations, and even many times by the
> installation where the problem is occurring.
>
> The windows event log (application, system, security) do not have any
> applicable entries, the SQL Server error log is clean.
>
> Is there any setting I can enable to get any sort of debug
> information out of ADODB?

Nope.

> Any suggestions on stuff I should be looking at (I ran component
> checker on the server and it's fine... mdac 2.8 sp2 on windows server
> 2003 sp2)?

My last reply is probably not relevant. Instead, make sure the
CommandTimeout is set to a reasonable value

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Rob MacFadyen]

Note that switching from the SQLOLEDB.1 provider to the SQLNCLI10 (SQL Native
Client) results in the command executing properly.

[Bob Barrows]

Rob MacFadyen wrote:
> Note that switching from the SQLOLEDB.1 provider to the SQLNCLI10
> (SQL Native Client) results in the command executing properly.

Oh good! You have your solution!

[Ronggang Yu]

Hi, Rob:

Do you have a simple repro? From your description, it seems like a
sqloledb bug. [switching to SNAC, your code works]. If you still have repro,
could you share with us? If we identify it is a product bug, we will fix it
on the latest O.S.

thanks,
ronggang.

[e.t...@gmail.com]

Hi, I'm facing same problem on Windows 2008 R2 x64 where my application uses provider SQLOLEDB.1

Was there any progress on this case, to identify real culprit why SqlComamnds randomly hang?

Thanks
ET;