Considerations for transferring Access to SqlServer (relatively full version)

1. to automatically add fields, you must rewrite them. The automatically numbered field that is frequently used in access. After being imported to mssql, it is not an auto-incrementing int and needs to be set manually, change the ID of the Imported Automatic number field to yes, and the seed and increment quantity are both 1 to be automatically numbered. 2. All default values are lost. Mainly numeric type and date

1. to automatically add fields, you must rewrite them. The automatically numbered field that is frequently used in access. After being imported to mssql, it is not an auto-incrementing int and needs to be set manually, change the ID of the Imported Automatic number field to yes, and the seed and increment quantity are both 1 to be automatically numbered. 2. All default values are lost. Mainly numeric type and date

1. to automatically add fields, you must rewrite them. The automatically numbered field that is frequently used in access. After being imported to mssql, it is not an auto-incrementing int and needs to be set manually, change "no" of the Imported Automatic number field to "yes", and "Seed" and "incremental" to "1" to be automatically numbered.

2. All default values are lost. It mainly belongs to the numeric type and date type. In the past, some fields do not need to be manually assigned by the program, such as the date. now you need to add another rs ("newsDate") = now.

3. All now (), time (), and date () must be changed to getdate (). This is what we say on the Internet, but after I change it, the program runs abnormally. You can do it without changing it.

4. Change all datediff ('D', time1, time2) to datediff (day, time1, time2)

5. Change all datediff ('ww ', time1, time2) to datediff (week, time1, time2)

6. Change all datediff ('D', time1, time2) to datediff (day, time1, time2)

7. In mssql server, there are many reserved words that are not available in access. When you import data to mssql, the problem arises. Mssql automatically adds "[field name]" to these fields (including the table names in the database) during import. If you want to remove [], the field name is not a reserved word of mssql.

8. In some cases, conn.exe cute can only read data from some fields, but cannot read data from some fields. It is very strange that the difference between rs.openand conn.exe cute in aspis largely related, and no essential answer is found. It's strange.

9. date functions are different. functions such as date () and time () can be used in ACCESS database processing. However, functions such as datediff and dateadd can only be used in SQL SERVER database processing, instead, functions such as date () and time () cannot be used.

10. For date-related fields during conversion, SQL SERVER is of the smalldatetime type by default. We recommend that you change it to the datetime type because the range of the datetime type is larger than that of the smalldatetime type. Sometimes, if the smalldatetime type is used, the conversion fails. If the datetime type is used, the conversion is successful.

11. Change isnull (rowname) to rowname = null

12. Change CursorType to 1, that is, when you open the database, the first numeric parameter must be set to 1. Otherwise, the record may not be displayed.Complete

13. Use cast (column as varchar) as the comment type.

14. The value of true or false type cannot be used. The value must be set to 1/0.

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

16. The original "DELETE * FROM…" in ASP ......" To change to "delete from ......", Some operations are normal, and some operations are invalid.

17. "" or "/" can be used for Division in access. "/" can only be used in MSSQL "/"

18. first, if you need to create an SQLSERVER website from the very beginning, you 'd better install your computer on a SERVER version like Windows 2003 or. Such a version supports the installation of the official SQLSERVER version, XP can only support personal or development versions. I have not installed it, but it is best to work in the official version for website debugging. The entire conversion process is not too complicated, but at least some energy can be saved. My machine is XP, so I had to debug it with ACCESS first.

19. Okay. When you find a machine with SQLSERVER installed, you can start the conversion and debugging.

A) use the conversion software: CooSel2.0 CreateSQL V1.05, which is very useful. For specific operations, I used to generate the SQL script for promotion, save it as the *. SQL file, and then transfer it to the SQL2000 query analyzer for execution. The specific operations of SQLSERVER are not described here. In this way, your database is converted into an MSSQL database.

B) for asp, the first step is to modify the database connection file: refer to the conn. asp

20. Dim ConnStr

21. 'SQL database connection parameters: Database Name, user password, user name, and connection name (local for local use, IP for foreign use)

Dim SqlDatabaseName, SqlPassword, SqlUsername, SqlLocalName

SqlDatabaseName = "dvbbs7" 'Database Name

SqlPassword = "" 'Database Password

SqlUsername = "dvbbs" 'database username

SqlLocalName = "(local )"

ConnStr = "Provider = Sqloledb; User ID =" & SqlUsername & "; Password =" & SqlPassword & "; Initial Catalog =" & SqlDatabaseName &"; data Source = "& SqlLocalName &";"

On Error Resume Next

Set conn = Server. CreateObject ("ADODB. Connection ")

Conn. open ConnStr

A) at this time, you can first browse the new website. If an error occurs, you don't have to worry about it. However, many pages can be displayed normally. The next step is to modify the whole site program. EspeciallyNote:All the modifications are only for statements that operate on the database, and almost all are modifications to SQL statements. In general code, there is absolutely no need to modify it! Otherwise, an error occurs !!

