SQL Script

The SQL Script job step type is used to execute batches of SQL statements against a wide variety of database providers including SQL Server, OLEDB, and ODBC data sources.

This job step type extends basic job step functionality. For details common to all job step types click here.

In addition to the common attributes shared by all job steps this job step type has the following attributes:

Connection tab:

Connection Provider: This is the provider used to connect to the database. Valid choices include SQL Server, OLEDB, and ODBC.

•SQL Server: When selected the SQL Server provider will allow for the execution of queries written in the Transact-SQL language.

•Enable Macros: Allows for the use of macro commands in the connection properties.

•SQL Server Name: The name of the SQL Server or SQL Server Named Instance to connect to.

•Windows Authentication: When checked the connection to SQL Server will be made using the Windows security context of the process that is executing the job. When executed manually this is the context of the interactive user. When executed automatically this is the security context of the pasUnity Processor Agent.

•SQL Server Authentication: When checked the connection to SQL Server will be made using SQL Standard authentication which is comprised of a login name and password pair.

•Login Name: The SQL Server login name when using SQL Server authentication.

•Password: The password for the SQL Server login when using SQL Server authentication.

•Remember Password: This should always be checked when using SQL Server authentication or the job will fail.

•Database Name: The SQL Server database to execute the batch in (must exist).

•Enable Macros: When checked macros can be used to supply connection details.

•OLEDB: When selected any OLEDB provider installed on the host with a compatible processor architecture may be used and the native SQL dialect of the provider may be used. Use of the OLEDB provider for ODBC is discouraged in favor of the ODBC native provider. Use of the OLEDB provider for SQL Server is discourage in favor of the SQL Server native provider.

•Connection String: Any valid OLEDB connection string may be provided. Double-click the yellow connection string text box to open the OLEDB Data Link editor UI which will assist you in writing your connection string through the use of a wizard.

•ODBC: When selected any ODBC DSN (Data Source Name) or ODBC connection string can be specified.

•Choose DSN: Clicking this button will allow you to select an existing DSN (Data Source Name) as the connection string.

•ODBC DSN Editor: Clicking this button will open the ODBC Data Source Administrator tool that matches the processor architecture that the application is targeted for.

•Connection String Help: Opens the online help for crafting connection strings.

•Connection String: Any valid ODBC connection string may be provided.

SQL Script tab:

•Command Type: This determines how the Parameters tab behaves and controls how you send and receive data.

•Text: In this default mode you are passing one or more batches of SQL statements to the remote server. You can define Parameters and they will be automatically declared and easily accessed by the remote server and in most server products can even be used to return values after batch execution is complete.

•Stored Procedure: In this mode the SQL Script should contain ONLY the name of a stored procedure. To communicate with the stored procedure use the Parameters tab to specify input macros and output capture parameters.

•Batch Terminator: This is a keyword used to break the SQL Script up into individual batches of statements to be executed in a single operation. The default value is GO and when employed should be on a line of code with no other statements or language.

•Allow Macros: Check if you wish to transform pasUnity macros and parsing expressions before the batch is executed.

•SQL Script: The SQL statements to execute against the provider. Scripts are divided into batches whenever the keyword Batch Terminator is found on a line by itself. Syntax highlighting may not be perfectly accurate as standards and language elements vary greatly by provider. To use the Query Designer to interactively develop your query press F4. If macros are enabled on the Connection tab the designer will attempt to parse and apply them.

Parameters tab: This allows you to define string parameters which can be used to send input values that can be used by the batches and collect output values after the batches have completed.

•Action buttons will appear selectively based on the the data provider and command type.

•Clear Parameters: Clicking this button removes all parameter definitions.

•Auto Generate Parameters: Visible only when the Command Type is set to Stored Procedure. Clicking this button will attempt to auto determine the name, data type, and direction of all parameters on the stored procedure named in the SQL Script. Existing parameters will have their type and direction updated. New parameters will also have their input macro and output parameter name set automatically. Obsolete parameters not referenced by the stored procedure will be removed.

•Reset Parameter Data Types: Resets all defined Database Parameter Type value to the default string type for the selected provider.

•Parameters edit grid is used to view and define parameter definitions. Order of parameters does not matter.

•Database Parameter Name: Specifies the name of the parameter that will be sent to the database provider. Parameter naming conventions vary by product but for the most part they need to start with characters or the @ character and do not allow spaces or special characters.

