Dear Vixens and Reynards:

My VFP app has always been using Fox tables. The company I am
working for is now looking at better data inegration and, for better
or worse, has decided to go with SQL Server.

The existing app is written using xBASE data access for the
basics (though I do use SQL for reports). Obviously, somewhat of a
rewrite is necessary.

How difficult is it to get access to an SQL Server database? I
tried to use Express some time ago, but it was a rather unpleasant
experience, and I did not get far.

I would appreciate pointers to books or articles on connecting
VFP and SQL Server. Ideally, I want a basic tutorial that I can
follow for the first try. It would cover setting up SQL Express and
VFP correctly and have a simple but not simplistic example of a
database.

Sincerely,

Gene Wirchenko

Gene Wirchenko presented the following explanation :

http://www.hentzenwerke.com/catalog/csvfp.htm
http://www.hentzenwerke.com/catalog/mysqlvfp.htm

The 2nd one uses MySQL but the concepts are the same.

What problems did you have with SQL Express? It's the same product as
SQL Server but has a throttle on usage and doesn't include some of the
addons like SSRS.

Dan

On Tue, 13 Jul 2010 16:06:07 -0700, Dan Freeman <dan (AT) dfapam (DOT) com>
wrote:

[snip]

Installation. It was unclear what options to choose.
Uninstalling did not take out everything, so the next installation
failed. When I got through that, it was unclear what to do with it. I
could not get any further with it and gave up.

Sincerely,

Gene Wirchenko

After serious thinking Gene Wirchenko wrote :
Can you install Access?

It's the same process to get started:

* Create a system DSN (in the ODBC control panel)
* Use that DSN in VFP to connect to the database
* Do stuff

Given that, you could:

lnHandle = SQLConnect("Your DSN Name")
lnResult = SQLExec(lnHandle, "create table foo (test c(10))")
If lnResult > 0
lnResult = SQLExec(lnHandle, "Select * from foo")
If lnResult > 0
Browse
Endif
Endif

And then you go from there.

Dan

Some steps that you can follow after installing some SQL server and creating
a database:
1. use the wizard to create same tables on sql server from your VFP tables
2. create a new dbc for views
3. create a connection to the sql database
4. create a view with same name for each table allowing to send updates to
the server
5. you can insert all data from your VFP tables into sql server by appending
to the views
6. in you vfp application now if you use the views dbc all the application
should be working as before but using data from the sql server, will be
inefficient but working.
7. start changing your application for sql server -- some simple steps
-- create for each table a new view like TableName_Qry view using filter
the main key so you will bring only the records you need not all data
-- by replacing now use TableName with use TableName_Qry alias TableName
nodata on each program and using requery('TableName') after you get the key
from the user you can have a sql server application with minimum changes
Is not as simple, you will have to analyze each program and maybe use a
combination of views, stored procedures or sql pass-thru, but it's a start.

Regards,

Viorel



"Dan Freeman" <dan (AT) dfapam (DOT) com> wrote

On 14.07.2010 01:10, Gene Wirchenko wrote:
Do you use Virtual Machines? They are perfectly suited for that kind of
troubles, IMO.
As for the MS(?) "Sql Server Express" themselves: they do usually work
quite well for me, especially "2005" and also "2008", almost all options
left as default, except "mixed authentication".

As for the difference between "Client/Server" backend vs Vfp:
I agree with Dan's books recommendation.

Basically it's all about

- the RDBMS Server is playing an active role, can "do" more for you than
Vfp DBCs.

- (almost) every "table" is opened with zero rows first
(NoDataOnLoad=.T.) in local clients, for instance in anyForm.Load().
Where "table" can be a Remote View, a CursorAdapter, or an SPT/SqlExec()
result cursor.

- Later, on demand, you'd "requery" only those rows that you (or the
user) actually wants to see: i.e. Grids are usually quite empty when a
form loads, and user is typing one or more "filter" values (in something
like separate text boxes), that would get evaluated via "parameterized"
queries, i.e. parameterized Views, CursorAdapter.SelectCmd or SqlExec()
strings:
... Where theColumn [Like | =] ?m.parameterVar1 ...



hth
-Stefan

On Tue, 13 Jul 2010 21:33:52 -0700, Dan Freeman <dan (AT) dfapam (DOT) com>
wrote:

I note that the below does not include installing SQL Express.
The installation was full of unexplained options, and I got it wrong
the first time. I may have gotten it wrong after that; who knows?
Even folowing a supposed tutorial I found on the Web did not do it.

If only.

I could not get that far.

Sincerely,

Gene Wirchenko

You should install the version with tools of sql server because you need
some control over it.
If you did not install the tools download and install.
Once you will have the tools installed you can verify if sql server is ok
and you should activate also the tcp and name pipes protocols.
If you were not able to install sql server at all take a look into
microsoft.public.sqlserver.setup where I believe you will find the help you
need.
Once you are sure that is working correctly and you have some control over
it we can go further with vfp.

Viorel


"Gene Wirchenko" <genew (AT) ocis (DOT) net> wrote

Gene Wirchenko wrote:

WHich version did you try to install? I recently installed SQLserver 2008
express and I hadn't much doubts about the setup options. Maybe things have
improved.
--
Vilco
And the Family Stone

On Mon, 19 Jul 2010 14:56:35 +0200, "ViLco" <villiber (AT) tin (DOT) spam> wrote:

2008.

Who knows? Without proper docs, it is all too possible (and
likely) to get stuck in the swamp.

Here are my download notes. Can you tell if I have what I
should?

[The next four items are required to be installed in the same order
for
SQL Server 2008 Express with Advanced Services (the last of the
four).]

dotnetfx35setup.exe
.NET 3.5 download program (Actual download is about 52 MB.)
2008-12-29
downloaded starting from
http://www.microsoft.com/downloads/d...displaylang=en

WindowsXP-KB942288-v3-x86.exe
Windows Installer 4.5
2008-12-29
downloaded starting from
http://www.microsoft.com/downloads/details.aspx
?FamilyId=5A58B56F-60B6-4412-95B9-54D056D6F9F4&displaylang=en

WindowsXP-KB926139-v2-x86-ENU.exe
Windows PowerShell 1.0
2008-12-29
downloaded starting from
http://www.microsoft.com/downloads/details.aspx
?FamilyId=6CCB7E0D-8F1D-4B97-A397-47BCC8BA3806&displaylang=en

SQLEXPRADV_x86_ENU.exe
SQL Server 2008 Express with Advanced Services
2008-12-29
downloaded starting from
http://www.microsoft.com/downloads/details.aspx
?FamilyId=B5D1B8C3-FDA5-4508-B0D0-1311D670E336&displaylang=en

[end of SQL Server installation programs]

Sincerely,

Gene Wirchenko