The SQL notification lets you execute a SQL or stored procedure and connect to any database using OLEDB or ODBC. You can use variables in the SQL text field or in the "values" of a parameter in a stored procedure.
Credential and trusted connections
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).
Connection
The information about database, connection string and provider type are stored in Connections. Create a SQL Connection and select it in the Notification.
Command timeout
Timeout in seconds before the query should exit.
SQL text query or Stored Procedure?
Choose tab of your choice. Only the active tab is saved.
SQL text query
Enter your SQL in the text window of the SQL text tab.
Stored Procedure
Choose the tab Stored procedure. Enter the name of the stored procedure to execute. The grid shows all parameters. Click the Add button to add a new parameter. When clicking on ad the Add parameter window will open. Choose parameter name, value, data type and direction.
Error reporting
Test your SQL before closing the VisualCron client. Errors will be reported to the log window if they occur.
Notes:
| • | 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 |
| • | If your SQL does not execute correctly, look for error message in the log |