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

This article introduces the relevant knowledge of "what do you need to pay attention to from Access to SqlServer". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. Automatically add fields need to be rewritten. The automatic numbering field, which is often used in access, is not a self-increasing int after it is imported into mssql. It needs to be set manually to change the identification of the imported automatic numbering field from "No" to "Yes", "seed" and "incremental" are both "1" in order to become an automatic numbering.

2. All default values are lost. Mainly number type and date type

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

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

5. All datediff ('ww', time1, time2) should be changed to datediff (week, time1, time2)

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

7. There are many reserved words in mssql server that are not available in access. When you import data into mssql, the problem arises. When mssql imports, it automatically adds "[field name]" to these fields (including table names in the database), so you must modify your script to add brackets to the corresponding field name (or table name), or change the field name to a reserved word that is not mssql.

8. When using access for the use of time, we like to use sql statements such as "select * from aaaa while time=" & now (). However, there is no "now ()" function in mssql, but "getdate ()" is used, so the "now ()" in all sql statements must be replaced with "getdate ()".

9. The date function is different. In dealing with ACCESS database, functions such as date () and time () are available, but for

In SQL SERVER database processing, only datediff,dateadd and other functions can be used, but not date (), time () and other functions.

10. When converting, SQL SERVER defaults to smalldatetime for date-related fields. We'd better change it to datetime, because the range of datetime is larger than that of smalldatetime. Sometimes when using the smalldatetime type, the conversion fails, but when using the datetime type, the conversion is successful

11. Isnull (rowname) should be changed to rowname = null

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

13. The comment type should be used through cast (column as varchar)

14. The true/false type cannot be used. It should be changed to 1Universe 0.

15. The sql statements for these two kinds of databases are not the same. For example, when deleting records in ACCESS database, it is used: "delete * from user where id=10", while for SQL SERVER database, it is used: "delete user where id=10".

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

17. When querying the time in the sql statement of access, we usually use "select * from aaaa while time=#" & variable name & "#", which is not allowed in mssql. His syntax is "select * from aaaa while time='" & variable name & "'". (it means that you use date-time variables as strings.)

18. The original "Delete * FROM …" in ASP To change it to "Delete FROM."

19. It is possible that rs.update failed. Change the update table name to set field = 'value' so as to pass.

20. Access division can use "\" or "/", while MSSQL can only use "/"

21. Establish a primary key in SqlServer

22. If you still have any questions, try rs.open sql,conn,3,2.

This is the end of the content of "what do you need to pay attention to from Access to SqlServer"? thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.