Foxpro with SQL Server, view not returning records

[bernardmanning]

Hi,

I wonder if somebody could shed some light on a problem i'm having using remote views to connect to a sql server database.

I'm seeing a problem where a view is not returning a recordset when it should do, stopping and starting the sql service and re-running my app makes the data show up correctly.

I've looked at the list of locks in sql server using the sp_lock stored procedure and see only shared locks.

I've checked my connection properties and the querytimeout and idletimeout properties are set to zero , wait forever,

has anybody got any suggestions?

Thanks, bernard

 

[Mike Lewis]

Bernard,

Are you confident that the query is actually running (even though it is running incorrectly)? In other words, is it possible that SQL Server is returning an error that you are not trapping, and that you are looking at an out-of-date version of the result cursor?

Assuming you are using a connection object (in a DBC), I would suggest you enable the 'display warning' setting. That will cause any error returned from the server to show up as a messagebox. It won't explain the problem, but it might provide more information.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[bernardmanning]

Thanks for the replies,

This is occuring out on site and I don't have access or seen this problem firsthand which makes it difficult.

I've set up a sql trace file and can see the statements being sent to sql server and they look ok.

The weird thing about this problem is that once you stop the service and restart it's fine.

The view in question is a foxpro remote view which makes use of a server side view within sql server.

Mike, I too thought the problem may be some error that we weren't picking up, but after cuttting the definition from the trace file and resubmitting it in the query analyser it runs fine.

This error occurs infrequently, once in three weeks, however it's a production environment and I need to nail it down.

Thanks, Bernard