Hinweis
Für den Zugriff auf diese Seite ist eine Autorisierung erforderlich. Sie können versuchen, sich anzumelden oder das Verzeichnis zu wechseln.
Für den Zugriff auf diese Seite ist eine Autorisierung erforderlich. Sie können versuchen, das Verzeichnis zu wechseln.
In diesem Artikel wird erläutert, wie Sie eine verteilte SQL Server-Abfrage ausführen, um Daten aus FoxPro-DBC- und DBF-Dateien mithilfe des VFP ODBC-Treibers oder des VFP OLE DB-Anbieters abzurufen.
Originalproduktversion: Visual FoxPro
Ursprüngliche KB-Nummer: 207595
Übersicht
In diesem Artikel wird das Ausführen einer verteilten SQL Server-Abfrage zum Abrufen von Daten aus FoxPro .dbc und .dbf Dateien mithilfe des VFP ODBC-Treibers oder des VFP OLE DB-Anbieters veranschaulicht.
Weitere Informationen
Microsoft SQL Server 2000 bietet die Möglichkeit, Abfragen für OLE DB-Anbieter auszuführen. Diese Abfrage erfolgt mithilfe der Transact-SQL-Funktionen OpenQuery oder OpenRowset oder durch eine Abfrage mit vierteiligen Namen, einschließlich eines verknüpften Servernamens.
Zum Beispiel:
sp_addlinkedserver 'mylinkedserver', 'product_name', 'myoledbprovider', 'data_source','location', 'provider_string', 'catalog'
SELECT * FROM OPENQUERY(mylinkedserver, 'select * from table1')
Sie sollten den Microsoft OLE DB-Anbieter für ODBC (MSDASQL) und den Visual FoxPro ODBC-Treiber verwenden, um einen verknüpften Server einzurichten, um verteilte Abfragen für FoxPro .dbc und .dbf Dateien auszuführen. Die Verwendung des Jet OLEDB-Anbieters mit FoxPro wird nicht unterstützt. Der VFP ODBC-Treiber ist nicht threadsicher. Da SQL Server multithreads ist, kann der VFP-ODBC-Treiber unter bestimmten Umständen Probleme verursachen. Wenn dies möglich ist, empfehlen wir die Verwendung des VFP OLE DB-Anbieters, um eine Verbindung mit den SQL Server-Daten herzustellen.
Das folgende T-SQL-Codebeispiel veranschaulicht, wie verteilte Abfragen mit FoxPro mit OpenQuery- und OpenRowset-Funktionen eingerichtet und verwendet werden. Außerdem wird veranschaulicht, wie eine FoxPro-Remotetabelle aus SQL Server 2000 aktualisiert wird. Sie können diesen Code in SQL Query Analyzer testen, nachdem Sie den Visual FoxPro ODBC-Treiber auf einem SQL Server 2000-Computer installiert haben. Sie müssen die Datenquellennamen und den Pfad zu den FoxPro-Dateien entsprechend ändern:
/* OPENROWSET and OPENQUERY examples with VFP via ODBC OLE DB provider */
/* These OPENROWSET examples depend on the sample files VFP98\data\Testdata.dbc
Modify your code accordingly for differences in location or DBC name */
--====================================================
-- Using DBC file , read and update
--====================================================
-- OPENROWSET DSN-less example
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:\VFP98\data\Testdata.dbc;
SourceType=DBC',
'select * from customer where country != "USA" order by country')
go
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:\VFP98\data\Testdata.dbc;
SourceType=DBC',
'select * from customer where region="WA"')
go
Update openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:\VFP98\data\Testdata.dbc;
SourceType=DBC',
'select * from customer where region="WA"')
set region = "Seattle"
go
-- check to verify which rows were updated
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:\VFP98\data\Testdata.dbc;
SourceType=DBC',
'select * from customer where region="Seattle"')
go
-- OPENROWSET DSN example
/* Note the DSN Example might fail if SQL Server is configured to use a local account.*/
select * from openrowset('MSDASQL',
'DSN=Visual FoxPro Database;
SourceDB=e:\VFP98\data\Testdata.dbc;
SourceType=DBC',
'select * from customer where country != "USA" order by country'
go
/* sp_addlinkedserver examples */
-- sp_addlinkedserver example with DSN
/* You will need to make a DSN and point it to the Testdata database.
Modify your code accordingly for differences in location or DBC name */
/* Note this Example may fail if SQL Server is configured to use a local account.*/
sp_addlinkedserver 'VFP Testdata Database With DSN',
'',
'MSDASQL',
'VFP System DSN'
go
sp_addlinkedsrvlogin 'VFP Testdata Database With DSN', FALSE, NULL, NULL, NULL
go
SELECT *
FROM OPENQUERY([VFP Testdata Database With DSN], 'select * from customer where region = "Seattle"')
go
-- Update using OpenQuery
Update OPENQUERY([VFP Testdata Database With DSN], 'select * from customer where region="WA"')
set region = "Seattle"
go
/* SP_addlinkedserver example with DSN-less connection */
/* This example also depends on the sample files Testdata.dbc
Modify your code accordingly for differences in location or DBC name */
sp_addlinkedserver 'VFP Testdata Database With No DSN',
'',
'MSDASQL',
NULL,
NULL,
'Driver={Microsoft Visual FoxPro Driver};UID=;PWD=;SourceDB=e:\VFP98\data\Testdata.dbc;SourceType=DBC;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;'
go
sp_addlinkedsrvlogin 'VFP Testdata Database With No DSN', FALSE, NULL, NULL, NULL
go
SELECT *
FROM OPENQUERY([VFP Testdata Database With No DSN], 'select * from customer where country != "USA" order by country')
go
--====================================================
-- Using VFP 6.0 driver, read and update data from VFP sample dbf files
--====================================================
-- OPENROWSET DSN-less example
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:\VFP98\data;
SourceType=DBF',
'select * from customer where country != "USA" order by country')
go
-- perform UPDATE
Update openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:\VFP98\data;
SourceType=DBF',
'select * from customer where region="Seattle"')
set region = "WA"
go
-- verify update
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:\VFP98\data;
SourceType=DBF',
'select * from customer where region = "WA"')
go<BR/>
-- OPENROWSET DSN example
-- DSN points to the folder where .dbf files are.
/* Note this Example may fail if SQL Server is configured to use a local account.*/
select * from openrowset('MSDASQL',
'DSN=Visual FoxPro Tables;
SourceDB=e:\VFP98\data;
SourceType=DBF',
'select * from customer where country != "USA" order by country')
go"?
-- SQL Server's QUOTED_IDENTIFIER has to be set to OFF.
SET QUOTED_IDENTIFIER OFF
-- OPENROWSET DSN-less example
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:\VFP90\samples\data\Testdata.dbc;
SourceType=DBC',
'select * from customer where country = "USA" order by city')
go
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:\VFP90\samples\data\Testdata.dbc;
SourceType=DBC',
'select * from customer where region="WA"')
go
Update openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:\VFP90\samples\data\Testdata.dbc;
SourceType=DBC',
'select * from customer where city = "Seattle"')
set region = "WW"
go
-- check to verify which rows were updated
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:\VFP90\samples\data\Testdata.dbc;
SourceType=DBC',
'select * from customer where region="WW"')
go
-- OPENROWSET DSN example
/* Note the DSN Example might fail if SQL Server is configured to use a local account.*/
select * from openrowset('MSDASQL',
'DSN=Visual FoxPro Database;
SourceDB=e:\VFP90\samples\data\Testdata.dbc;
SourceType=DBC',
'select * from customer where country = "USA" order by city')
go
/* sp_addlinkedserver examples */
-- sp_addlinkedserver example with DSN
/* You will need to make a DSN and point it to the Testdata database.
Modify your code accordingly for differences in location or DBC name */
/* Note this Example may fail if SQL Server is configured to use a local account.*/
sp_addlinkedserver 'VFP Testdata Database With DSN',
'',
'MSDASQL',
'VFP System DSN'
go
sp_addlinkedsrvlogin 'VFP Testdata Database With DSN', FALSE, NULL, NULL, NULL
go
SELECT *
FROM OPENQUERY([VFP Testdata Database With DSN], 'select * from customer where city = "Seattle" ')
go
-- We will set the region back to "WA" if it currently is "WW".
-- Update using OpenQuery
Update OPENQUERY([VFP Testdata Database With DSN], 'select * from customer where city = "Seattle" ')
set region = "WA"
go
-- Make sure that the region got updated.
SELECT *
FROM OPENQUERY([VFP Testdata Database With DSN], 'select * from customer where city = "Seattle" ')
go
/* SP_addlinkedserver example with DSN-less connection */
/* This example also depends on the sample files Testdata.dbc
Modify your code accordingly for differences in location or DBC name */
sp_addlinkedserver 'VFP Testdata Database With No DSN',
'',
'MSDASQL',
NULL,
NULL,
'Driver={Microsoft Visual FoxPro Driver};UID=;PWD=;SourceDB=e:\VFP90\samples\data\Testdata.dbc;SourceType=DBC;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;'
go
sp_addlinkedsrvlogin 'VFP Testdata Database With No DSN', FALSE, NULL, NULL, NULL
go
SELECT *
FROM OPENQUERY([VFP Testdata Database With No DSN], 'select * from customer where country = "USA" order by city')
go
--====================================================
-- Using VFP 6.0 driver, read and update data from VFP sample dbf files
--====================================================
-- OPENROWSET DSN-less example
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:\VFP90\samples\data;
SourceType=DBF',
'select * from customer where country != "USA" order by country')
go
-- perform UPDATE
Update openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:\VFP90\samples\data;
SourceType=DBF',
'select * from customer where city = "Seattle"')
set region = "WW"
go
-- verify update
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:\VFP90\samples\data;
SourceType=DBF',
'select * from customer where region = "WW"')
go
-- OPENROWSET DSN example
-- DSN points to the folder where .dbf files are.
/* Note this Example may fail if SQL Server is configured to use a local account.*/
select * from openrowset('MSDASQL',
'DSN=Visual FoxPro Tables;
SourceDB=e:\VFP90\samples\data;
SourceType=DBF',
'select * from customer where country != "USA" order by country')
go
Sie können auch den Visual FoxPro OLE DB-Anbieter verwenden, um eine verteilte Abfrage zu erstellen. Es ist die bevorzugte Technologie, die verwendet werden soll. Obwohl dieser Code zeigt, wie Daten aktualisiert und gelöscht werden können, wird das Hinzufügen, Aktualisieren (Bearbeiten) und Löschen von Daten in einer verteilten Abfrage mithilfe des OLE DB-Anbieters nicht unterstützt.
Das folgende T-SQL-Codebeispiel zeigt, wie eine verteilte Abfrage mit FoxPro mittels OpenQuery und OpenRowset-Funktionen eingerichtet und verwendet wird. Sie können diesen Code in sql Query Analyzer testen, nachdem Sie den Visual FoxPro OLE DB-Anbieter auf einem SQL Server 2000-Computer installiert haben. Sie müssen die Datenquellennamen und den Pfad zu den FoxPro-Dateien entsprechend ändern:
'/* These OPENROWSET examples depend on the sample files VFP98\data\Testdata.dbc
'Modify your code accordingly for differences in location or DBC name */
--*====================================================
--* Using the DBC file, reading and updating data.
--*====================================================
--* A couple of OPENROWSET queries.
select * from openrowset('VFPOLEDB',
'e:\vfp7junk\Testdata.dbc';'Exclusive=No';'Data Source=DBC',
'select * from customer where country != "USA" order by country')
go
Select * from openrowset('VFPOLEDB',
'e:\vfp7junk\Testdata.dbc';'Exclusive=No';'Data Source=DBC',
'select * from customer where region="WA"')
go
--* Need to use an error trapping routine with the UPDATE and DELETE functions:
select * from
openrowset('VFPOLEDB',
'E:\VFP7Junk\Testdata.DBC';'Exclusive=No';'Data Source=DBC',
'Update Customer Set city = "SEATTLE" where region = "WA" ')
go
declare @upderror int
select @upderror = @@error
print ''
if @upderror != 7357 and @upderror != 0
print 'Update failed with error '+convert(varchar(5),@upderror)
else
print 'Ignore the error above, the Update succeeded'
go
-- check to verify which rows were updated
select * from openrowset('VFPOLEDB',
'E:\VFP7junk\Testdata.DBC';'Exclusive=No';'Data Source=DBC',
'select * from customer where region = "WA"')
go
--* Change the City field back to "Seattle".
select * from
openrowset('VFPOLEDB',
'E:\VFP7Junk\Testdata.DBC';'Exclusive=No';'Data Source=DBC',
'Update Customer Set city = "Seattle" where region = "WA" ')
go
declare @upderror int
select @upderror = @@error
print ''
if @upderror != 7357 and @upderror != 0
print 'Update failed with error '+convert(varchar(5),@upderror)
else
print 'Ignore the error above, the Update succeeded'
go
--* The DELETE fucntion also causes an error, but the DELETE works.
select * from
openrowset('VFPOLEDB',
'E:\VFP7Junk\Testdata.DBC';'Exclusive=No';'Data Source=DBC',
'Delete from Customer where country = "Spain" ')
go
declare @delerror int
select @delerror = @@error
print ''
if @delerror != 7357 and @delerror != 0
print 'Delete failed with error '+convert(varchar(5),@delerror)
else
print 'Ignore the error above, the Delete succeeded'
go
--* Check to see that the records are deleted.
Select * from openrowset('VFPOLEDB',
'e:\vfp7junk\Testdata.dbc';'Exclusive=No';'Data Source=DBC',
'select * from customer where country = "Spain"')
go
--* Here are some examples using the VFP OLE DB Provider to create Linked Servers.
--* Using sp_addlinkedserver to create the Linked Server.
sp_addlinkedserver @server='VFP_Linked_Server',
@srvproduct='Microsoft Visual FoxPro OLE DB Provider',
@provider='VFPOLEDB',
@datasrc = 'E:\vfp7junk'
go
SELECT *
FROM OPENQUERY([VFP_Linked_Server], 'select * from customer where city = "Seattle"')
go
-- The Update command will update the table with the OPENQUERY function when using the
-- linked server, but the same error 7357 error will occur.
select * from
OPENQUERY([VFP_Linked_Server],
'Update Customer Set city = "SEATTLE" where region = "WA" ')
go
declare @upderror int
select @upderror = @@error
print ''
if @upderror != 7357 and @upderror != 0
print 'Update failed with error '+convert(varchar(5),@upderror)
else
print 'Ignore the error above, the Update succeeded'
go
-- Check and see if the City field is all uppercase with "SEATTLE".
SELECT *
FROM OPENQUERY([VFP_Linked_Server], 'select * from customer where region = "WA"')
go
--* Let's check for how many records have the word "London" in the City field.
SELECT *
FROM OPENQUERY([VFP_Linked_Server], 'select * from customer where city = "London"')
go
-- We can also use the Delete command to remove records with the OPENQUERY function when using the
-- linked server, but the same error 7357 error will occur.
select * from
OPENQUERY([VFP_Linked_Server],
'Delete from Customer where city = "London"')
go
declare @delerror int
select @delerror = @@error
print ''
if @delerror != 7357 and @delerror != 0
print 'Delete failed with error '+convert(varchar(5),@delerror)
else
print 'Ignore the error above, the Delete succeeded'
go
/* SP_addlinkedserver example with DSN-less connection */
/* This example also depends on the sample files Testdata.dbc
Modify your code accordingly for differences in location or DBC name */
sp_addlinkedserver 'VFP Testdata Database With No DSN',
'',
'MSDASQL',
NULL,
NULL,
'Driver={Microsoft Visual FoxPro Driver};UID=;PWD=;SourceDB=e:\VFP8junk\Testdata.dbc;SourceType=DBC;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;'
go
SELECT *
FROM OPENQUERY([VFP Testdata Database With No DSN], 'select * from customer where country = "USA" order by country')
go
Quellen
Weitere Informationen zum Einrichten und Verwenden von verteilten Abfragen finden Sie in SQL 7.0-Büchern online unter "OpenQuery", "OpenRowset" und verwandten Themen.
Weitere Informationen zu FoxPro und .dbf.dbc Dateien finden Sie in der FoxPro-Produktdokumentation.