ODBC Connect to SQL Server

[PeteBe]

Appreciate any help here as it has been a VERY long time since I have had to do anything with VFP and SQL server. I have installed SQL Server 2022 and now want to access tables in VFP. I downloaded the ODBC driver. Next I opened the ODBC Data Source Administrator. If I remember correctly I need to click on System DSN. I then click on Add and I am queried on which driver I want to set up a Data Source. Not sure which to select? Also whatever I select the next window has a drop down for the Server but the drop down is empty and I don't see SQL Server which I would have expected.

Last question - are there any known issues with running VFP in Windows 11 - seems to run fine in Windows 10.
Thanks so much
Pete

 

[Chris Miller]

SQL Server is just installing the server. To get the ODBC drivers needed you need to either install an SQL Server setup with client tools - that's MS terminology for anything the clients could use like the ODBC driver and the Management studio (SSMS). Otherwise you need a separate client tools installation and that's also recommended because usually unless you're a developer the server and client are separate commputers anyway. Then it's also necessary to configure SQL Server to allow network connections in the first place.

Also, you need 32 bit ODBC drivers for VFP. They're not naturally included in any client tools setup.

Chriss

 

[PeteBe]

I have SSMS installed.According to Msft the ODBC driver is no longer supported or avaiable

 

[Mike Lewis]

Pete,

Chris is correct when he says that you a 32-bit ODBC driver for VFP, but I don't think that is relevant to your question.

If I've understood your question correctly, it is the SQL Server driver that you specify in the ODBC applet, not the VFP driver. Are you not seeing SQL Server in the list of drivers?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[PeteBe]

Thanks Chris, Mike. I'll try to be as specific as possible but not that familiar with interfacing the two. I am just running on a single PC. I have just installed SQL Server 2022 and SSMS. I have a VFP application I did many years ago that gets its data from SQL Server. The PC I am using did not have any ODBC set up. I am just trying to set up the ODBC Data Source Administrator so the application can access the database in SQL Server. Here is the first screen where I selected SQL Server and the 2nd screen which shows the dropdown with no SQL Server available.

 

[Chris Miller]

First, you are using the 64bit ODBC Data Source Administrator, which only manages DSNs with 64bit ODBC drivers, that won't work anyway.

Secondly, as already said SQL Server has to allow connections and you neither manage this within the ODBC Administrator nor within SSMS, but in the SQL Server Configuration Manager. Both the SQL Server Services and SQL Server Network Configuration are of interest.

Then also don't rely on servers automatically listing, write in (local) or (local)\SQLEXPRESS or (local)\MSSQLSERVER for the server.

Chriss

 

[PeteBe]

Thanks Chris, will give it a shot.

 

[Chris Miller]

Just to make one thing clear:

If using the 32bit ODBC Data Source Administrator you see no SQL Server drivers, your setup only installed 64bit client tools and ODBC drivers. You don't need the 32bit SSMS, and even less so the 32bt SQL Server Configuration manager, as that's a server tool, not a client tool. But you have to have 32bit ODBC drivers and although using the 64bit ODBC Data Source Administrator allows you to define a DSN using the 64bit drivers that are installed, VFP won't be able to use that DSN, even if you would find the server by ma advice.

The DSN you define isn't unbinding VFP from using the ODBC driver, using a DSN doesn't mean VFP connects to something that uses the ODBC driver specified in the DSN, the DSN just is used to lookup the information about which driver to use, which server instance to connect to etc. and then that's done by VFP itself (or whatever system uses a DSN). So eventually VFP is using the driver you specify and so it has to be a DSN specifying a 32bit ODBC driver and those are only definable in the 32bit ODBC Data Source Administrator. And onlyy when 32bit SQL Server drivers exist, which can connect to a SQL 2022 server instance.

Chriss

 

[PeteBe]

Thanks Chris, makes sense but where do I find a 32 bit ODBC driver, seems like MSFT no longer provides it.
Best
Pete

 

[Chris Miller]

Did you even look?

See the driver date, MS surely still provides 32bit drivers. 32bit Office also needs them, for example.

Chriss

 

[Chris Miller]

Did you not find the 32bit ODBC Data Source Administrator application on your PC?

It's there, it was even there before you installed SQL Server, that's an OS component.
Windows Key, then search ODBC:

Chriss

 

[Chris Miller]

are there any known issues with running VFP in Windows 11

The only issue I know of are people believing it won't run but I have it running on Windows 11 (even though that screenshot above is Windows 10, but I also have a Win11 system). So I still don't know any issue that's really based on running on Win11.

Chriss

 

[Chris Miller]

Besides, you find 32bit ODBC drivers, just one Google search hit:

https://learn.microsoft.com/en-us/s...c-driver-for-sql-server?view=sql-server-ver16

That's MS download page for the "ODBC Driver N for SQL Server" series and you find versions 18 and 17 there. But as shown there also are other driver series, Native Client and the SQL Server driver I showed that's simply called that.

The x86 downloads are the 32bit versions. If you didn't know that, think about the differentiation in applications, too, the Program Files (x86) system folder is for 32bit software.

Chriss

 

[PeteBe]

I finally figured out the issue was that SQL Server installs with SQL Server Browser "stopped". Started it and good to go. Thanks for your help.
Pete

 

[Chris Miller]

The browser service is optional. I mean, your problem isn't just, that servers are not listed in the dialog to create a DSN.

Chriss