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()

In order to utilize the SQL Manager effectively, you must possess a basic understanding of SQL (Structured Query Language). To learn more about SQL, visit the SQL Tutorial on W3 Schools.

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

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

Enabling Permissions

The SQL Manager 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.

Supported Syntax

In addition to 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.
For a library of queries you can use, see SQL Query Library.

Enter a New SQL Query

The Enter Query tab allows you to run and save a new 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.

Table Schema

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

Select Table Schema. The Table Schema pop-up window will appear.
Utilize the search bar to locate a table name or scroll through the Table Name List.
Select a table name to view the table's schema.
The view-only search results will now display 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.

To see a table’s fields in the type-ahead, you must first 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-hand corner.

Saved Queries

The Saved Queries tab shows a list of all the queries that have been saved. The list includes the Saved Query Name, Created By (the creator who first created the query), and the date Last Modified columns.

Running a Saved Query

To run a saved query, click on the query name shown within the Saved Query Name list.

You will now be directed to the Enter Query tab. The saved query will automatically populate within the query box.

From here, you can adjust the query as needed. Click the Run button to see the results below.

Saving a Query

To save a query:

Enter a query in the Enter Query Box.
Click the Save button.
The Save Query pop-up window will appear.

Enter a new Query Name.

Warning: If you are editing a saved query, the Query Name field will be populated with the current query name. If you save a query with the name of an existing query, it will replace the previous query with the new query.

Click Save.

Deleting a Saved Query

To delete a saved query, go to the Saved Queries tab and click on the button next to the saved query you want to delete.

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).