MS-SQL stops returning result

I have a procedure where in event:timer every few seconds I do:

file{prop:sql}=‘SELECT a,b from c where d=1’
next(file)

Most of the time in a day it just works. But once or twice next(file) does not return a result even though the records is there (single record, in the db all the time, does not change at all).

Then I have to restart whole app to make it work again.

As if some SQL connection would not work anymore. Any idea why? I have driver string set to /BUSYHANDLING=2, tried without the string but it’s the same.

Anyone got a solution to this?

Thanks

Bostjan

have you tried to log error with FILEERROR after next?
Sorry, have no idea otherwise

I changed code so instead of using prop:sql, I’m doing it cw way (access:file.next()).

Now I get error “connection is busy with results for another hstmt” from time to time.

No idea why. It’s a super small db, with few records in few tables…

One idea.
To check if it is clarion or database setting you could do the following.
Create simple ms dos batch or powershell, what is easier for you. Put infinite loop and sleep inside and issue sqlcmd doing the same

1 Like

Cool idea. Will do! Thx!

Hi,
Another thing worth looking at once caught me out. With event timer the Windows timer will always execute high priority requests at the expense of others in each cycle, so at times when other events took priority my Clarion timer was simply skipped. Shortening the time gave me more shots and cured it. If you don’t want to execute the action so frequently, you can use the timer to run an internal count and only send the request and reset the count on whatever value you decide is appropriate.

Yes, this is exactly what I do. I set quick timer but for slower firing I add some counter and fire only once in 10 or 20 timer cycles.