Using the SQL Manager to View Data

Learning Experience Team

April 28, 2020 20:33

Admin: Corporate Admin
Page: Reports > SQL Manager
URL: [CLIENTID].corpadmin.directscale.com/Corporate/Reports/SqlViewer
Permission: SqlManagerAdministrator(), SqlManagerBasic(), or SqlManagerReadOnly()

Suppose you need to view or export data. In that case, you can use the SQL Manager to query and export data directly from a read-only copy of the Database.

The SQL Manager is for viewing data only. If you need to modify database data, use the Data Editor.

Enabling Permissions

This feature requires one of the following permissions to be enabled for your Corporate Admin Role:

Permission

Run

Save

Export

Delete

SqlManagerAdministrator

✅

SqlManagerBasic

✅

❌*

SqlManagerReadOnly

✅

❌

✅

❌

* You can delete your queries but not other users'.

Learn more about permissions in Assigning Employee Corporate Admin Permission Roles.

Running a SQL Query

To run a query, enter your SQL statement in the query box and click Run.

The query results will display under Query Results. The query will return up to 300 results.

Supported Syntax

Besides vanilla SQL, the SQL Manager also supports Microsoft’s Transact-SQL (T-SQL) syntax to perform additional capabilities such as declaring variables.

For T-SQL tips and tricks, see this article by SQLShack.

Other resources:

For a library of queries you can use, see SQL Query Library.
For help with SQL, visit the SQL Tutorial on W3 Schools.

Table Schema

If you need to view all the available tables and the types of data they contain:

Click Table Schema. The Table Schema pop-up opens.
In the pop-up, use the search bar to find a table name or scroll through the Table Name List.
Click a table name to view the table.

The view-only results show below the Query Box.

Client Schema (`[client]`)

Database schemas are logical collections of data objects. The Database has the following schemas:

Client schema ([client]) - Contains all tables created and maintained in the Client Extension
DBO schema ([dbo]) - Contains DirectScale tables

To specify the schema in your query, precede the table name with the schema in brackets [].

Examples:

SELECT * FROM [dbo].[CRM_Distributors]

SELECT * FROM [client].[MyCustomTable]

SQL Type-Ahead

While writing your query statement, if you need help with the syntax, press Ctrl Space on your keyboard to enable SQL Type-Ahead.

SQL Type-Ahead displays a menu of keywords, tables, or column options available to you at that point in the statement.

Press Ctrl Space on your keyboard to enable the SQL Type Ahead feature.

To have a specific table’s fields available, you must give the table an alias.

Example of setting an alias:

SELECT d.BackofficeID FROM CRM_Distributors d

Running Only Selected Lines

Want to run individual lines or only part of a longer query?

Use your mouse to highlight specific lines.
Click Run.

The results will only display the data queries from the highlighted lines, an excellent tool for troubleshooting.

Expanding the Query Box

If you need to change the size of the Query Box, you can click and drag the right corner.

Saving a SQL Query

To save a particular query to come back to later, or make it available to other users:

Enter the SQL Statement in the Query Box.
Click the Save button.

The Save Query pop-up window opens.
In the pop-up, type a Query Name.
Click Save.

The pop-up closes, and the query saves.

Editing an Existing Saved SQL Query

You must save the edits as a new query. You can’t overwrite an existing saved query. You can, however, delete any duplicates to de-clutter your Saved Queries list.

Click the View Saved Queries link.

The Saved Queries pop-up window opens.
In the pop-up, select a Query Name.

The Query Box will populate with the saved SQL statement.
Make any necessary edits to the query.
Follow the preceding steps to Save the query.
Click the Replace button to confirm.

Deleting an Existing Saved SQL Query

Click View Saved Queries. The Saved Queries pop-up window opens.
In the pop-up, click the icon for the query you want to delete.

A Delete Query confirmation pop-up opens.
Click the Delete button to confirm.

A success status message appears, and the query deletes.

Exporting a SQL Query

After running a query, you can export the results:

Click the Export button.

A CSV file downloads. You can open this file in any spreadsheet program (such as Microsoft Excel or Google Sheets).