ID Artikel: 207595 - Kajian Terakhir: 19 September 2011 - Revisi: 2.0

Bagaimana melakukan SQL Server 2000 didistribusikan pertanyaan dengan FoxPro .dbf file

Klik disini untuk menampilkan sumber Inggris dan terjemahan oleh mesin secara berdampingan

Peringatan: artikel ini adalah terjemahan oleh mesin

Melihat produk di mana artikel ini berlaku.

Tips SistemThis article applies to a different operating system than the one you are using. Article content that may not be relevant to you is disabled.

Perbesar semua | Perkecil semua

RINGKASAN

Artikel ini menunjukkan cara melakukan permintaan didistribusikan SQL Server untuk mengambil data dari FoxPro .dbc dan .dbf file menggunakan ODBC VFP Driver atau VFP OLE penyedia DB.

Kembali ke atas

Microsoft SQL Server 2000 menyediakan kemampuan untuk melakukan query terhadap penyedia OLE DB. Hal ini dilakukan dengan menggunakan fungsi-OpenQuery atau OpenRowset Transact-SQL atau dengan menggunakan permintaan dengan empat bagian nama termasuk nama server terkait.

Misalnya:

sp_addlinkedserver 'mylinkedserver', 'product_name', 'myoledbprovider', 'data_source', 'lokasi', 'provider_string', 'katalog'

Pilih * dari OPENQUERY (mylinkedserver, ' Pilih * from table1')

Anda harus menggunakan Microsoft OLE DB penyedia untuk ODBC (MSDASQL) dan Visual FoxPro ODBC driver untuk mendirikan sebuah server yang terkait untuk melakukan query didistribusikan terhadap FoxPro .dbc dan .dbf file. Menggunakan penyedia OLEDB Jet dengan FoxPro tidak didukung. Pengandar VFP ODBC bukanlah benang yang aman. Karena SQL Server multi-threaded, pengandar ODBC VFP dapat menyebabkan masalah dalam beberapa keadaan. Jika mungkin, sebaiknya menggunakan VFP OLE DB penyedia untuk terhubung ke data SQL Server.

Contoh kode T-SQL berikut menunjukkan bagaimana untuk membuat dan menggunakan query didistribusikan dengan FoxPro dengan fungsi OpenQuery dan OpenRowset. Ini juga menunjukkan cara memperbarui tabel FoxPro jauh dari SQL Server 2000. Anda dapat menguji kode ini dalam SQL Query Analyzer setelah Anda menginstal pengandar Visual FoxPro ODBC pada mesin SQL Server 2000. Anda akan perlu untuk mengubah nama sumber data dan path ke file FoxPro yang sesuai:

/* 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 = &quot;Seattle&quot; 
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&amp;lt;BR/&amp;gt;

-- 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

Anda juga dapat menggunakan Visual FoxPro OLE DB penyedia untuk menciptakan permintaan yang didistribusikan. Ini adalah pilihan untuk menggunakan teknologi. Perlu diketahui bahwa sementara kode ini menunjukkan bagaimana untuk memperbarui dan menghapus data, menambahkan, memperbarui (penyuntingan), dan menghapus data pada permintaan didistribusikan menggunakan OLE DB penyedia tidak didukung.

Contoh kode T-SQL berikut menunjukkan bagaimana untuk membuat dan menggunakan query didistribusikan dengan FoxPro dengan fungsi OpenQuery dan OpenRowset. Anda dapat menguji kode ini dalam SQL Query Analyzer setelah Anda menginstal Visual FoxPro OLE penyedia DB pada mesin SQL Server 2000. Anda akan perlu untuk mengubah nama sumber data dan path ke file FoxPro yang sesuai:

 '/* 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

Kembali ke atas

REFERENSI

Untuk detail lebih lanjut tentang pengaturan dan menggunakan didistribusikan Queries, lihatlah sp_addlinkedserver, OpenQuery, OpenRowset dan topik terkait dalam SQL 7.0 buku Online.

Untuk mempelajari lebih lanjut tentang FoxPro, dan file .dbf dan .dbc, lihat dokumentasi produk FoxPro.

Kembali ke atas

Berlaku bagi:

Microsoft Visual FoxPro 3.0 Standard Edition
Microsoft Visual FoxPro 5.0 Standard Edition
Microsoft Visual FoxPro 6.0 Professional Edition
Microsoft Visual FoxPro 7.0 Professional Edition
Microsoft Visual FoxPro 8.0 Professional Edition
Microsoft Visual FoxPro 9.0 Professional Edition
Microsoft SQL Server 2000 Standard Edition

Kembali ke atas

kbdatabase kbhowto kbmt KB207595 KbMtid

Kembali ke atas

Penerjemahan Mesin

PENTING: Artikel ini diterjemahkan menggunakan perangkat lunak mesin penerjemah Microsoft dan bukan oleh seorang penerjemah. Microsoft menawarkan artikel yang diterjemahkan oleh seorang penerjemah maupun artikel yang diterjemahkan menggunakan mesin sehingga Anda akan memiliki akses ke seluruh artikel baru yang diterbitkan di Pangkalan Pengetahuan (Knowledge Base) dalam bahasa yang Anda gunakan. Namun, artikel yang diterjemahkan menggunakan mesin tidak selalu sempurna. Artikel tersebut mungkin memiliki kesalahan kosa kata, sintaksis, atau tata bahasa, hampir sama seperti orang asing yang berbicara dalam bahasa Anda. Microsoft tidak bertanggung jawab terhadap akurasi, kesalahan atau kerusakan yang disebabkan karena kesalahan penerjemahan konten atau penggunaannya oleh para pelanggan. Microsoft juga sering memperbarui perangkat lunak mesin penerjemah.

Klik disini untuk melihat versi Inggris dari artikel ini:207595� (http://support.microsoft.com/kb/207595/en-us/ )

Kembali ke atas