Project Settings (Conversion) (MySQLToSQL)

Article
02/28/2023

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	Specifes 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	Specifes 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	Specifes 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	Specifes 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()