Stored proc timeout issue

[reposted from sqlservers]

I have a stored proc that deletes certain records in half a dozen tables,
one table after another, in a D7 app. The sp creates a temp table at the
beginning with IDs of the records to be deleted. Ado, ms sql server 2k5.

The program Opens the proc and times out after 2 minutes. When I run the
proc from sql server mgmt studio, it completes in 11 seconds.

I've stepped carefully through the code, looking for where the pgm might be
opening something and not letting go. Nothing. I set a breakpoint just
before Open, and examine sqlms for any process with a lock, obviously
expecting to find my program. Nothing. No apparent reason for this proc to
timeout in one case and not in another.

So obviously I've got a case of Programmer's Block. Can anyone suggest
something to get the neurons firing again?

Jeremy

.

Relevant Pages

  • Re: Auditing SP Execute.
    ... > As long as the users only have execute permissions on the proc i.e. they ... This assumes that the app doesn't use a single ... > Jasper Smith (SQL Server MVP) ... >> particular sp was execute and by whom. ...
    (microsoft.public.sqlserver.security)
  • Re: Another Concurrency Issue!!!!
    ... > allocating in blocks, using tables with identities, etc. ... > Columnist, SQL Server Professional ... I have a proc which returns a value after some DML operations on ... >> TIA ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL 2005
    ... The SQL Server implementation is not just Stored Procs, ... it works very tightly in proc with the SQL Server Engine(most ... Now with all that I have said T-SQL is not going away and is still a needed ... >> the CLR is now supported in stored procedures and triggers. ...
    (borland.public.delphi.non-technical)
  • Re: Performance Problem Using ADO and Stored Procs
    ... >I have a stored proc that executes in < 15 seconds through Query Analyzer. ... If I execute this proc ... > of CPU activity on the server hosting SQL server for the SQL server ... > Private Function RunProc(vntDB As Variant, strProcName As String, ...
    (microsoft.public.data.ado)
  • Re: Stored proc timeout issue
    ... The program Opens the proc and times out after 2 minutes. ... proc from sql server mgmt studio, ... I began diagnosing the problem by commenting out various lines of code in the stored proc ... One thing that remained changed was the command timeout value. ...
    (borland.public.delphi.database.ado)