Port the Access database to MicrosoftSQLServer7.0

Abstract: This article describes the process and tool for converting an Access database to an SQL Server database. 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. MicrosoftOffice200

Abstract: This article describes the process and tool for converting an Access database to an SQL Server database. 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. Microsoft Office 200

Abstract: This article describes DatabaseConvert to SQL Server DatabaseProcess and tool Introduction DatabaseFrom 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 Wizard PortTo SQL Server. If you are not willing to use Access 2000 and Upsizing Wizard Port, This article can be used PortA Guide to SQL Server. To transfer an Access application, you first need to transfer the data to SQL Server, and then transfer the query DatabaseOr transfer it to an SQL file for later execution. The last step is PortApplication. Database PortThe used SQL Server tool 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 scripts manage backup data and transaction processing log management tables, views, stored procedures, triggers, indexes, rules, default values, and user-defined data of SQL Server objects full text index, DatabaseCharts and DatabaseMaintenance Plan input and output data conversion 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, by default, it is a server component, and on machines running Windows NT and Microsoft Windows 95, it will be a client 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) Data transfer service allows you to input and output Data between multiple heterogeneous Data sources based on DatabaseOr transfer between multiple computers using SQL Server 7.0 DatabaseAnd DatabaseObject. 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 you DatabaseCopy 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 can be captured in real time DatabaseContinuous 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 architecture PortThis 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 use DTS Wizard to transfer your Access data to SQL Server. You can take the following steps: In the SQL Server Manager tool menu, 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 select DatabaseServer, 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 scripts (Transact-SQL scripts) Transaction Processing SQL statements are usually composed DatabaseYou 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 generated from 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 in Database. Stored Procedures can contain or do not contain parameters. DatabaseThe program is called or manually executed by the SQL Server Query analyzer. Views 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 Server PortSELECT and recommend that a SELECT statement be stored in the SQL file, stored procedure, or view for transaction processing. The stored procedure is DatabaseApplication Development and DatabaseThe 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 on DatabaseChanges, and stored procedures are carefully modified to reflect these changes, the call to stored procedures will not be damaged. Cross tabulation (CROSSTAB) 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 ). Create a TABLE) you can use the SQL script program for transaction processing or the SQL statement "CREATE TABLE" for transaction processing in the stored procedure. Syntax: 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) UPDATE statements can be stored in the transaction _ SQL script program, however, a better way to execute the UPDATE statement is to create a stored procedure. The APPEND (ADD) ALLEND statement can be stored in the transaction _ SQL script program. However, a better way to execute the APPEND statement is to create a stored procedure. PortWhen Microsoft Access queries stored procedures and views, each Access query must be replaced with the following statements: CREATE PROCEDURE AS <SELECT, UPDATE, DELETE, INSERT, create table statement from Microsoft Access> GO CREATE VIEW AS GO should execute the following command for each Access query: open Access, and then open the SQL Server Query analyzer in SQL Server. In the Access DatabaseClick 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, or Database. You can save the SQL statement for transaction processing to a script program. PortMicrosoft Access queries most Access queries of transaction processing SQL statements should be converted into stored procedures and views. However, some application developers are not