SQL Output Format Parameters

Log Parser

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.

Note: For security reasons, values specified for this parameter are not persisted when using the Log Parser command-line Defaults Override Mode.

  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.

Note: For security reasons, values specified for this parameter are not persisted when using the Log Parser command-line Defaults Override Mode.

  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.

Note: For security reasons, values specified for this parameter that contain a username and/or a password are not persisted when using the Log Parser command-line Defaults Override Mode.

  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
 


© 2004 Microsoft Corporation. All rights reserved.