I use the forum program provided by the mobile network to create an internal forum. It turns out that this forum is on IIS5 in Windows2000. His database is for Access. However, after I migrate this forum to Windows 2003, I found it often dead. I have found it online, and I have consulted Microsoft engineers, and many people think that I will use Access. Also, I want to add a race on the website home page to display a new post in the forum, but if you continue to use Access, you will not be so convenient. Then decided to migrate into SQL.
Step 1: Import data into SQL Server
Go to SQL Server Enterprise Manager, create a new database, then select Import Data, select the Access database as the import source, import all data.
Step 2: Modify the data sheet
In this way, the data table established is that there is no primary key and default, and the original Access library modifies each table. (This is more annoying, but must do, because these default values will be used in the following programs)
Step 3: Modify the program
Migrate the site to the appropriate server and create a new site. First, to be changed is the database connection. Since some functions in Access are different in SQL, the program will report an error, and modify the relevant place according to the error information of the program. Here mainly modifies two places:
Now () function in the SQL statement:
Use the now () function in Access to get the current time, and use the getDate () function in SQL Server to get the current time. However, because the original program is written by VB Script, the NOW () function is also used in VBScript, so be careful
The first parameter of the dateDiff function in the SQL statement:
In the ACCESS, the first parameter of Datediff is quoted, and only one letter is used to represent the comparison portion, and the quotation marks are not required in SQL Server, and is expressed with full name and or simply, and is also two letters.
After the change is finished, all the errors are corrected (all simple).
The program can be used normally, it may cost more for more than 4 hours before and after.
I think the following points:
1. If we want to use the Access database, we should try to avoid the use of the database as much as possible, but a function of multi-use programming, in the outside of the SQL statement. Calculation problem. If you don't avoid using these database functions, we should also set a clear annotation tag in your code for future migration.
2. When we design the program, we must fully consider the needs of the database. Insert the statement, query statements, etc., try to use standard SQL syntax, and do not tire the inserted defaults, not to rely on the database to provide default values. , Doing so, the future improvement may be beneficial.
