熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> SQL Server >> 正文

用VFP的DBF進行SQL Server的分布式查詢

2013-11-15 14:34:19  來源: SQL Server 

  概述
  
  本文演示如何執行一個 SQL Server 分布式查詢來從 FoxPro dbc 和 dbf 文件獲取數據
  
  更多信息
  
  Microsoft SQL Server 提供了執行基於 OLE DB providers 的查詢的能力 這是靠使用
  OpenQuery 或 OpenRowset TransactSQL 函數或使用包含一個已連接服務器名四部查詢來執行的
  
  例如:
  sp_addlinkedserver mylinkedserver product_name myoledbprovider data_source
  location provider_string catalog
  
  SELECT * FROM OPENQUERY(mylinkedserver select * from table)
  
  可以用 Microsoft OLE DB provider for ODBC (MSDASQL) 和 Visual FoxPro ODBC driver 來
  設置一個已連接服務器 (linked server) 來執行基於 FoxPro dbc 和 dbf 文件的分布式查詢 不支持用 Jet OLEDB Provider 和 FoxPro
  
  以下 TSQL 示例代碼演示如何用 OpenQuery 和 OpenRowset 函數設置和使用分布式查詢 FoxPro
  它也演示了如何從 SQL Server 更新一個遠程 FoxPro 表 你可以在一個 SQL Server 機器上安裝了 Visual FoxPro ODBC driver 後 在 SQL Query Analyzer 中測試這些代碼 你需要改變數據源名和路徑到適當的 FoxPro 文件:
  
  /* OPENROWSET 和 OPENQUERY 示例 以 VFP 通過 ODBC OLE DB provider */
  
  /* 這些 OPENROWSET 示例依賴於示例文件 VFP\data\Testdatadbc
  如果你的數據在不同的位置請修改你的代碼 */
  
  ====================================================
  使用 DBC 文件 讀和更新
  ====================================================
  OPENROWSET DSNless 示例
  
  select * from openrowset(MSDASQL
  Driver=Microsoft Visual FoxPro Driver;
  SourceDB=e:\VFP\data\Testdatadbc;
  SourceType=DBC
  select * from customer where country != USA order by country)
  go
  
  select * from openrowset(MSDASQL
  Driver=Microsoft Visual FoxPro Driver;
  SourceDB=e:\VFP\data\Testdatadbc;
  SourceType=DBC
  select * from customer where region=WA)
  go
  
  Update openrowset(MSDASQL
  Driver=Microsoft Visual FoxPro Driver;
  SourceDB=e:\VFP\data\Testdatadbc;
  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:\VFP\data\Testdatadbc;
  SourceType=DBC
  select * from customer where region=Seattle)
  go
  
  OPENROWSET DSN 示例
  /* 注意如果 SQL Server 是配置為使用本地帳號 DSN 示例可能失敗*/
  select * from openrowset(MSDASQL
  DSN=Visual FoxPro Database;
  SourceDB=e:\VFP\data\Testdatadbc;
  SourceType=DBC
  select * from customer where country != USA order by country)
  go
  
  /* sp_addlinkedserver 示例 */
  sp_addlinkedserver example with DSN
  
  /* 你需要生成一個 DSN 並使它指向 Testdata 數據庫
  修改你的代碼以反映 DBC 位置 */
  
  /* 注意如果 SQL Server 是配置為使用本地帳號 DSN 示例可能失敗*/
  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 DSNless connection */
  
  /* This example also depends on the sample files Testdatadbc
  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:\VFP\dataTestdatadbc;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
  
  ====================================================
  使用 VFP driver read and update data from VFP sample dbf files
  ====================================================
  
  OPENROWSET DSNless example
  
  select * from openrowset(MSDASQL
  Driver=Microsoft Visual FoxPro Driver;
  SourceDB=e:\VFP\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:\VFP\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:\VFP\data;
  SourceType=DBF
  select * from customer where region = WA)
  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:\VFP\data;
  SourceType=DBF
  select * from customer where country != USA order by country)
  go

From:http://tw.wingwit.com/Article/program/SQLServer/201311/22003.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.