把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中更新带来的麻烦