FoxPro Help and Support - Maximum sized database or table?

Author

Maximum sized database or table?

Barley Man

2005-05-18, 9:24 am

I have been trying to read a client's SQL database using MSDE. However, it's
over 2gb in size and MSDE will not allow access to dtabases above that size.
I now have agreed with the client to access it via their FULL copy of SQL
Server 2000 and that gets round that 2Gb limit, I hope.

However, if I use an ODBC link and read that SQL file by 'simple' ODBC, will
I ALSO run into an maximum database file size in VFP7? To clarify the
question, does VFP7 have a maximum database size and/or is there a limit to
the SQL database that I can access within VFP7 that wil prevent me from
reading a 3gb, 4gb or 10gb SQL database?

I appreciate that, if I can access 10gb SQL databases, I can always use an
SQLEXEC statement to return a reduced size file into a Fox table but I want
to avoid that step if I can. (I'll be running the system on their server via
Terminal Server so speed should NOT be a problem).

Ian

Craig Berntson

2005-05-18, 9:24 am

VFP has a maximum TABLE size of 2 Gig. You normally won't read an entire SQL
table at once (SELECT * FROM table), but rather filter it through the WHERE
clause. That should help keep the size of the resulting cursor much smaller.

--
Craig Berntson
MCSD, Visual FoxPro MVP
www.craigberntson.com
Salt Lake City Fox User Group
www.slcfox.org
www.foxcentral.net

"Barley Man" < BarleyMan@discussion
s.microsoft.com> wrote in message
news:413BA974-4778-4F2F-BD6A- BC7C405A2B7B@microso
ft.com...
>I have been trying to read a client's SQL database using MSDE. However,
>it's
> over 2gb in size and MSDE will not allow access to dtabases above that
> size.
> I now have agreed with the client to access it via their FULL copy of SQL
> Server 2000 and that gets round that 2Gb limit, I hope.
>
> However, if I use an ODBC link and read that SQL file by 'simple' ODBC,
> will
> I ALSO run into an maximum database file size in VFP7? To clarify the
> question, does VFP7 have a maximum database size and/or is there a limit
> to
> the SQL database that I can access within VFP7 that wil prevent me from
> reading a 3gb, 4gb or 10gb SQL database?
>
> I appreciate that, if I can access 10gb SQL databases, I can always use an
> SQLEXEC statement to return a reduced size file into a Fox table but I
> want
> to avoid that step if I can. (I'll be running the system on their server
> via
> Terminal Server so speed should NOT be a problem).
>
> Ian

Anders Altberg

2005-05-18, 11:24 am

SQL Server 2005 Express is free and goes up to 4GB.
VFP will be fine as long as you don't try to create result sets over 2GB.
-Anders

"Barley Man" < BarleyMan@discussion
s.microsoft.com> wrote in message
news:413BA974-4778-4F2F-BD6A- BC7C405A2B7B@microso
ft.com...
> I have been trying to read a client's SQL database using MSDE. However,
it's
> over 2gb in size and MSDE will not allow access to dtabases above that
size.
> I now have agreed with the client to access it via their FULL copy of SQL
> Server 2000 and that gets round that 2Gb limit, I hope.
>
> However, if I use an ODBC link and read that SQL file by 'simple' ODBC,
will
> I ALSO run into an maximum database file size in VFP7? To clarify the
> question, does VFP7 have a maximum database size and/or is there a limit
to
> the SQL database that I can access within VFP7 that wil prevent me from
> reading a 3gb, 4gb or 10gb SQL database?
>
> I appreciate that, if I can access 10gb SQL databases, I can always use an
> SQLEXEC statement to return a reduced size file into a Fox table but I
want
> to avoid that step if I can. (I'll be running the system on their server
via
> Terminal Server so speed should NOT be a problem).
>
> Ian

Barley Man

2005-05-18, 11:24 am

Hi Craig

