Solved

Timeout Error

Posted on 2006-06-26
12
470 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

747 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

14 Experts available now in Live!

Get 1:1 Help Now