Port the Access database to SQLServer7.0

With the increasing demand for enterprise-level high-performance databases, users often need to switch from the file-server environment of the MicrosoftAccessJet engine to the customer-server environment of MicrosoftSQLServer. Access2000UpsizingWizard in MicrosoftOffice2000 transfers data tables and queries to SQLServer7.0

With the increasing demand for enterprise-level high-performance databases, users often need to switch from the file-Server environment of the Microsoft Access Jet Engine to the customer-Server environment of Microsoft SQL Server. In Microsoft Office 2000, Access 2000 Upsizing Wizard transfers data tables and queries to SQL Server 7.0.

With enterprise-level High PerformanceDatabaseFrom the file-Server environment of Microsoft Access Jet Engine to the customer-Server environment of Microsoft SQL Server. In Microsoft Office 2000, Access 2000 Upsizing Wizard transfers data tables and queries to SQL Server 7.0. If you are using an earlier version of Access, you can first upgrade it to Access 2000, and then use the Upsizing WizardPortTo SQL Server.

If you are not willing to use Access 2000 and Upsizing WizardPort, This article can be usedPortA Guide to SQL Server. To transfer an Access application, you first need to transfer the data to SQL Server, and then transfer the queryDatabaseOr transfer it to an SQL file for later execution. The last step isPortApplication.

　　DatabasePortSQL Server tools used

SQL Server Manager)

The SQL Server Manager allows enterprise-level configuration and management of SQL Server and SQL Server objects. The SQL Server Manager provides a powerful scheduling engine with high fault tolerance and an embedded copy management interface.

You can use the SQL Server Manager to implement the following functions:

Manage connections and user licenses

Create a script

Manage the backup of SQL Server objects

Backup Data and transaction processing logs

Manage tables, views, stored procedures, triggers, indexes, rules, default values, and user-defined data types

Create a full text index,DatabaseCharts andDatabaseMaintenance Plan

Input and Output Data

Convert data

Execute various network management tasks

In a computer with Microsoft Windows NT as the operating system, SQL Server Manager is installed by SQL Server Setup and is default as a Server component, however, on machines running Windows NT and Microsoft Windows 95, it will be the customer component by default. You will start the Data transfer service (DTS, Data Transformation Services) from the graphical user interface of SQL Server Manager ). Data transfer Services (DTS) allows you to input and output Data between multiple heterogeneous Data sources. These Data sources are based onDatabaseOr transfer between multiple computers using SQLServer 7.0DatabaseAndDatabaseObject. You can also use the data transfer service to create a data warehouse and data center in an online transaction processing system (OLTP. DTS Wizard allows you to create data transmission packages interactively and input, output, verify, and transfer data through ole db and ODBC. DTS Wizard also allows youDatabaseCopy the schema and data between them.

SQL Server Query Analyzer is a graphical Query tool that analyzes a Query and executes multiple queries at the same time, view data and obtain index suggestions. The SQL Server Query analyzer provides the showplan option to display the data extraction method selected by the SQL Server Query Optimizer.

SQL Server Profiler, which can be captured in real timeDatabaseContinuous records of server activity. SQL Server Profiler allows you to monitor events generated by SQL Server, filter events based on user-specified standards, or output operation steps to screens, files, or data tables. With SQL Server Profiler, you can re-execute the last captured operation. This tool helps application developers identify transactions that may reduce application performance. In the process of converting an application based on the file architecturePortThis feature is useful when it comes to the customer/Server structure, because its last step involves optimizing the application to the new customer/server environment.

Transfer tables and data

To use DTS Wizard to transfer your Access data to SQL Server, take the following steps:

In the tool menu of SQL Server Manager (Enterprise Manager), point to "Data Transformation Services" and click "Import Data. "in the" Select Data Source "dialog box, select Microsoft Access as the Data Source, and enter your. mdb File Name (mdb is the file extension) or select Browse File.

In the dialog box of "Select data target" (Choose a Destination), select "Microsoft ole db Provider for SQL Server" and selectDatabaseServer, and then click the Authentication mode.

In the Specify Table Copy or Query dialog box, click Copy tables ).

In the "Select data source" dialog box, click "Select All ).

　　PortMicrosoft Access Query

You can transfer one of the following formats of Access query to SQL Server:

Transaction Processing SQL script program (Transact-SQL scripts)

Transaction Processing SQL statements are usually composedDatabaseYou can also use the SQL Server Query analyzer included in SQL Server 7.0 to directly run them. The SQL Server Query analyzer can help developers test transaction processing SQL statements, or run query processing, data operations (insert, modify, delete), and data definitions (create tables).

Stored Procedure)

Developers can transfer most of the transaction processing SQL statements that generate self-Access queries (search, insert, modify, and delete) to the stored procedure. A stored procedure written using a transaction to process SQL statements can be used to package and standardize your data access, and the stored procedure is actually stored inDatabase. Stored Procedures can contain or do not contain parameters.DatabaseThe program is called or manually executed by the SQL Server Query analyzer.

Views)

A view is a virtual table that displays specific rows and columns from one or more tables. They allow users to establish queries without directly executing complex connections that constitute the basic query. The view does not support the use of parameters. Views connected to multiple data tables cannot be modified using the INSERT, UPDATE, or DELETE statements. The view is called by SQL statements for transaction processing. It can also be used in SQL Server to query program segments running in analyzer. The SQL Server View and SQL-92 standards do not support ORDER BY sort clauses in the view. For more information about transaction processing SQL, stored procedures, and views, see SQL Server online reference books.

