Using the SQL Manager to View Data

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

Read more: 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.

Query Box

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

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

Other resources:

Table Schema

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

  1. Click Table Schema. The Table Schema pop-up opens.

  2. In the pop-up, use the search bar to find a table name or scroll through the Table Name List.

  3. Click a table name to view the table.

    Table Schema pop-up window

    The view-only results show below the Query Box.

    Example table schema

Client Schema ([client])

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

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.

Alias example

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?

  1. Use your mouse to highlight specific lines.

    Highlighted lines
  2. 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.

Enlarge the Query Box

Saving a SQL Query

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

  1. Enter the SQL Statement in the Query Box.

  2. Click the Save button.

    Save button

    The Save Query pop-up window opens.

    Save Query pop-up window
  3. In the pop-up, type a Query Name.

    Query Name text field
  4. Click Save.

    Save button

    The pop-up closes, and the query saves.

Editing an Existing Saved SQL Query

Important: 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.

  1. Click the View Saved Queries link.

    View Saved Queries link

    The Saved Queries pop-up window opens.

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

    The Query Box will populate with the saved SQL statement.

  3. Make any necessary edits to the query.

  4. Follow the preceding steps to Save the query.

  5. Click the Replace button to confirm.

    Replace button

Deleting an Existing Saved SQL Query

  1. Click View Saved Queries. The Saved Queries pop-up window opens.

  2. In the pop-up, click the Trash icon for the query you want to delete.

    Trash icon

    A Delete Query confirmation pop-up opens.

  3. Click the Delete button to confirm.

    A success status message appears, and the query deletes.

    Success dialog box

Exporting a SQL Query

After running a query, you can export the results:

  1. Click the Export button.

    Export button

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

Was this article helpful?
0 out of 0 found this helpful
Previous Next

Comments

0 comments

Please sign in to leave a comment.