Timeout expired for stored procedure won't go away

Timeout expired for stored procedure won't go away

Post by convince » Fri, 20 Sep 2002 02:53:58



Hello All,

(this is a re-post, the first didn't seem to make it up)

I have a "timeout expired" error problem. I'm trying to execute the
below Stored Procedure for SQL server 2000 in my VB 6.0 app.

________________________________
CREATE PROCEDURE [Clear_Staffing]

AS
set nocount on

DELETE FROM tblmonjobs

WHERE

GO
_____________________________

I get a time out error in VB after 30 seconds all though I've tried
setting my connection as such:

Set cn = New ADODB.Connection
cn.ConnectionTimeout = 0
cn.CommandTimeout = 0
cn.Open strconstr

I then ran the stored procedure in the query analyzer and it just ran
and ran so I killed it.

Can anyone think of what I might be doing wrong here?

I'm guessing my procedure is written wrong, but it gives me no syntax
error.

thanks,
Newbie-to-stored-procedures!

PS- I can execute other SP just fine from my VB and access app's.

mt3513

 
 
 

Timeout expired for stored procedure won't go away

Post by Andrew J. Kell » Fri, 20 Sep 2002 03:29:50


How many rows will be deleted out of how many total?  Did you check to see
if there was any blocking going on?

--
Andrew J. Kelly   SQL MVP
Targitinteractive, Inc.


> Hello All,

> (this is a re-post, the first didn't seem to make it up)

> I have a "timeout expired" error problem. I'm trying to execute the
> below Stored Procedure for SQL server 2000 in my VB 6.0 app.

> ________________________________
> CREATE PROCEDURE [Clear_Staffing]


> AS
> set nocount on

> DELETE FROM tblmonjobs

> WHERE

> GO
> _____________________________

> I get a time out error in VB after 30 seconds all though I've tried
> setting my connection as such:

> Set cn = New ADODB.Connection
> cn.ConnectionTimeout = 0
> cn.CommandTimeout = 0
> cn.Open strconstr

> I then ran the stored procedure in the query analyzer and it just ran
> and ran so I killed it.

> Can anyone think of what I might be doing wrong here?

> I'm guessing my procedure is written wrong, but it gives me no syntax
> error.

> thanks,
> Newbie-to-stored-procedures!

> PS- I can execute other SP just fine from my VB and access app's.

> mt3513


 
 
 

Timeout expired for stored procedure won't go away

Post by Jim Clar » Fri, 20 Sep 2002 03:33:21




Change the timeout on your sql server but if the sp is running to long
in query analyser you may have a problem with the sp or your grabbing
massive amounts of data.

>Hello All,

>(this is a re-post, the first didn't seem to make it up)

>I have a "timeout expired" error problem. I'm trying to execute the
>below Stored Procedure for SQL server 2000 in my VB 6.0 app.

>________________________________
>CREATE PROCEDURE [Clear_Staffing]


>AS
>set nocount on

>DELETE FROM tblmonjobs

>WHERE

>GO
>_____________________________

>I get a time out error in VB after 30 seconds all though I've tried
>setting my connection as such:

>Set cn = New ADODB.Connection
>cn.ConnectionTimeout = 0
>cn.CommandTimeout = 0
>cn.Open strconstr

>I then ran the stored procedure in the query analyzer and it just ran
>and ran so I killed it.

>Can anyone think of what I might be doing wrong here?

>I'm guessing my procedure is written wrong, but it gives me no syntax
>error.

>thanks,
>Newbie-to-stored-procedures!

>PS- I can execute other SP just fine from my VB and access app's.

>mt3513

 
 
 

Timeout expired for stored procedure won't go away

Post by Scott Morri » Fri, 20 Sep 2002 05:15:46


Check for poorly written triggers


> Hello All,

> (this is a re-post, the first didn't seem to make it up)

> I have a "timeout expired" error problem. I'm trying to execute the
> below Stored Procedure for SQL server 2000 in my VB 6.0 app.

> ________________________________
> CREATE PROCEDURE [Clear_Staffing]


> AS
> set nocount on

> DELETE FROM tblmonjobs

> WHERE

> GO
> _____________________________

> I get a time out error in VB after 30 seconds all though I've tried
> setting my connection as such:

> Set cn = New ADODB.Connection
> cn.ConnectionTimeout = 0
> cn.CommandTimeout = 0
> cn.Open strconstr

> I then ran the stored procedure in the query analyzer and it just ran
> and ran so I killed it.

> Can anyone think of what I might be doing wrong here?

> I'm guessing my procedure is written wrong, but it gives me no syntax
> error.

> thanks,
> Newbie-to-stored-procedures!

> PS- I can execute other SP just fine from my VB and access app's.

> mt3513

 
 
 

Timeout expired for stored procedure won't go away

Post by convince » Fri, 20 Sep 2002 11:45:34


Hello,

Thank You for your reply!

anywhere from 1 to 100 records will be found for deletion on average
for this SP. Currently there are like 10 for this trial.

The table has 37,000 records right now, but will eventually grow to
half a million!


> How many rows will be deleted out of how many total?  Did you check to see
> if there was any blocking going on?

> --
> Andrew J. Kelly   SQL MVP
> Targitinteractive, Inc.



> > Hello All,

> > (this is a re-post, the first didn't seem to make it up)

> > I have a "timeout expired" error problem. I'm trying to execute the
> > below Stored Procedure for SQL server 2000 in my VB 6.0 app.

> > ________________________________
> > CREATE PROCEDURE [Clear_Staffing]


> > AS
> > set nocount on

> > DELETE FROM tblmonjobs

> > WHERE

> > GO
> > _____________________________

> > I get a time out error in VB after 30 seconds all though I've tried
> > setting my connection as such:

> > Set cn = New ADODB.Connection
> > cn.ConnectionTimeout = 0
> > cn.CommandTimeout = 0
> > cn.Open strconstr

> > I then ran the stored procedure in the query analyzer and it just ran
> > and ran so I killed it.

> > Can anyone think of what I might be doing wrong here?

> > I'm guessing my procedure is written wrong, but it gives me no syntax
> > error.

> > thanks,
> > Newbie-to-stored-procedures!

> > PS- I can execute other SP just fine from my VB and access app's.

> > mt3513

 
 
 

Timeout expired for stored procedure won't go away

Post by convince » Fri, 20 Sep 2002 11:50:21


Hi,

Thanks for the reply!

Quote:>>>Change the timeout on your sql server

Is there a setting inside SQL server I can set? Could someone direct
me to it if so?

Quote:>>>>you may have a problem with the sp

Yes, I might. But since the query analyze isn't telling me I don't
know.

Quote:>>>>>or your grabbing massive amounts of data.

No, not grabbing, but searching through a lot. And that will only get
worse over time. I wonder what the limitations are? I would believe
37,000 records is nothing. Access can handle that! But this will grow
to hundreds of thousands. SQL Server has to be able to handle it for
me to have any use of it. Of course I'll have to learn how to properly
set everything up which takes time.

I've done nothing with indexing so far as I know nothing about it, but
I assume this is the next step.

Thanks!




> Change the timeout on your sql server but if the sp is running to long
> in query analyser you may have a problem with the sp or your grabbing
> massive amounts of data.

> >Hello All,

> >(this is a re-post, the first didn't seem to make it up)

> >I have a "timeout expired" error problem. I'm trying to execute the
> >below Stored Procedure for SQL server 2000 in my VB 6.0 app.

> >________________________________
> >CREATE PROCEDURE [Clear_Staffing]


> >AS
> >set nocount on

> >DELETE FROM tblmonjobs

> >WHERE

> >GO
> >_____________________________

> >I get a time out error in VB after 30 seconds all though I've tried
> >setting my connection as such:

> >Set cn = New ADODB.Connection
> >cn.ConnectionTimeout = 0
> >cn.CommandTimeout = 0
> >cn.Open strconstr

> >I then ran the stored procedure in the query analyzer and it just ran
> >and ran so I killed it.

> >Can anyone think of what I might be doing wrong here?

> >I'm guessing my procedure is written wrong, but it gives me no syntax
> >error.

> >thanks,
> >Newbie-to-stored-procedures!

> >PS- I can execute other SP just fine from my VB and access app's.

> >mt3513

 
 
 

Timeout expired for stored procedure won't go away

Post by convince » Fri, 20 Sep 2002 11:51:30


I'm afraid I know nothing of triggers either. Do they cause time
out's? I'll read up on them.

Thanks!


> Check for poorly written triggers



> > Hello All,

> > (this is a re-post, the first didn't seem to make it up)

> > I have a "timeout expired" error problem. I'm trying to execute the
> > below Stored Procedure for SQL server 2000 in my VB 6.0 app.

> > ________________________________
> > CREATE PROCEDURE [Clear_Staffing]


> > AS
> > set nocount on

> > DELETE FROM tblmonjobs

> > WHERE

> > GO
> > _____________________________

> > I get a time out error in VB after 30 seconds all though I've tried
> > setting my connection as such:

> > Set cn = New ADODB.Connection
> > cn.ConnectionTimeout = 0
> > cn.CommandTimeout = 0
> > cn.Open strconstr

> > I then ran the stored procedure in the query analyzer and it just ran
> > and ran so I killed it.

> > Can anyone think of what I might be doing wrong here?

> > I'm guessing my procedure is written wrong, but it gives me no syntax
> > error.

> > thanks,
> > Newbie-to-stored-procedures!

> > PS- I can execute other SP just fine from my VB and access app's.

> > mt3513

 
 
 

Timeout expired for stored procedure won't go away

Post by Andrew J. Kell » Fri, 20 Sep 2002 21:13:32


Do you have an index on Company?  Can you show the full DDL including
Indexes for the table in question?  You can also try running sp_who2 from
query analyzer when you attempt to run this sp to see if anyone is blocking
you.

--
Andrew J. Kelly   SQL MVP
Targitinteractive, Inc.


> Hello,

> Thank You for your reply!

> anywhere from 1 to 100 records will be found for deletion on average
> for this SP. Currently there are like 10 for this trial.

> The table has 37,000 records right now, but will eventually grow to
> half a million!




- Show quoted text -

> > How many rows will be deleted out of how many total?  Did you check to
see
> > if there was any blocking going on?

> > --
> > Andrew J. Kelly   SQL MVP
> > Targitinteractive, Inc.



> > > Hello All,

> > > (this is a re-post, the first didn't seem to make it up)

> > > I have a "timeout expired" error problem. I'm trying to execute the
> > > below Stored Procedure for SQL server 2000 in my VB 6.0 app.

> > > ________________________________
> > > CREATE PROCEDURE [Clear_Staffing]


> > > AS
> > > set nocount on

> > > DELETE FROM tblmonjobs

> > > WHERE

> > > GO
> > > _____________________________

> > > I get a time out error in VB after 30 seconds all though I've tried
> > > setting my connection as such:

> > > Set cn = New ADODB.Connection
> > > cn.ConnectionTimeout = 0
> > > cn.CommandTimeout = 0
> > > cn.Open strconstr

> > > I then ran the stored procedure in the query analyzer and it just ran
> > > and ran so I killed it.

> > > Can anyone think of what I might be doing wrong here?

> > > I'm guessing my procedure is written wrong, but it gives me no syntax
> > > error.

> > > thanks,
> > > Newbie-to-stored-procedures!

> > > PS- I can execute other SP just fine from my VB and access app's.

> > > mt3513

 
 
 

1. 6.5 - help temp tables (#) won't go away at end of procedure

We are converting from Sybase to SQL 6.5 and the temp tables created by our
stored procedures don't go away until the session is terminated. We are
creating them with a single # - so we are really unsure as to why this is
happening.

Any help or direction would be appreciated....


2. ADO in VB6 Compared to ADO in AsP

3. Hstmt error that just won't go away

4. Data control

5. Permissions won't go away

6. How to test run a procedure at SQL plus mode

7. Error 2540 Won't Go Away

8. Visual Basic & Sybase

9. .ldb file won't go away

10. Excel won't go away

11. Demo of Apollo won't go away!

12. Servlet won't go away...

13. Oracle connections that won't go away till app closes