Solved TD-18618: TD 6.1 SP2 .NET - Timeout always 30 sec in .NET

Post found bugs and possible workarounds.
Harald
Germany
Posts: 164
Joined: 30 Mar 2017, 06:16
Location: Rosenheim, Germany

TD-18618: TD 6.1 SP2 .NET - Timeout always 30 sec in .NET

Post by Harald » 12 Oct 2012, 10:00

When a huge query against a SQLserver 2008 database is executed after 30 seconds a timeout is reported. SqlSetParameter with DBP_LOCKWAITTIMEOUT has no effect, it’s not possible to change this value. We need a different timeout-handling or possibility for setting for some report-queries.

Testcase: not available, we have tried to cause a lock with a non commited update, but a second db cursor with a different user was able to read the record, maybe DBP_AUTOCOMMIT has also no effect?

Jeff Luther

Re: TD-18618: TD 6.1 SP2 .NET - Timeout always 30 sec in .NET

Post by Jeff Luther » 17 Oct 2012, 21:29

Hi, Harald:

While Ana is waiting for a reply to her internal email about your timeout, a couple questions occurred to me that I thought I'd ask based on a couple things you wrote:
after 30 seconds a timeout is reported
What's the 'report'? A SQL error from TD? Or...? It would be useful to know the exact error # and msg. syntax of this timeout report.

** Suggestion: That you try your long query by running it directly in SQLTalk (the exe is in the TD install folder) and see what, if any, difference there is in the behavior. Does the same timeout report occur?
(NOTE: I use SQLTalk a lot to test a query or other SQL to see if some issue in TD is reproducible in SQLTalk. BTW, there is one diff. between TD and SQLTalk upon connection: By default TD has Result Set mode = 1/ON/TRUE, SQLTalk has RS mode = 0/OFF/FALSE.)
...we have tried to cause a lock with a non commited update, but a second db cursor with a different user was able to read the record
"second DB cursor" -- second cursor/Sql Handle in the same appl? Or in a different appl? If in the same appl, that makes sense, generally, all handles to the same DB in the same appl. are in the same transaction, so you can update with handle1, then fetch the updated row with handle2.

If, however, this second cursor was in a separate appl -- that would be part of a test you could write -- then that surprises me. With a different appl, different transaction and any locked row(s) because of the DML (update, insert, delete) from app1 with no COMMIT should certainly have the server put app2's SELECT into 'wait mode,' waiting for app1's COMMIT of the changes.

Harald
Germany
Posts: 164
Joined: 30 Mar 2017, 06:16
Location: Rosenheim, Germany

Re: TD-18618: TD 6.1 SP2 .NET - Timeout always 30 sec in .NET

Post by Harald » 19 Oct 2012, 13:09

Hello Jeff,

thanks for the answer (of course also thanks to Ana).

I made a screenshot, it seems to be a .NET exception:
Timeout.png
The exception occurs exactly 30 sec after I started the report. It's a very huge report which seqarches in 3 databases with ~90GB, and it can run more than 2 hours if I select a long time period. I tried to set the lock timeout to a high value, but every time after 30 sec I got the error message.

For the "second DB cursor", i used the same application and the same DB, but a different user. But it could be possible, that the reason is ADS. The SQl Server is running in a mixed authorization mode with ADS and database users, so the reason for the lock behaviour in this testcase could be, that the user in SqlUser was ignored and I was connected with my network user.

Kind regards
harald
You do not have the required permissions to view the files attached to this post.

Jean-Marc Gemperle

Re: TD-18618: TD 6.1 SP2 .NET - Timeout always 30 sec in .NET

Post by Jean-Marc Gemperle » 29 Oct 2012, 12:43

Hi

This problem is easily reproducible on slqserver using the wait for delay command with a query.
My sqlsever SETTINGS would have the COMMAND TIMEOUT or ( SQL QUERY TIMEOUT) set to 600 seconds ( right click server in sqlserver management studio and see properties/connections/remote query time out). Setting it to 0 should give no timeout...
With .NET it seems indeed the timeout is set by default to 30 seconds...Its not a specific settings from us but the default it seems for .NET command timeout on SQLSERVER. Programatically you should be able to change it on the COMMAND object ( not the connection string.... ) but in TD .NET you do not have this option SO IMO this will endup to be a feature requests... though will ask If I missed something....The problem here is that setting that command timeout on SQLSERVER itself does not seem to work.

The attached tescase shows a query delayed for one minute would return its resutls after a minute in WIN32 BUT it would timeout in .NET

Setting DB parameter on ANYTHING except SQLBASE ( at the time with the router) is done by using SqlSetParameterAll() NOT SqlSerParameter() ( AFAIK specificallt for SB)

In sqlnwkcn.apl DBPROP_COMMANDTIMEOUT= 34 would DEFINE the command timeout...BUT this seems to be ONLY FOR OLEDB AFAIK
LOCKTIMEOUT would not work it is a completely different setting, and autcommit would do nothing...

So at this point indeed we do not have a mean in TD .NET it seems to change the default COMMAND TIMEOUT of 30 sec to something else as far as I can see

Cheers
JM
You do not have the required permissions to view the files attached to this post.

Ana Paula Bonani

Re: TD-18618: TD 6.1 SP2 .NET - Timeout always 30 sec in .NET

Post by Ana Paula Bonani » 06 Nov 2012, 09:40

Just to update this thread with the defect number logged for it:

TD-18618: TD .NET and SQLSERVER not possible to change default 30 seconds command time out

Regards,

Jean-Marc Gemperle

Re: TD-18618: TD 6.1 SP2 .NET - Timeout always 30 sec in .NET

Post by Jean-Marc Gemperle » 13 Nov 2012, 14:55

Hi

It tuned out that this can be achieved using
If SqlSetParameter( hSql, DBP_TIMEOUT, dfTimeoutInSeconds, '' )

So just modify to use this above in the original testcase pbHowto
instead of this bellow where I knew this would not work as it is only for OLEDB....

! JM this bellow would not work believe for OLEDB only
If SqlSetParameterAll( hSql, DBPROP_COMMANDTIMEOUT, 1, '', TRUE )
Call SalMessageBox( ", SqlSetParameterAll of DBPROP_COMMANDTIMEOUT returned TRUE", "info", 0 )

This shall get documented.
JM

Harald
Germany
Posts: 164
Joined: 30 Mar 2017, 06:16
Location: Rosenheim, Germany

Re: TD-18618: TD 6.1 SP2 .NET - Timeout always 30 sec in .NET

Post by Harald » 15 Nov 2012, 10:28

Hello JM,

thanks for the info. I tested this parameter, and it is very helpful. It's not possible to set it to "wait forever" with -1, but I can enter a very high value. In the moment I get a lot of .NET-exceptions after a longer time period (> 1 hr). I will investigate this.

Kind regrds
Harald

Return to “Bug Reports”

Who is online

Users browsing this forum: [Ccbot] and 0 guests