vb6 activex dll debugging causing sql to timeout

zell71
zell71 used Ask the Experts™
on
Hi,
im struggling to debug from an activex dll in vb6.  the is called from vba code within excel.
i set the debugging > start programs to excel and this all works fine.
if i place a break point in my vb6..once vba calls reference to that part of the vb6, i hit the break point and all is great.
However, for some strange reason why i call a stored procedure during debugging in this method..i get the following error:
run-time error '-2147217871 (80040e31)': timeout expired

if i step through my code, the stored procedure is called and i have data correctly returned but if i try to manually run a sql query..SQL just hangs.

this process is causing my dll to fail once attempts to run a 2nd stored procedure..(see error message above).

i've unregistered the dll before running my activex dll to see if that made a difference - no change.
However, if i make the dll and reference it via excel etc...the DLL / SQL do what i expect and i have no problems.
any ideas what is causing this? i want to add additional functionality into my dll and i need to debug.

tools: VB6, VBA, Excel 2003, SQL 2000

many thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
i have a question just to make sure i understand.  

> if i place a break point in my vb6..once vba calls reference to that part of the vb6,
> i hit the break point and all is great.  However, for some strange reason why i call
> a stored procedure during debugging in this method..i get the following error:
> run-time error '-2147217871 (80040e31)': timeout expired

so in your debug session, you have removed the reference to the compiled dll from your excel vba program and added a reference to the vb6 project, right?  then, if this is true, you have placed a break somewhere in that project, right?

if all that is true, then you hit the break point and all is good.  then i guess you hit continue, the program hits a point where you are running a sql statement to the database, and this is where the timeout occurs?

Author

Commented:
yes, communication between excel and my activex dll are working as planned.  i can step through my code fine and data is returned from the initial stored procedure on the SQL 2000 database.
the code then continues to step through the code..but once it hits another stored procedure..i get the time out error.  at this stage, if i attempt to run a manual query...no results in sql are returned.

so in brief..the first stored proc runs fine..data is returned..after that..any further SQL calls are creating a timeout error.  if i'm not debugging, this initial startup of the dll makes 3 calls to SQL and all return data within milliseconds.  this is why im so confused...why its not working in debug mode.
Commented:
i suggest that you follow this general design for all database accesses using vb6:

open a connection
execute your query
close your connection

this is just my way, there are others.  :)

when you run compiled, the three queries run fast enough to complete before the server closes the connection on timeout.  in debug, any manual delays will cause you to hit the timeout.  you could change your server's timeouts, but then you have other things to consider, especially if you are going to have multiple users running this app.  for example, sql server 2000 i think allows 250 connections, easy to hit that if you persist them.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
thanks for the assistance! resolved the issue, my initial call was to a sql table containing user info (last login etc).. the stored proc did a select and then an update..removing the update resolved the problem.
why would the update lock the table in debug?any suggestions?

Commented:
i don't see any reason why that would cause an issue.  i have many stored procs that do a select then an update.  what sort of an update is it?  are you updating many records?  are there indexes on the affected table(s)?  if your update is changing a large set of data it would run slowly.  and if the table doesn't have indexes to support the where clause fields, it can take a long time on a large table even if only one record is being updated.

so i would first check the indexes and make sure they are defined.  if you need help with this, post your select and update queries and i will walk you thru it.

Author

Commented:
thanks, helped find the solution

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial