Project Settings (Conversion) (MySQLToSQL)
The Conversion page of the Project Settings dialog box contains settings that customize how SSMA converts MySQL syntax to SQL Server or SQL Azure syntax.
The Conversion pane is available in the Project Settings and Default Project Settings dialog boxes.
Use the Default Project Settings dialog box to set configuration options for all projects. To access the conversion settings, on the Tools menu, select Default Project Settings, select migration project type for which settings are required to be viewed /changed from Migration Target Version drop down, click General at the bottom of the left pane, and then select Conversion.
To specify settings for the current project, on the Tools menu click Project Settings, then click General at the bottom of the left pane, and then click Conversion.
Options
Collate Clause
| Term | Definition |
|---|---|
| Explicit COLLATE clause conversion | Explicit COLLATE clause conversion option specifies how to convert explicit COLLATE clauses in MySQL code. Possible Choices: Ignore and Mark with a Warning / Generate an Error Default Mode: Ignore and Mark with a Warning Optimistic Mode: Ignore and Mark with a Warning Full Mode: Ignore and Mark with a Warning |
Column Constraints
| Term | Definition |
|---|---|
| Generate Constraint for columns of ENUM data type | Generates constraint for columns of ENUM data type in the SQL Server or SQL Azure table, if it is not present in the MySQL table. If yes, all converted columns of ENUM data type will be accompanied with CHECK constraint controlling the value. Default Mode: No Optimistic Mode: No Full Mode: Yes |
| Generate Constraint for columns of SET data type | Generates constraint for columns of SET data type in the SQL Server or SQL Azure table, if it is not present in the MySQL table. If yes, all converted columns of SET data type will be accompanied with CHECK constraint controlling the value. Default Mode: No Optimistic Mode: No Full Mode: Yes |
| Generate Constraint for columns of UNSIGNED numeric data type columns | Add CHECK for non-negative value to columns of UNSIGNED numeric data types. Default Mode: No Optimistic Mode: No Full Mode: Yes |
| Generate Constraint for YEAR data type columns | Generates constraint for YEAR data type columns in the SQL Server or SQL Azure table, if it is not present in the MySQL table. If yes, all converted columns of YEAR data type will be accompanied with CHECK constraint controlling the value. Default Mode: No Optimistic Mode: No Full Mode: Yes |
Data Types
| Term | Definition |
|---|---|
| ENUM data type conversion | Specifies how MySQL ENUM data type should be converted either as Convert to NVARCHAR or Convert to Numeric Default Mode: Convert to NVARCHAR Optimistic Mode: Convert to NVARCHAR Full Mode: Convert to NVARCHAR |
| SET data type conversion | Specifies how MySQL SET data type should be converted, Convert to NVARCHAR(L)/Convert to BINARY(L) Default Mode: Convert to NVARCHAR(L) Optimistic Mode: Convert to NVARCHAR(L) Full Mode: Convert to NVARCHAR(L) |
Generic
| Term | Definition |
|---|---|
| Columns without DEFAULT value in INSERT and REPLACE | If 'Yes', all the statements that reference tables using stored engines other than MyISAM and InnoDb should be marked with warning conversion messages. Default Mode: Add to Column list Optimistic Mode: Add to Column list Full Mode: Add to Column list |
| Division by Zero Conversion Produces | Specifies whether or not to emulate MySQL without ERROR_FOR_DIVISION_BY_ZERO behavior. Default Mode: Error Optimistic Mode: Error Full Mode: NULL |
| IN operator | Specifies how to convert MySQL IN operator. Default Mode: Always convert to IN Optimistic Mode: Always convert to IN Full Mode: Expand if necessary |
| MySQL Function Conversion | Specifies how to convert MySQL standard functions. Default Mode: Optimistic Optimistic Mode: Optimistic Full Mode: Precise |
| Not supported storage engines | If 'Yes', all the statements that reference tables using stored engines other than MyISAM and InnoDb should be marked with warning conversion messages. Default Mode: No Optimistic Mode: No Full Mode: Yes |
| Suppress ROWID auxiliary column generation | If Yes, prohibits creation of ROWD auxiliary column creation on target tables. May affect migration of some structures. Default Mode: No Optimistic Mode: No Full Mode: No |
| TRUNCATE statement conversion | Specifies how to convert TRUNCATE statements. Default Mode: TRUNCATE Optimistic Mode: TRUNCATE Full Mode: TRUNCATE |
Misc
| Term | Definition |
|---|---|
| Default Schema Mapping | Specifies how to map MySQL databases into SQL Server schemas. Default Mode: Database to Database Optimistic Mode: Database to Database Full Mode: Database to Database |
Procedures and Functions
| Term | Definition |
|---|---|
| Default Function conversion | Specifies if functions should be by default be converted to T-SQL functions or to stored procedures. Default Mode: Convert to Function Optimistic Mode: Convert to Function Full Mode: Convert to Function |
| Generate SET XACT_ABORT ON | Specifies whether or not SET XACT_ABORT ON needs to be added to the beginning of the converted procedure or trigger. Default Mode: Yes Optimistic Mode: Yes Full Mode: Yes |
| Generate SET NOCOUNT ON | Specifies whether or not SET NOCOUNT ON needs to be added to the beginning of the converted procedure or trigger. Default Mode: Yes Optimistic Mode: Yes Full Mode: Yes |
Spatial Data Types
| Term | Definition |
|---|---|
| Default bounding box {XMAX|XMIN|YMAX|YMIN} for spatial indexes | Defines default value for {XMAX|XMIN|YMAX|YMIN} parameter of bounding box used in spatial indexes. Default Mode XMAX: 100 XMIN: 0 YMAX: 100 YMIN: 0 Optimistic Mode XMAX: 100 XMIN: 0 YMAX: 100 YMIN: 0 Full Mode XMAX: 100 XMIN: 0 YMAX: 100 YMIN: 0 |
| Default grid density for spatial indexes | Defines default value for LEVEL_1, LEVEL_2, LEVEL_3, and LEVEL_4 of grid density used in spatial indexes. Default Mode LEVEL_1: Default LEVEL_2: Default LEVEL_3: Default LEVEL_4: Default Optimistic Mode LEVEL_1: Default LEVEL_2: Default LEVEL_3: Default LEVEL_4: Default Full Mode LEVEL_1: Default LEVEL_2: Default LEVEL_3: Default LEVEL_4: Default |
Transactions
| Term | Definition |
|---|---|
| Non-transactional tables | Specifies whether or not all references to table that do not support transactions should be marked with warning conversion messages. Default Mode: No Optimistic Mode: No Full Mode: Yes |
| Transaction isolation level | Specifies what transaction isolation level should be used for new transactions. Default Mode: Default Optimistic Mode: Default Full Mode: Repeatable read |
Value Control
| Term | Definition |
|---|---|
| Character to Numeric conversion | Specifies how to handle implicit and explicit conversion from Character data type to numeric data types. Default Mode: Optimistic Optimistic Mode: Optimistic Full Mode: Precise |
| Control UNSIGNED numeric values | Control assigning values to UNSIGNED numeric variables and parameters. Default Mode: No Optimistic Mode: No Full Mode: Yes |
| Control UNSIGNED Subtraction | Modify negative values inserted into table columns of UNSIGNED datatype. Default Mode: Convert 'as-is' Optimistic Mode: Convert 'as-is' Full Mode: Mark With a Warning |
| Conversion to and from Binary data type | Specifies how to handle implicit and explicit conversion from Binary data type. Default Mode: Optimistic Optimistic Mode: Optimistic Full Mode: Precise |
| Conversion to Date/Time data type | Specifies how to handle implicit and explicit conversion to Date/Time data type. Default Mode: Emulate MySQL format Optimistic Mode: Use SQL Server format Full Mode: Emulate MySQL format |
| Numeric Literals With Precision Exceeding 38 | Specifies how to convert numeric literals with precision exceeding 38. Default Mode: Round if Possible Optimistic Mode: Round if Possible Full Mode: Round if Possible |
| Zero-date in NOT NULL columns | Specifies how to handle assignment to NOT NULL columns of Zero-date, Zero-in-date or invalid date/time values. Default Mode: GETDATE() Optimistic Mode: GETDATE() Full Mode: GETDATE() |