Today I helped my classmates change an old system as a graduation design, and this system technology is definitely behind asp+access. In order to cope with the teacher's acceptance, the student's programming was not very good, and he thought of starting to do the graduation project at the last moment, so he found me.
I read the code he got from somewhere and ran it. It was OK and could run. In two days, the program was accepted. I had no time to change the asp to, so I suggested changing the database and then decided to convert the access to SQL server. It seems that the product of a company has a lot of problems when it turns out. Now summarized as follows,
First talk about how to transfer data, open the enterprise manager of SQLServer, create a table, then right-click the table, click to import the data, and then select the data source (I am the mdb file of access here), next all the way, basically there is no problem, the data is transferred like this, but the problem is not that simple, the mechanisms of SQL server and access are very different
1. Since there is no automatic numbering in SQL2000, your fields set with automatic numbering will become non-empty fields. This requires manual modification of these fields and selecting its label as: seed is 1 and increment is 1.
2. In addition, after ACCESS2000 is converted to SQL2000, the field with the original attribute yes/no will be converted to a non-empty bit. At this time, you must modify it to the attribute you want;
3. In addition, everyone should pay attention to the grasp of time functions. ACCESS and SQL are many differences.
Some experiences from ACCESS to MS SQL database
When converting the automatic numbering type in the database, the SQL server does not set it to the automatic numbering type. We need to add identity to the SQL creation statement to indicate automatic numbering!
2. When converting, SQL SERVER defaults to smalldatetime type for fields related to date. We should change it to datetime type because the range of datetime type is larger than that of smalldatetime type. I encountered this situation. When using the smalldatetime type, the conversion failed, and when using the datetime type, the conversion was successful.
3. The SQL statements that operate on these two databases are not the same. For example: when deleting records on ACCESS database, use: delete * from user where id=10, and when deleting SQL SERVER database, use: delete user where id=10.
4. The date functions are different. In the processing of ACCESS database, functions such as date() and time() can be used, but in the processing of SQL SERVER database, functions such as datediff and dateadd can only be used, and functions such as date() and time() cannot be used.
5. In the processing of ACCESS database, some VB functions can be used directly in the SQL statement, such as the cstr() function, but in the processing of SQL SERVER database, it cannot be used.
I read the code he got from somewhere and ran it. It was OK and could run. In two days, the program was accepted. I had no time to change the asp to, so I suggested changing the database and then decided to convert the access to SQL server. It seems that the product of a company has a lot of problems when it turns out. Now summarized as follows,
First talk about how to transfer data, open the enterprise manager of SQLServer, create a table, then right-click the table, click to import the data, and then select the data source (I am the mdb file of access here), next all the way, basically there is no problem, the data is transferred like this, but the problem is not that simple, the mechanisms of SQL server and access are very different
1. Since there is no automatic numbering in SQL2000, your fields set with automatic numbering will become non-empty fields. This requires manual modification of these fields and selecting its label as: seed is 1 and increment is 1.
2. In addition, after ACCESS2000 is converted to SQL2000, the field with the original attribute yes/no will be converted to a non-empty bit. At this time, you must modify it to the attribute you want;
3. In addition, everyone should pay attention to the grasp of time functions. ACCESS and SQL are many differences.
Some experiences from ACCESS to MS SQL database
When converting the automatic numbering type in the database, the SQL server does not set it to the automatic numbering type. We need to add identity to the SQL creation statement to indicate automatic numbering!
2. When converting, SQL SERVER defaults to smalldatetime type for fields related to date. We should change it to datetime type because the range of datetime type is larger than that of smalldatetime type. I encountered this situation. When using the smalldatetime type, the conversion failed, and when using the datetime type, the conversion was successful.
3. The SQL statements that operate on these two databases are not the same. For example: when deleting records on ACCESS database, use: delete * from user where id=10, and when deleting SQL SERVER database, use: delete user where id=10.
4. The date functions are different. In the processing of ACCESS database, functions such as date() and time() can be used, but in the processing of SQL SERVER database, functions such as datediff and dateadd can only be used, and functions such as date() and time() cannot be used.
5. In the processing of ACCESS database, some VB functions can be used directly in the SQL statement, such as the cstr() function, but in the processing of SQL SERVER database, it cannot be used.