Timeout Error

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


dchau12Asked:
Who is Participating?
 
ShogunWadeConnect With a Mentor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
dchau12Author Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
having a (clustered) index on the field year_1 + month_1 should solve the timeout...
note: 1 index with both fields in it
0
 
Atlanta_MikeCommented:
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
 
dchau12Author Commented:
Its only got about 800 records in it, but about 300 columns.  

0
 
dchau12Author Commented:
I run the exact same query from enterprise manager and it runs instantly.  Wierd...
0
 
dchau12Author Commented:
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
 
dchau12Author Commented:
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
 
dchau12Author Commented:
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
 
dchau12Author Commented:
Good call ShogunWade
0
 
ShogunWadeCommented:
You're welcome
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.