•Database Parameter Type: Specifies the data type used by the target database. Data types adhere to the following patterns:

•Simple types: name: i.e uniqueidentifier or bit

•String and binary types: name(length): i.e. nvarchar(max) or char(50)

•Decimal types: name(precision,scale): i.e. decimal(18,3) or decimal(30)

•Table types: structured(type): i.e. structured(dbo.YourTableType)

•User-defined types: name(type): i.e. udt(sys.geometry)

•Database Parameter Direction: While dependent on the actual database implementation the acceptable values are as follows:

•ReturnValue: Only usable with stored procedures. Input values are not accepted. Typically return a number that indicates a success or error code. Only a single return parameter can be defined.

•Input: Used to declare a parameter to send a value to a stored procedure or a variable used with batch code.

•InputOutput: Used to declare a parameter to send a value to a stored procedure or a variable used with batch code and allows retrieval of the value after the command completes.

•Output: Used to declare a parameter to receive a value from a stored procedure after the command completes.

•Input Macro: A macro expression that is used to provide an initial value for a parameter or variable and is exposed to the batches. In the case of a structured parameter you can specify the name of a cached lookup to send tabular data. Compatible only with Input or InputOutput parameters.

•Output Parameter Name: This is the name of the pasUnity parameter that you want the batches to populate. Compatible only with Stored Procedure commands and parameters marked as ReturnValue, Input, or InputOutput.

Export tab:

•Export Format: Specifies an export format for the queries in the SQL script that return result sets.

•None: Specifies that the script should execute normally and not store any output to file.

•Delimited: Specifies that the script should store tabular query results to a data file. Any tabular data will be intercepted and written to a file. Tabular data is generated by stored procedures or batches that contain a SELECT statement.

•Destination File Name: The name of the file to save query results to. If multiple results are returned they will be stored separately to multiple files with incrementing base file name parts. For instance, if you specified the file name C:\TEMP\Results.txt the resulting files would take the form C:\TEMP\Results1.txt, C:\TEMP\Results2.txt, etc.

•File Encoding: The encoding to use when saving files to disk. The default encoding is Unicode (also known as UTF-16) which is a double-byte character set and the default character set in SQL Server.

•Export Column Names: When checked the first row of output in the resulting file will be the names of the columns from the source query.

•Row Delimiter: The character string used to terminate a row of data. This is usually a carriage return followed by a newline represented by the string \r\n.

•Column Delimiter: The character string used to separate columns on the same row of data. This is usually a comma or a tab (represented by the string \t).

•Text Qualifier: The character string used to wrap the contents of the output values so that if they contain row or column delimiter characters they will not cause the column to be fragmented or improperly read by other tools. Typically, the single or double quote characters are used at text qualifiers. If the text qualifier character itself is detected inside the column value it will be doubled up for detection by other tools. For example, the value This contains "quotes" would be converted to "This contains ""quotes""" when qualified by a double-quote character.

•Excel: Specifies that all tabular output from the script will be written to an Excel spreadsheet (version 2010 or higher) with each set of tabular results written to a separate worksheet. Any tabular data will be intercepted and written to a file. Tabular data is generated by stored procedures or batches that contain a SELECT statement.

•Destination File Name: The name of the file to save query results to. The file extension should be .XLSX (not .XLS). If multiple results are returned they will be written to individual worksheets within the workbook.

•XML: SQL Server provider only option that specifies that the script should store XML query results to a data file. This is useful for intercepting the results of a query that contains the FOR XML clause. Statements that return data that do not use the FOR XML clause will not be intercepted.

•Include XML Declaration: This will make the assumption that the data being returned from the server was generated with a FOR XML clause that included the ROOT clause to create a well-formed XML document that has a single declaration and single root element with zero, one, or more child elements. If checked and the ROOT predicate is not specified, the query will not be intercepted. Additionally, this result must be the only result in the batch.

Advanced tab:

•Echo Script Code to the Log: Check if you wish to save the SQL script code to the execution log.

•Continue Processing Script If Errors Occur: If checked errors will not halt execution of the subsequent batches in the scripts and errors will not cause the job step to return failure.

•Batch Execution Timeout: The length of time that pasUnity will attempt to execute the current statement batch before aborting. If the timeout period is exceeded and the Continue Processing Script If Errors Occur option is checked, processing will proceed to the next batch in the script; otherwise, the job step will report failure and exit.

After making changes be sure to press the Update button to save your changes.

Send comments on this topic.