View the latest version.Version 8.0

BizTalk360 provides a platform to manage SQL queries within the application. There are a set of pre-loaded queries that come as an out-of-the-box functionality. In addition, BizTalk360 allows users (with the adequate permissions) to manage the existing SQL queries and add/edit/delete their own queries.


Quick Links


Managing The Pre-Loaded Queries

To edit a pre-loaded query, follow the steps as shown below in exact order. For example, let us consider the query "All Disabled Receive Locations [BNBTS2013ENT: BizTalkMgmtDb]". This query will fetch the list of disabled receive locations from the BizTalkMgmtDb. Lets try to modify the query to retrieve the list of all receive locations from the BizTalkMgmtDb.

  1. Log in to the BizTalk360 application
  2. Click 'Custom SQL Queries' in the Menu panel on the left side of the screen
  3. In the Custom SQL Queries screen, 
    1. Select the pre-stored query "All Disabled Receive Locations [BNBTS2013ENT: BizTalkMgmtDb]" from the drop down
    2. The query will execute and the results will be displayed in the Query Results pane
  4. Once the query results are generated, click the >Build Query Expression. This will open the Query Details pane.
  5. Modify the query (in this case remove the last 2 lines; WHERE RL.[Disabled] = -1) as per the requirement
  6. Click Execute Query to generate the new query results for the modified query information
If you enter a wrong query while modifying the information, BizTalk360 will throw an exception error message while executing the query.


Click the sliders to view the process of managing the pre-loaded queries.



Adding A New Custom SQL Query

BizTalk360 allows users to build custom SQL queries that are more appropriate to the organization's business in addition to the pre-loaded queries that are made available out-of-the-box.

Note: You can only create SELECT queries in BizTalk360. Any INSERT or UPDATE queries will be rejected.


Let's take an example of adding a new query that will fetch the Spool Size of the BizTalkMsgBoxDb. This will involve a SELECT statement that will retrieve the value of 'SpoolSize' from the message box database. Follow the steps as shown below to add a new query:

  1. Log in to the BizTalk360 application
  2. Click 'Custom SQL Queries' in the Menu panel on the left side of the screen
  3. In the Custom SQL Queries screen, click the New Query icon. This will pop open the Custom SQL Query Details page.
  4. In the Custom SQL Query Details page, 
    1. Friendly Name - Enter a friendly name for the Custom SQL query that can be easily understood by users Ex. Calculate Spool Size
    2. SQL Instance - Enter the name of the SQL instance (In this case, BNBTS2013ENT)
    3. Database - Enter the name of the message box database (In this case, BizTalkMsgBoxDb)
    4. Enter the SQL Query - Type the query into the space provided.
  5. Click Save to save the query
If you enter a wrong query while adding a new query, BizTalk360 will throw an error message while saving the query.
BizTalk360 allows the users to open up to 4 tabs at a time to execute multiple queries. To open a new tab, click the + button. To close the tabs individually, click the * button next to the tab name. To close all the tabs in one click, click the X button next to the + button.


Click the sliders to view the process of adding a new custom SQL query.


2014-08-26_1957.png


Editing A Custom SQL Query

BizTalk360 allows users to make modifications to the pre-loaded queries and custom queries added by the users. Users can modify the query information according to their requirements. Follow the steps as shown below to edit a query:

  1. Log in to the BizTalk360 application
  2. Click 'Custom SQL Queries' in the Menu panel on the left side of the screen
  3. In the Custom SQL Queries screen, click the Edit Query icon. This will pop open the Custom SQL Query Details page.
  4. In the Custom SQL Query Details page, you can modify the value for the following fields as per the requirement:
    1. Friendly Name - Enter a friendly name for the Custom SQL query that can be easily understood by users Ex. Calculate Spool Size
    2. SQL Instance - Enter the name of the SQL instance (In this case, BNBTS2013ENT)
    3. Database - Enter the name of the message box database (In this case, BizTalkMsgBoxDb)
    4. Enter the SQL Query - Type the query into the space provided.
  5. Click Save to save the query


Click the sliders to view the process of editing a custom SQL query.


2014-08-26_1957.png


Deleting A Custom SQL query

To delete a query, follow the steps as shown below:

  1. Log in to the BizTalk360 application
  2. Click 'Custom SQL Queries' in the Menu panel on the left side of the screen
  3. In the Custom SQL Queries screen, click the Delete Query icon. This will pop open the "Confirm Delete" message box with the message as "Are you sure, you want to delete this query? This query may be used by other users as well". Click Yes to delete the custom SQL query.
  4. The query will no longer be available in the drop down
You cannot delete the pre-loaded queries from the drop down list. Only custom SQL queries entered by users can be deleted. 


Click the sliders to view the process of deleting a query (user created one).


2014-08-26_1957.png