Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to change Access database into SQLServer database". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to change Access database into SQLServer database.

First, the premise of rewriting:

The system has installed SQLServer2000 and patched SP3; installed Access; in the Office suite uses an editor that supports plain text editing and displays with line numbers. UltraEdit is recommended, and of course, you can also use FrontPage2003, but the previous version of the line number display is not easy to use.

Personal ability requirements: basic asp syntax, operation of access database, basic operation of SQLServer enterprise manager.

II. Preparation of the database

Generally speaking, there are two situations:

1. The program provides the SQL database format: there is a MDF file, or a SQL script file (suffix .sql) is provided to create the SQL database.

If you have a mdf file, you can use Enterprise Manager to attach it directly, and if you provide a sql script file, then use the Enterprise Manager to create a sql database by yourself, and then the database uses the query Analyzer in Enterprise Manager to run the script to create database tables.

The database established in this way basically does not have to rewrite anything.

2, more do not provide SQL database or script files, at this time, we have to do all this, this is our post to solve the main problem. Generally, such a program will provide an access database, so you can use Enterprise Manager to import the access database. After import, you need to rewrite the following things:

Rewrite the following section against the original access:

(1) there are no automatic fields in the sql database table, so the automatic fields in the original access have been converted into ordinary fields, which need to be manually changed to the identification type with an increment of 1.

(2) for all time fields, if the default value is defined, it must be now (), which needs to be changed to getdate ().

(3) the default values of the original fields are generally not automatically introduced, and need to be manually added against the fields in the original table.

(4) due to different databases, many field types of access and sql will be changed after conversion, such as the original "whether" field will be converted into bit or int, memo field will be converted into longtext,text field into varchar, etc., generally speaking, it will not affect the operation of the program, if there is a problem, we will talk about it in the rewriting section below.

(5) if you want to use a ForSQL program that uses stored procedures, then you should have the program's own way to build a SQL database: have its own SQL database file, or sql script If not, it is impossible to establish a stored procedure by importing the access database, so you'd better give up the program version of the ForSQL, use the same version of the ForAccess program, import the access database, and then use the following rewriting method to change the SQL version of the program.

Third, the rewriting of connection string

You can refer to this section of Mobile Network, which is for access and SQL, respectively.

DimConnStrIfIsSqlDataBase=1Then'sql database connection parameters: database name, user password, user name, connection name (local local, local IP) DimSqlDatabaseName,SqlPassword,SqlUsername,SqlLocalNameSqlDatabaseName= "dvbbs7" SqlPassword= "SqlUsername=" dvbbs "SqlLocalName=" (local) "ConnStr=" Provider=Sqloledb;UserID= "& SqlUsername&"; Password= "& SqlPassword&"; InitialCatalog= "& SqlDatabaseName&"; DataSource= "& SqlLocalName&" "if you use Else' for free for the first time, please modify the database address of this office and modify the database name in the data directory accordingly, such as changing dvbbs6.mdb to dvbbs6.aspDb=" data/fengerqingqing.mdb "ConnStr=" Provider=Microsoft.Jet.OLEDB.4.0. DataSource= "& Server.MapPath (db) EndIfOnErrorResumeNextSetconn=Server.CreateObject (" ADODB.Connection ") conn.openConnStr of course if you use SQL, the use statement about access can be deleted, that is, after else and in front of onerrorresumenext, it becomes like this: DimConnStr'sql database connection parameters: database name, user password, user name, connection name (local locally, IP locally) DimSqlDatabaseName,SqlPassword,SqlUsername,SqlLocalNameSqlDatabaseName=" dvbbs7 "SqlPassword=" SqlUsername= "dvbbs" SqlLocalName= "(local)" ConnStr= "Provider=Sqloledb;UserID= & SqlUsername&" Password= "& SqlPassword&"; InitialCatalog= "& SqlDatabaseName&"; DataSource= "& SqlLocalName&"; "OnErrorResumeNextSetconn=Server.CreateObject (" ADODB.Connection ") conn.openConnStr

You can also be concise and write it like this:

