How to prevent query timeout expired SQLNCLI11 error 80040e31

Question

I have a connection to a MS SQL Server 2012 database in classic ASP  VBScript   This is my connection string   Provider SQL Server Native Client 11 0 Server localhost  Database databank Uid myuser Pwd mypassword    When I execute this SQL command   UPDATE  info  SET  stamp   2014-03-18 01 00 02    data   12533 characters goes here    saved   2014-03-18 01 00 00    confirmed  0  ip  0  mode  3  rebuild  0   updated  1  findable  0  WHERE  ID  193246    I get the following error   Microsoft SQL Server Native Client 11 0  error  80040e31  Query timeout expired     functions asp  line 476   The SQL query is pretty long  the data field is updated with 12533 characters  The ID column is indexed so finding the post with ID 193246 should be fast   When I execute the exact same SQL expression  copied and pasted  on SQL Server Management Studio it completes successfully in no time  No problem what so ever  So there isn t a problem with the SQL itself  I ve even tried using a ADODB Recordset object and update via that  no self-written SQL  but I still get the same timeout error   If I go to Tools   Options   Query Execution in the Management Studio I see that execution time-out is set to 0  infinite   Under Tools   Options   Designers I see that transaction time-out is set to 30 seconds  which should be plenty enough since the script and database is on the same computer   localhost  is in the connection string    What is going on here  Why can I execute the SQL in the Management Studio but not in my ASP code     Edit  Tried setting the 30 sec timeout in the Designers tab to 600 sec just to make sure  but I still get the same error  happens after 30 sec of page loading btw    Here is the code that I use to execute the SQL on the ASP page   Set Conn   Server CreateObject  ADODB Connection   Conn Open  Provider SQL Server Native Client 11 0  Server localhost Database databank Uid myuser Pwd mypassword   Conn Execute  UPDATE  info  SET  stamp   2014-03-18 01 00 02    data   12533 characters goes here   saved   2014-03-18 01 00 00    confirmed  0  ip  0  mode  3  rebuild  0  updated  1  findable  0  WHERE  ID  193246       Edit 2  Using Conn CommandTimeout   0 to give infinite execution time for the query does nothing  it just makes the query execute forever  Waited 25 min and it was still executing   I then tried to separate the SQL into two SQL statements  the long data update in one and the other updates in the other  It still wouldn t update the long data field  just got timeout   I tried this with two additional connection strings   Driver  SQL Server  Server localhost Database databank Uid myuser Pwd mypassword  Driver  SQL Server Native Client 11 0  Server localhost Database databank Uid myuser Pwd mypassword    Didn t work  I even tried changing the data to 12533 A s just to see if the actual data was causing the problem  Nope  same problem   Then I found out something interesting  I tried to execute the short SQL first  before the long update of the data field  It ALSO got query timeout exception     But why  It has so little stuff to update in it  the whole SQL statement is less than 200 characters   Will investigate further     Edit 3  I thought it might have been something to do with the login but I didn t find anything that looked wrong  I even tried changing the connection string to use the sa-account but even that didn t work  still getting  Query timeout expired    This is driving me mad  There is no solution  no workaround and worst of all no ideas     Edit 4  Went to Tools   Options   Designers in the Management Studio and ticked off the  Prevent saving changes that require table re-creation   It did nothing   Tried changing the  data  column data type from  nvarchar MAX   to the inferior  ntext  type  I m getting desperate   It didn t work   Tried executing the smallest change on the post I could think of   UPDATE  info  SET  confirmed  0 WHERE  ID  193246    That would set a bit column to false  Didn t work  I tried executing the exact same query in the Management Studio and it worked flawlessly   Throw me some ideas if you have got them because I m running out for real now     Edit 5  Have now also tried the following connection string   Provider SQLOLEDB 1 Password mypassword Persist Security Info True User ID myuser Initial Catalog databank Data Source localhost   Didn t work  Only tried to set confirmed to false but still got a time out     Edit 6  Have now attempted to update a different post in the same table   UPDATE  info  SET  confirmed  0 WHERE  ID  1    It also gave the timeout error  So now we know it isn t post specific   I am able to update posts in other tables in the same  databank  database via ASP  I can also update tables in other databases on localhost   Could there be something broken with the  info  table  I used the MS Access wizard to auto move data from Access to MS SQL Server 2012  it created columns of data type  ntext  and I manually went and changed that to  nvarchar MAX   since ntext is deprecated  Could something have broken down  It did require me to re-create the table when I changed the data type   I have to get some sleep but I will be sure to check back tomorrow if anybody has responded to me  Please do  even if you only have something encouraging to say     Edit 7  Quick edit before bed  Tried to define the provider as  SQLNCLI11  in the connection string as well  using the DLL name instead of the actual provider name   It makes no difference  Connection is created just as fine but the timeout still happens   Also I m not using MS SQL Server 2012 Express  as far as I know   Express  wasn t mentioned anywhere during installation   It s the full thing   If it helps  here s the  Help     About     info that is given by the Management Studio   Microsoft SQL Server Management Studio  11 0 2100 60   Microsoft Analysis Services Client Tools  11 0 2100 60   Microsoft Data Access Components  MDAC   6 3 9600 16384   Microsoft MSXML  3 0 5 0 6 0    Microsoft Internet Explorer  9 11 9600 16521   Microsoft  NET Framework  4 0 30319 34011   Operating System  6 3 9600     Edit 8  also known as the  programmers never sleep  edit    After trying some things I eventually tried to close the database connection and reopening it right before executing the SQL statements  It worked all of a sudden  What the      I have had my code inside a subroutine and it turns out that outside of it the post that I was trying to update was already opened  So the reason for the timeout was that the post or the whole table was locked by the very same connection that tried to update it  So the connection  or CPU thread  was waiting for a lock that would never unlock   Hate it when it turns out to be so simple after trying so hard   The post had been opened outside the subroutine by this simple code   Set RecSet   Conn Execute  SELECT etc     I just added the following before calling the subroutine   RecSet Close Set RecSet   Nothing   The reason why this never crossed my mind is simply because this was allowed in MS Access but now I have changed to MS SQL Server and it wasn t so kind  or sloppy  rather   The created RecSet by Conn Execute   had never created a locked post in the database before but now all of a sudden it did  Not too strange since the connection string and the actual database had changed   I hope this post saves someone else some headache if you are migrating from MS Access to MS SQL Server  Though I can t imagine there are that many Access users left in the world nowadays

User · Accepted Answer

Turns out that the post  or rather the whole table  was locked by the very same connection that I tried to update the post with   I had a opened record set of the post that was created by   Set RecSet   Conn Execute     This type of recordset is supposed to be read-only and when I was using MS Access as database it did not lock anything  But apparently this type of record set did lock something on MS SQL Server 2012 because when I added these lines of code before executing the UPDATE SQL statement     RecSet Close Set RecSet   Nothing      everything worked just fine   So bottom line is to be careful with opened record sets - even if they are read-only they could lock your table from updates

[sql] How to prevent 'query timeout expired'? (SQLNCLI11 error '80040e31')

Examples related to sql

Examples related to sql-server

Examples related to vbscript

Examples related to asp-classic

Examples related to sql-server-2012-express