ExecuteNonQuery hangs

ExecuteNonQuery hangs

• PRSDeveloper

  You could try to call OdbcCommand.Cancel from a secondary thread. That will not use the timeout, but will instead use the ODBC API SQLCancel to attempt to stop the query from executing.

  Did you check to make sure the QUERYTIMEOUT property was not disabled via the connection string or DSN The setting I am referring to is documented here: http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp topic=/rzaik/rzaikconnstrkeywordsperfprop.htm (I mentioned this in e-mail but am including it here for others' reference). Other than that, my only suggestion is to contact IBM through support or similar forums/newsgroups. Since System.Data.Odbc is just calling into their driver internally, I can't explain why their driver is behaving like it is.

  Thanks,
  Sarah

  
  
  

• Niels A-J

  Hi,
  
  I am developing an ODBC 3.5 standard compliant driver, and would like it to work with the Visual Studio 2005 .NET Data Provider. Can you please tell me the ODBC conformance level expected by the .NET Data Provider (.NET framework v2.0)
  
  Thanks,
  Sameer
  
  

• jerv_it

  Hi Sarah,
  
  Sorry for not getting back yesterday. I upgraded my ODBC drivers, but no luck. Is there anything else that you can think of to make it work If not then I guess I'll have to live with the ExecuteNonQuery hanging once in a while (though its a huge problem).
  
  Thanks a lot for all your help,
  -Mehul Patel
  
  

• KyleLewis

  Hi Sarah,
  
  This is what I've found:
  
  DotNetNotificat 408-1e0 ENTER SQLSetStmtAttrW
  SQLHSTMT 04611838
  SQLINTEGER 0 <SQL_ATTR_QUERY_TIMEOUT>
  SQLPOINTER 0x00000037
  SQLINTEGER -5
  
  DotNetNotificat 408-1e0 EXIT SQLSetStmtAttrW with return code 0 (SQL_SUCCESS)
  SQLHSTMT 04611838
  SQLINTEGER 0 <SQL_ATTR_QUERY_TIMEOUT>
  SQLPOINTER 0x00000037 (BADMEM)
  SQLINTEGER -5
  
  
  But there is one SQL_ERROR:
  
  DotNetNotificat 408-1e0 ENTER SQLSetStmtAttrW
  SQLHSTMT 04611838
  SQLINTEGER 1228 <unknown>
  SQLPOINTER [Unknown attribute 1228]
  SQLINTEGER -6
  
  DotNetNotificat 408-1e0 EXIT SQLSetStmtAttrW with return code -1 (SQL_ERROR)
  SQLHSTMT 04611838
  SQLINTEGER 1228 <unknown>
  SQLPOINTER [Unknown attribute 1228]
  SQLINTEGER -6
  
  DIAG [HYC00] [IBM][iSeries Access ODBC Driver]Driver not capable. (30058)
  
  
  I am not sure what that is. I've sent you the whole log file.
  
  Thanks,
  -Mehul
  
  

• moodyj

  Can you provide the following additional information:

  • The type of store you are connecting to
  • The connection string (you may blur network address, user ID, and password)
  • The exact ADO.NET types you use for Connection, Command, and Transaction
  • Properties explicitly set on Connection, Command, and Transaction

  Thanks,

  Zlatko

  
  
  

• Larry Cleeton

  What type of statement are you executing (UPDATE, etc) and what is the backend database Some backends don't support CommandTimeout, so that could be why it continues to execute.

  Is the problem that the execution is taking longer than expected (i.e. you expect 5 secs and it's taking a minute), or is it that you know it's a long running query and the timeout is just not working even though it's less than the expected execution time

  What is the timeout value you are using How long does the command take to run if you run it outside of your app (in SQL Server Query Analyzer, for example, or a similar tool for your backend) Depending on the backend, it may not be possible to cancel the command, but first we need to know what the backend is.

  Thanks,
  Sarah

  
  
  

• nguyen_van

  Sarah, thanks for the great information on ODBC..! I'll give it a try and let you know my findings.
  
  -Mehul
  
  

• CMR12963

  When you are calling the ExecuteNonQuery are you also setting the Transaction property of the command to be transaction currently in use
  Like this
  myCommand.Transaction = myTransaction
  
  
  Maybe you could fill in the code for these areas so we could see what is going on.
  
  Create Command(connection, myTransaction)
  Set TimeOut property
  
  
  
  

• racka4279

  I tried executing the ExecuteNonQuery from a secondary thread and cancelling it from the main thread before the post but that fails.
  
  Yeah, I made sure that the QUERYTIMEOUT property was not disabled.
  
  Will try contacting the IBM support.
  
  Thanks again for your help Sarah,
  -Mehul
  
  

• SanooD

  Out of all of this advice, Sarah's advice seems to be the best.

  Sgtpazo

  http://www.leadersmerchantaccounts.com

  
  

• James Wilkinson

  I don't know if the ODBC driver you are using supports query timeout, but we should be able to find out if you turn on ODBC tracing. See KB 274551 for info on how to turn on this tracing (http://support.microsoft.com/Default.aspx id=274551).

  You are looking for something like this:

  odbct32w 2a4-164c ENTER SQLSetStmtAttrW
  SQLHSTMT 00941EF0
  SQLINTEGER 0 <SQL_ATTR_QUERY_TIMEOUT>
  SQLPOINTER 0x0000001E
  SQLINTEGER -3

  odbct32w 2a4-164c EXIT SQLSetStmtAttrW with return code -1 (SQL_ERROR)
  SQLHSTMT 00941EF0
  SQLINTEGER 0 <SQL_ATTR_QUERY_TIMEOUT>
  SQLPOINTER 0x0000001E
  SQLINTEGER -3

  DIAG [S1C00] [Microsoft][ODBC driver for Oracle]Driver not capable (0)

  Some of the values will be different depending on your scenario, but this is basically what you will see. If your trace shows an error like the one above, then your driver does not support query timeout.

  Your other option is to try to cancel the query from a secondary thread. In that case, you have to make your OdbcCommand object available to the other thread, and then you'll call Cancel while the main thread is still executing the ExecuteNonQuery. This will cause System.Data.Odbc to call the ODBC API SQLCancel. If the driver really supports cancelling queries in this way, it will work, otherwise it won't. The .NET data provider is totally dependent on what the underlying driver can do.

  Aside from just showing you about the query timeout, the trace will also show if ExecuteNonQuery is hanging because the ODBC execution is hanging. You will see a call to SQLExecDirect or SQLExecute. If you see an ENTER but no EXIT for the call, then the driver has not returned, and therefore ExecuteNonQuery cannot return.

  If you need help reading the trace, please email it to me. My e-mail address is in my forum profile but you need to REMOVE the word "online" from the address. If you send me something please post here so I know to expect it.

  Thanks,
  Sarah

  
  
  

• irtaza

  Hi Sarah,
  
  Before I answer your questions, I've found out something else this morning: There is a trigger on the database, and when my app inserts a row, the trigger fires up, does some work and returns the control back to my code (ExecuteNonQuery stmt). In this case, the trigger doesnt finish the work. So, I have to abort the ExecuteNonQuery depending on the timeout value set in the registry and rollback the row.
  
  I am executing an Insert statement on DB2 database on AS400 server. It's a small insert statement and on other backends it executes in less than a second. I tried using 15 seconds, and 30 seconds as the timeout values.
  
  Thanks for your reply Sarah,
  -Mehul Patel
  
  
  
  

• bslim

  I replied to this in e-mail as well, but just wanted to make a note here in case others see the same thing in their ODBC traces.

  The 1228 attribute is expected to fail for most ODBC drivers, so that is not a problem. There are a few other attributes like this as well that you will see in a typical trace. However, since the query timeout setting is not failing, yet not being honored, that is a problem. The ODBC driver seems to support this property, yet further in the trace we see SQLExecDirect appears to be hung. Although I can't tell how long it was hung from the trace, I assume it's longer than the 55 seconds set in the query timeout, otherwise this would be expected if the query is long-running.

  It is the responsibility of the ODBC driver to respect the timeout value, if it supports that property. System.Data.Odbc will not enforce its own timeout, but instead delegates to the driver.

  Thanks,
  Sarah

  
  
  

• Bone

  Hi Zlatko,
  
  * I am connecting to DB2 on AS400.
  * connstr = "DSN=" & _regSettings.DSNname & ";Uid=" & _regSettings.DSNuser _
  & ";Pwd=" & _regSettings.DSNpswd
  
  * I am using ODBCConnection, ODBCCommand and ODBCTransaction
  * All the properties are default values other than the CommandTimeout for the ODBCCommand object.
  
  I've provided more information in the reply I posted to Sarah's question.
  
  Thanks a lot for your help Zlatko,
  -Mehul Patel
  
  
• ExecuteNonQuery hangs