CAN U SUGGEST ME HOW I CONNECT TO 64 BIT SQL SERVER IN VFP
CURRENT I AM USING SQL SERVER 2000 32 BIT USING ODBC

+++ Rick ---

DEAR EXPERTS

CAN U SUGGEST ME HOW I CONNECT TO 64 BIT SQL SERVER IN VFP
CURRENT I AM USING SQL SERVER 2000 32 BIT USING ODBC

Hi All,

I need to create numerous VFP tables in a specific format to maintain compatibility with numerous downstream systems. The source of the information is now SQL Server (2008 64bit). I've written T-SQL to handle all other aspects of the work which is reasonably complex and if possible would like output the VFP table directly within the same script.

I know an alternative would be to write a VFP application, call the T-SQL written so far and then output the VFP table but I'm trying to avoid creating more VFP code! Ultimately the downstream systems will change but it's too big a step right now.

Note that there is a requirement to support integer and datetime field format types within the VFP table to be created. I've tried using the 64-bit Microsoft Access dBASE Driver (*.dbf, *.ndx, *.mdx) but it doesn't support the required field types.

Anyone have any ideas on how I can accomplish this with an example? I've got a feeling I've reached the limits of SQL connectivity to VFP here unless I install another 32-bit SQL instance...

Steve,

Forget the distinction between SQL Server 32 bit and 64 bit. As Rick said, the correct 32 bit ODBC driver will work with either SQL server. Stick with the 64 bit SQL.

The problem you might be having is that different SQL ODBC drivers correctly pull SQL datatypes and some don't. There's a sweet spot that you need to find that will work for you with these drivers. I've written about this on Universal Thread (Extreme) and don't have my extensive notes.

• SQL smalldate works only with one driver as far as I know, but that driver does not support SQL datetime. I would avoid SQL smalldate and use SQL Datetime2 instead.

• As far as I know, Varchar(MAX) does not work with any driver and VFP. And this is where we used a SQL Text datatype. The largest number in the Varchar(nnnn) is 8000. I believe varchar(MAX) works, but you get an empty string.

• SQL Big Integers don't work. So, it's best to avoid them until your conversion is completed.

So, I'm sorry I don't have the exact names of the SQL ODBC drivers and their capabilities and lack thereof - you'll have to dig them out of Microsoft. I believe there are 3 such drivers.

Now, another wrench is that there is a hacked version of VFP from China that works with these datatypes (e.g. varchar(max), big int, etc.), but that's a whole other story as to whether one can use it legally.

Good luck. If I have time, I'll look into this further as to the various drivers and what they can and cannot do.

Hi Jeff,

Thanks for taking the time to reply. I just wanted to ensure I have explained my requirements properly.

i.e. To get data out of SQL Server and into a dbf, using T-SQL and not VFP code.

I'm aware I can use either a 32bit or 64bit ODBC driver to connect to SQL from VFP. My attempts to use the VFP ODBC driver within 64bit SQL Server result in an Architecture mismatch error.

The resulting VFP table must use the following schema:

idrel Integer 4

branchno Integer 4

account Character 50

nextpay DateTime 8

weekmonth Character 1

payevery Integer 4

enrolldate DateTime 8

mandateact DateTime 8

prevref Character 50

terminated DateTime 8

total Numeric 11, 2

lastedit DateTime 8

itemtypeno Integer 4

idy Integer 4

Don't ask me why my predecessor decided to mix Integer and Numeric types, or even decide to push this info into a DBF to then PCAnywhere it to a remote location, to then push it back into SQL! 😦

I could change the downstream stuff but am trying to avoid it for now.

Any further info appreciated before I bite the bullet on this.

Yes, you will write a TSQL select statement to bring down the data into VFP through the ODBC driver.

You need to post the schema of the SQL Server table or just the relevant columns that match the VFP table.

I believe the 'architecture' mismatch is because you are trying to use a 64 bit ODBC in VFP. You need to use the 32 bit driver.

