把Access數據庫移植到SQL SERVER7.0

2024-08-31 00:49:30

字體：大中小

來源：轉載

供稿：網友

當對企業級的高性能的數據庫的要求和需求增加時，客戶有時需要從文件?服務器環境的microsoft access jet引擎遷移到客戶?服務器環境的microsoft sql server。隨同微軟office 2000發行的access 2000 upsizing wizard（升級向導）可以把access表格和查詢遷移到sql server 7.0。如果你使用access的早期版本，你也可以通過升級到access 2000，并使用升級向導把你的應用程序移植到sql server。

　　如果你不喜歡使用access 2000和升級向導進行移植，那么這篇文章作為向遷移一個access應用程序到sql server。要遷移access應用程序，你需要首先把數據遷移到sql server 7.0，然后把access查詢移植到數據庫或sql文件，最后是移植應用程序。

　　移植中使用的sql server工具

　　 sql server中的一些工具可以幫助你移植access數據和應用程序

　　 sql server企業管理器

　　 sql server企業管理器允許對sql server和sql server對象的企業范圍的設置和管理。sql server企業管理器提供了一個強大的任務安排引擎、管理警告能力、以及內建的復制管理界面。你還可以用sql server企業管理器來：

　　管理登錄和用戶權限創建腳本管理sql server對象的備份備份數據庫和事務日志管理表、視圖、存儲過程、觸發器、索引、規則、缺省設定和自定義數據類型

　　創建全文索引、數據庫圖表和數據庫維護方案輸入輸出數據轉換數據執行不同的web網絡管理任務缺省情況下，sql server企業管理器被sql server作為服務器軟件的一部分安裝在運行windows nt操作系統的計算機上，同時作為客戶軟件的一部分安裝在運行windows nt和windows 95操作系統的計算機上。你很可能要從sql server企業管理器的圖形用戶界面啟動數據轉換服務（data transformation services，dts）。

　　數據轉換服務(dts)

　　數據轉換服務允許你在使用一個ole db結構的多個數據源之間輸入和輸出數據，這樣的數據源有microsoft excel電子表格，以及在運行sql server 7.0的多臺計算機之間執行數據庫和數據庫對象的傳輸。你還可以用dts來執行數據轉換，以便用它來通過一個在線事務處理系統建立數據倉庫和數據商場。

　　 dts向導允許你交互的創建使用ole db和odbc來進行輸入、輸出、確認和不同數據的轉換的dts包。向導還允許你在相關數據庫之間拷貝計劃和數據。

　　 sql server查詢分析器（server query analyzer）

　　 sql server查詢分析器是一個圖形化查詢工具，它允許你可視化的分析一個查詢方案，同時執行多重查詢、查看數據、獲得索引建議。sql server查詢分析器提供顯示方案（showplan）選項，可以用它來報告sql server查詢優化器選擇的數據獲取方案。

　　 sql server輪廓（server profiler）

　　 sql server輪廓實時捕捉服務器活動的連續的記錄。sql server輪廓允許你監控通過sql server產生的事件，過濾基于用戶指定準則的事件和直接跟蹤到顯示器、文件或者表的輸出。使用sql server輪廓，你可以重放先前的捕捉記錄。這個工具幫助應用程序開發人員鑒別可能使應用程序性能惡化的事務。當從一個基于文件的體系結構移植一個應用程序到一個基于客戶/服務器的體系結構時，它是很有幫助的，因為最后的步驟包含針對應用程序的新客戶/服務器環境優化該應用程序的操作。

　　移動表格和數據

　　使用dts向導遷移你的access數據到sql server，你可以使用這些步驟:

　　在sql server企業管理器中的tools（工具）菜單上，選擇data transformation

　　 services（數據轉換服務），然后選擇import data（導入數據）。

　　在choose a data source（選擇數據源）對話框中選擇microsoft access as th e source，然后鍵入你的.mdb數據庫(.mdb文件擴展名)的文件名或通過瀏覽尋找該文件。

　　在choose a destination（選擇目標）對話框中，選擇microsoft ole　db prov ider for sql　server，選擇數據庫服務器，然后單擊必要的驗證方式。

