Task Database - SQL

The SQL Task can execute a SQL command or a stored procedure against any database through ODBC, OLEDB and support for direct connections to MSSQL, Oracle and MySQL.

Select type of query; Text or Stored Procedure, only the active tab is saved.

SQL tab

Connection To use the SQL Task you need to create a Connection first. You do that in the Connections dialog.

Credential

If you want to Execute with a trusted connection in MSSQL you need to use a Credential in combination with the Connection for trusted connection. The Credential must be a valid Windows user on the local server (if database is local) or a valid user on remote server (if server is not local).

Command Timeout

Timeout in seconds before the query should exit.

Text

Enter your SQL text query.

Split and run multiple queries with semicolon as separator

When checked, the SQL text is split on semicolons and each query is executed individually in sequence.

SQL > Stored Procedure sub tab

Name of stored procedure

The name of the stored procedure to be executed. Click the Refresh icon to populate the drop-down list of stored procedures.

Parameters

The grid shows all defined parameters. Click the Add button to open the parameter window. Click other buttons to update the list of parameters.

Enter parameter values for Name, Value, Data type and Parameter direction.

Use null value

If checked, the NULL value will override the Value.

Validate value

If checked, VisualCron will validate the value with the value type. Leave not checked if you are using a Variable as value and the Variable is not yet set.

Date and DateTime parameter formats

When a parameter data type is a date or date/time type, VisualCron parses the Value text before sending it to the provider. Leading and trailing whitespace is ignored. Parsing uses this order:

1. Exact match against the primary format for that provider and data type (see table below)
2. Exact match against a fixed, year-first format list (same on every Server culture): yyyyMMddHHmmss, yyyyMMdd, yyyy-MM-dd HH:mm:ss, yyyy-MM-ddTHH:mm:ss.fff, yyyy-MM-ddTHH:mm:ss, yyyy-MM-dd
3. Exact match against date/time patterns from the Server's current culture
4. A general date parse using the Server's current culture
5. If nothing matches, the value is rejected as invalid

Ambiguous regional values (for example 03/04/2026) follow the Server culture's day/month order. Culture-invariant pattern matching is not used for that step, so day and month are not silently swapped.

Provider / data type	Primary format	Example values
OLE DB Date	yyyyMMddHHmmss	20260630143000, 20260630, 2026-06-30, 06/30/2026
OLE DB DBDate	yyyyMMdd	20260630
OLE DB DBTime	HHmmss	143000
ODBC Date	yyyyMMdd	20260630
ODBC DateTime	yyyyMMddHHmmss	20260630143000
Native MSSQL Date	yyyyMMdd	20260630
Native MSSQL DateTime / DateTime2 / SmallDateTime	yyyyMMddHHmmss	20260630143000
Other native providers (Oracle, MySQL, PostgreSQL, etc.) for date/time types	Typically yyyyMMdd or yyyyMMddHHmmss	Same pattern as above

Prefer the primary format or a year-first value from the fixed list when you need the same result on every Server culture. Culture-based forms (step 3–4) depend on the Server's regional settings.

Test your SQL Task before closing the VisualCron client. Errors will be reported in the log window.

SQL > Job sub tab

The Job definitions are only valid for MSSQL.

Use this option if you want to execute a remote Package of type SSIS/DTS. You need to create a Job of your package first.

Name of Job

Specify the Job name if you want to execute a Job in the database.

Job steps

A read-only grid displaying the steps of the selected SQL job. Columns show the step number, name, database, and query. Click Refresh steps to reload the steps from the server.

Output tab

The output tab controls the format of the date output from the SQL Task.

Output to standard output

When selected, query results are written to the Task's standard output.

Output to file

When selected, query results are written to a file. Enables the File output settings section below.

Include field names

When checked, column headers are included in the output.

Field separator

Controls what character separates each field in the output.

Text qualifier

The character surrounding each field value. Default is none.

Line break

Controls what type of line break is used between rows.

Indent XML

When checked, XML output is formatted with indentation for readability.

Add info messages to output

When checked, internal SQL database engine debug and warning messages are added to the Task output.

File output settings

These settings are enabled when Output to file is selected.

Credential

Optional credential to use when writing to the output file.

File path

The full path to the output file.

Append to file

When checked, output is appended to the file rather than overwriting it.

Include BOM (if UTF8)

When checked, a byte order mark is included at the start of the file when using UTF-8 encoding.

Write buffer size

The size of the write buffer in bytes used when writing the output file.

Note

• use appropriate prefix on variable, normally @variablename
• take advantage of predefined connection strings
• when using ODBC, remember to add your parameters in the order you want to use them
• if you have DNS connections - take advantage of them
• test SQL Tasks and look for error message in the log window
• if you are running PL-SQL statement on Oracle write them like this: BEGIN FM.MQ.ADD_MSG_DIRECT('FORM','LV_SENDER','LV_POSN_UPLOAD',213,'ORG_ID'); END;
• to get Output parameters please use the result Variables below:

Troubleshooting

** ODBC->ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid parameter 1 (''): Data type 0x23 is a deprecated large object, or LOB, but is marked as output parameter. Deprecated types are not supported as output parameters. Use current large object types instead.**

• Try changing data type from Text to VarChar or NVarChar.
• Also, make sure you are using the exact length of the parameter compared to the object in MSSQL.

The Size property has an invalid size of 0.

Try setting the same exact size as the parameter.

ORA03113

Try upgrading to the latest version of VisualCron.