Remote View Indexes

Is there a way to create persistant indexes on a remote view. I have a
sample table that I upsized to MS SQL server and am trying to go through the
existing code that I have switched to access a remote view of the MS SQL
data. A lot of the code uses the set and seek statement like the following:

SET ORDER TO TAG InvID
SEEK Invoice.InvID

Is there any way that a remote view will work with that line of code. Any
help would be greatly apperciated. If at all possible we would like to not
modify this code as it is repeated everywhere throughout the application.

Thanks,
Curtis

Dan
Tue Oct 11 18:00:38 CDT 2005

No. Indexes on remote views are not persistent.

A far better way to deal with this sort of remote view is to make it
parameterized on Invoice.invID. In your code, you'd:

lnViewParameter = Whatever
Requery("YourRemoteView")

The result would only be the matching record. Bringing down only one record,
as you need it, would improve performance and reduce network bandwidth.

Dan

Curtis wrote:
> Is there a way to create persistant indexes on a remote view. I have a
> sample table that I upsized to MS SQL server and am trying to go
> through the existing code that I have switched to access a remote
> view of the MS SQL data. A lot of the code uses the set and seek
> statement like the following:
>
> SET ORDER TO TAG InvID
> SEEK Invoice.InvID
>
> Is there any way that a remote view will work with that line of code.
> Any help would be greatly apperciated. If at all possible we would
> like to not modify this code as it is repeated everywhere throughout
> the application.
>
> Thanks,
> Curtis

Curtis
Wed Oct 12 09:19:55 CDT 2005

Thanks for the reply. Let me explain our situation a little further just to
make sure I am not missing something here. We have a rather large
application that has grown in use to warrent migrating the data to MS SQL
server away from visual foxpro dbf tables. I was under the impression that
remote views would work very similar to vfp dbfs and am therefore
experminting on a few tables. The objective would be to move the data to sql
while changing as little foxpro code as possible. It appears that everywhere
the code makes use of an index we will have to modify that code is that
correct? Is there any other differences between how vfp code calls dbfs
versus remote views? Or should we just plan on changing a whole lot of
coding and call the remote SQL server data directly through SQLEXEC calls.
Any comments or resources would be greatly apperciated.

Thanks,
Curtis

"Dan Freeman" <spam@microsoft.com> wrote in message
news:e$9xZerzFHA.3180@TK2MSFTNGP14.phx.gbl...
> No. Indexes on remote views are not persistent.
>
> A far better way to deal with this sort of remote view is to make it
> parameterized on Invoice.invID. In your code, you'd:
>
> lnViewParameter = Whatever
> Requery("YourRemoteView")
>
> The result would only be the matching record. Bringing down only one
> record,
> as you need it, would improve performance and reduce network bandwidth.
>
> Dan
>
> Curtis wrote:
>> Is there a way to create persistant indexes on a remote view. I have a
>> sample table that I upsized to MS SQL server and am trying to go
>> through the existing code that I have switched to access a remote
>> view of the MS SQL data. A lot of the code uses the set and seek
>> statement like the following:
>>
>> SET ORDER TO TAG InvID
>> SEEK Invoice.InvID
>>
>> Is there any way that a remote view will work with that line of code.
>> Any help would be greatly apperciated. If at all possible we would
>> like to not modify this code as it is repeated everywhere throughout
>> the application.
>>
>> Thanks,
>> Curtis
>
>

Dummy
Wed Oct 12 09:36:28 CDT 2005

> It appears that everywhere
> the code makes use of an index we will have to modify that code is that
> correct? Is there any other differences between how vfp code calls dbfs

Anywhere (probably) you use anything other than SQL statements you'll need to
adjust. Maybe not much, but you will.

Regards
Mark

Fred
Wed Oct 12 10:54:01 CDT 2005

Using a backend database is an entirely different mindset, that's what
you'll have to adjust. <s>

No longer will you have the luxury have having all the records at your
finger tips, you'll need to start thinking in record sets, not entire tables
with all the records being available. You only pull down the records that
you need, not the entire table.

--
Fred
Microsoft Visual FoxPro MVP