I think I get your 'drift' but can you clarify this a bit, please? I have
yet to see the client's SQL files in full so I cannot tell if any of the
'tables' within those databases are in excess of 2Gb. Assuming that some ARE
in excess of 2gbs (and remembering the MSDE limit is eliminated as they run
SQL on a full sized verison) will that Fox 2gb limit affect me reading the
info, via ODBC WITHOUT using SQLEXEC() type commands?

If it will, please confirm that, in those cases, I should 'filter' the SQL
tables down to a maximum of 2gbs to handle it within Fox

Ian

"Craig Berntson" wrote:

> VFP has a maximum TABLE size of 2 Gig. You normally won't read an entire SQL
> table at once (SELECT * FROM table), but rather filter it through the WHERE
> clause. That should help keep the size of the resulting cursor much smaller.
>
> --
> Craig Berntson
> MCSD, Visual FoxPro MVP
> www.craigberntson.com
> Salt Lake City Fox User Group
> www.slcfox.org
> www.foxcentral.net
>
>
> "Barley Man" < BarleyMan@discussion
s.microsoft.com> wrote in message
> news:413BA974-4778-4F2F-BD6A- BC7C405A2B7B@microso
ft.com...
>
>
>

Cindy Winegarden

2005-05-18, 1:24 pm

Hi Ian,

You can read about the Visual FoxPro table size limits in the VFP Help. Look
for the "Visual FoxPro System Capacities" topic.

The whole point of using SQL Server is that you don't retrieve all the
records, but rather limit the data to only what you need. Servers are
usually powerful machines that are suited to handling data-intensive tasks.