B) Real value judgment. ACCESS provides the boolean data type, but in SQL Server, it is incorrect if you enter Where AA = True in SQL statements, because MSSQL does not support True or False judgment in the database. In this case, you need to change True and False in all SQL statements to 1 and 0. In general programs, such as rs ("aa") = True, you do not need to modify it! This statement is not an operation on the database. It may be difficult for a large website to do so, but there are some simple methods. For example, in dreamweaver, you can find all the True and False values by searching the entire folder, filter the True and False values for database operations, and change them to 1 and 0. Another point: you also need to change it in SQLSERVER. After the ACCESS is converted, the Boolean value is changed to the Bit value. It is best to set the default value to 0, that is, set the default value to False like ACCESS.

C) some websites mentioned that SQL database tables do not have automatic fields. Therefore, the original Automatic fields in access are converted to common fields, which must be manually changed to the ID type and the increment is 1. In fact, the software has already completed the conversion process for you. If you are manually converting, you really need to modify the ID type. I have never met one other point, but some websites also mentioned: the expression of null: in access, the null value is usually expressed by =, however, this error often occurs in SQL. If an error occurs or the program is not running properly, you can change it to where (name is null ).

D) if you use the VB Function in SQL statements at the same time, it is a pity that this cannot be identified in MSSQL. The best way is to process it out of SQL first, then, it is saved to the variables and imported into the SQL statement.

E) The most important thing is the modification of time functions, which accounts for 90% of the workload. One is to convert NOW () in all database operation statements to GETDATE (); the other is that functions such as datediff and dateadd need to remove the quotation marks in the first item, that is, datediff ("s ", date1, date2) is changed to datediff (s, date1, date2). Third, the time strings in ACCESS are all included in #, while those in MSSQL are expressed in, it is equivalent to a general string. When the time in the access SQL statement is queried using a variable, generally, "select * from aaaa while time = #" & variable name & "#" cannot be used in mssql, its syntax is "select * from aaaa while time = '" & variable name &"'"". (This means that you can use the date and time variables as strings ~~~)

22. There are some time functions that are not recognizable in MSSQL. Basically, all the time and date functions in SQL Server are as follows:

23. day (), month (), year ()

24. dateadd (datepart, number, date). datapart specifies the addition of this part, number indicates the number, and date indicates the addition of which. Datepart values include year, quarter, month, dayofyear, day, week, hour, minute, and second. For example, dateadd (day, 1, getdate () tomorrow ())

25. datediff (datepart, date1, date2). datapart is the same as above. The result of the entire function is date2-date1.

26. Take that part of datename (datepart, date) and return the string.

27. Take part of datepart (datepart, date) and return an integer.

28. getdate () Current Time

Last, sorting:

When I tested the program, I found that the display of Chinese was normal at the beginning. When I updated it, the normal Chinese that I read immediately became a question mark, in addition, the data filled in the database records directly becomes the type of the problem. This is caused by an error specified by the database sorting rule (Collation). You must correctly write Chinese characters to the NVARCHAR field, either add N (N 'Chinese value') before the Chinese value of the SQL statement, or determine that the database sorting rule is Chinese_PRC. So I created a new database, set the sorting rule to chinses_ci_ai, and then import the data. This solves the problem.

PS: What is a sorting rule? "In Microsoft SQL Server 2000,

The physical storage of strings is controlled by sorting rules. Sorting rules specify the bit mode and storage of each character

Rules Used for saving and comparing characters. "

Run the following statement in the query analyzer to obtain all the sorting rules supported by SQL SERVER.

Select * from: fn_helpcollations ()

The name of a sorting rule consists of two parts. The first half is the character set supported by this sorting rule.

For example:

Chinese_PRC_CS_AI_WS

First half: the UNICODE character set. The Chinese_PRC _ pointer sorts UNICODE in simplified Chinese characters.

The second half of the sorting rule is the suffix meaning:

_ BIN binary sorting

_ CI (CS) is case sensitive, CI is case insensitive, and CS is case sensitive

_ Whether AI (AS) distinguishes stress, AI does not distinguish,

_ KI (KS) indicates whether Kana is distinguished. KI is not distinguished, and KS is distinguished.

_ Whether WI (WS) is differentiated by width WI and WS

Case Sensitive: select this option if you want to make the comparison between uppercase and lowercase letters different.

Accent differentiation: select this option if you want to treat the comparison as different from the accent and non-accent letters. If this option is selected,

Comparison also treats letters with different accents as unequal.

Kana differentiation: select this option if you want to treat Katakana and katakana as different Japanese syllables.

Width differentiation: select this option if you want to make the comparison between halfwidth and fullwidth characters.