"Curtis" <csch_nu@hotmail.com> wrote in message
news:%23FKyGgzzFHA.3780@TK2MSFTNGP12.phx.gbl...
> Thanks for the reply. Let me explain our situation a little further just
> to make sure I am not missing something here. We have a rather large
> application that has grown in use to warrent migrating the data to MS SQL
> server away from visual foxpro dbf tables. I was under the impression that
> remote views would work very similar to vfp dbfs and am therefore
> experminting on a few tables. The objective would be to move the data to
> sql while changing as little foxpro code as possible. It appears that
> everywhere the code makes use of an index we will have to modify that code
> is that correct? Is there any other differences between how vfp code calls
> dbfs versus remote views? Or should we just plan on changing a whole lot
> of coding and call the remote SQL server data directly through SQLEXEC
> calls. Any comments or resources would be greatly apperciated.
>
> Thanks,
> Curtis
>
> "Dan Freeman" <spam@microsoft.com> wrote in message
> news:e$9xZerzFHA.3180@TK2MSFTNGP14.phx.gbl...
>> No. Indexes on remote views are not persistent.
>>
>> A far better way to deal with this sort of remote view is to make it
>> parameterized on Invoice.invID. In your code, you'd:
>>
>> lnViewParameter = Whatever
>> Requery("YourRemoteView")
>>
>> The result would only be the matching record. Bringing down only one
>> record,
>> as you need it, would improve performance and reduce network bandwidth.
>>
>> Dan
>>
>> Curtis wrote:
>>> Is there a way to create persistant indexes on a remote view. I have a
>>> sample table that I upsized to MS SQL server and am trying to go
>>> through the existing code that I have switched to access a remote
>>> view of the MS SQL data. A lot of the code uses the set and seek
>>> statement like the following:
>>>
>>> SET ORDER TO TAG InvID
>>> SEEK Invoice.InvID
>>>
>>> Is there any way that a remote view will work with that line of code.
>>> Any help would be greatly apperciated. If at all possible we would
>>> like to not modify this code as it is repeated everywhere throughout
>>> the application.
>>>
>>> Thanks,
>>> Curtis
>>
>>
>
>

Dan
Wed Oct 12 10:53:02 CDT 2005

As Mark said, you're going to have to rewrite something. How much depends on
you and your application.

Moreover, the whole "zen" of using a database changes when you go to a
remote back end. I'm willing to bet dinner and a movie your dbf-based code
didn't take the applicable considerations into account.

Go to www.hentzenwerke.com and grab a copy of the book in their catalog on
Client-Server apps with VFP.

While you *can* simply swap out your tables for remote views, I wouldn't
recommend it. You'll work hard to get it going and still won't be taking
advantage of the new platform.

Dan

Curtis wrote:
> Thanks for the reply. Let me explain our situation a little further
> just to make sure I am not missing something here. We have a rather
> large application that has grown in use to warrent migrating the data
> to MS SQL server away from visual foxpro dbf tables. I was under the
> impression that remote views would work very similar to vfp dbfs and
> am therefore experminting on a few tables. The objective would be to
> move the data to sql while changing as little foxpro code as
> possible. It appears that everywhere the code makes use of an index
> we will have to modify that code is that correct? Is there any other
> differences between how vfp code calls dbfs versus remote views? Or
> should we just plan on changing a whole lot of coding and call the
> remote SQL server data directly through SQLEXEC calls. Any comments
> or resources would be greatly apperciated.
>
> Thanks,
> Curtis
>
> "Dan Freeman" <spam@microsoft.com> wrote in message
> news:e$9xZerzFHA.3180@TK2MSFTNGP14.phx.gbl...
>> No. Indexes on remote views are not persistent.
>>
>> A far better way to deal with this sort of remote view is to make it
>> parameterized on Invoice.invID. In your code, you'd:
>>
>> lnViewParameter = Whatever
>> Requery("YourRemoteView")
>>
>> The result would only be the matching record. Bringing down only one
>> record,
>> as you need it, would improve performance and reduce network
>> bandwidth.
>>
>> Dan
>>
>> Curtis wrote:
>>> Is there a way to create persistant indexes on a remote view. I
>>> have a sample table that I upsized to MS SQL server and am trying
>>> to go through the existing code that I have switched to access a
>>> remote view of the MS SQL data. A lot of the code uses the set and
>>> seek statement like the following:
>>>
>>> SET ORDER TO TAG InvID
>>> SEEK Invoice.InvID
>>>
>>> Is there any way that a remote view will work with that line of
>>> code. Any help would be greatly apperciated. If at all possible we
>>> would like to not modify this code as it is repeated everywhere
>>> throughout the application.
>>>
>>> Thanks,
>>> Curtis