SQL Configuration Assistant
What is the Object Browser?
The Configuration Assistant, or Object Browser is a dialogue that can be used throughout the configuration of a SQL DataSource to use object names or snippets of codes from the database directly instead of typing them manually and risking errors.
Where do I find it?
The Object Browser can be accessed from 3 areas during the configuration of a SQL or Azure DataSource:
- When configuring the read query, to assist with schema, tables, views stored procedures and columns names
- When configuring the write access, to assist with the tables, views, keys and columns names
- When configuring the actions to get predefined SQL statement for INSERT and DELETE actions
Assistant for the Read Query
The Assistant for the query contains a tree with all SQL objects from the database:
- The first level contains groups of objects by type
- The second level contains the objects themselves (tables, views and stored procedure)
- Tables and views have a third level with the column names
TIPS |
|
The tree can be filtered to only show objects from a given schema/owner or using the filter to perform a full text search. When using the filter, only the nodes with a matching schema, table, view, procedure or column name will be shown. |
To use any object in the query, put the cursor in the query where to insert the object name and click Use Selected. You can also select text in the query to replace it with the inserted text.
If there are multiple nodes checked in the tree when you click the Use Selected button, they will be inserted consecutively separated by a coma.
From the example above the inserted text will be:
[dbo].[Milestones].[ActualDate], [dbo].[Milestones].[LotNumber], [dbo].[Milestones].[Milestone], [dbo].[Milestones].[PlannedDate]
You can chose to use short names or fully qualified names to avoid the risk of duplication, and to enclose names in square brackets to support special characters.
With both options deactivated the inserted text above will be:
ActualDate, LotNumber, Milestone, PlannedDate
Note that any column name that contains special characters such as spaces or dash, must be enclosed in brackets, as per T-SQL specifications.
Assistant for the Writing Permissions
The Assistant for the writing permissions contains a grid with the object Type, Name, Column Name, Table Key Name(s) and current user permissions.
To add a column to the list of columns that the user can edit from the DataView, double click the corresponding row. Alternatively, select multiple rows and click Use Selected.
This will automatically add the column to the list of editable columns with the corresponding parent Table and and ID Column(s). Note that when a table has a composite key, all columns of the key are showing coma delimited.
TIPS |
|
The grid can be filtered to only show columns from a given schema/owner or using the filter to perform a full text search. When using the filter, only the columns with a matching schema, table, view or column name will be shown. |
Assistant for the Standard Statements
The Assistant for the custom Actions contains a list of table and view names.
Select the type of command to create from the Command drop down, either INSERT (action type create) or DELETE (action type delete).
Select the table or view to create the query for and check the result in the Query space.
Click Use to use the statement as the Action's query.
When you create a DELETE statement, the configuration assistant tries to use the table primary key to limit the scope using a WHERE close.
If the table has no key it will replace the WHERE close with a warning. Make sure you correct the statement once it is used in the command.
warning |
|
Be careful with delete statement, failing to provide the appropriate WHERE close can result in the loss of data |