How to Connect to SQL Server from Visual FoxPro

In Microsoft public newsgroups, I’ve noticed a recent increase in the number of questions that deal with how to connect from Visual Foxpro to SQL Server, and the problems related to making this connection. So I’ve decided to write this article  to cover such an important topic.

There are two functions that can be used to establish a connection with the a remote SQL Server from Visual FoxPro:

  • SQLConnect()
  • SQLStringConnect()

The SQLConnect() Function

There are two ways to use the SQLConnect() function to connect to a remote data source, such as SQL Server. The first requires that you supply the name of a data source as defined in the ODBC Data Source Administrator applet of the Control Panel.

The following example creates a connection to a remote server using the ODBCNorthwind DSN:

LOCAL hConn

hConn = SQLConnect(“ODBCNorthwind”, “sa”, “”)

The second way to use SQLConnect() is to supply the name of a Visual FoxPro  connection that was created using the create connection command. The CREATE CONNECTION command stores the metadata that Visual FoxPro needs to connect to a remote data source.

The following example creates a Visual FoxPro connection named Northwind and then connects to the database described by the connection:

LOCAL hConn

CREATE DATABASE cstemp

CREATE CONNECTION Northwind ;

DATASOURCE “ODBCNorthwind” ;

USERID “sa” ;

PASSWORD “”

hConn = SQLConnect(“Northwind”)

SQLStringConnect() Function

The other function that can be used to establish a connection to a remote data source, such as SQL Server, is SQLStringConnect(). Unlike SQLConnect(), SQLStringConnect() requires a single parameter, a string of semicolon-delimited options that describes the remote data source and optional connections settings.

The valid options are determined by the requirements of the ODBC driver. Specific requirements for each ODBC driver can be found in that ODBC driver’s documentation.

The following table lists some commonly used connection string options for SQL Server:

Option Description
DSN References an ODBC DSN.
Driver Specifies the name of the ODBC driver to use.
Server Specifies the name of the SQL Server to connect to.
UID Specifies the login ID or username.
PWD Specifies the password for the given login ID or username.
Database Specifies the initial database to connect to.
APP Specifies the name of the application making the connection.
WSID The name of the workstation making the connection.
Trusted_Connection Specifies whether the login is being validated by the Windows NT Domain.

Not all of the options listed in the above table have to be used for each connection.

For instance, if you specify the Trusted_Connection option and connect to SQL Server using NT Authentication, there is no reason to use the UID and PWD options since SQL Server would invariably ignore them. The following code demonstrates some examples of using SQLStringConnect().

Note: You can use the name of your server instead of the string.

SQL Server 2000 code example:

LOCAL hConn

hConn = SQLStringConnect(“Driver=SQL Server;Server=<SQL2000>;”+ ;

UID=sa;PWD=;Database=Northwind”)

hConn = SQLStringConnect(“DSN=ODBCNorthwind;UID=sa;PWD=;Database=Northwind”)

hConn =
SQLStringConnect(“DSN=ODBCNorthwind;Database=Northwind;Trusted_Connection=Yes”)

Continues…

Pages: 1 2




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |