16

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.

user692942
  • 16,398
  • 7
  • 76
  • 175
user3435078
  • 329
  • 1
  • 2
  • 10
  • 1
    If you are using SqlCommand mySql, then try mySql.CommandTimeout = 0. – Erran Morad Mar 18 '14 at 21:24
  • There is no MySQL or MySQL related tools here, only MS SQL. – user3435078 Mar 18 '14 at 21:26
  • mySql is the name of the variable. I know its SQL server. – Erran Morad Mar 18 '14 at 21:26
  • Which class do you use to store and execute your SQL command ? – Erran Morad Mar 18 '14 at 21:27
  • Class? Sorry, I don't know what you mean. Is it something in MS SQL server? I'm new to it, just changed all databases from the old Access type (where this code worked fine). If you mean in my code, I don't have any classes. Maybe you mean what object I use to execute the SQL command? I will add it to my post, please hang on a second. – user3435078 Mar 18 '14 at 21:29
  • Alright, added the code. Pardon the delay. – user3435078 Mar 18 '14 at 21:35
  • More on your error - http://technet.microsoft.com/en-us/library/ms190181(v=sql.105).aspx . Add this `Conn.CommandTimeout = 0` before you do Conn.Execute(blah...blah....) – Erran Morad Mar 18 '14 at 21:48
  • Did you remove your answer? Was just going to post this reply: Tried setting it to 600 sec and so far the page has been loading for 7 minutes. Could it be that the provider refuses to deliver to the database engine? Is there a different connection string I should try? I've tried using driver instead of provider but then it had big problems delivering text to ASP, especially if it was long text (also recordset fields turned out blank after they had been read once while with provider I can read many times from the same recordset field). – user3435078 Mar 18 '14 at 21:51
  • After running for 10 minutes I finally get the same "Query timeout expired" error. I doubt it should take that long, especially since it finishes in no time when executed inside the Management Studio. – user3435078 Mar 18 '14 at 21:56
  • I removed my answer because I do NOT know ASP VB_Script. I could have added it as a comment instead, but comment boxes have too little space. I tried the `CommandTimeout = 0` a while ago in another language for a timeout and it helped me. If it works for you, then let me know. – Erran Morad Mar 18 '14 at 21:57
  • Like I said, it didn't help. Just for good measure I tried setting it t0 0 as well (instead of 600) and it just executes forever (5 min so far). People need to stop worrying about being voted down on stackoverflow, these comment fields often contain more info than the actual answers. Pain to read and not what stackoverflow intended. They should remove the ability to vote an answer negative, that would probably make people less afraid to give answers that they don't think might be accepted as correct. – user3435078 Mar 18 '14 at 22:19
  • Wait for other members to answer. Until then, google is your friend. Also, try the troubleshooting tips for the error here - http://technet.microsoft.com/en-us/library/ms190181(v=sql.105).aspx – Erran Morad Mar 18 '14 at 22:21
  • Tried all of that and I've been googling constantly from 30 minutes before I started this thread. Google isn't my friend, it just feeds me lies. I'm very tired of unexplainable nonsense related to databases. The UPDATE query has been running for 18 minutes now, still hasn't finished. This is really, REALLY testing my patience. There is no explanation for this and this question has less than 17 views on stackoverflow, and most views are gotten the first hour. This won't be answered and I'm stuck with my enemy google who is doing nothing for me right now. – user3435078 Mar 18 '14 at 22:31
  • I'm wondering about the connection string... This is the format I've used in the past with VBScript: `Provider=SQLOLEDB.1;Password=YourPassword;Persist Security Info=True;User ID=YourLogin;Initial Catalog=YourDatabase;Data Source=YourSqlServer`. Wanna try that? – Dave Mason Mar 18 '14 at 23:20
  • I'll gladly give it a go, please hang on. – user3435078 Mar 18 '14 at 23:30
  • Didn't work. It accepted the connection string but upon execute I got the same old "Query timeout expired". – user3435078 Mar 18 '14 at 23:35
  • The usual way of specifying Native Client 11 is `Provider=SQLNCLI11`, does that make any difference? Also, are you using the Express version of SQL Server – John Mar 18 '14 at 23:57
  • Sadly it doesn't matter if I use the short name of the provider, still get the timeout. I'm not using Express either. – user3435078 Mar 19 '14 at 00:20
  • Do `SELECT` statements timeout as well? What about `INSERT` or `DELETE`? – Bond Mar 19 '14 at 01:12
  • Just solved it, will update my original post. Gah, it was so simple too... – user3435078 Mar 19 '14 at 01:29
  • Your question about SELECT/INSERT/DELETE was valid btw, I didn't ignore you. I just didn't see your suggestion before I had solved it so I never needed to try it out. Knowing now that the post (or whole table probably since I couldn't update a different post) was locked I don't think INSERT/DELETE would have worked, but SELECT might have. – user3435078 Mar 19 '14 at 01:49
  • I'm still curious about your solution (Edit 8). Using `Conn.Execute()` should return a *read-only* recordset (`adLockReadOnly`) by default. Therefore, you should still be able to update the record even with an open recordset. You're not using `Recordset.Open()` somewhere else, are you? – Bond Mar 19 '14 at 01:58
  • I have to ask here, are you really pushing 12533 characters in the text field? Do you not have problems with that? [Have a read of this](http://technet.microsoft.com/en-us/library/ms143432.aspx) it may be helpful. – Paul Mar 19 '14 at 10:41
  • Bond: That's what I feel was strange as well, I'm pretty sure it is still a read-only recordset returned but it still locked the post. I didn't have to close anything else to get it to work. Lankymart: I can do that, didn't do that before since stackoverflow have a dumb 8 hour time limit before you can accept our own answers for new accounts. Westie: The column data type is nvarchar(max) which supports something like 2 GB of data and the max length of SQL is over 65000 characters so it worked without a problem. – user3435078 Mar 19 '14 at 11:09
  • Now I'm getting "You can accept your own answer tomorrow". Stackoverflow, this is retarded. People like me just create an account, post a question and then want to forget about it and move on once it's done. I don't want to have to come back here day after day just because your system is broken. I really miss the days when you could post questions here without an account. Imagine how much great info has been lost because so many won't bother to sign up... Many times I have wanted to post but didn't just because I wouldn't bother with an account. – user3435078 Mar 19 '14 at 11:17
  • @user3435078: I did suspect as much, but still had to ask. – Paul Mar 20 '14 at 09:57
  • Can finally accept my answer. I can't even imagine how many that don't put up with this waiting, so many unaccepted answers just because of this stupid wait. – user3435078 Mar 21 '14 at 12:16
  • I created some Indexes on the table, and this fixed the Query timeout. – MarceloBarbosa Sep 08 '14 at 13:36

1 Answers1

14

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.

user3435078
  • 329
  • 1
  • 2
  • 10