What are precautions for converting Access to ms SQL?

Often on some bbs saw someone asking Access to convert to ms SQL what to pay attention to, or writing stored procedures, now, I want to write some notes or common syntaxes here for your reference (some are common asp functions)

-------------------

Access: Now ()

Ms SQL: GetDate ()

-------------------

Access: mid

SQL MS: SubString (expression, start, length)
Expression-target to be searched, but cannot use aggregate Functions
Start-specify the start position of the string
Length-specify the length of the obtained string

-------------------

Asp: Instr

Ms SQL: CharIndex (expression1, expression2)
The parameter is in the opposite position as the Asp Instr.

-------------------

Asp: Dim

Ms SQL: Declare @ variable DataType
For example, Declare @ VarName VarChar (50) declares that the variable @ VarName is of the VarChar type and the length is 50.

-------------------

Assignment:
Declare @ VarName VarChar (10)
Set @ VarName = 'this is content'

-------------------

Data type conversion functions, such as Asp Cstr and Clng

Ms SQL: Cast (expression As DataType)
Expression is the target object.
DataType is the data type to be converted

Example:

Declare @ VarName VarChar (20) -- Declare a variable named @ VarName in the VarChar type with a length of 20
Set @ VarName = '2' -- assign a value to @ VarName
Cast (@ VarName As Int) -- convert to Int type data

-------------------

String Connection Symbol: +

-------------------

Loop:

While Boolean_expression
Begin
-- Statement to be executed
End

Boolean_expression is a Boolean expression. If the specified condition is true, statements are executed cyclically.

-------------------

If usage
If (Boolean_expression)
Begin
-- Execute True branch ....
End

Usage of if... else...
If (Boolean_expression)
Begin
-- Execute True branch ....
End
Else
Begin
-- Execute the False Branch
End

-------------------

What should I pay attention to when changing ACCESS to SQL?

 
 
You can see other people sometimes ask this question .. So I summarized the previous ideas of senior citizens on various websites and shared them with them:

After the database is imported, fields that need to be automatically added must be overwritten, and the length of all numeric types must be increased. It is best to use decimal.

All default values are lost. It mainly belongs to the numeric type and date type.

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

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

Some types of true/false may not be available and must be changed to 1/0.

The remarks type must be cast (column as varchar.

The value of CursorType must be changed to 1, that is, when the database is opened, the first numeric parameter must be set to 1. Otherwise, the record may be incomplete.

Change isnull (rowname) to rowname = null

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

During conversion, SQL SERVER defaults to the smalldatetime type for date-related fields. We recommend that you change it to the datetime type because the datetime type has a larger range than the smalldatetime type. Sometimes, if the smalldatetime type is used, the conversion fails. If the datetime type is used, the conversion is successful.

The SQL statements used to operate these two databases are not all the same. For example, when you delete a record in an ACCESS database, use: "delete * from user where id = 10 ", to delete an SQL SERVER database, use: "delete user where id = 10 ".

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.

In the process of ACCESS database, some VB functions can be used in SQL statements, such as the cstr () function, but cannot be used in the process of SQL SERVER database.