Access Query type SQL ServerPortSelection and Suggestions

A select statement can be stored in SQL files, stored procedures, or views for transaction processing. The stored procedure isDatabaseApplication Development andDatabaseThe best way to separate the physical implementation of the design. Stored procedures can be created in one place and called by applications.

If the stored procedure is based onDatabaseChanges, and stored procedures are carefully modified to reflect these changes, the call to stored procedures will not be damaged.

Cross tabulation (CROSSTAB)

Cross tabulation is often used to summarize reports. An Access cross-tabulation can be executed through SQL program segments, stored procedures, or transactions in the view to process SQL statements. When a query is sent, the data connection is re-executed to ensure that the latest data is used. Based on the actual application, it is more appropriate to store the data in the cross tabulation as a temporary TABLE (refer to make table below). The temporary tables have less resource requirements, however, when creating a temporary table, only one snapshot of the data is provided ).

MAKE TABLE)

In Access, "make table" (create table) can be executed through the SQL script program for transaction processing or the create table statement for the SQL language for transaction processing in the stored procedure. The syntax is as follows:

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 (modify)

The UPDATE statement can be stored in the transaction _ SQL script program. However, a better way to execute the UPDATE statement is to create a stored procedure.

APPEND (ADD)

The ALLEND statement can be stored in the transaction _ SQL script program. However, a better method to execute the APPEND statement is to create a stored procedure.

　　PortMicrosoft Access queries stored procedures and views

Each Access query must be replaced with the following statements:

CREATE PROCEDURE

GO

CREATE VIEW

GO

Execute the following command for each Access query:

Open Access, and then open the SQL Server Query analyzer in SQL Server.

In the AccessDatabaseClick the "Queries" tab in the window, and then click the "Design" button.

Click SQL in the View menu.

Paste the entire Query into the SQL Server Query analyzer.

Test syntax, save the SQL statement for transaction processing for future use, orDatabase. You can save the SQL statement for transaction processing to a script program.

　　PortMicrosoft Access queries transaction processing SQL statements

Most Access queries should be converted to stored procedures and views. However, some application developers can store SQL scripts for transaction processing, a text file with SQL as the file extension. These files can be run in the SQL Server Query analyzer.

If you want to convert some Access queries to SQL files, you can consider placing these transaction processing SQL statements in several script programs according to their usage methods. For example, you can classify transaction processing SQL statements that must run at the same frequency into the same script. Another script should contain all SQL statements for transaction processing that are only run under certain conditions. In addition, SQL statements that must be executed in a certain order should be classified into a discontinuous script.

Transfers the Access statement to the SQL file for Transaction Processing

Copy the statement to the SQL Server Query analyzer.

Use the blue multi-option icon to analyze the statement

Execute this statement as appropriate

The developer who wants to execute the make table query task in Access has several options in SQL Server. Developers can create one of the following objects:

One view

A view has a dynamic virtual table effect and provides the latest information. This is an input/output enhancement, because every time a query is made, it requires a connection to reproduce the data table.

A temporary table

A temporary table creates a snapshot for a connected user session. You can create local or global temporary tables. Local temporary tables are only visible in the current session, while global temporary tables are visible in all sessions. Add the prefix (# table_name) of a single number before the name of a local temporary table, and add the prefix of two digits before the name of the global temporary table (# table_name ). The query of temporary tables is very fast, because they usually only use one table when getting a result set, rather than dynamically joining multiple tables together.

Data transmission in SQL Server 7.0 allows you to create packages to standardize, automate, and schedule temporary tables. For example, when youPortExample of Northwind in Access 2.0DatabaseThe cross-tabulation used for quarterly data reports can be converted into a view or a temporary table that can be created on a standardized basis. For more information about DTS, see SQL Server online reference books.

Other design considerations

The following figure shows howPortTo SQL Server, you must consider some other issues:

Parameters Used

The stored procedure of SQL Server with parameters requires a syntax format different from Access query, for example:

Access 2.0 format:

Query name: Employee Sales By Country, in NWIND. mdb:

PARAMETERS [Beginning Date] DateTime, [Ending Date] DateTime;

SELECT Orders. [Order ID], [Last Name] & "," & [First Name] AS Salesperson, Employees. Country, Orders.

[Shipped Date], [Order Subtotals]. Subtotal AS [Sale Amount]

FROM Employees inner join (Orders inner join [Order Subtotals] ON Orders. [Order ID] = [Order Subtotals].

[Order ID]) ON Employees. = Orders.

WHERE (Orders. [Shipped Date]) Between [Beginning Date] And [Ending Date])

Order by [Last Name] & "," & [First Name], Employees. Country, Orders. [Shipped Date];

SQL Server 7.0 format:

Create procedure EMP_SALES_BY_COUNTRY

@ BeginningDate datetime,

@ EndingDate datetime

AS

SELECT Orders. [Order ID], [Last Name] + "," + [First Name] AS Salesperson, Employees. Country,

Orders. [Shipped Date], [Order Subtotals]. Subtotal AS [Sale Amount]

FROM Employees inner join Orders (end)