Solved

Timeout Error

Posted on 2006-06-26
12
472 Views
Last Modified: 2008-01-09
Hi Guys,

I keep getting a timeout error in sql server when I try to run a delete or update query.  Is there any way to increase this timeout threshhold or turn it off all together?

Thanks,

Ryan


0
Comment
Question by:dchau12
  • 7
  • 2
  • 2
  • +1
12 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16986148
first thing to try would be to check why the query takes that much time to cross the timeout.
also, you need to specify which coding language you use to help you with setting timeouts
0
 

Author Comment

by:dchau12
ID: 16986173
It is not a complicated query.  It is something like this:

delete from table 1
where month_1 = 5
and year_1 = 2006

It should delete about a hundred records.  The coding language is vb.net.

Ryan
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16986181
having a (clustered) index on the field year_1 + month_1 should solve the timeout...
note: 1 index with both fields in it
0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16986739
If you have added the index Angel referred to...and still having issues:

In vb you can set the COMMAND TIMEOUT value.

How big is the table you are trying to delete from?
0
 

Author Comment

by:dchau12
ID: 16986793
Its only got about 800 records in it, but about 300 columns.  

0
 

Author Comment

by:dchau12
ID: 16986802
I run the exact same query from enterprise manager and it runs instantly.  Wierd...
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:dchau12
ID: 16986838
Here is the exact code I am using to execute the query:

'**** Transaction set information for ComericaSurvey *******
        Dim ConnectionString2 As String
        ConnectionString2 = Utils.getSurvConnectionString
        Dim con2 As New SqlClient.SqlConnection(ConnectionString2)
        con2.Open()
        Dim tr2 As SqlClient.SqlTransaction = con2.BeginTransaction()
        Dim cmd2 As New SqlClient.SqlCommand
        '***********************************************************

        strqry = " DELETE from tbl_ComConsumer_Survey " & _
                 " WHERE QreportMonth_1 = " & Calculate.txt_AnalysisMonth.Text & " " & _
                 " AND QreportYear_1 = " & Calculate.txt_AnalysisYear.Text & ""
        cmd2 = New SqlClient.SqlCommand(strqry, con2, tr2)
        cmd2.ExecuteNonQuery()

I tried the following directly after the cmd2 sql command declaration and it had no effect.  I still get the timeout error:

cmd2.CommandTimeout = 0
I also tried
cmd2.CommandTimeout = 3600

0
 

Author Comment

by:dchau12
ID: 16986917
AngelIII,

I tried creating the clustered index, but I already have a primary key on that table, and it says that I cannot have more than one clustered index per table.  When I try to remove the clustered property from the primary key, it tell me that sql server cannot have more than 249 nonclustered indicies or column statistics on one table.

Ryan
0
 

Author Comment

by:dchau12
ID: 16986951
I think I figured it out.  I had a previous transaction set that I had not commited before I tried to execute this one with a new connection and transaction object.  That seemed to be the problem.  When I commented out the code from the previous transactions, it ran fine.

Ryan
0
 
LVL 18

Accepted Solution

by:
ShogunWade earned 500 total points
ID: 16990042
for reference.....    if a small statement like this is timing out it is often due to blocking,  running sp_who2 will show you if it is and who is blocking.   This is the case here.
0
 

Author Comment

by:dchau12
ID: 16992734
Good call ShogunWade
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 16995644
You're welcome
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now