Query Browser utility

Do the following to run the Query Browser:

1. Back up the database so that it can be restored. You cannot undo permanent deletions.

Warning!
If you copy QueryBrowser.exe to a new system, you must reconfigure it:

1. Copy the C:\Program Files (x86)\ASI\iMIS\Net\bin\QueryBrowser.exe.config file.
2. Edit the connection string in the QueryBrowser.exe.config file to point to the correct database on the new system.
2. Run Query Browser:
   1. Go to the ...\net\bin folder.
   2. Launch QueryBrowser.exe.
   3. Provide the required information for the Connect to Database window.
   Click to view image

   

   You must enter the User Password before you can select the Database Name from the drop-down.

3. Click OK.

Note: Query Browser can take some time to open, because all queries in the database must be loaded.

4. Verify the [server].[database] that is displayed in the title bar is the database to which you want to be connected.
5. After Query Browser has loaded all queries, navigate using the tree control.
6. Click any query to view the panel.
Click to view image



From this main window, you can navigate through available queries, or search for queries:

• Click Find All to search for a list of all broken queries. When you click Find All, a Bad Queries window is displayed, listing all bad queries.
• Double-click any query to navigate to that query in the main window.
Click to view image



You can also do the following from the main window:

• Click Delete to permanently delete a query.

Warning!
There is no undo function. If you delete a query that iMIS requires, you might break the iMIS installation.

• Click Search Queries to open the Search/Replace Queries window. You can then search for all queries that use given sources. Double-clicking a query in the results list locates it in the main tree view, with the details displayed.
• Click Find Next to search for bad queries from the currently selected tree node. The search will start from the top of the Available Queries navigation tree if no node is selected.
• Click Find All to scan the entire set of queries to locate any problem queries, and then display the results in a list. Double-clicking a result locates the query in the main tree view.
• Click Refresh List to reload all the queries and scan the queries again. This is useful if you made changes to the Document system, such as fixing a query, restoring a missing DLL, and so forth.

You can search for query names, paths, and up to three sources joined with an and operation. Click Search Queries to open the Search/Replace Queries window.

The Query Name tab allows you to specify a query or path in which to search. In the Search for Name field, the value entered will match any query name, based on the specified Name Options. In the Search for Folder in Path field, the value entered will match any folder based on the Path Options. For example, create a search using Contains as the name and path option:

1. Set Name Options to Contains.
2. Set Path Options to Contains.
3. Enter Default in the Search for Name field.
4. Enter Com in the Search for Folder in Path field.
5. Click Search.

The search returns the query $/Common/Queries/PaymentMethodSet/Default, as the query name contains Default. The search will not return $/Content/Queries/PaymentMethods/Default, as the path does not contain Com in the pathname. If you enter text in both fields, the query must pass both filters. If you leave both fields blank, any query will match.

Note: In order to perform a search with both of these fields empty, you must have some value entered on one of the tabs. If there is no value entered on any tab, the Search button is disabled.

The Query Sources tab lets you specify up to three business objects or table names, and up to three properties or column names. The values are joined with an and operation, so if you use the Exact Match search option, and enter CsContactBasic in the Business Object Name #1 field, and enter CsDonations in the Business Object Name #2 field, the search will only find queries that use both of those business objects as sources. There is no way to perform an or search. The more information you provide, the more narrow the search parameters become.

Note: This tab can be used in addition to information on the Query Name tab. If you want to find a query named Default that uses the CsContactBasic business object, then you could have values on both tabs.

The Search Based On option tells the search utility whether the values are business objects or table names, and properties or column names. The search field names change when you specify a different Search Based On option:

• The BO Properties option will search based on the Business Object Property names as defined in Business Object Designer.
• The BO Captions option will search based on the label provided in the UI. This option is useful if you do not know the BO Property name, and are using what is visible in the UI.
• The Table Schema option will search based on the underlying SQL Table and Column names in the database.

