How to add a Visual FoxPro database to SQL Server as a linked server (199131)
The information in this article applies to:
- Microsoft Visual FoxPro for Macintosh 3.0b
- Microsoft Visual FoxPro for Windows 3.0
- Microsoft Visual FoxPro for Windows 3.0b
- Microsoft Visual FoxPro for Windows 5.0
- Microsoft Visual FoxPro for Windows 5.0a
- Microsoft Visual FoxPro for Windows 6.0
- Microsoft SQL Server 7.0
- Microsoft Visual FoxPro for Windows 7.0
- Microsoft Visual FoxPro 8.0
- Microsoft Visual FoxPro 9.0 Professional Edition
This article was previously published under Q199131 SUMMARY SQL Server 7.0 and SQL Server 2000 allows the addition of external data sources
as linked servers. This feature provides access to distributed, heterogeneous
queries against OLE DB data sources. This article shows how to programmatically
add and query a Visual FoxPro data source as a linked server from Visual
FoxPro. MORE INFORMATION sp_addlinkedserver is a new stored procedure introduced in
SQL Server 7.0 and SQL SErver 2000. sp_addlinkedserver creates a linked server, which allows access
to distributed, heterogeneous queries against OLE DB data sources.
The syntax for adding a linked server from Transact - SQL is:
sp_addlinkedserver [@server =] 'server',
[@srvproduct =] 'product_name',
[@provider =] 'provider_name',
[@datasrc =] 'data_source',
[@location =] 'location',
[@provstr =] 'provider_string',
[@catalog =] 'catalog'
[@server =] 'server' Is the name of the linked server to
create with sp_addlinkedserver.
[@srvproduct =] 'product_name' Is the product name of the OLE DB data
source to add as a linked server.
[@provider =] 'provider_name' Is the unique provider identifier of the
OLE DB provider corresponding to the
data source.
[@datasrc =] 'data_source' Is the name of the data source, as
interpreted by the OLE DB provider.
[@location =] 'location' Is the location of or path to the
database as interpreted by the OLE DB
provider.
[@provstr =] 'provider_string' Is the OLE DB provider-specific.
[@catalog =] 'catalog' Is the catalog to be used when making a
connection to the OLE DB provider.
The following code snippet adds the Visual FoxPro database
Testdata.DBC from the SAMPLES\DATA directory to SQL Server as a linked server.
This code snippet assumes that Visual FoxPro and SQL Server are
being run on the same machine. Note The user, Username, must have permissions to perform these operations on the database.
Source_Path=IIF(VAL(SUBSTR(VERSION(),15,2))=6,HOME(2),HOME()+"SAMPLES\")
Connect_String='DRIVER={SQL Server};' + ;
'SERVER=MY_SERVER;DATABASE=PUBS;UID=UserName;PWD=StrongPassword'
gnConnHandle=SQLSTRINGCONN(Connect_String)
IF gnConnHandle > 0
* Create a command string to pass to SQL Server via SQLExec
SQLCommand="sp_addlinkedserver 'VFP','','MSDASQL',NULL,NULL,"+ ;
"'DRIVER={Microsoft Visual FoxPro Driver};" + ;
"SourceDB="+Source_Path+"DATA\TESTDATA.DBC;SourceType=DBC;NULL'"
* CREATE the LINKED Server"
Create_Linked_Server=SQLExec(gnConnHandle,SQLCommand)
IF Create_Linked_Server > 0
* The linked server was successfully created
* Run the query
=RunQuery()
ELSE
* The Linked Server either already exists or the command failed.
* Test for existence of linked server with aerror()
=AERROR(s_failed)
IF "VFP' ALREADY EXISTS."$UPPER(s_failed[1,2])
* The linked server exists, so run the query
=RunQuery()
ELSE
* The linked server doesn't exist, so display a message
=MESSAGEBOX(s_failed[1,2],32,'Failed')
ENDIF
ENDIF
=SQLDISCONN(gnConnHandle)
ENDIF
PROCEDURE RunQuery
SQLCommand="SELECT * FROM OPENQUERY(VFP,'SELECT * FROM CUSTOMER')"
QRYVal=SQLExec(gnConnHandle,SQLCommand,'SQLRESULTS')
IF QRYVal > 0
SELECT SQLResults
BROW
ELSE
=AERROR(L_Server)
=MESSAGEBOX(L_Server[1,2],32,'Query Failed')
ENDIF
RETURN
After running the code snippet, open SQL Server Enterprise Manager and
expand the Linked Servers node. Note that a linked server named VFP has been
added. REFERENCES For more information about sp_addlinkedserver, search for
"sp_addlinkedserver" in the Transact - SQL Reference Help
file.
Modification Type: | Major | Last Reviewed: | 2/24/2005 |
---|
Keywords: | kbDatabase kbhowto kbSQLProg KB199131 kbAudDeveloper |
---|
|