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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FoxPro

From novice to tech pro — start learning today.