　　在specify table copy（指定表格復制）或query（查詢）對話框中，單擊copy tables（復制表格）。

　　在select source tables（選擇源表格）對話框中，單擊select all（全部選定）。

　　移植microsoft access查詢

　　你必須以這些格式之一遷移你現有的access查詢到sql server:

　　 transact-sql腳本 transact-sql語句通常從數據庫程序被調用，但是你可以使用包含在sql server

　　 7.0中的sql server查詢分析器在數據庫中直接運行它們。sql server查詢分析器幫助開發者針對開發數據庫測試transact-sql語句，或運行transact-sql語句執行查詢、數據操作(insert、update、delete)或數據定義（create table）。

　　存儲過程開發者可以把大多數源于access查詢(select、insert、update和delete)的tr ansact-sql語句轉換為存儲過程。用transact-sql寫的存儲過程可以用來封裝和標準化你的數據訪問，存儲過程實際上存儲在數據庫內。存儲過程能帶或者不帶參數運行，可以從數據庫程序調用，也可以從sql server查詢分析器手動執行。

　　視圖視圖作為虛擬的表格從一個或更多的表格顯露指定的行和列。它們允許用戶創建查詢而不用直接實現那些復雜的底層查詢。視圖不支持參數。向視圖加入一個更多的表格不能使用insert、update、或delete語句修改。視圖可以用transact-s ql語句調用，并且也可以在sql server查詢分析器中的*.scripts中使用。sql s erver視圖和sql-92標準不支持視圖中的order by子句。

　　更多關于transact-sql、存儲過程或者視圖的信息，見sql server books onlin e。

　　 access查詢類型

　　 sql　server移植選項和注釋

　　一個select語句可以被存儲在transact-sql文件、存儲過程或者視圖中。

　　創建存儲過程是實現把數據庫應用程序開發同數據庫設計的物理實現分離的最好方法。存儲過程在一個地方創建，并且被應用程序調用。

　　如果底層數據庫改變了，并且存儲過程被仔細的修改以反映這些變化，則對存儲過程的調用不會被“中斷”。

　　 crosstab 交叉表經常用于摘要報告。

　　 access交叉表可以在sql腳本中作為transact-sql select語句實現，也可以作為存儲過程或者一個視圖實現。每次一個查詢被列舉的時候，數據節點將會重執行，請確保使用最新的數據。

　　根據不同的數據庫需要，也許把交叉表中的數據存儲為臨時表比較合適（請參看下一行的make table）。臨時表只需要很少的資源，但是只提供了創建臨時表的時刻，數據的一個照相。

　　 make table

　　 access的make table可以在transact-sql腳本或者存儲過程中作為transact-sq l的create table語句實現。語法如下：

　　 select [ all | distinct ]

　　 [ {top integer | top integer percent} [ with ties] ]

　　 [ into new_table ]

　　 [ from {} [,…n] ]

　　 [ where ]

