About Ispirer Systems

Ispirer Home Page Database Migration Application Conversion Downloads


Sqlways.ini File - Section [MSSQL]

This article describes sqlways.ini file's [MSSQL] section and options it contains.

Option name Description
BIN Specifies the directory where Microsoft SQL Server utilities like BCP and ISQL are located.
TARGET_VERSION Specifies the target version of MSSQL database. If this option is empty the latest MSSQL database version will be used. Note: According to the version number conversion results may differ.
SERVER_NAME Specifies the server name which is used in generated scripts for the BCP and ISQL utilities (Parameter -S of BCP).
DATABASE Specifies the database name which is used in generated scripts for the BCP and ISQL utilities.
USER Specifies the user name which is used in generated scripts for the BCP and ISQL utilities (Parameter -U of BCP). To use this option, set the TRUSTED_CONNECTION option (see below) to “No”.
PWD Specifies the user password which is used in generated scripts for the BCP and ISQL utilities (Parameter -P of BCP). To use this option set the TRUSTED_CONNECTION (see below) option to “No”.
TRUSTED_CONNECTION Specifies that a trusted connection to Microsoft SQL Server is used in generated scripts for the BCP and ISQL utilities (Parameters -T of BCP and -E of ISQL). When “Yes” is chosen in this option, security credentials of the network user are used and a user name (login_id) and a password are not required. Possible values are “Yes”, “No”. The default value is “No”.
MAX_ERRORS Specifies the maximum number of errors that can occur before the BCP utility is canceled. Each row that cannot be copied by BCP is ignored and counted as one error (Parameter -m of BCP). The default value is 10.
CODE_PAGE Specifies a code page of the data in the data file for the BCP utility (Parameter -C of BCP). The following values can be specified for the CODE_PAGE option:
* ACP - ANSI/Microsoft Windows (ISO 1252).
* OEM - Default code page used by the client. This is the default code page used by bcp if -C is not specified.
* RAW - No conversion from one code page to another is taking place.
* <value> - Specific code page number, for example, 437.
int2dec38 Specifies how to convert INT and INTEGER Oracle PL/SQL data types to Microsoft SQL Server.
By default INT and INTEGER Oracle PL/SQL data types are converted to INT data type.
With the option set to “Yes”, INT and INTEGER data types will be converted to DECIMAL(38,0) in SQL Server.
smallint2dec38 Specifies how to convert SMALLINT Oracle PL/SQL data type to Microsoft SQL Server.
By default SMALLINT Oracle PL/SQL data type is converted to SMALLINT data type.
With the option set to “Yes”, SMALLINT will be converted to DECIMAL(38,0) in Microsoft SQL Server.
USE_CONVERT_CHAR_TO_VARCHAR This option controls whether to convert CHAR data type to VARCHAR data type while converting to Microsoft SQL Server. The lower limit of CHAR size, for which the conversion is required, is controlled by CONVERT_CHAR_TO_VARCHAR option. The default is “No”.
CONVERT_CHAR_TO_VARCHAR This option controls the lower limit of CHAR size for which the conversion from CHAR to VARCHAR is required. The optimized default is 20.
ROWID_FORMAT This option controls the way the ROWID from an Oracle database is being converted to Microsoft SQL Server database. Possible values are “physloc” (returns the address of the data in hex format), “lockres” (returns the address of the data in [File:Page:Slot] format). The default is “physlock”.
SET_NOCOUNT_IN_SP If this option is set to “ON”, SET NOCOUNT ON statement will be added to the beginning of stored procedures as well as SET NOCOUNT OFF to the end.
Possible values - “On”, “Off”. Default value - “Off”.
DOUBLE_SIZE_CHARACTERS_DATA_TYPES If this option is set to “Yes”, SQLWays will double the size of the character columns in the converted tables.
Possible values - “Yes”, “No” or Empty. Default value - “No” or Empty.
ORIGINAL_LOGIN_WITHOUT_DOMAIN This option controls the way how Informix function USER is converted to MS SQL Server. If this option is set to “Yes”, SQLWays will generate SWF_ORIGINAL_LOGIN() function that will remove domain name from the result returned by ORIGINAL_LOGIN() function. If this option is empty or set to “No”, ORIGINAL_LOGIN() function will be used.
Possible values - “Yes”, “No” or Empty. Default value - “No” or Empty.
CHECK_DATETIME_DATA Applicable when during the migration from Firebird to MSSQL global data type mapping is used to change conversion of TIMESTAMP data type to DATETIME instead of DATETIME2 and when in FIREBIRD database TIMESTAMP columns contain dates before “January 1, 1753”, for example “May 15, 1555”. There are cases when such dates cannot be inserted into DATETIME columns in MSSQL. So this option can be used to check the dates stored in such columns. And if the tool defines that a certain value may not be inserted in DATETIME column, SQLWays will change it to “January 1, 1753” value. For PostgreSQL to MSSQL direction this option checks the range and also cuts the millisecond part of a date.
Possible values - “Yes”, “No” or Empty. Default value - “No” or Empty.
MAP_CHAR_TO_NCHAR If this option is set to “Yes”, all CHAR columns with length less or equal to 4000 will be converted to NCHAR in MSSQL.
Possible values - “Yes”, “No” or empty. Default value - “No” or empty.
MAP_VARCHAR_TO_NVARCHAR If this option is set to “Yes”, all VARCHAR columns with length less or equal to 4000 will be converted to NVARCHAR in MSSQL.
Possible values - “Yes”, “No” or empty. Default value - “No” or empty.
FN_IDX_TO_IDX_VIEW This option defines the way how Oracle function-based indexes will be converted to MSSQL. If this option is set to “Yes”, function-based indexes will be converted into indexed views in MSSQL.
Possible values - “Yes”, “No” or empty. Default value - “No” or empty.
HINT This option specifies the hint or hints to be used during a bulk import of data into a table in MSSQL Database. All possible hints are supported in our toolkit. More information about the hints supported by MSSQL BCP utility can be found here: MSSQL Server BCP Hints.
Possible values: Hints supported by MSSQL Server.
Default value: Empty.
MS_QUOTED_IDENTIFIER This option defines the way how MSSQL native utilities (sqlcmd.exe and bcp.exe) will treat quoted delimiters and data. If this option is set to “Yes”, identifiers should be delimited with double quotes and literals must be delimited with single quotation marks. When this option is set to “No”, then only brackets [] can be used to delimit identifiers and double quotation marks will be used to delimit character strings.
Possible values - “Yes”, “No” or empty.
Default value - “No” or empty.
PARTITION_ENABLE This option controls whether partition tables conversion is required. When option is set to 'Yes', table partitioning logic will be migrated and additional partition function and partition scheme will be created. Otherwise, partitioning logic will be omitted.
Possible values - “Yes”, “No” or empty.
Default value - “No” or empty.
PARTITION_FILEGROUP The option sets filegroups names for the conversion solution for table partitioning. It can take any value which is the name of filegroup. PRIMARY filegroup is used by default.
Please, take into account that filegroups will not be created automatically during the migration. Please, create required filegroups manually.
Default value - “PRIMARY” or empty.
USE_SOURCE_FILEGROUP_NAMES If the option is set to 'Yes', source filegroups/tablespaces names will be used as filegroups names for table partitioning solution in target MSSQL database. Otherwise, filegroups names will be controlled by PARTITION_FILEGROUP option.
Possible values - “Yes”, “No” or empty.
Default value - “No” or empty.
USE_ACTIVE_DIRECTORY This option switches authentication mechanism to use ACTIVE DIRECTORY authentication. Thus, if this option is set to “Yes”, option -G will be added to the commands in SQLCMD and BCP utilities. The -G switch requires at least sqlcmd version 13.1.
Possible values - “Yes”, “No” or empty.
Default value - “No” or empty.
BATCH_SIZE Specifies the number of rows per batch of data copied. By default, bcp copies n rows in one batch, where n is equal to the batch size. Batch size applies only when you are bulk copying in; it has no effect on bulk copying out. The smallest number bcp accepts for batchsize is 1.
CASE_SENSITIVE_COLLATION This option checks object names case. If this option is set to “Yes”, our tool will check the object names taking into account name case. If two objects have the same names and they are written in the same way, for example 'Tab' and 'Tab', suffix will be added to one of the objects. If there are two objects with the same names, but written in different manner, for example 'Tab' and 'TAB', no suffix will be added, as this option is set to “Yes”.
Possible values - “Yes”, “No” or Empty.
Default value - “No” or Empty.
TABLE_FILEGROUP This option defines FILGROUP for tables.
INDEX_FILEGROUP This option defines FILGROUP for indexes.
FK_DEL_CASCADE_SET_NULL_TO_TRIG This option defines how to convert ON DELETE CASCADE and ON DELETE SET NULL foreign key options from Firebird database to MSSQL. If it is set to Yes, then ON DELETE CASCADE and ON DELETE SET NULL foreign key options will be converted into a trigger that emulates the same behavior as in Firebird.
Possible values - “Yes”, “No” or Empty.
Default value - “No” or Empty.
FK_UPD_CASCADE_SET_NULL_TO_NO_ACT This option defines how to convert ON UPDATE CASCADE and ON UPDATE SET NULL foreign key options from Firebird database to MSSQL. If it is set to “Yes”, then ON UPDATE CASCADE and ON UPDATE SET NULL foreign key options will be converted into ON UPDATE NO ACTION in Microsoft SQL Server.
Possible values - “Yes”, “No” or Empty.
Default value - “No” or Empty.
USE_SCOPE_IDENTITY This option defines how to convert @@identity global variable when migrating from Sybase Adaptive Server Anywhere to Microsoft SQL Server. If it is set to “Yes”, then @@identity will be converted to SCOPE_IDENTITY() in Microsoft SQL Server. If this option is set to “No”, then @@identity global variable will be left as is in MS SQL.
Possible values - “Yes”, “No” or Empty.
Default value - “No” or Empty.
USE_CURSORS_IN_TRIGGERS If it is set to “No”, then cursor usage inside Triggers will not be converted. If this option is set to No or empty, will be used default conversion.
Possible values - “Yes”, “No” or Empty.
Default value - “No” or Empty.
SEL_INTO_TMP_TAB_TO_INSERT This option defines the list of procedures where SELECT INTO TEMP TABLE statement will be converted into separate CREATE TEMP TABLE and INSERT INTO statements. In this options should be specified a path to a file with the names of the objects. This option works only for Informix to Microsoft SQL Server migration direction.
SEQUENCE_TO_IDENTITY This option defines the conversion of a trigger that insert new generator value into a column only. If this option is set to “Yes” and table, generator and a trigger that insert new value of generator into a table column were selected for a conversion, trigger will be commented and IDENTITY option will be added to a column in a table. If this option is set to “No”, default conversion will be used.
Possible values - “Yes”, “No” or Empty.
Default value - “No” or Empty.
EXTENT_TO_SPARSE This option defines the conversion of extent columns from Progress to SQL Server. When this option is set to No or Empty, will be used default conversion when each extent is converted into a separate column in SQL Server and for each Progress extent column is generated a computed column in target. If this option is set to “Yes”, then extents will be converted to sparse columns and additional COLUMN_SET column will be generated. This option can be used when number of columns and extents is greater than 1000.
Possible values - “Yes”, “No” or Empty.
Default value - “No” or Empty.
CONVERT_SP_TO_SP The option is responsible for ensuring that objects that can be converted to functions are still converted to procedures. The value of the option specifies the path to the file in which objects names that have to be converted as procedures are specified as an XML structure, e.g. CONVERT_SP_TO_SP=C:\\Users\\Documents\\Ispirer\\pr.xml
where the content of the pr.xml file is:
<Procedures>
<Procedure Name=“sproc1” />
<Procedure Name=“sproc2” />
</Procedures>
CONVERT_SP_WITH_OUTPUT_PARAMS Option contains the path to a file that contains a list of procedures that will be converted with the following changes:
1) If a stored procedure does not use RETURN WITH RESUME, the converted SQL Server SP will have a set of output parameters created with default of NULL. Additionally, a new parameter called @SWV_Output_Set will be created with default value of 1. If @SWV_Output_Set = 1, then the stored procedure would return the data in a select statement. If not, the output parameters would communicate the result back to the caller.
2) If a stored procedure uses RETURN WITH RESUME, then the caller stored procedure will continue to create a temp table to transport the result back to the caller. Additionally, a new parameter called @SWV_Output_Set will be created with default value of 1. If @SWV_Output_Set = 1, then the stored procedure would return the data in a select statement without inserting into a temp table first.
Default value - Empty.
Usage example.
PROCEDURE_WITH_OUTPUT_PARAMS Option defines the way procedures will be converted for MSSQL. If the option set to “Yes”, then the procedure will have a set of output parameters created with default NULL values. If the option set to “No”, the converted procedure will use the temporary table instead of output parameters in order to insert result set into it and then select them from that table.
Possible values - “Yes”, “No”, Empty.
Default value - “Yes”. Usage example.

Example:

[MSSQL]
USE_CONVERT_CHAR_TO_VARCHAR=Yes
CONVERT_CHAR_TO_VARCHAR=10