EQuIS SQL Form

<< Click to Display Table of Contents >>

 >EQuIS Library > Forms > EarthSoft Standard Forms >

EQuIS SQL Form

The EQuIS SQL Form allows you to query the database to find specific data. The form only allows you to select data, not to delete, create or update records. Some users may not have Microsoft SQL Server Management Studio or have the permissions to access the database directly, so this form allows them to query the database using EQuIS.

 

The form allows you to write database queries for viewing data. If you do not have direct access to the database or to Microsoft SQL Server Management Studio, the form allows you to quickly find certain records in the database or to combine records from multiple tables using a join statement. For example, the form supports UNION and EXCEPT operators, along with TOP, ORDER BY and COUNT commands. Note that certain functions such as deleting or updating, are disabled in the form.

 

The SQL Form query output window also contains all the standard report functions such as crosstab reports, xy charts and export options.

 

Additionally, you can publish your SQL Form query to the database and utilize it in EQuIS just as you would any other EQuIS grid report, e.g., as a crosstab in the Enterprise Data Grid or EZView Widget.

 

Note: This form is not available for an Application Level Security login, except "admin", unless set up specifically for such as described below.

 

SQL for EQuIS Query

 

30342-sql_query

 

SQL for EQuIS Results

 

30342-EQuIS_sql_form

 

This form is contained within EarthSoft.Forms.Library.42804.dll and is available on request from support@earthsoft.com. Please include the version of EQuIS this .dll is being requested for.

 

Set Up Instructions for Non-Admin User

 

Note: "VIEWER" permission to ALL facilities in the database is required, and use of the form enables viewing data (query results) which are typically hidden from non-admin ALS users (i.e., certain system tables (ST_*)).

 

The following instructions must be followed so that a non-admin ALS user can use the EQuIS SQL Form:

 

1.Publish the EQuIS SQL Form DLL to the database (i.e., EarthSoft.Forms.Library.42804.dll. (Once published, the DLL does not need to be installed on the computer running Professional).

2.Using SQL Server Management Studio (or other similiar tool), execute the script.
 
30348-create_sql_form_rolegrantviewer
 

Right-click the image above and select Save link as. Once downloaded, unblock the file and change the file extension from .txt to .sql. When executed, this script does the following:

a.Creates a special role named "EQuIS SQL Form" (used for assigning permissions).

b.Grants the appropriate permissions to "EQuIS SQL Form" role.

3.Using EQuIS Enterprise, associate the users (ALS non-admin) that will use the form to the "EQuIS SQL Form" role.

 

For example, assign roles to a non-admin ALS as shown below.

 

SQL_Form_roles

 

If after following the steps above (in order), a user is not able to use the EQuIS SQL Form, it is likely that they do not have "VIEWER" permission to EVERY facility in the database. To find facilities that a user does not have "VIEWER" permission to, execute the script.

 

30348-find_facil_user_no_view_permiss

 

Right-click the image above and select Save link as. Once downloaded, unblock the file and change the file extension from .txt to .sql.

 

4.Make all facilities visible to this non-admin ALS as shown below.

 

SQL_Form_permissions_facilities