The Search Options apply to all fields on this tab. You cannot perform an Exact Match on a table name or business object along with a Starts With search on the column name or property.

All controls on the Replace tab are disabled until you select one of the result queries in the Search Results box.

When a single query is selected, the Find text boxes are enabled. When a value is entered in one of those text boxes, the corresponding Replace text boxes are enabled. This allows you to replace specific sources and properties with other sources and properties.

This is used to repair a query that is broken because a business object or property was renamed. If you specify only a Business Object Name, the utility will replace all instances of that Business Object Name (regardless of property) in the query with the new one. If you specify only a Business Object Property, the utility will replace all matching properties (regardless of business object) with the new one. If both are specified, then only that specific property on that specific business object is replaced.

The Replace functionality should be used with care. You can experience problems if you replace a critical item with an item that does not exist, for example, if you misspell the replacement Business Object Name or Business Object Property. It is recommended that you have a good backup before performing this kind of activity on queries.  This functionality is useful for repairing queries that cannot be viewed in IQA because they're broken, and should only be used for this purpose.  If a query is not broken, it's always better to edit or update the query using IQA.

• Click Clear Page to clear the current tab, leaving data in the other tabs.
• Click Clear All to clear the data in all the tabs, and clear out the results pane as well.
• Click Search to search all queries using the specified filters in the Query Name and Query Sources tabs.
• Double-click on any query in the Search Results list to open that query in the main Query Browser window.

The following SQL can be used to find which iMIS tables are used by your existing business objects. If you are looking to create a new business object or IQA query that references a specific iMIS table, you can first use the following SQL to see which business objects are referencing the table you are searching for. Then, you can use Query Browser to determine which queries are using the given business object.

SELECT DocumentMain.DocumentName, DocumentMain.DocumentTypeCode, DocumentMain.DocumentStatusCode
            , DocumentStatusRef.DocumentStatusDesc
            , [Path] = [dbo].[asi_DocumentPathFromHierarchyKey](Hierarchy.HierarchyKey)
            , o.[name] AS ObjectName, s.[definition] As ObjectDefinition
 
FROM Hierarchy
            INNER JOIN HierarchyRoot ON Hierarchy.RootHierarchyKey = HierarchyRoot.RootHierarchyKey
            INNER JOIN DocumentMain ON Hierarchy.UniformKey = DocumentMain.DocumentVersionKey
            INNER JOIN DocumentStatusRef ON DocumentMain.DocumentStatusCode = DocumentStatusRef.DocumentStatusCode
            INNER JOIN sys.objects o ON 'vBo' + DocumentMain.DocumentName = o.[name]
            INNER JOIN sys.sql_modules s ON o.[object_id] = s.[object_id]
 
WHERE DocumentMain.DocumentStatusCode = 40
AND         DocumentMain.DocumentTypeCode = 'BOD'
AND         s.[definition] LIKE '%Name_Fin%' -- Table name you are looking for
 
Order by DocumentMain.DocumentName

The following query lists all business objects and the schema (tables or views) they reference, in order by the referenced schema:

SELECT d.DocumentName AS [BOName], o.[name] as [BOView],
       CASE WHEN CHARINDEX(' WHERE ', m.[definition]) = 0 
           THEN SUBSTRING(m.[definition], CHARINDEX(' FROM ', m.[definition]) + 6, LEN(m.[definition]) - 5)
           ELSE SUBSTRING(m.[definition], CHARINDEX(' FROM ', m.[definition]) + 6,
                          CHARINDEX(' WHERE ', m.[definition]) - CHARINDEX(' FROM ', m.[definition]) - 5)
       END AS [ReferencedSchema]
  FROM DocumentMain d
       INNER JOIN sys.objects o ON 'vBo' + d.DocumentName = o.[name]
       INNER JOIN sys.sql_modules m ON o.[object_id] = m.[object_id]
 WHERE d.DocumentTypeCode = 'BOD' and d.DocumentStatusCode = 40
 ORDER BY [ReferencedSchema]