Thanks again but I still think we are talking about different things here. I'm not using VFP (yet), just SQL (via SSMS).

AFAIK there isn't a 64bit VFP ODBC driver. If there is, and you have a link please point me to it - I will be eternally grateful. In fact I had to hunt down the 32bit VFP ODBC driver because the link from the VFP MSDN product page is broken now. I have attempted to use the VFP OLEDB Provider but can't seem to "see" that in SSMS either.

Cheers, Steve

Of course we're talking about different things if you now say that you are trying to use a VFP ODBC driver and not a MSSQL ODBC driver. The VFP ODBC driver is very deprecated and is only 32 bit and will never be 64 bit. If you are trying to use the VFP ODBC in C or C# or Java or MSSQL or wherever in a 64 bit environment you will get an architecture mismatch.

If you're trying to contact and work with VFP from within MSSQL trying to use the VFP ODBC driver, you would definitely have to downgrade your SQL Server to 32 bit and I would not suggest that. I don't endorse using the VFP ODBC driver for much at all.

I think at this point, it would be best for you to describe the various applications and their languages and detail the data flow. Like are you trying to populate a FoxPro DBF with SQL data? And if so, then where are you trying to do that from? (VFP, MSSQL, C, C#, Java, PCAnywhere, etc.) VFP works quite well with SQL Server if you are working in VFP. VFP will push and pull data using TSQL or Stored Procedures using the MSSQL ODBC driver(s).

At this point, I am rather perplexed about the use of PCAnywhere and what is trying to be accomplished.

Oh, I see now. You want to export from within SQL Server to a DBF without writing VFP code.

The only person who could possibly help you with that is Rick Strahl.

You got it! 😃

I've used VFP on and off for 20+ years so I think I'm qualified. Sorry if I wasn't clear enough in my original posts.

I think I'm going to swap out those DateTime's for Date's and Integer's for Numeric's and see if the downstream systems complain...

Once again thanks for taking the time to try and help - it really is appreciated.

Steve

No, it's got nothing to do with integer versus numeric.

If you're using SQL Server services to try and export FoxPro data and you're running 64 bit you're out of luck. VFP ODBC and OleDb drivers are 32 bit only and can't be used from a 64 bit application. There's no way around that. BTW, if you want the most recent driver support for FoxPro you typically will want to use the OleDb driver which was the last driver that Microsoft built and supported. The ODBC driver is older and doesn't support some of the newer language features.

All that said it seems to me the way to go is do this from the FoxPro end rather than from SQL Server since you can access 64 bit SQL server from FoxPro just fine. You don't have to use FoxPro either - you could use .NET or some other platform as long as it is 32 bit.

+++ Rick ---

Cheers Rick - That's where I had got to. Although I hadn't considered using .Net to create the VFP table.

I know the OLEDB provider is more recent and offers additional support but I was hoping to somehow trick SQL with an ODBC System DSN. In hindsight that was never going to fly... It just would have been nice to keep everything in 1 place (SQL).

I was considering using the MS Access Driver located here: https://www.microsoft.com/en-us/download/details.aspx?id=13255 as it offers support for dBase but the support for the NUMERIC datatype is not what I need either, as detailed here: https://docs.microsoft.com/en-us/sql/odbc/microsoft/dbase-data-types?view=sql-server-2017

One final question though is regarding VFP Advanced. I don't suppose you know if there is a 64bit ODBC / OLEDB provider as part of that? I'm not sure I could propose that as a solution anyway but it could be interesting.

Thanks again, Steve

I have no idea - I never looked at VFP Advanced. If I want something more modern I'm looking past FoxPro to other platforms 😃

Right Tools for the the Right Job.

+++ Rick ---

Yeah - agreed. I'm also looking to get SQL 2008 upgraded too, along with the OS it's running on. The overall system is huge so just trying to take one step at a time.

I only asked about VFP Advanced because I noticed it's mentioned in the South-West Fox talks this year. I'm a converted c# man now though.