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

This article introduces the relevant knowledge of "what matters need to be paid attention to when converting Access to SQL Server". 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!

First of all, I'm talking about switching between ACCESS2000,SQL2000. I haven't tried anything else. I hope you can try it a lot. There must be a way.

Second, the method of conversion

1. Open Database Source in Administrative tools under Control Panel.

2. Press "add" to add a new data source, and select "Driver do microsoft Access" in the selection column.

(* .mdb) ", a box will appear when you are finished

Enter the name you want to write in the "database source". I'll call it "ABC", which means you don't need to fill it in. Then, press the selection below to find your database address and select it (note, please back up your ACCESS database first), and then make sure.

The data source is built here, and the rest is converted.

3. Open SQL2000 Enterprise Manager, enter the database, and create an empty database "ABC".

4. Select the newly created database, right-click, select "Import data" under "all tasks", and press "next" to continue.

5. Select "Driver do microsoft Access (* .mdb)" in the database source drop-down, and in "user / system DSN", select the "ABC" you just added, and click "next"

6. "purpose" does not need to be modified. Select the server (generally, it is your own native "local", or you can also select the server address or local area network address to determine whether your permissions can be operated). "using WINDOWS authentication" refers to the operation with your own system administrator identity. "using SQL authentication operation" can be used for the operation of the website. The latter is recommended.

7. After "use SQL authentication" is selected, fill in your user name and password. I choose the system default number "sa" and "*". Select the newly created "ABC" in the database, and press "next".

8, the two individual selections for this step, "copy tables and views from data sources" and "specify data to be transferred with a query instruction", select the former, and press "next" to continue

9, the table of your own ACCESS database will appear here. After pressing "Select all", the next step

10, "DTS Import / Export Wizard", see "run now" is selected and press "next"

11, press "finish" to continue

12, this step you will see your data is imported into SQL2000, when the words "XXX tables have been successfully imported into the database" appears, and all tables have green ticks in front of them, it means that all data has been successfully imported. If there is a problem or there is a red cross in front of the table, it means that the table has not been successfully imported. At this time, you should go back and check whether your operation is correct.

Third, data modification

1. Since there is no "automatic numbering" in SQL2000, your fields set with "automatic numbering" will become non-empty fields, so you must manually modify these fields and mark them as "Yes", seed as "1" and increment as "1".

2. In addition, after ACCESS2000 is converted to SQL2000, the field with the original attribute of "Yes / No" will be converted to a non-empty "bit". At this time, you must change it to the attribute you want.

3, in addition, we should pay attention to the grasp of the time function. Access and SQL are many different.

Which points should be paid attention to when ACCESS is changed to SQL?

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 ('day, time1, time2) 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 (column as varchar).

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) should be changed to rowname = null

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

When converting, the date-related field, SQL SERVER defaults to smalldatetime, and we'd better change it to datetime, because the scope 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 databases are not the same, for example: "delete * from user where id=10" is used to delete records in ACCESS database, and "delete user where id=10" is used to delete SQL SERVER database.

The date function is different. In dealing with ACCESS database, we can use date (), time () and other functions, but in SQL SERVER 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 SQL SERVER database processing.

Some experiences from ACCESS to SQL SERVER Database

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

two。 When converting, the date-related field, SQL SERVER defaults to smalldatetime, and we'd better change it to datetime, because the scope of datetime is larger than smalldatetime. I encounter this situation, when using smalldatetime type, the conversion fails, but when using datetime type, the conversion is successful.

3. The sql statements for these two databases are not the same, for example: "delete * from user where id=10" is used to delete records in ACCESS database, and "delete user where id=10" is used to delete SQL SERVER database.

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

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

This is the end of the content of "what do you need to pay attention to when converting Access to SQL Server"? thank you for 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.