　　 [ group by [all] group_by_expression [,…n]

　　 [ with { cube | rollup } ]

　　 create table mytable (low int, high int)

　　 update

　　 update語句可以被存儲在transact-sql腳本中；但是，實現update語句的推薦方法是創建一個存儲過程。

　　 append

　　 append語句可以被存儲在transact-sql腳本中；但是，實現append語句的推薦方法是創建一個存儲過程。

　　 delete

　　 delete語句可以被存儲在transact-sql腳本中；但是，實現delete語句的推薦方法是創建一個存儲過程。

　　把微軟access查詢遷移到存儲過程和視圖

　　每個access查詢必須被置入這集合語句的集合:

　　 create procedure as

　　 < select, update, delete, insert, create table statement from microsof t access >

　　 go

　　 create view as

　　 go

　　對每個access查詢:

　　打開access，然后在sql server上打開sql server查詢分析器。

　　在access中，在數據庫窗口，單擊queries（查詢）標簽，然后單擊design（設計）。

　　在view（視圖）菜單上，單擊sql。

　　粘貼整個查詢到sql server查詢分析器。

　　測試語法和并且為以后使用保存transact-sql語句，或者在數據庫中運行語句。

　　你可以有選擇的保存transact-sql為腳本。

　　移植微軟access查詢到transact-sql腳本

　　大多數access查詢應該被翻譯成存儲過程和視圖。然而，應用程序開發人員很少運行一些語句可以作為一個transact-sql腳本存儲，這是一個文件擴展名為.sql 的文本文件。這些文件可以從sql server查詢分析器中運行。

　　如果你計劃把你的一些access查詢轉換為.sql文件，考慮把transact-sql語句分成幾個腳本，具體操作要根據如何使用這些語句而定。例如，你可以把那些必須以同一頻率運行的transact-sql語句組合到一個腳本中。另一個腳本可能包含那些只能在特定條件下運行的transact-sql語句。另外，那些必須按一定的順序執行的transact-sql語句應該組合在一個離散腳本中。

　　要把一個access語句轉換為transact-sql文件:

　　復制該語句到sql server查詢分析器。

　　使用藍色的檢查標記圖標解析語句。

　　如果合適就執行語句。

　　 make table　語句在sql server中有幾種轉換方式。開發人員可以創建下面任意一種:

　　視圖視圖創建一個動態的、提供最新信息的虛擬臨時表格的效果。這是i/o密集的，因為每次列舉一個查詢時就要重新加入數據表。

　　臨時表。

　　臨時表為一個連接用戶會話創建一個數據快照。你可以創建局部和全局臨時表。

　　局部臨時表只能在當前會話中是可視的；全局臨時表對所有會話都是可視的。局部臨時表的前綴用單一的數字符號命名(#table_name)，全局臨時表的前綴用兩個數字符號命名(##table_name)。對臨時表的查詢運行得很快，因為它們通常只使用一個表格而不是動態的連接幾個表來獲得一個結果集。

　　關于臨時表的更多信息，見sql server books online。

　　在sql server7.0中數據變換服務(dts)允許你通過創建包實現臨時表創建的標準化、自動化和定期計劃執行。

　　例如，當你移植access 2.0 northwind示例數據庫時，為季度數據報告而創建的交叉表被轉換為一個視圖或者是在常規基礎上創建臨時表的數據轉換。關于dts的更多信息，參見sql server books online。

　　附加設計考慮

　　下面是一些當你把你的access應用程序移植到sql server時應該考慮的問題。

　　使用參數

　　帶參數的sql server存儲過程需要一種來自access查詢的不同的語法，例如：

　　 access 2.0:

　　查詢名稱：employee sales by country, in nwind.mdb:

　　 parameters [beginning date] datetime, [ending date] datetime; select orders.[order id], [last name] & ", " & [first name] as salespe rson, employees.country, orders.[shipped date], [order subtotals].subt otal as [sale amount]

　　 from employees inner join (orders inner join [order subtotals] on orde rs.[order id] = [order subtotals].[order id]) on employees.

= orders.

　　 where (((orders.[shipped date]) between [beginning date] and [ending d ate]))

　　 order by [last name] & ", " & [first name], employees.country, orders. [shipped date];

　　 sql　server 7.0:

　　 create procedure emp_sales_by_country @beginningdate datetime,

　　 @endingdate datetime

　　 as

　　 select orders.[order id], [last name] + ", " + [first name] as salespe rson, employees.country,

　　 orders.[shipped date], [order subtotals].subtotal as [sale amount]

　　 from employees inner join (orders inner join [order subtotals] on orde rs.[order id] = [order subtotals].[order id]) on employees.

= orders.

　　 where (((orders.[shipped date]) between @beginningdate and @endingdate ))

　　 order by [last name] + ", " + [first name], employees.country, orders. [shipped date]

　　 go

　　更多信息，請參看sql server book online

　　嵌套查詢（nested queries）

　　在嵌套形式中，某些access查詢是在別的查詢的頂層上創建的。access中的嵌套查詢在sql server中變成為嵌套視圖。而order by不能作為視圖定義的一部分，可以用附加一個查詢視圖的select語句來代替它。如果你有嵌套的access查詢，你需要創建一系列視圖，然后創建那些在視圖上執行select語句的存儲過程，并且把order by子句粘貼到select語句上。

　　例如，下面的access查詢：

　　 select * from students

　　 where country = "usa"

　　 order by last_name

　　變成一個sql server視圖和存儲過程

　　 create view us_students as select * from students

　　 where country = "usa"

　　 create procedure us_students_order as

　　 select * from us_students order by last name

　　檢查sql server

　　你可以使用sql server查詢分析器的查詢菜單上的parse命令檢查sql server中的視圖或存儲過程函數。在下面的例子中，access查詢使用distinctrow。sql ser ver使用transact-sql distinct命令執行相同的操作。parse命令允許開發人員孤立并且修改他們的access查詢中的語法問題。

　　連接你的應用程序

　　許多access應用程序是用microsoft visual basic for applications或visual basic for applications access用戶接口寫成的。

　　使用visual basic for applications作為應用程序開發環境的應用程序能在sql

　　 server下運行，用jet odbc驅動程序。

　　使用access用戶接口中的窗體和報表的應用程序能使用連接表訪問sql server。

　　如果你的應用程序要使用連接表，確保所有access表轉移到sql server以增強性能。對access (jet)和sql server的混合體使用連接表創建查詢是資源密集的。

　　移植你的文件-服務器應用程序到客戶/服務器模型第一步是確保應用程序能在新數據庫上工作。下一步是通過下面的方法為客戶/服務器環境優化應用程序:

　　監聽傳送到服務器的transact-sql語句。

　　 sql server輪廓是監聽transact-sql語句怎樣被送到數據庫的一個有用的工具。

　　如果你在sql server上運行沒有修改的access應用程序，你可能會通過使用dao和jet/odbc驅動程序向數據庫傳送未經優化的transact-sql。例如，使用jet/odbc 驅動程序的delete語句來刪除1000行將產生1000次到數據庫的調用，這將會嚴重的影響產品數據庫的性能。在這個例子中，sql server輪廓展示了1000個刪除語句，允許你使用有microsoft ole db provider for sql server和microsoft ac tivex data objects (ado)修改應用程序，從而改進應用程序的效率。

　　實現高效索引。

　　在你確定傳送到數據庫的transact-sql語句的效率以后，你可以使用索引更有效的調整那些語句。索引調整向導（index tuning wizard）允許你查找瓶頸，并且向你提出建議。你的語句并沒有被修改，但是由于正確使用索引而使它們的性能提高了。

　　 sql server和access查詢語法

　　下列的表格顯示sql server和access查詢語法之間對應的區別。

　　 access查詢語法

　　 sql　server查詢語法

　　查詢中的order by 在視圖中不支持order by

　　 distinctrow

　　 distinct

　　用"&"做字符串聯用"+"做字符串聯

　　支持的子句/操作符:

　　 select select top n into from where group by having union (all) order by with owneraccess 支持的子句/操作符：

　　 select select top n into from where group by having union (all) order by compute for browse option

　　不支持: compute, for browse, option 不支持: with owneraccess

　　統計函數:

　　 avg

　　 count(column)

　　 count(*)

　　 min

　　 max

　　 first

　　 last

　　 stdev, stdevp

　　 sum

　　 var, varp 統計函數：

　　 avg([all | distinct] expression)

　　 count([all | distinct] expression)

　　 count(*)

　　 grouping (column_name)

　　 max(expression)

　　 min(expression)

　　 stdev, stdevp

　　 sum([all | distinct] expression)

　　 var, varp

　　不支持: first, last

　　 transform

　　 (select statement)

　　 pivot

　　 with rollup, with cube on select statements

　　 make table, alter table

　　支持的子句:

　　 constraint

　　 add column

　　 drop column

　　 drop index

　　 also, stand-alone statement: drop index

　　 create table, alter table

　　支持的子句:

　　 constraint

　　 add column

　　 drop column

　　 stand-alone statement: drop index

最大的網站源碼資源下載站，

上一篇：使用SQL Server數據轉換服務升遷Access數據庫

下一篇：時間格式在sql server中更新帶來的麻煩