Stored Query Maintenance

 

Stored Queries provide the ability to create and store ad hoc tSQL queries that can be executed in a secure manner by an end-user. An administrator can create a query which an end user can run, but the user will not be able to view the query or make any changes to it. A query can also have parameters, such as an Item Number, which the user must provide before running the query.

Navigation: Tools >> Utilities >> System >> Stored Query Maintenance

Query ID: Provide an ID for the Query.

Name: When a user runs a Stored Query, they will use the Execute Stored Query window. The Execute Stored Query window has a drop-down list of Stored Query Names that the user has access to.

Description: Description is also displayed in the Execute Store Query window to help the user identify what the query does.

Execute Only: The user will be able to select and run the query, but it will provide no feedback to the user other than completion status. A query must be marked as Execute Only for it to appear in the Execute Stored Query window.

Show Results: The Execute window will display the results of the query. The Show Results option also enables the Export To Excel button on Execute Stored Query. Otherwise the Excel button is hidden.

Email Results: The Execute window will automatically export the results to a CSV file, create an email to the specified Email address, and attach the file to the email.

Email: The email results are sent to this address.

Execute In Database: Select the database in which the query will be executed. When the Execute Stored Query window is opened, it will only show users queries to which they have been granted security, and which are assigned to the current company database or DYNAMICS database. The “All Companies” option means that the query will be available in all company databases (not that it will execute across all databases at the same time).

 

Parameters

The first step to creating a query is to write the query in Query Analyzer (SQL Management Studio) to ensure the syntax is correct. The Query window in Consulting Toolkit can also be used.

The next step is to create the parameters. When a stored query is executed, the user will be prompted to enter Parameters (if there are any). For example, perhaps the user wants to find all item numbers that begin with a specific user entered string (i.e. all items beginning with ABC). The SQL query would look like this:

Select * from IV00101 where ITEMNMBR like ‘ABC%’

The Stored Query using a parameter would look like this:

Select * from IV00101 where ITEMNMBR like ‘{1}%’

Click the PARAMETERS button to open the Parameters window.

The first parameter you create will have ID {1}, and the second will be {2} and so on. The ID is auto-generated. After creating parameters, add the numbered parameters into the Query in the place where you want to substitute user entered data.

Select * from IV00101 where ITEMNMBR like ‘{1}%’

Delete a parameter by selecting the row, then clicking Edit >> Delete Row.

Query Builder Window:

Enter the tSql query. To ensure correct syntax is used, the query should be created in Query Analyzer and pasted into this window. A simple query is:

Select * from IV00101

Parameters are added by inserting the Parameter ID. For example:

Select * from IV00101 where ITEMNMBR = ‘{1}’

Note that the apostrophes before and after the parameter are included in the query because it is searching for a string. The parameter {1} will be replaced by the user entered data.

When the Execute Stored Query window runs a query with parameters, it replaces only the parameter, such as {1}, with the data entered by the user. Here is another example showing a numeric parameter:

Select * from IV00101 where ITMTRKOP = {1}

This query would retrieve all item numbers where the Item Tracking Option = (the value entered by the user). In this case there are no apostrophes because the user entered data (the Parameter) is a number.

When designing a Stored Query, keep in mind that the parameter will be replaced with the user entered data as-is.

 

Security

Click the Security button on the Stored Query Maintenance window.

Security is set per user per Stored Query. Add users who should have access to the Stored Query. The Execute Stored Query window will only show users the Stored Queries to which they have been given security.

By default “SA” and “DYNSA” have access to all queries.

Remove a user by selecting the row, then clicking Edit >> Delete Row.

 

Import/Export

The GoTo button on Stored Query Maintenance provides options to Import and Export a stored query.

Export: select a Stored Query, then click the GoTo button and select Export. On the Export window, click the Folder icon and select a path to save the Stored Query. Click Process to export the query.

Import: Clear the Stored Query window, then click GoTo and select Import. On the Import window, click the folder icon and select the Stored Query to import. Click Process to import the query.