You mention using SQL Exec as an alternative data access method, so I assume
you're using a remote view as your first choice. Please realize that
underneath a remote view is a SQL Exec statement so with the same Select
criteria and setting some properties on the SQL Exec result cursor (like
whether it's updatable) the two data access methods would be equivalent.

What kinds of things will you be doing with the data where you need all of
it downloaded into FoxPro?

--
Cindy Winegarden MCSD, Microsoft Visual Foxpro MVP
cindy_winegarden@msn
.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden

"Barley Man" < BarleyMan@discussion
s.microsoft.com> wrote in message
news:413BA974-4778-4F2F-BD6A- BC7C405A2B7B@microso
ft.com...
>I have been trying to read a client's SQL database using MSDE. However,
>it's
> over 2gb in size and MSDE will not allow access to dtabases above that
> size.
> I now have agreed with the client to access it via their FULL copy of SQL
> Server 2000 and that gets round that 2Gb limit, I hope.
>
> However, if I use an ODBC link and read that SQL file by 'simple' ODBC,
> will
> I ALSO run into an maximum database file size in VFP7? To clarify the
> question, does VFP7 have a maximum database size and/or is there a limit
> to
> the SQL database that I can access within VFP7 that wil prevent me from
> reading a 3gb, 4gb or 10gb SQL database?
>
> I appreciate that, if I can access 10gb SQL databases, I can always use an
> SQLEXEC statement to return a reduced size file into a Fox table but I
> want
> to avoid that step if I can. (I'll be running the system on their server
> via
> Terminal Server so speed should NOT be a problem).
>
> Ian

Olaf Doschke

2005-05-18, 1:24 pm

VFP has a limit of 2GB for each file,
so a single table can grow up to 6GB
with 2GB records in the DBF 2GB memo
fields in the FPT and 2GB Indexes in a
CDX file.

Normally one of those files grows large
faster than the other and everybody
speaks of a 2GB limit.

But as a database consists of many
tables with several files for each table
your VFP database may grow very
much larger.

Bye, Olaf.

Barley Man

2005-05-18, 8:27 pm

Hi Cindy

Errrrrr....... How do you retrieve data from an SQL server WITHOUT using the
cleint-server type of SQLEXEC() command? I am assuming that, so long is I
return a dataset of less than 2gbs with a client-server type request, then
Fox will be happy. I am assuing that the client-server type request (e.g.
SQLEXEC())is being handled by SQL so the limits are those of SQL rather than
Fox.

Are you implying that ANY ODBC request of the (let's say) 10gb SQL server
file which returns less than 2gbs will ALSO work within Fox?

As you can tell, I am VERY new to interrogating SQL with any system (well;
for precision, I am a 'virgin' at it!)

Ian

"Cindy Winegarden" wrote:

> Hi Ian,
>
> You can read about the Visual FoxPro table size limits in the VFP Help. Look
> for the "Visual FoxPro System Capacities" topic.
>
> The whole point of using SQL Server is that you don't retrieve all the
> records, but rather limit the data to only what you need. Servers are
> usually powerful machines that are suited to handling data-intensive tasks.
>
> You mention using SQL Exec as an alternative data access method, so I assume
> you're using a remote view as your first choice. Please realize that
> underneath a remote view is a SQL Exec statement so with the same Select
> criteria and setting some properties on the SQL Exec result cursor (like
> whether it's updatable) the two data access methods would be equivalent.
>
> What kinds of things will you be doing with the data where you need all of
> it downloaded into FoxPro?
>
> --
> Cindy Winegarden MCSD, Microsoft Visual Foxpro MVP
> cindy_winegarden@msn
.com www.cindywinegarden.com
> Blog: http://spaces.msn.com/members/cindywinegarden
>
>
> "Barley Man" < BarleyMan@discussion
s.microsoft.com> wrote in message
> news:413BA974-4778-4F2F-BD6A- BC7C405A2B7B@microso
ft.com...
>
>
>

Cindy Winegarden

2005-05-18, 8:27 pm

Hi Ian,

Just to make sure you understand, VFP's SQLExec() command uses a connection
handle parameter (sold seperately) to pass a text string (Select statement)
to the SQL Server via middleware like ODBC. The ODBC drivers pass the text
string along to the server. SQLExec() is a FoxPro "thing". The Select
statement is a SQL Server "thing," except that the FoxPro data engine also
responds to SQL language statements. Other SQL language statements are
Insert, Update, and Delete.

There are several ways (that I can think of) to access external data via
FoxPro. Some, like Import and low-level file functions won't help in your
situation. The others are:

1. ODBC via a remote view
2. ODBC via SQLExec()
3. ADO, which is a data object. The problem with "plain" ADO is that you
can't "see" the data the way you can with the ODBC methods.
4. ADO/OLE DB with a Cursor Adapter. Cursor Adapters just make it easy to
use the OLE DB data providers to create the ADO recordsets and then view
them as cursors. I guess you could call this "WITHOUT using the
client-server type of SQLEXEC() command" but behind the CursorAdapter is an
SQL Select statement that is sent off to the server via the middleware of
OLE DB.

With all four you can only retrieve 2 GB of data (we won't talk about memos
right now) since VFP can only create a cursor/table that is 2 GB or less.

The limits of the SQL Server are the speed and power it takes to slice and
dice a jillion records into a dataset you want to see. SQL Server's limit
for a database are in Terrabytes. I'm not sure about an individual table. If
the resulting dataset is over 2 GB then SQL Server start sending all the
data back, just as it would to a VB or C# application, but FoxPro will give
errors as the retrieved data hits the 2 GB limit.

As for "ANY ODBC request of the (let's say) 10gb SQL server file which
returns less than 2gbs will ALSO work within Fox," no matter how you "send"
it, the ODBC request is the same: a text string with a command that the
server understands. The problem with any of the requests is what happens as
the server begins returning the data. If it's less than 2 GB there won't be
any problems (other than speed) but when the returned data reaches the 2GB
limit you will have a problem.

Again, what do you want to do in FoxPro that you need to retrieve so much
data? It's possible that doing it a different way (the SQL way, not the
XBase way) would solve your problem.

--
Cindy Winegarden MCSD, Microsoft Visual Foxpro MVP
cindy_winegarden@msn
.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden

"Barley Man" < BarleyMan@discussion
s.microsoft.com> wrote in message
news:42D1CF25-7EC6-4477-AD5A- B600D513BE9D@microso
ft.com...

> Errrrrr....... How do you retrieve data from an SQL server WITHOUT using
> the
> cleint-server type of SQLEXEC() command? I am assuming that, so long is I
> return a dataset of less than 2gbs with a client-server type request, then
> Fox will be happy. I am assuing that the client-server type request (e.g.
> SQLEXEC())is being handled by SQL so the limits are those of SQL rather
> than
> Fox.
>
> Are you implying that ANY ODBC request of the (let's say) 10gb SQL server
> file which returns less than 2gbs will ALSO work within Fox?

Olaf Doschke

2005-05-18, 8:27 pm

> Are you implying that ANY ODBC request of the (let's say) 10gb SQL server
> file which returns less than 2gbs will ALSO work within Fox?
That's about it.

Cindy has listed all the different methods of retrieving data from
an SQL server.

Remote View, Cursor Adapter or SQLExec will all generate
foxpro cursors, which have the 2 GB limit.

But you'd never do something like "SELECT * from TABLE"
to a giant table stored at the SQL server.

Client-Server Applications work different than USING a table
and having it as a recordsource of a grid.

Think about internet applications with a big data base like
eBay. Will you ever want to see a list of all auctions running?
You do a search and only get presented a first page of the result
with the auctoins ending next.

That's about the way you would work with SQL server.
A typical result will never ever reach the 2 gb limit, as you
can compute for yourself, that such a request would need
about 80 seconds for a transfer from server to client over a
100mbit lan on ideal conditions. No one would work with
such an application.

So there is no need to really worry about a resultset getting
that large.

The only situation I could think of and thought of, when initially
answering your question is importing data from an sql server
database to a foxpro database, which you would do just once.

If you're talking about an sql server as a backend to your app,
then there are more important limits like the performance of
data retrieval through the LAN.

Bye, Olaf.

Barley Man

2005-05-19, 7:24 am

Now those are what I CALL replies!!!!

Clear, accurate and non-ambiguous!!!!

I now understand that there are multiple methods of getting the data; the
size of the SQL server database has no effect on Fox (so long as your copy of
SQL supports large databases), so long as you don't return a 'table' of over
2gb (a daft thing to do anyway!).

I accept your admonitions not to make a request that causes a >2gb table to
be returned but............ we all know how these things happen accidentally!
Errors do occur, especially in MY code!

Thanks to all of you for your patience on this one. I think I now understand
the answers!

Ian

"Olaf Doschke" wrote:

> That's about it.
>
> Cindy has listed all the different methods of retrieving data from
> an SQL server.
>
> Remote View, Cursor Adapter or SQLExec will all generate
> foxpro cursors, which have the 2 GB limit.
>
> But you'd never do something like "SELECT * from TABLE"
> to a giant table stored at the SQL server.
>
> Client-Server Applications work different than USING a table
> and having it as a recordsource of a grid.
>
> Think about internet applications with a big data base like
> eBay. Will you ever want to see a list of all auctions running?
> You do a search and only get presented a first page of the result
> with the auctoins ending next.
>
> That's about the way you would work with SQL server.
> A typical result will never ever reach the 2 gb limit, as you
> can compute for yourself, that such a request would need
> about 80 seconds for a transfer from server to client over a
> 100mbit lan on ideal conditions. No one would work with
> such an application.
>
> So there is no need to really worry about a resultset getting
> that large.
>
> The only situation I could think of and thought of, when initially
> answering your question is importing data from an sql server
> database to a foxpro database, which you would do just once.
>
> If you're talking about an sql server as a backend to your app,
> then there are more important limits like the performance of
> data retrieval through the LAN.
>
> Bye, Olaf.
>
>
>

Sponsored Links