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

[Harald]

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]

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]

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

[Jean-Marc Gemperle]

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

[Ana Paula Bonani]

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]

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]

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