FoxPro Version 8 View with SQL Server, Adding NoLock statement

Hi,

I have a foxpro view on a remote SQL sever table. When I look at the view code, I see that it queries the SQL Server table without using the 'nolock' statement within the query. This slows down our database which is used by many users. Is there any way to insert the nolock statement into the Foxpro view?

Foxpro version 8.0

esak2000Asked:
Who is Participating?
 
pcelbaCommented:
So, let suppose you've created a shared connection to the database. Then you may connect using SQL pass-through function SQLCONNECT() and issue the following command:

SQLEXEC(lnHandle, "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED")

which will force all SELECT commands to work as if NOLOCK is used.

lnHandle is connection handle returned by SQLCONNECT()

More details: http://msdn.microsoft.com/en-us/library/ms173763(SQL.90).aspx
http://msdn.microsoft.com/en-us/library/0esyccz6(VS.80).aspx
0
 
jrbbldrCommented:
One way to accomplish this would be to not use a View, but instead use SQL Pass Though.

If you did it that way, you would utilize the appropriate SQL Server Syntax within your command and it could include the NOLOCK option.

When you execute the SQLEXEC() you would return the results and use it as needed.

Good Luck

0
 
esak2000Author Commented:
Thanks for the response.

I'm aware of that solution, but I can't use it because I'm working with a third party software that relies on views and to use SQL pass through would require rewriting the entire software.

Is there any other way to insert the nolock into the view itself?
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
pcelbaCommented:
If you are working with 3rd party software then you should not change their settings. Dirty reads could cause unpredictable problems.
0
 
esak2000Author Commented:
Thanks for the advice. I'm willing to take the risk if there is a solution.
0
 
Olaf DoschkeSoftware DeveloperCommented:
What do you mean by looking at the view code? Are you actually looking at what code is executed on the SQL Server side or are you looking at the SQL of the VFP view designer for remote views?

When using a view and selecting data the SQL Select code sent to SQL Server surely is rather 1:1 what you see in VFPs view designer.

The only influence you have is within the Connection. Views defined by
CREATE VIEW [ViewName] ... CONNECTION ConnectionName SHARE ... or by selecting "Share connection" in the Remote view defaults area of the Remote Data Tab of the VFP Extras->Options dialog before creating remote views.

If the remote views share a connection you can open that connection manually by SQLCONNECT() and then apply pcelba's SQLEXEC(lnHandle, "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED")

But that also requires a change of the software. No way to do that on the DBC itself without changing the EXE of the application, I fear.

Bye, Olaf.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.