SQL Output Format Parameters
The SQL output format supports the following parameters:
| server | ||
| Values: | server name | |
| Default: | . | |
| Description: | Name of the database server. | |
| Details: | Setting a value for the "oConnString" parameter causes this parameter to be ignored. | |
| Example: | -server:SQLREPORTS | |
| database | ||
| Values: | database name | |
| Default: | not specified | |
| Description: | Name of the target database. | |
| Details: | Setting a value for the "oConnString" parameter causes this parameter to be ignored. | |
| Example: | -database:LogParserLogs | |
| driver | ||
| Values: | ODBC driver name | |
| Default: | SQL Server | |
| Description: | Name of the ODBC driver to use. | |
| Details: | Setting a value for the "oConnString" parameter causes this parameter to be ignored. | |
| Example: | -driver:"Microsoft Access Driver (*.mdb)" | |
| dsn | ||
| Values: | DSN name | |
| Default: | not specified | |
| Description: | Name of the DSN to use. | |
| Details: | This parameter can be used to specify a
Data Source Name that contains information about the connection to the target
database. Setting a value for the "oConnString" parameter causes this parameter to be ignored. |
|
| Example: | -dsn:"My DSN" | |
| username | ||
| Values: | SQL username | |
| Default: | not specified | |
| Description: | Database username. | |
| Details: | When this parameter is not specified, the SQL output
format uses the current user's credentials through Windows Integrated
Authentication. Setting a value for the "oConnString" parameter causes this parameter to be ignored.
|
|
| Example: | -username:MyDBUser | |
| password | ||
| Values: | SQL password | |
| Default: | not specified | |
| Description: | Database user password. | |
| Details: | Setting a value for the "oConnString" parameter
causes this parameter to be ignored.
|
|
| Example: | -password:MyPassword | |
| oConnString | ||
| Values: | connection string | |
| Default: | not specified | |
| Description: | ODBC connection string containing the parameters for the connection to the database. | |
| Details: | Setting a value for this parameter causes the SQL output format to ignore any value set for
the "server", "database", "driver", "dsn", "username",
and "password" parameters. The SQL output format does not enforce any syntax on the connection string. The value specified for this parameter is handed directly to the ODBC subsystem when initiating the connection to the database.
|
|
| Example: | -oConnString:"Driver={SQL Server};Server=MyServer;db=pubs;uid=sa;pwd=MyPassword" | |
| createTable | ||
| Values: | ON | OFF | |
| Default: | OFF | |
| Description: | Create a new table when the table specified in the into-entity does not exist. | |
| Details: | When this parameter is set to "ON" and the target table does not already exist in
the specified database, the SQL
output format creates a table with as many columns as the number of fields in the
SELECT clause of the query. In this case, the SQL type of
each column is determined by the data type of the
corresponding output record field, as described in
Column Type Mappings. When this parameter is set to "OFF" and the target table does not already exist in the specified database, the SQL output format generates an error, causing the currently executing query to abort. |
|
| Example: | -createTable:ON | |
| clearTable | ||
| Values: | ON | OFF | |
| Default: | OFF | |
| Description: | Clear existing table before inserting new rows. | |
| Details: | Setting this parameter to "ON" causes the SQL output format to delete existing rows in the target table before inserting the query output records. | |
| Example: | -clearTable:ON | |
| fixColNames | ||
| Values: | ON | OFF | |
| Default: | ON | |
| Description: | Automatically remove invalid characters from column names when creating the target table. | |
| Details: | When the "createTable" parameter is set to "ON" and the target table does not already exist in the specified database, the SQL output format creates the table naming its columns with the names of the query output record fields. When this parameter is set to "ON", the SQL output format processes the field names and removes or substitutes those characters that are considered illegal by most databases, including space characters, parenthesys characters, and dash (-) characters. | |
| Example: | -fixColNames:OFF | |
| maxStrFieldLen | ||
| Values: | number of characters | |
| Default: | 255 | |
| Description: | Maximum number of characters declared for string columns when creating a table. | |
| Details: | When the "createTable" parameter is set to "ON" and the target table does not already exist in the specified database, the SQL output format creates the table determining the SQL type of each column from the data type of the corresponding output record field, as described in Column Type Mappings. Columns corresponding to output record fields of the STRING data type are declared as SQL strings having a maximum length equal to the value specified for this parameter. | |
| Example: | -maxStrFieldLen:511 | |
| transactionRowCount | ||
| Values: | number of rows | |
| Default: | 0 | |
| Description: | Number of rows enclosed in a SQL transaction. | |
| Details: | When this parameter is set to "0", the SQL output
format works in "auto commit" mode, where each single output record uploaded to
the target table is automatically committed. When this parameter is set to "-1", the SQL output format initiates a SQL transaction when uploading the first output record, and commits or rollbacks the transaction after uploading the last record or when an error causes the query execution to abort. Setting this parameter to any other value causes the SQL output format to create multiple SQL transactions, each containing a number of records equal to the specified value. |
|
| Example: | -transactionRowCount:200 | |
| ignoreMinWarns | ||
| Values: | ON | OFF | |
| Default: | ON | |
| Description: | Ignore minor warnings. | |
| Details: | When this parameter is set to "ON", the SQL output
format ignores minor warnings that might occur while uploading records to the target table,
including data truncation warnings and invalid escape character errors. When this parameter is set to "OFF", all minor warnings are reported as warnings when the query execution is complete. |
|
| Example: | -ignoreMinWarns:OFF | |
| ignoreIdCols | ||
| Values: | ON | OFF | |
| Default: | OFF | |
| Description: | Ignore "identity" columns in the target table. | |
| Details: | When this parameter is set to "OFF" and the target
table specified in the into-entity already exists,
the SQL output format expects a 1-to-1 match between the columns in the target table
and the fields in the query output records, regardless of whether or not any column
in the target table is an "identity" column. In this case, the values of the output
record fields will be uploaded to all the columns in the table, including eventual
"identity" columns. When this parameter is set to "ON" and the target table specified in the into-entity already exists, the SQL output format ignores "identity" columns in the target table, checking for a 1-to-1 match only between the non-identity columns and the fields in the query output records, and uploading output record field values to non-identity columns only. |
|
| Example: | -ignoreIdCols:ON | |
Note: For security reasons, values specified for this parameter are not
persisted when using the Log Parser command-line