Setconn=Server.CreateObject ("ADODB.Connection") conn.open "Provider=Sqloledb;UserID=sa;Password=1234567;InitialCatalog=dvbbs7;DataSource= (local);"

The database name, data source, user and password inside will be rewritten according to your actual situation.

How to change Access database of ASP website into SQLServer database

IV. Rewriting of the program

There are also two situations:

1, if you are lucky to get the ForSQL program, then if the above database establishment process does not encounter trouble, the program can basically run, error, only the bug of the program itself, how to modify is not the content discussed in this post, do not repeat.

2. In most cases, the program itself is ForAccess, and the main difference between the program and ForSQL is the SQL query statement used in the program. Note that SQL query statements are an indispensable part of database applications, whether ForSQL or ForAceess programs use roughly the same syntax, but there are some subtle differences. It is these differences that make the program not universal and are the main content that we need to modify. In this way, the general parts to be modified are as follows:

(1) the problem of time function: the time function of SQL database is different from that of access. The most common function is to take the present time. Access is now () and SQL is getdate (). Therefore, wherever now () is used in the where clause, it should be changed to getdate (); note that the now () function should also be used in the asp program itself, and never change the now () function that is not used in database queries or execution statements.

(2) time comparison function: datediff is the format used in access query. These quotation marks should be removed in SQl. At the same time, a # may be added before and after the time format, which is also removed. This also means that what is in the sql statement should be left as it is in the asp statement.

(3) the expression of null value: in access, the judgment of null value is usually expressed as whether or not = "", but this is often wrong in SQL. If you encounter an error or the program is not running properly, you can judge it like this: where (nameisnull)

(4) judgment of true and false values: in access, = true and = false can be used to judge, but there will be errors in SQL, so this kind of judgment should be changed to = 1 and = 0 in SQL query or execution statement, respectively. Note: some programs are written as = "true", but because of the quotation marks, this field is a character type, you can't change it to = 1, just leave it as it is.

The above are more common rewriting places, and some are not very common. If you encounter it, you can discuss it in this reply.

5. Debugging of the program

Previously, an editor with a line number is recommended because the above rewriting is unlikely to be done by directly searching the source code of the program, and it is difficult to find it all.

The way I take is generally like this: when the database is rewritten, debug the program directly, and after the error, look at the error prompt and find the line of code of the corresponding file, but the root is often not that line, for example, the error statement is: conn.execute (sql), but there is nothing wrong with the sentence itself. the reason for the error is the sql string inside, so look up at how the sql string is generated. Modify it in accordance with the procedure described above.

After the database is imported, the auto-increment fields need to be rewritten, and all numeric types need to be increased in length, preferably with decimal.

All default values are lost. Mainly numeric type and date type.

All now (), time (), date () should be changed to getdate ().

All datediff should be changed to datediff (day,time1,time2).

It is possible that some true/false types cannot be used, so it should be changed to 1 true/false 0.

The comment type is used through cast (columnasvarchar).

CursorType should be changed to 1, that is, the first numeric parameter should be given when opening the database, otherwise the record may be incomplete.

Isnull (rowname) will be changed to rowname=null

When the auto-numbering type in ACCESS's database is converted, SQLServer does not set it to auto-numbering. We need to add identity to the SQL creation statement to indicate automatic numbering!

When converting, SQL Server defaults to smalldatetime, so we'd better change it to datetime, because the range of datetime is larger than smalldatetime. Sometimes when using the smalldatetime type, the conversion fails, but when using the datetime type, the conversion is successful.

The sql statements for these two kinds of databases are not the same, for example, when deleting records in ACCESS database: "delete*fromuserwhereid=10", while deleting SQLSERVER database is used: "deleteuserwhereid=10".

The date function is different. In dealing with ACCESS database, we can use date (), time () and other functions, but in SQLServer database processing, we can only use datediff,dateadd and other functions, but not date (), time () and other functions.

In ACCESS database processing, some VB functions, such as cstr () function, can be used directly in sql statements, but not in SQLServer database processing.

At this point, I believe you have a deeper understanding of "how to change Access database into SQLServer database". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.